1.List the emps who are senior to King.
select * from emp
where hiredate < ( select hiredate from emp where ename = ‘KING’);
2.List the details of the department where maximum number of emps
are working.
select * from dept where deptno in
(select deptno from
emp group by deptno
having
count(*) in
(select
max(count(*)) from emp group by deptno) ); (OR)
select d.deptno,d.dname,d.loc,count(*) from emp e ,dept d
where
e.deptno = d.deptno group by d.deptno,d.dname,d..loc
having count(*) = (select max(count(*) )
from emp group by deptno);
3.List the names of the emps who are getting the highest sal dept wise.
select
e.ename,e.deptno from emp e where e.sal in
(select max(sal) from emp group
by deptno) ;
4.List the emps whose sal is equal to the average of max and
minimum
A)
select * from emp where sal =(select (max(sal)+min(sal))/2 from emp);
5.List the managers whose sal is more than his employess avg
salary.
select * from emp m where m.empno in (select mgr from emp)
and m.sal > (select avg(e.sal) from
emp e wheree.mgr = m.empno );
6.List the emp whose sal<his manager but more than any other
manager.
a)
select distinct W.empno,W.ename,W.sal
from
(select w.empno,w.ename,w.sal from emp w,emp m where
w.mgr
= m.empno and w.sal<m.sal) W,
(select
* from emp where empno in (select mgr from emp)) A
where
W.sal > A.sal; (OR)
B)
select * from emp w,emp m where w.mgr = m.empno and w.sal < m.sal
and
w.sal > any (select sal from emp where empno in (select mgr from emp));
7.Find out emps whose salaries greater
than salaries of their managers.
A) select * from emp
w,emp m where w.mgr = m.empno and w.sal> m.sal; (OR)
B) select * from emp
e ,(select * from emp where empno in (select mgr from emp)) a
where e.sal >a.sal
and e.mgr = a.empno
8.List THE Name of dept where highest
no.of emps are working.
A) select dname from
dept where deptno in
(select
deptno from emp group by deptno
having
count(*) in
(select
max(count(*)) from emp group by deptno) );
9.List the emps who joined in the
company on the same date.
A) select * from emp
e where hiredate in
(select hiredate from
emp where e.empno <> empno);
10.List the Managers name who is having
max no.of emps working under him.
)select m.ename,count(*) from emp w,emp m
where
w.mgr = m.empno
group
by m.ename
having
count(*) = (select max(count(*)) from emp group by mgr);
(OR)
B)
select * from emp where empno = (select mgr from emp group by mgr having
count(*) = (select max(count(*)) from emp group by mgr)) ;
11.Print the details of all the emps who
are sub-ordinates to Blake.
A) select * from emp
where mgr in (select empno from emp where ename = 'BLAKE');
List the emps who are working as Managers
using co-related sub-query.
A) select * from emp
where empno in (select mgr from emp);
20 ravi
SQL> rollback
to p;
Rollback
complete.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 askhay
20 ravi