Tough SQL Queries - Part 3

61.

List of emps of emp1 who are not found in emp2.
62.
Find the highest sal of EMP table.
A) select max(sal) from emp;
63.
Find details of highest paid employee.
A)
select * from emp where sal in (select max(sal) from emp);
64.
Find the highest paid employee of sales department.
A) select * from emp where sal in (select max(sal) from emp where deptno in (select d.deptno from
dept d where d.dname = 'SALES'));
65.
List the most recently hired emp of grade3 belongs to location CHICAGO.
A) select * from emp e where e.deptno in ( select d.deptno from dept d where d.loc = 'CHICAGO') and
e.hiredate in (select max(hiredate) from emp where empno in (select empno from emp e,salgrade s
where e.sal between s.losal and s.hisal and s.grade = 3)) ; (or)
select * from emp e,dept d where d.loc='chicago'
and hiredate in(select max(hiredate) from emp e,salgrade s
where sal between losal and hisal and grade=3);
66.
List the employees who are senior to most recently hired employee working under king.
A) select * from emp where hiredate < (select max(hiredate) from emp where mgr in
(select empno from emp where ename = 'KING')) ;
67.
List the details of the employee belongs to newyork with grade 3 to 5 except ‘PRESIDENT’ whose sal> the highest paid employee of Chicago in a group where there is manager and salesman not working under king
A) select * from emp where deptno in (select deptno from dept where dept.loc ='NEW YORK')
and empno in (select empno from emp e,salgrade s where e.sal between s.losal and s.hisal and
s.grade in (3,4,5) ) and job != 'PRESIDENT' and sal >(select max(sal) from emp where deptno in
(select deptno from dept where dept.loc = 'CHICAGO') and job in ('MANAGER','SALESMAN') and
mgr not in (select empno from emp where ename = 'KING'));
68.
List the details of the senior employee belongs to 1981.
A)
select * from emp where hiredate in (select min(hiredate) from emp where to_char( hiredate,’YYYY’) = ‘1981’); (OR)
B)
select * from emp where hiredate = (select min(hiredate) from emp where to_char(hiredate,’YYYY’) = ‘1981’);
69.
List the employees who joined in 1981 with the job same as the most senior person of the year 1981.
A)select * from emp where job in (select job from emp where hiredate in
(select min(hiredate) from emp where to_char(hiredate,’YYYY’) =’1981’));
70.
List the most senior empl working under the king and grade is more than 3.
A) select * from emp where hiredate in (select min(hiredate) from emp where empno in
(select empno from emp e ,salgrade s where e.sal between s.losal and s.hisal and s.grade in (4,5)))
and mgr in (select empno from emp where ename = 'KING');
71.
Find the total sal given to the MGR.
A)
select sum (sal) from emp where job = ‘MANAGER’; (OR)
B) select sum(sal) from emp where empno in(select mgr from emp);
72.
Find the total annual sal to distribute job wise in the year 81.
A) select job,sum(12*sal) from emp where to_char(hiredate,'YYYY') = '1981'
group by job ;
73.
Display total sal employee belonging to grade 3.
A)
select sum(sal) from emp where empno
in (select empno from emp e ,salgrade s
where e.sal between s.losal and s.hisal and s.grade = 3)
74.
Display the average salaries of all the clerks.
A) select avg(sal) from emp where job = ‘CLERK’;
75.
List the employeein dept 20 whose sal is >the average sal 0f dept 10 emps.
A) select * from emp where deptno =20 and sal >(select avg (sal) from emp where deptno = 10);
76.
Display the number of employee for each job group deptno wise.
A)
select deptno ,job ,count(*) from emp group by deptno,job; (or)
B) select d.deptno,e.job,count(e.job) from emp e,dept d where e.deptno(+)=d.deptno group by e.job,d.deptno;
77.
List the manage rno and the number of employees working for those mgrs in the ascending Mgrno.
A)
select w.mgr ,count(*) from emp w,emp m
where w.mgr = m.empno
group by w.mgr
order by w.mgr asc;
78.
List the department,details where at least two emps are working
A)
select deptno ,count(*) from emp group by deptno
having count(*) >= 2;
79.
Display the Grade, Number of emps, and max sal of each grade.
A) select s.grade ,count(*),max(sal) from emp e,salgrade s where e.sal between s.losal and s.hisal
group by s.grade;
80.
Display dname, grade, No. of emps where at least two emps are clerks.
A) select d.dname,s.grade,count(*) from emp e,dept d,salgrade s where e.deptno = d.deptno and
e.job = 'CLERK' and e.sal between s.losal and s.hisal group by d.dname,s.grade having count(*) >= 2;
81.
List the details of the department where maximum number of emps are working.
A)
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)
B)
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);
82.
Display the emps whose manager name is jones.
A)
select * from emp where mgr in
(select empno from emp where ename = ‘JONES’); (OR)
B)
select * from emp where mgr =
(select empno from emp where ename = ‘JONES’);
83.
List the employees whose salary is more than 3000 after giving 20% increment.
A)
SELECT * FROM EMP WHERE (1.2*SAL) > 3000 ;
84.
List the emps with dept names.
A) select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname
from emp e ,dept d where e.deptno = d.deptno;
85.
List the emps who are not working in sales dept.
A)
select * from emp where deptno not in
(select deptno from emp where dname = ‘SALES’);
86.
List the emps name ,dept, sal and comm. For those whose salary is between 2000 and 5000 while loc is Chicago.
A) select e.ename,e.deptno,e.sal,e.comm from emp e ,dept d where e.deptno = d.deptno and
d.loc = 'CHICAGO' and e.sal between 2000 and 5000;
87.
List the emps whose sal is greater than his managers salary
A) select * from emp w,emp m where w.mgr = m.empno and w.sal > m.sal;
88.
List the grade, EMP name for the deptno 10 or deptno 30 but sal grade is not 4 while they joined the company before ’31-dec-82’.
A) select s.grade ,e.ename from emp e,salgrade s where e.deptno in (10,20) and
hiredate < ('31-DEC-82') and (e.sal between s.losal and s.hisal and s.grade not in (4));
89.
List the name ,job, dname, location for those who are working as MGRS.
A)
select e.ename,e.job,d.dname,d.loc from emp e ,dept d
where e.deptno = d.deptno and
e.empno in (select mgr from emp ) ;
90.
List the emps whose mgr name is jones and also list their manager name.
A) select w.empno,w.ename,w.job,w.mgr,w.hiredate,w.sal,w.deptno,m.ename from emp w ,emp m
where w.mgr = m.empno and m.ename = 'JONES';