In this blogpost we would be providing Sample Practice SQL Queries with Solutions For Employee Table. If you are an Database Administrator or a Database Analyst oor someone who wants to know how to manipulate data from employee tables in your organization, you can use make use of this SQL solutions. You can use this SQL Queries with Solutions For Employee Table for most common problems related to retrieving data from SQL Database. 

We have divided the Practice SQL Queries with Solutions For Employee Table in four different parts, as we do not want to make each post lengthy and boring. Each post will contain 50 Practice SQL Queries with Solutions For Employee Table. Use the following table to browse across different parts.

Practice SQL Queries with Solutions For Employee Table
Practice SQL Queries with Solutions For Employee Table – Part 1Practice SQL Queries with Solutions For Employee Table – Part 2
Practice SQL Queries with Solutions For Employee Table – Part 3Practice SQL Queries with Solutions For Employee Table – Part 4

Here is the List of 50 Practice SQL Queries with Solutions For Employee Table – Part 4

151.List all the emps by name and number along with their manager’s name and number also list KING who has no ‘Manager’
SQL>
select distinct A.empno, A.ename, A.mgr, B.empno, B.ename

from emp A, emp B where A.mgr = B.empno ;

 

HOW TO RETRIEVE KING’S INFO

 

select * from emp where empno in

(select distinct A.empno from

emp A, emp B where A.mgr = B.empno)

union

(select B.empno from emp B where

ename=’KING’) ;

ERRRO : Query Block has incorrect number of result columns.

152.Find all the emps who earn minimum sal for each job wise in asc order of sal
SQL>select * from emp where sal in (select min(sal) from emp

group by job) order by sal ;

Also Read: Best PL/SQL and SQL Tips To Read Before Interview
153.Find out all the emps who earn highest sal in each job type. Sort in des order of sal
SQL>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
SQL>select * from emp where hiredate in (select max(hiredate) from emp

group by job) order by hiredate desc ;

 
155.List the emp name, sal and deptno for each emp who earns a sal greater than the avg for their dept order by deptno
SQL>
select ename, sal, deptno from emp where sal in

(select sal from emp where sal > (select avg(sal) from emp

group by deptno) group by deptno) order by deptno

 

NOT A GROUP BY EXPRESSION

 

156.List the deptno where there are no emps
SQL>select deptno, dname from dept where deptno not in

(select deptno from emp) ;

 
157.List the no of emps and avg salary within each dept for each job.
SQL>select deptno, job, count(empno), avg(sal) from emp group by deptno, job ;
158Find the max avg salary drawn for each job except for ‘PRESIDENT’
SQL>select job, max(sal), 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.
SQL>select ename, job, sal, comm from emp

where sal=(select max(sal) from emp) or

comm=(select max(comm) from emp) ;

160.List the name, job and salary of the emps who are not belonging to the dept 10 but who have the same job and salary as the emps of dept 10.
SQL>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) ;

 

NO SUCH RECORDS, SO GIVE :

select ename, job, sal from emp where deptno <> 10

and job in (select job from emp where deptno=10)

or sal in (select sal from emp where deptno=10) ;

 
161.List the deptno, name, job, salary and sal+comm. Of the emps who earn the second highest earnings (sal+comm)
SQL> select ename, job, sal, sal+decode(comm,null,0), deptno from

emp where empno = (select empno from emp where sal+decode(comm,null,0) =

(select max(sal+decode(comm,null,0)) from emp where

sal+decode(comm,null,0) in (select sal+decode(comm,null,0)

from emp where sal+decode(comm,null,0) <

(select max(sal+decode(comm,null,0)) from emp)))) ;

 

Select ename, job, sal, sal+decode(comm,null,0), deptno from emp where

sal = (select max(sal) from emp where sal in

(select sal from emp where sal < (select max(sal) from emp))) ;

 

Select ename, job, sal, sal+comm, deptno from emp where

sal+comm = (select max(sal+decode(comm,null,0)) from emp

where sal+decode(comm,null,0) in (select sal+decode(comm,null,0)

from emp where sal+decode(comm,null,0) < (select

max(sal+decode(comm,null,0)) from emp)))

 

NO ROWS SELECTED

162.List the deptno, name, job, salary and sal+comm. Of the salesman
SQL>select ename, job, sal, comm, deptno from emp where job = ‘SALESMAN’
163.List the deptno and their avg salaries for dept with the avg salary less than the avg for all departments.
SQL>select deptno, avg(sal) from emp having avg(sal) =

(select min(avg(sal)) from emp having avg(sal) in

(select avg(sal) from emp group by deptno) group by deptno)

group by deptno ;

 
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.
SQL>select A.ename, A.sal, B.ename, B.sal from emp A, emp B

