Nested Queries
subqueries
1.select ename
From emp where sal=
(select max(sal) from emp);
2.SELECT ename
FROM EMP
WHERE sal >
( SELECT sal
FROM emp
WHERE
empno=7566);
3. SELECT ename, sal, deptno
FROM EMP
WHERE sal IN
( SELECT MIN(sal)
FROM emp
GROUP BY
deptno );
4. SELECT empno, ename, job
FROM emp
WHERE sal > ANY
( SELECT AVG(sal)
FROM emp
GROUP BY
deptno ) ;
5. SELECT empno, ename, job
FROM emp
WHERE sal > ALL
( SELECT AVG(sal)
FROM emp
GROUP BY
deptno ) ;
Corelated
queries;
1.List the emps who are working
as Managers using co-related sub-query.
A)
select * from emp
where empno in (select mgr from emp);
display
the name of the emploess whose salary is gretham of their managers
2.SQL>
select e1.ename
from
emp e1
where
e1.sal>
(select e2.sal from emp e2 where
e2.empno=e1.mgr);
ENAME
----------
SCOTT
FORD
5) Count
the number of different sailors names
6) find
the names of sailors who have not reserved a red boat
7) find
the names of sailors who have not reserved boat 103
8) find
the names of sailors who have reserved at least one boat
9)
Compute
increments for the ratings of persons who have sailed two different
boats on the same day.
10) Find
the names of sailors who have reserved a red or a green boat
11) find
the all sids of sailors who have rating 10 or have reserved boat 104..
12) Find
the number of reservations for each red boat.
13) Find
the minimum age of the sailor.
14) Find
the sum of the rating of sailors
15) find the id and names of sailors who have reserved id=22 or
age<25.
Full join
SQL>
select s.sid,s.sname,o.orderdate
from
suppliers s
full join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
44 sony
22 hewlett packard
13-DEC-12
type of join operation to
perform
Display
names of the employees and their loc.(cartisian product)
Display
names of the employees along their manager names.(self join)
Display
names of the employees who are working
in NEW YORK.(equi join)
Display
names of the employee,salary of each and grade of each .(non equi join)
SQL> select *
from student;
no rows selected
SQL> rollback;
Rollback
complete.
SQL> select *
from student;
ROLLNO NAME AGGR ADDR
----------
-------------------- ---------- ----------
1 akshay 98.5
2 shruthi 97.4
3 venkat 87.6
SQL> drop
table student;
Table dropped.
SQL> create table student(rollno number,name
varchar2(20));
Table created.
SQL> insert
into student values(10,'askhay');
1 row created.
SQL> insert
into student values(20,'ravi');
1 row created.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 askhay
20 ravi
SQL> commit;
Commit complete.
SQL> savepoint
p;
Savepoint
created.
SQL> update
student set name='madhu' where rollno=1;
0 rows updated.
SQL> update
student set name='madhu' where rollno=10;
1 row updated.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 madhu
20 ravi
SQL> rollback
to p;
Rollback
complete.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 askhay
20 ravi