41. Display the Empno, Ename, Sal, Dname, Loc, Deptno, Job of all emps working at

CJICAGO or working for ACCOUNTING dept with Ann Sal>28000, but the Sal

should not be=3000 or 2800 who doesn’t belongs to the Mgr and whose no is

having a digit ‘7’ or ‘8’ in 3rd position in the asc order of Deptno and desc order

of job.

A) select E.empno,E.ename,E.sal,D.dname,D.loc,E.deptno,E.job from emp E,dept D where (D.loc = 'CHICAGO' or D.dname = 'ACCOUNTING') and E.deptno=D.deptno and E.empno in (select E.empno from emp E where (12*E.sal) > 28000 and E.sal not in (3000,2800) and E.job !='MANAGER' and ( E.empno like '__7%' or E.empno like '__8%')) order by E.deptno asc , E.job desc;

42. Display the total information of the emps along with Grades in the asc order.

A) select * from emp e ,salgrade s where e.sal between s.losal and s.hisal order by grade asc; (OR) B) select * from emp e ,salgrade s where e.sal >= s.losal and e.sal <= s.hisal order by s.grade asc; (using between and is a bit simple)

43. List all the Grade2 and Grade 3 emps.

A) select * from emp e where e.empno in (select e.empno from emp e ,salgrade s where e.sal between s.losal and s.hisal and s.grade in(2,3)); (OR) B) select * from emp e ,salgrade s where e.sal between s.losal and s.hisal and s.grade in (2,3) ;

44. Display all Grade 4,5 Analyst and Mgr.

A) select * from emp e, salgrade s where e.sal between s.losal and s.hisal and s.grade in (4,5) and e.empno in (select e.empno from emp e where e.job in (‘MANAGER’,’ANALYST’) );

45. List the Empno, Ename, Sal, Dname, Grade, Exp, and Ann Sal of emps working for Dept10 or20.

```
A)selectE.empno,E.ename,E.sal,S.grade,D.dname,(months_between(sysdate,E.hired
ate)/12) "EXP" ,12*E.sal “ANN SAL”
from emp E,dept D ,salgrade S
where E.deptno in (10,20) and E.deptno = D.deptno and E.sal between S.losal
and S.hisal ;
```

46. List all the information of emp with Loc and the Grade of all the emps belong to

the Grade range from 2 to 4 working at the Dept those are not starting with char

set ‘OP’ and not ending with ‘S’ with the designation having a char ‘a’ any where

joined in the year 1981 but not in the month of Mar or Sep and Sal not end with

‘00’ in the asc order of Grades

A) select e.empno,e.ename,d.loc,s.grade,e.sal from emp e ,dept d,salgrade s where e.deptno = d.deptno and (d.dname not like 'OP%' and d.dname not like '%S') and e.sal between s.losal and s.hisal and s.grade in (2,3,4) and empno in (select empno from emp where job like '%A%'and sal not like '%00' and (to_char (hiredate,'YYYY') = '1981' and to_char(hiredate,'MON') not in ('MAR','SEP')));

47. List the details of the Depts along with Empno, Ename or without the emps

A) select * from emp e,dept d where e.deptno(+)= d.deptno;

48. List the details of the emps whose Salaries more than the employee BLAKE.

A) select * from emp where sal > (select sal from emp where ename = ‘BLAKE’);

49. List the emps whose Jobs are same as ALLEN.

A) select * from emp where job = (select job from emp where ename = ‘ALLEN’);

50. List the emps who are senior to King.

A) select * from emp where hiredate < ( select hiredate from emp where ename = ‘KING’);

51. List the Emps who are senior to their own MGRS.

A) select * from emp w,emp m where w.mgr = m.empno and w.hiredate < m.hiredate ; (OR) B) select * from emp w,emp m where w.empno= m.mgr and w.hiredate> m.hiredate;

52. List the Emps of Deptno 20 whose Jobs are same as Deptno10.

A) select * from emp e ,dept d where d.deptno = 20 and e.deptno = d.deptno and e.job in ( select e.job from emp e,dept d where e.deptno = d.deptno and d.deptno =10);

53. List the Emps whose Sal is same as FORD or SMITH in desc order of Sal.

Select * from emp where sal in (select sal from emp where ( ename = ‘SMITH’ or ename = ‘FORD’ )) order by sal desc;

54. List the emps Whose Jobs are same as MILLER or Sal is more than ALLEN.

A) select * from emp where job = (select job from emp where ename = ‘MILLER’ ) or sal>(select sal from emp where ename = ‘ALLEN’);

55. List the Emps whose Sal is > the total remuneration of the SALESMAN.

A) select * from emp where sal >(select sum(nvl2(comm,sal+comm,sal)) from emp where job = ‘SALESMAN’);

56. List the emps who are senior to BLAKE working at CHICAGO & BOSTON.

A) select * from emp e ,dept d where d.loc in (‘CHICAGO’,’BOSTON’) and e.deptno = d.deptno and e.hiredate <(select e.hiredate from emp e where e.ename = ‘BLAKE’) ;

57. List the Emps of Grade 3,4 belongs to the dept ACCOUNTING and RESEARCH

whose Sal is more than ALLEN and exp more than SMITH in the asc order of

EXP.

A) select * from emp e where e.deptno in (select d.deptno from dept d where d.dname in (‘ACCOUNTING’,’RESEARCH’) ) and e.sal >(select sal from emp where ename = ‘ALLEN’) and e.hiredate <( select hiredate from emp where ename = ‘SMITH’) and e.empno in (select e.empno from emp e ,salgrade s where e.sal between s.losal and s.hisal and s.grade in (3,4) ) order by e.hiredate desc;

58. List the emps whose jobs same as SMITH or ALLEN.

A) select * from emp where job in (select job from emp where ename = ‘SMITH’ or ename = ‘ALLEN’); (OR) B) select * from emp where job in (select job from emp where ename in (‘SMITH’,’ALLEN’);

60. Any jobs of deptno 10 those that are not found in deptno 20.

A) select e.job from emp e where e.deptno = 10 and e.job not in (select job from emp where deptno =20);

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;

Part 1|Part 2|Part 3|Part4|Part5|Part6