where A.mgr = B.empno and A.sal > B.sal ;

165.List out the name, job, sal of the emps in the department with the highest avg sal.
SQL>select ename, job, sal from emp where sal >=

(select max(avg(sal)) from emp group by deptno) ;

166.List the empno, sal and comm. of emps
SQL>Select empno, sal, comm From emp ;
167.List the details of the emps in the asc order of salary.
SQL>Select * from emp order by sal ;
168.List the dept in asc order of job and the desc order of emps print empno, ename
SQL>select empno, ename, job, sal, deptno from emp order by job, empno desc ;
169.Display the unique dept of emps.
SQL>select * from emp where deptno = (select deptno from emp

having count(deptno) = 1 group by deptno);

 

To get the answer enter a new record with deptno as 40.

170.Display the unique dept with jobs.
SQL>select dname,job from emp, dept where emp.deptno = (select deptno from emp

having count(deptno) = 1 group by deptno) ;

 
171.Display the details of BLAKE.
SQL>Select * from emp where ename = ‘BLAKE’ ;
172.List all CLERKs
SQL>Select * from emp where job = ‘CLERK’ ;
173.List all emps joined on 1 May 81.
SQL>Select * from emp where hiredate = ’01-MAY-81’ ;
174.List empno, ename,sal, deptno of dept 10 emps in the asc order of sal.
SQL>Select empno, ename,sal, deptno from emp where deptno = 10 order by sal ;
 
175.List the emps whose salaries are less than 3500.
SQL>Select * from emp where sal < 3500 ;
176.List the emps Empno, Ename,sal for all emp joined before 1 apr 81
SQL>
Select Empno, Ename,sal,hiredate from emp where hiredate in (

select hiredate from emp where hiredate > ’01-APR-81′) ;

 

Select Empno, Ename,sal,hiredate from emp where hiredate < ’01-APR-81′

 

Giving wrong output.

177.List the emps whose ann sal is < 25000 in the asc order of salaries
SQL>Select * from emp where sal*12 < 25000 order by sal ;
178.List the empno, ename, ann sal, daily sal of all salesman in the asc ann sal.
SQL>Select empno, ename, sal*12 AnnSal, sal/30 DailySal

from emp where job = ‘SALESMAN’ order by sal ;

179.List the empno, ename, hiredate, current date & exp in the asc order of exp.
SQL>Select empno, ename, hiredate, sysdate,

months_between(sysdate,hiredate)/12 Exp from emp order by hiredate ;

180.List the emps whose exp is more than 10 years.
SQL>select * from emp where

months_between(sysdate,hiredate)/12 > 10 ;

 
181.List the empno, ename,sal TA 30%, DA 40%, HRA 50%, gross, LIC, PF, net deductions, net allowances and net sal in the asc order of net sal.
SQL>select empno, ename,sal,sal*.3 TA,sal*.4 DA,sal*.5 HRA, 80 IT,

sal*.04 LIC, sal*.12 PF, (sal*.16)-80 NetDeductions, sal*1.2 NetAllowances,

sal+sal*1.2 Gross, (sal+sal*1.2 – (sal*.16)-80) NetSal from emp order by sal ;

182.List the emps who are working as Mgrs.
SQL>Select * from emp where job = ‘MANAGER’ ;
183.List the emps who are either CLERKs or MANAGERs.
SQL>Select * from emp where job = ‘MANAGER’ or job = ‘CLERK’;
184.List the emps who joined in the following dates : 1 may 81, 17 nov 81, 30 dec 81.
SQL>select * from emp where hiredate in

(’01-MAY-81′, ’17-NOV-81′, ’30_DEC-81′) ;

 

NO ROWS WILL BE SELECTED. THERE IS NO SUCH HIREDATES

 
185.List the emps who joined in year 81.
SQL>select * from emp where hiredate like ‘%81’ ;
186.List the emps whose ann sal ranging from 23000 to 40000.
SQL>Select * from emp where sal*12 between 23000 and 40000 ;
187.List the emps working under the Mgrs 7369, 7890, 7654, 7900.
SQL>Select * from emp where Mgr in (7369, 7890, 7654, 7900) ;

 

NO ROWS WILL BE SELECTED. THERE IS NO SUCH EMPNOs

 
188.List the emps who joined in the second half of 82.
SQL>
Select * from emp where hiredate in (select hiredate

from emp where hiredate > ’30-JUN-82′) ;

 

Select * from emp where hiredate > ’30-JUN-82′;

 

NO ROWS SELECTED

189.List all the 4 chars emps.
SQL>Select ename from emp having

length(ename) = 4 group by ename ;

 
190.List the emps names starting with ‘M’ with 5 chars
SQL>Select ename from emp where ename like ‘M____’ ;

 

