SQL QUERIES TO PRACTICE3

SQL-QUERIES:

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)select 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) select * 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) select empno,ename,sal, to_char(hiredate,'MONTH DD,YYYY') from emp;

131) 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) select 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) select 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) DEFINE 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) select 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) select length(replace(ename,’ ‘,null)) from emp;

137) Find out the emps who are getting decimal value in their Sal without using like operator.
A) select * from emp where instr(sal,’.’,1,1) > 0;

138) List those emps whose Salary contains first four digit of their Deptno.
A) select * from emp where instr(to_char(sal,,9999),deptno,1,1)>0 and instr(to_char(sal,9999),deptno,1,2)> 0 ;

139) List those Managers who are getting less than his emps Salary.
A) select 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) select * from emp where mgr in (select empno from emp where ename = 'BLAKE');

141) List the emps who are working as Managers using co-related sub-query.
A) select * 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) select 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) select 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 ;

145) Find Average salary and Average total remuneration for each Job type. Remember Salesman earn commission.secommm
A) select avg(sal),avg(sal+nvl(comm,0)) from emp;

146) Check whether all the emps numbers are indeed unique.
A) select 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)selecte.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) select * 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) select * 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)

151) List all the emps by name and number along with their Manager’s name and number. Also List KING
       who has no ‘Manager’.
A) select w.empno,w.ename,m.empno,m.ename from emp w,emp m where w.mgr= m.empno(+);

152) Find all the emps who earn the minimum Salary for each job wise in ascending order.
A) select * from emp where sal in
(select min(sal) from emp group by job)
order by sal asc;

153) Find out all the emps who earn highest salary in each job type. Sort in descending salary order.
A) select * from emp where sal in
(select max(sal) from emp group by job)
order by sal desc;

154) Find out the most recently hired emps in each Dept order by Hiredate.
A) select * from emp e where hiredate in
(select max(hiredate) from emp where e.deptno = deptno )
order by hiredate;

155) List the employee name,Salary and Deptno for each employee who earns a salary greater than the average for their department order by Deptno.
A) select * from emp e
where sal > (select avg(sal) from emp where e.deptno = deptno );
B) select e.ename,e.sal,e.deptno from emp e,(select avg(sal) A,deptno D from
emp group by deptno) D1 where D1.D = e.deptno and e.sal > D1.A;

156) List the Deptno where there are no emps.
A) select deptno ,count(*) from emp
group by deptno
having count(*) = 0;

157) List the No.of emp’s and Avg salary within each department for each job.
A) select count(*),avg(sal),deptno,job from emp
group by deptno,job;

158) Find the maximum average salary drawn for each job except for ‘President’.
A) select max(avg(sal)) from emp where job != 'PRESIDENT' group by job;

159) Find the name and Job of the emps who earn Max salary and Commission.
A) select * from emp where sal = (select max(sal) from emp) and comm. is not null;

160) List the Name, Job and Salary of the emps who are not belonging to the department 10 but who have the same job and Salary as the emps of dept 10.
A) select ename,job,sal from emp where deptno != 10 and job in (select job from emp where deptno = 10)
and sal in (select sal from emp where deptno = 10);

161) List the Deptno, Name, Job, Salary and Sal+Comm of the SALESMAN who are earning maximum salary and commission in descending order.
A)select deptno,name,job,sal,sal+nvl(comm.,0) from emp where job = ‘SALESMAN’ and sal in (select max(sal+nvl(comm.,0)) from emp where comm. is not null)
Order by (sal +nvl(comm.,0)) desc;

162) List the Deptno, Name, Job, Salary and Sal+Comm of the emps who earn the second highest earnings (sal + comm.).
A) select deptno,ename,sal,job,sal+nvl(comm,0) from emp e where 2 = (select count(distinct sal+nvl(comm,0)) from emp where (e.sal+nvl(comm.,0))<(sal+nvl(comm.,0));

163) List the Deptno and their average salaries for dept with the average salary less than the averages for all department
A) select deptno,avg(sal) from emp group by deptno
having avg(sal) <(select avg(Sal) from emp);

164) List out the Names and Salaries of the emps along with their manager names and salaries for those emps who earn more salary than their Manager.
A) select w.ename,w.sal,m.ename,m.sal from emp w,emp m
where w.mgr = m.empno and w.sal > m.sal;

165) List out the Name, Job, Salary of the emps in the department with the highest average salary.List out the Name, Job, Salary of emps in department with highest average salary.O
A) select * from emp where deptno in
(select deptno from emp e
having avg(sal) =(select max(avg(sal)) from emp group by deptno)
group by deptno);

166) List the empno,sal,comm. Of emps.
A) select empno,sal,comm. from emp;

167) List the details of the emps in the ascending order of the sal.
A) select * from emp order by sal asc;

168) List the dept in the ascending order of the job and the desc order of the emps print empno, ename.
A) select * from emp e order by e.job asc,e.empno desc ;

169) Display the unique dept of the emps.
A)select * from dept where deptno in (select unique deptno from emp);

170) Display the unique dept with jobs.
A) select unique deptno ,job from emp ;

171) Display the details of the blake.
A) select * from emp where ename = ‘BLAKE’;

172) List all the clerks.
A) select * from emp where job = ‘CLERK’;

173) list all the employees joined on 1st may 81.
A) select * from emp where hiredate = ’01-MAY-81’;

174) List the empno,ename,sal,deptno of the dept 10 emps in the ascending order of salary.
A) select e.empno,e.ename,e.sal,e.deptno from emp where e.deptno = 10
or
der by e.sal asc;

175) List the emps whose salaries are less than 3500.
A) select * from emp where sal <3500;

176) List the empno,ename,sal of all the emp joined before 1 apr 81.
A) select e.empno ,e.ename .e.sal from emp where hiredate <’01-APR-81’;

177) List the emp whose annual sal is <25000 in the asc order of the salaries.
A) select * from emp where (12*sal) < 25000 order by sal asc;

178) List the empno,ename,annsal,dailysal of all the salesmen in the asc ann sal
A) select e.empno,e.ename ,12*sal "ANN SAL" , (12*sal)/365 "DAILY SAL" from emp e
where e.job = 'SALESMAN'
order by "ANN SAL" asc ;

179) List the empno,ename,hiredate,current date & exp in the ascending order of the exp.
A) select empno,ename,hiredate,(select sysdate from dual),((months_between(sysdate,hiredate))/12) EXP
from emp
order by EXP asc;

180) List the emps whose exp is more than 10 years.
A) select * from emp where ((months_between(sysdate,hiredate))/12) > 10;