Tough SQL Queries - Part 4

91. list the name AND salary Afford 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. list 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) the subquery does the same AS  

100. list the name, salary, comm. FOR those employees whose net pay IS greater thanOR 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) s elect * 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) 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 ;

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 recordsFROM emp whose deptno is not 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 salaryIS 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) s elect * 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);