Tough SQL Queries - Part 5

121. 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) );
122. Count the No.of emps who are working as ‘Managers’(using set option).
A)se
lect count(*)
from(select * from emp minus select * from emp where job != 'MANAGER')
123. List the emps who joined in the company on the same date.
A) s
elect * from emp e where hiredate in
(select hiredate from emp where e.empno <> empno);
124. List the details of the emps whose Grade is equal to one tenth of Sales Dept.
A) select * from emp e,salgrade s
where e.sal between s.losal and s.hisal and
s.grade = 0.1* (select deptno from dept where dname = 'SALES');
125. List the name of the dept where more than average no. of emps are working.
A) select d.dname from dept d, emp e where e.deptno = d.deptno
group by d.dname
having count(*) > (select avg(count(*)) from emp group by deptno);
126. List the Managers name who is having max no.of emps working under him.
A
)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)) ;
127. List the Ename and Sal is increased by 15% and expressed as no.of Dollars.
A) select ename,to_char(1.15*sal,'$99,999') as "SAL" from emp; (only for $ it works)
B) select ename,'$'||1.15*sal “SAL” from emp;
128. Produce the output of EMP table ‘EMP_AND_JOB’ for Ename and Job.
A) select ename|| job as "EMP_AND_JOB" from emp ;
129. Produce the following output from EMP.
EMP
LOYEE
SMITH (clerk)
ALLEN (Salesman)
A) select ename || ‘(‘|| lower(job)||’)’ as “EMPLOYEE” from emp;
130) List the emps with Hire date in format June 4, 1988.
A) s
elect empno,ename,sal, to_char(hiredate,'MONTH DD,YYYY') from emp;
13
1) Print a list of emp’s Listing ‘just salary’ if Salary is more than 1500, on target if Salary is 1500 and ‘Below 1500’ if Salary is less than 1500.
A) s
elect empno,ename,sal|| ‘JUST SALARY’ "SAL" from emp where sal > 1500 union
select empno,ename, sal|| ‘ON TARGET’ "SAL" from emp where sal = 1500
union
select empno,ename, sal|| ‘BELOW 1500’ "SAL" from emp where sal < 1500; (OR)
B)select empno,ename,sal,job,
case
when sal = 1500 then 'ON TARGET'
when sal < 1500 then 'BELOW 1500'
when sal > 1500 then 'JUST SALARY'
else 'nothing'
end "REVISED SALARY"
from emp;
132) Write a query which return the day of the week for any date entered in format ‘DD-MM-YY’.
A) s
elect to_char(to_date('& s','dd-mm-yy'),'day') from dual ;
133) Write a query to calculate the length of service of any employee with the company, use DEFINE to avoid repetitive typing of functions.
A) D
EFINE service = ((months_between(sysdate,hiredate))/12)
B)
Select empno,ename,&service from emp where ename = ‘& name’;
134) Give a string of format ‘NN/NN’, verify that the first and last two characters are numbers and that the middle character is’/’. Print the expression ‘YES’ if valid, ‘NO’ if not valid. Use the following values to test your solution. ‘12/34’,’01/1a’, ‘99/98’.
A)
135) Emps hired on or before 15th of any month are paid on the last Friday of that month those hired after 15th are paid on the first Friday of the following month. Print a list of emps their hire date and the first pay date. Sort on hire date.
A) s
elect ename,hiredate,next_day(last_day(hiredate),'FRIDAY')-7 from emp where to_char(hiredate,'DD') <=15
union
select ename,hiredate,next_day(last_day(hiredate),'FRIDAY') from emp where to_char(hiredate,'DD') > 15;
136) Count the no. of characters with out considering spaces for each name.
A) s
elect length(replace(ename,’ ‘,null)) from emp;
13
7) Find out the emps who are getting decimal value in their Sal without using like operator.
A) s
elect * from emp where instr(sal,’.’,1,1) > 0;
138) List those emps whose Salary contains first four digit of their Deptno.
A) s
elect * from emp where instr(to_char(sal,,9999),deptno,1,1)>0 and instr(to_char(sal,9999),deptno,1,2)> 0 ;
13
9) List those Managers who are getting less than his emps Salary.
A) s
elect distinct m.ename,m.sal from emp w,emp m where w.mgr = m.empno and w.sal>m.sal;
B)
select * from emp w where sal < any ( select sal from emp where w.empno=mgr);
C)
select * from emp w where empno in ( select mgr from emp where
w.sal<sal);
140) Print the details of all the emps who are sub-ordinates to Blake.
A) s
elect * from emp where mgr in (select empno from emp where ename = 'BLAKE');
14
1) List the emps who are working as Managers using co-related sub-query.
A) s
elect * from emp where empno in (select mgr from emp);
142) List the emps whose Mgr name is ‘Jones’ and also with his Manager name.
A) s
elect w.ename,m.ename,(select ename from emp where m.mgr = empno) "his MANAGER"
from emp w,emp m where w.mgr = m.empno and m.ename = 'JONES'; (or)
B) select e.ename,w.ename,m.ename from emp e,emp w,emp m where e.mgr = w.empno and w.ename = ‘JONES’ and w.mgr = m.empno;
143) Define a variable representing the expression used to calculate on emps total annual remuneration use the variable in a statement, which finds all emps who can earn 30000 a year or more.
A) Set define on
B)
Define annual = 12*nvl2(comm.,sal+comm.,sal) (here define variable is a session variable)
C)
Select * from emp where &annual > 30000;
144) Find out how may Managers are their in the company.
A) s
elect count(*) from emp where job = ‘MANAGER’; (or)
B)
select count(*) from emp where empno in (select mgr from emp); (or)
C)
select count(distinct m.empno) from emp w,emp m where w.mgr = m.empno ;
14
5) Find Average salary and Average total remuneration for each Job type. Remember Salesman earn commission.secommm
A) s
elect avg(sal),avg(sal+nvl(comm,0)) from emp;
146) Check whether all the emps numbers are indeed unique.
A) s
elect empno,count(*) from emp group by empno;
147) List the emps who are drawing less than 1000 Sort the output by Salary.
A)select * from emp where sal < 1000 order by sal;
148) List the employee Name, Job, Annual Salary, deptno, Dept name and grade who earn 36000 a year or who are not CLERKS.
A)se
lecte.ename,e.job,(12*e.sal)"ANNUALSALARY", e.deptno,d.dname,s.grade
from emp e,dept d ,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal
and (((12*e.sal)>= 36000) or (e.job != 'CLERK'))
149) Find out the Job that was filled in the first half of 1983 and same job that was filled during the same period of 1984.
A) s
elect * from emp where (to_char(hiredate,'MM ') <= 06 and to_char(hiredate,'YYYY') = 1984) and job in (select job from emp where to_char(hiredate,'MM' ) <= 06 and to_char(hiredate,'YYYY') <= 1983) ;
150) Find out the emps who joined in the company before their Managers.
A) s
elect * from emp w,emp m where w.mgr = m.empno and
w.hiredate< m.hiredate;(or)
B) select * from emp e where hiredate < (select hiredate from emp where empno = e.mgr)