List of 200 SQL Queries for Practice Before Interview Part – 3
You are currently learning SQL and looking for some SQL Queries for Practice before Interview related to employee table. To get you going with practicing SQL in local database on your computer, we have put together list of 200 SQL and PLSQL Queries for Practice.
Since we don’t want to make each post on SQL Queries for Practice very length, we will be dividing this list of SQL Question and Answers for Practice in 4 different Parts.
Each part will contain list of 50 SQL Queries for Practice. Answers for these Practice SQL Queries will be provided, but my suggestion is try it on your own first and then see the answer for SQL questions. If you do not have any sample data to practice SQL Queries, you can read this post by me on How to Sample Oracle Database Tables for PLSQL Queries Practice
101
ANS
Display those employees whose manager names is Jones, and also display there manager name.
select e.empno, e.ename, m.ename MANAGER from emp e, emp m where e.mgr=m.empno and m.ename='JONES';
102
ANS
Display name and salary of ford if his Sal is equal to high Sal of his grade.
select ename,sal from emp e where ename='FORD' and sal=(select hisal from salgrade where grade=(select grade from salgrade where e.sal>=losal and e.sal<=hisal));
103
ANS
Display employee name, his job, his dept name, his manager name, his grade and make out of an under department wise.
break on deptno; select d.deptno, e.ename, e.job, d.dname, m.ename, s.grade from emp e, emp m, dept d, salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.mgr=m.empno order by e.deptno;
104
ANS
List out all the employees name, job, and salary grade and department name for every one in the company except ‘CLERK’. Sort on salary display the highest salary.
select empno, ename, sal, dname, grade from emp e, dept d, salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.job<>'CLERK' order by sal;
105
ANS
Display employee name, his job and his manager. Display also employees who are without manager.
select e.ename, e.job, m.ename Manager from emp e,emp m where e.mgr=m.empno union select ename,job,'no manager' from emp where mgr is null;
ALSO READ
106
ANS
Find out the top 5 earner of company.
select * from emp e where 5>(select count(*) from emp where sal>e.sal) order by sal desc;
107
ANS
Display the name of those employees who are getting highest salary.
select empno,ename,sal from emp where sal=(select max(sal) from emp);
108
ANS
Display those employees whose salary is equal to average of maximum and minimum.
select * from emp where sal=(select (max(sal)+min(sal))/2 from emp);
109
ANS
Display count of employees in each department where count greater than 3.
select deptno, count(*) from emp group by deptno having count(*)>3;
110
ANS
Display dname where at least 3 are working and display only dname
select dname from dept where deptno in (select deptno from emp group by deptno having count(*)>3);
111
ANS
Display name of those managers name whose salary is more than average salary of company.
select ename, sal from emp where empno in(select mgr from emp) and sal > (select avg(sal) from emp);
112
ANS
Display those managers name whose salary is more than an average salary of his employees.
select ename, sal from emp e where empno in(select mgr from emp) and e.sal>(select avg(sal) from emp where mgr=e.empno);
113
ANS
Display employee name, Sal, comm and net pay for those employees whose net pay are greater than or equal to any other employee salary of the company?
select ename, sal, comm, sal+nvl(comm,0) netPay from emp where sal+nvl(comm.,0)>=any(select sal from emp);
114
ANS
Display those employees whose salary is less than his manager but more than salary of any other managers.
select * from emp e where sal<(select sal from emp where empno = e.mgr) and sal>any(select sal from emp where empno!=e.mgr);
115
ANS
Display all employees names with total Sal of company with employee name. Select ename,
select ename, sal from emp
116
ANS
Find out the last 5(least) earner of the company?
select * from emp e where 5>(select count(*) from emp where sal
117
ANS
Find out the number of employees whose salary is greater than there manager salary?
select count(*) from emp e where sal>(select sal from emp where empno=e.mgr);
118
ANS
Display those manager who are not working under president but they are working under any other manager?
select * from emp e where mgr in(select empno from emp where ename<>'KING');
119
ANS
Delete those department where no employee working?
delete from dept d where 0=(select count(*) from emp where deptno=d.deptno);
120
ANS
Delete those records from EMP table whose deptno not available in dept table?
delete from emp where deptno not in(select deptno from dept);
121
ANS
Display those earners whose salary is out of the grade available in Sal grade table?
select * from emp where sal<(select min(losal) from salgrade) or sal>(select max(hisal) from salgrade);
122
ANS
Display employee name, Sal, comm. and whose net pay is greater than any other in the company?
Select ename, sal, comm from emp where sal+sal*15/100-sal*5/100 +sal*10/100 = (select max(sal+sal*15/100-sal*5/100+sal*10/100) from emp);
123
ANS
Display name of those employees who are going to retire 31-dec-99. If the maximum job is period is 18 years?
select * from emp where (to_date('31-dec-1999')-hiredate)/365>18;
124
ANS
Display those employees whose salary is ODD value?
select * from emp where mod(sal,2)=1;
125
ANS
Display those employees whose salary contains at least 4 digits?
select * from emp where length(sal)>=4;
126
ANS
Display those employees who joined in the company in the month of DEC?
select * from emp where upper(to_char(hiredate,'mon'))='DEC';
127
ANS
Display those employees whose name contains “A”?
select * from emp where instr(ename,'A',1,1)>0;
128
ANS
Display those employees whose deptno is available in salary?
select * from emp where instr(sal,deptno,1,1)>0;
129
ANS
Display those employees whose first 2 characters from hire date-last 2 characters of salary?
select substr(hiredate,0,2)||substr(sal,length(sal)-1,2) from emp; select concat( substr(hiredate,0,2), substr(sal,length(sal)-1,2) ) from emp;
130
ANS
Display those employees whose 10% of salary is equal to the year of joining?
select * from emp where to_char(hiredate,'yy')=sal*10/100;
131
ANS
Display those employees who are working in sales or research?
select * from emp where deptno in(select deptno from dept where dname in('SALES','RESEARCH'));
132
ANS
Display the grade of Jones?
select grade from salgrade where losal<=(select(sal) from emp where ename='JONES') and hisal>=(select(sal) from emp where ename='JONES');
133
ANS
Display those employees who joined the company before 15th of the month?
select empno,ename from emp where hiredate<(to_date('15-'||to_char(hiredate,'mon')||'-'||to_char(hiredate,'yyyy')));
134
ANS
Delete those records where no of employee in a particular department is less than 3?
delete from emp where deptno in(select deptno from emp group by deptno having count(*)>3);
135
ANS
Delete those employees who joined the company 21 years back from today?
select * from emp where round((sysdate-hiredate)/365)>21; or select * from emp where (to_char (sysdate, 'yyyy')-to_char (hiredate ,'yyyy') )>21;
136
ANS
Display the department name the no of characters of which is equal to no of employees in any other department?
Select dname from dept where length(dname) in (select count(*) from emp group by deptno);
137
ANS
Display those employees who are working as manager?
select * from emp where empno in(select mgr from emp);
138
ANS
Count the no of employees who are working as manager (use set operation)?
select count(*) from emp where empno in(select mgr from emp);
139
ANS
Display the name of then dept those employees who joined the company on the same date?
select empno,ename,hiredate,deptno from emp e where hiredate in (select hiredate from emp where empno<>e.empno);
140
ANS
Display those employees whose grade is equal to any number of Sal but not equal to first number of Sal?
SELECT ENAME FROM EMP WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE=TO_CHAR(HIREDATE,'MM')) AND (SELECT HISAL FROM SALGRADE WHERE GRADE=TO_CHAR(HIREDATE,'MM'));
141
ANS
Display the manager who is having maximum number of employees working under him?
Select mgr from emp group by mgr having count(*)=(select max(count(mgr)) from emp group by mgr);
142
ANS
List out employees name and salary increased by 15% and expressed as whole number of dollars?
select empno,ename,lpad(concat('$',round(sal*115/100)),7) salary from emp;
143
ANS
Produce the output of the EMP table “EMPLOYEE_AND_JOB” for ename and job?
select * from EMPLOYEE_AND_JOB;
144
ANS
List all employees with hire date in the format ‘June 4 1988’?
select to_char(hiredate,'month dd yyyy') from emp;
145
ANS
Print a list of employees displaying ‘Less Salary’ if less than 1500 if exactly 1500 display as ‘Exact Salary’ and if greater than 1500 display ‘More Salary’?
select empno,ename,'Less Salary '||sal from emp where sal<1500 union select empno,ename,'More Salary '||sal from emp where sal>1500 union select empno,ename,'Exact Salary '||sal from emp where sal=1500
146
ANS
Write query to calculate the length of employee has been with the company?
Select round(sysdate-hiredate) from emp;
147
ANS
List the emps who are drawing less than 1000 Sort the output by Salary.
select * from emp where sal < 1000 order by sal;
148
ANS
List the employee Name, Job, Annual Salary, deptno, Dept name and grade who earn 36000 a year or who are not CLERKS.
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
ANS
Display those mangers who are getting less than his employees Sal
Select empno from emp e where sal < any(select sal from emp where mgr=e.empno);
150
ANS
150. Print the details of all the employees who are sub ordinate to Blake.
Select * from emp where mgr=(select empno from emp where ename='BLAKE');
ALSO READ
List of 200 SQL Queries for Practice Before Interview Part -3
TAGS