NO ROWS WILL BE SELECTED. THERE IS NO SUCH EMPs. GIVE ONE MORE ‘_’ IN ENAME.

191.List the emps names end with ‘H’ all together 5 chars.
SQL>Select ename from emp where ename like ‘____H’ ;
192.List the names start with ‘M’
SQL>Select ename from emp where ename like ‘M%’
193.List the emps who joined in the year 81
SQL>Select * from emp where hiredate like ‘%81’ ;
194.List the emps whose sal is ending with 00.
SQL>Select * from emp where sal like ‘%00’ ;
 
195.List the emps who joined in the month of jan
SQL>Select * from emp where hiredate like ‘%JAN%’ ;
196.List the emps who joined in the month having a char ‘a’
SQL>Select * from emp where hiredate like ‘%A% ;’
197.List the emps who joined in the month having a second char is ‘A’
SQL>Select * from emp where hiredate like ‘%-_A%’ ;
 
198.List the emps whose salary is 4 digit no.
SQL>Select * from emp where sal like ‘____’ ;
199.List the emps who joined in 80’s.
SQL>Select * from emp where hiredate like ‘%8_’ ;
 
200.List the emps who are either clerks with an exp more than 8y
SQL>Select * from emp where job = ‘CLERK’ or

Months_between(sysdate,hiredate)/12 > 8 ;

http://techhowdy.com/wp-content/uploads/2018/02/Practice-SQL-Queries-with-Solutions-For-Employee-Table-–-Part-4.pnghttp://techhowdy.com/wp-content/uploads/2018/02/Practice-SQL-Queries-with-Solutions-For-Employee-Table-–-Part-4-150x150.pngDemonDatabase Programmingadvanced sql queries for practice pdf,basic sql queries practice questions,best book to practice sql queries,best site to practice sql queries,best software to practice sql queries,best way to practice sql queries,complex sql queries for practice in oracle,dbms sql queries practice,download sql queries practice questions,editor to practice sql queries online,employee table queries in sql,how can i practice sql queries online,how to practice sql queries,how to practice sql queries at home,how to practice sql queries offline,how to practice sql queries online free,i want to practice sql queries online,interview questions sql queries employee table,mysql queries for practice,oracle 10g sql practice queries,oracle sql queries practice questions,practice advanced sql queries,practice basic sql queries,practice complex sql queries,practice complex sql queries online,practice of sql queries,practice of sql server queries,practice oracle sql queries online free,practice pl sql queries,practice pl sql queries online,practice queries for sql server,practice queries in sql,practice queries in sql server,practice set for sql queries,practice sql join queries online,practice sql nested queries,practice sql queries,practice sql queries adventureworks,practice sql queries for interview,practice sql queries free,practice sql queries offline,practice sql queries on mac,practice sql queries on sql server,practice sql queries online,practice sql queries online free,practice sql queries pdf,practice sql queries questions,practice sql queries software,practice sql queries with answer,practice sql server queries,practice sql server queries online,practice writing complex sql queries,practice writing sql queries,practice writing sql queries online,sample database to practice sql queries,sample sql queries to practice,site to practice sql queries,sql join queries for practice with answers,sql practice queries on joins,sql practice queries with answers pdf,sql practice queries with solutions,sql practice queries with solutions pdf,sql queries based on employee table,sql queries best practice,sql queries employee department table,SQL Queries Employee Table,sql queries examples on employee table,sql queries for employee and department table,sql queries for employee table,sql queries for practice,sql queries for practice in oracle,sql queries for practice on emp table,sql queries for practice with answers doc,sql queries for practice with answers free download,sql queries for practice with answers pdf,sql queries on employee and department table,sql queries on employee table pdf,sql queries pdf for practice with answers free download,sql queries practice book,sql queries practice examples,sql queries practice exercises,sql queries practice exercises pdf,sql queries practice questions and answers,sql queries practice questions online,sql queries practice questions pdf,sql queries practice questions with answers,sql queries practice questions with answers pdf,sql queries practice quiz,sql queries practice test,sql queries practice w3schools,sql queries to practice,sql queries to practice on oracle,sql queries using employee table,sql queries with employee table,sql query employee table,sql query to create employee table,sql server practice queries pdf,sql server queries on employee table,sql server queries practice exercises,sql subqueries practice,sql subqueries practice questions,t-sql practice queries,teradata sql practice queries,website practice sql queries,website to practice sql queries,where can i practice sql queries onlineIn this blogpost we would be providing Sample Practice SQL Queries with Solutions For Employee Table. If you are an Database Administrator or a Database Analyst oor someone who wants to know how to manipulate data from employee tables in your organization, you can use make use of this SQL...Latest technology news