SQL-QUERIES:
61.List of emps of emp1 who are not found in emp2.?
A)
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';
91.List the name and salary of ford if his salary is equal to hisal of his grade.
A) select e.ename,e.sal from emp e ,salgrade s where e.ename = 'FORD' and e.sal between s.losal and s.hisal and e.sal = s.hisal ;
92.Lit the name, job, dname ,sal, grade dept wise
A)select e.ename,e.job,d.dname,e.sal,s.grade from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal
order by e.deptno ;
93.List the emp name, job, sal, grade and dname except clerks and sort on the basis of highest sal.
A)select e.ename,e.job,e.sal,s.grade,d.dname from emp e ,dept d ,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal and
e.job not in('CLERK')
order by e.sal desc;
94.List the emps name, job who are with out manager.
A) select e.ename,e.job from emp e where mgr is null;
95.List the names of the emps who are getting the highest sal dept wise.
A)select e.ename,e.deptno from emp e where e.sal in
(select max(sal) from emp group by deptno) ;
96.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);
97.List the no. of emps in each department where the no. is more than 3.
A) select deptno,count(*) from emp group by deptno having count(*) < 3;
98.List the names of depts. Where atleast 3 are working in that department.
A)select d.dname,count(*) from emp e ,dept d where e.deptno = d.deptno
group by d.dname
having count(*) >= 3 ;
99.List the managers whose sal is more than his employess avg salary.
A)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 )
100.List the name,salary,comm. For those employees whose net pay is greater than or equal to any other employee salary of the company.
A)select e.ename,e.sal,e.comm from emp e where nvl2(e.comm.,e.sal+e.comm.,e.sal) >= any (select sal from emp); (OR)
B)select ename,sal,comm. from emp where sal+nvl(comm.,0) >= any (select sal from emp);/
101.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));
102.List the employee names and his average salary department wise.
A)select d.deptno, round(avg(nvl2(e1.comm, e1.sal+e1.comm, e1.sal))) avg, e2.ename from emp e1, emp e2, dept d where d.deptno =e1.deptno and d.deptno = e2.deptno group by d.deptno, e2.ename; (or)
B) select d.maxsal,e.ename,e.deptno as "current sal" from emp e,
(select avg(Sal) maxsal,deptno from emp group by deptno) d
where e.deptno=d.deptno;
103. Find out least 5 earners of the company.
A) select * from emp e where 5> (select count(*) from emp where e.sal >sal); (or)
B)
select rownum rank,empno,ename,job,sal from (select * from emp order by sal asc) where rownum < 6 ; (or)
C)
select * from emp e where 5 >(select count(distinct sal) from emp where e.sal > sal);
104. Find out emps whose salaries greater than salaries of their managers.
A) s
elect * 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;
105. List the managers who are not working under the president.
A) select * from emp where empno in(select mgr from emp) and mgr not in
(select empno from emp where job = 'PRESIDENT');
106. List the records from emp whose deptno isnot in dept.?
107. List the Name , Salary, Comm and Net Pay is more than any other employee.
A) Select e.ename,e.sal,e.comm,nvl2(comm,sal+comm,sal) NETPAY
from emp e
where nvl2(comm,sal+comm,sal) > any (select sal from emp where empno =e.empno) ;
108. List the Enames who are retiring after 31-Dec-89 the max Job period is 20Y.
A) s
elect ename from emp where add_months(hiredate,240) > '31-DEC-89';
B) select ename from emp
where add_months(hiredate,240) > to_date(’31-DEC-89’,’DD-MON-RR’);
109. List those Emps whose Salary is odd value.
A) s
elect * from emp where mod(sal,2) = 1;
110. List the emp’s whose Salary contain 3 digits.
A) s
elect * from emp where length (sal) = 3;
111. List the emps who joined in the month of DEC.
A) s
elect * from emp where to_char(hiredate,’MON’) =’DEC’; (OR)
B)
select * from emp where to_char(hiredate,’MON’) in (‘DEC’); (OR)
C)
select * from emp where to_char(hiredate,’MONTH’) like ‘DEC%’;
112. List the emps whose names contains ‘A’.
A) select * from emp where ename like ‘%A%’;
113. List the emps whose Deptno is available in his Salary.
A) select * from emp where instr(sal,deptno) > 0;
114. List the emps whose first 2 chars from Hiredate=last 2 characters of Salary.
A) select * from emp
where substr(hiredate,1,2) = substr(sal,length(sal)-1,length(sal));
115. List the emps Whose 10% of Salary is equal to year of joining.
A) select * from emp where to_char(hiredate,'YY') in (select .1*sal from emp);
116. List first 50% of chars of Ename in Lower Case and remaining are upper Case.
A)select lower(substr(ename,1,round(length(ename)/2)))
||substr(ename,round(length(ename)/2)+1,length(ename)) from emp ; (OR)
B) select lower(substr(ename,1,ciel(length(ename)/2)))
|| substr(ename,ciel(length(ename)/2)+1,length(ename)) from emp ;
117. List the Dname whose No. of Emps is =to number of chars in the Dname.
A) select * from dept d where length(dname) in (select count(*) from emp e where e.deptno = d.deptno ); (or)
B)select d.dname,count(*) from emp e ,dept d where e.deptno = d.deptno group by d.dname having count(*) = length (d.dname);
118. List the emps those who joined in company before 15th of the month.
A) s
elect * from emp where to_char(hiredate,'DD') < '15';
119. List the Dname, no of chars of which is = no. of emp’s in any other Dept.
A) s
elect * from dept d where length(dname) in (select count(*) from emp where d.deptno <> deptno group by deptno ); (or)
B)
select * from dept where length(dname) = any (select count(*) from emp where d.deptno <> deptno group by deptno);
C)
select * from dept d , (select count(*) s,e.deptno "M"from emp e group by e.deptno) d1
where length(dname)=d1.s and d1.M <>d.deptno;
120. List the emps who are working as Managers.
A) s
elect * from where job = ‘MANAGER’; (or)
B)
select * from emp where empno in (select mgr from emp );
No comments:
Post a Comment