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 2

Q.NOPractice SQL Queries with Solutions For Employee Table – Part 1 
51.List the emps who are senior to their own MGRs
SQL>select * from emp A where hiredate <

 (select hiredate from emp B where b.Empno = A.Mgr)

 
52.List the emps of deptno 20 whose jobs are same as deptno 10
SQL>Select * from emp where deptno=20 and job in (select job from emp where deptno=10) ;
  
53.List the emps whose Sal is same as FORD or SMITH in desc order of Sal.
QL>Select distinct * from Emp where sal in

(select sal from emp where ename in (‘FORD’,’SMITH’))

and ename not in (‘FORD’,’SMITH’)

Order By sal Desc ;

  
54.List the emps whose Jobs are same as MILLER or Sal is more than ALLEN.
SQL>Select * from Emp where job = (select job from emp where ename=’MILLER’) OR Sal > (select sal from emp where ename =’ALLEN’) ;
  
055.List the emps whose Sal is > the total remuneration of the SALESMAN.
SQL>Select * from Emp where sal > (select SUM(sal) from emp where JOB=’SALESMAN’);

Note : no rows will be selected. If you choose job=’CLERK’, one record will be fetched.

  
56.List the emps who are Senior to ‘BLAKE’ working at CHICAGO & BOSTON.
SQL>Select * from Emp,dept where Hiredate <

 (select hiredate from emp where ename=’BLAKE’)

 AND loc in (‘CHICAGO’,’BOSTON’) and emp.deptno = dept.deptno ;

  
57.List the emps of Grade 3,4 belongs to the dept ACCOUNTING and RESEARCH whose sal is more than ALLEN and exp more than SMITH in the asc order of Exp.
SQL>Select * from Emp,dept,salgrade where grade in(3,4)

AND dname in (‘ACCOUNTING’,’RESEARCH’) and

Sal > (select sal from emp where ename=’ALLEN’)

and Hiredate < (select hiredate from emp where ename=’SMITH’)

and emp.deptno = dept.deptno and sal between losal and hisal;

Note : no rows will be selected. Because, ’SMITH’ is the senior most in the emp table. If you choose ename=’TURNER’, Two record will be fetched.

  
58.List the emps whose jobs same as ALLEN Or SMITH.
SQL>Select * from Emp where job in

(select distinct job from emp where ename in

(‘ALLEN’,’SMITH’)) and ename not in (‘ALLEN’,’SMITH’).

  
59.Write a Query to display the details of emps whose Sal is same as of

a)    Employee Sal of Emp1 table.

b)    ¾ Sal of any Mgr of Emp2 table.

c)    The Sal of any sales person with exp of 5 yrs belongs to the sales dept of emp3 table.

d)    Any Grade 2 employees of Emp4 table.

e)    Any Grade 2&3 employee working for SALES dept or OPERATIONS dept joined in 89.

SQL>Select * from emp where sal in (select sal from emp1) OR    

  sal in (select sal*3 from Emp2 where job=’MANAGER’) OR    

  sal in (select sal from Emp3 where months_between(sysdate,hiredate)/12 > 5) OR    

  sal in (select sal from Emp4,salgrade where grade=2 and sal between losal and hisal) OR    

  sal in (select sal from Emp,salgrade,dept where grade in(2,3) and                   

      dname in (‘SALES’,’OPERATIONS’) and hiredate like ‘%89’ and

      sal between losal and hisal and emp.deptno=dept.deptno)

 
60.List the jobs of Deptno 10 those are not found in dept 20.
SQL>Select * from emp where deptno = 10 and job not in (select job from emp where deptno=20)
  
61.List the emps of Emp1 who are not found in deptno 20
SQL>Select * from emp where ename not in (select ename from emp where deptno=20)
  
62.Find the highest Sal of the Emp table.
SQL>Select Max(sal) from emp ;
 
63.Find the details of highest paid employee.
SQL>Select * from emp where sal = (select Max(sal) from emp) ;
  
64.Find the highest paid employee of sales dept.
SQL>Select * from emp where sal = (select Max(sal) from emp,dept where dname=’SALES’ and emp.deptno = dept.deptno ) ;
 
65.List the most recently hired emp of grade 3 belongs to the Loc CHICAGO
SQL>Select * from emp,dept where loc=’CHICAGO’ and

     hiredate = (Select max(hiredate) from emp);

  
66.List the emps who are senior to most recently hired emp who is working under Mgr KING
SQL>Select * from emp where hiredate < (Select max(hiredate) from emp) and

  mgr=(select empno from emp where ename=’KING’) ;

  
67.List the details of emp belongs to New York with the Grade 3 to 5 except ‘PRESIDENT’ whose sal > the highest paid emp of CHICAGO in Group where there is ‘MANAGER’ & ‘ANALYST’ not working for Mgr KING
SQL>Select * from emp,dept,salgrade where loc=’NEW YORK’ and

Grade in (3,4,5) and job <> ‘PRESIDENT’ and Sal >

(select Max(sal) from emp,dept where loc=’CHICAGO’ and

Job not in (‘MANAGER’,’ANALYST’) and emp.deptno = dept.deptno) and Mgr =

(select empno from emp where ename=’KING’)  And

emp.deptno = dept.deptno and sal between losal and hisal ;

  
068.Display the details of most senior employee belongs to 1981.
SQL>select * from emp where hiredate = (select min(hiredate)

  from emp where hiredate like ‘%81’) ;

  
69.List the emps who joined in 81 with job same as the most senior person of year 81.
SQL>select * from emp where hiredate like ‘%81’ and job =

 (select job from emp where hiredate= (select min(hiredate) from emp

   where hiredate like ‘%81’)) ;

  
70.List the most senior emp working under KING and Grade is more than 3.
SQL>select emp.* from emp,salgrade where hiredate =

 (select max(hiredate) from emp where mgr=

  (select empno from emp where ename = ‘KING’)) and

   grade > 3 and sal between losal and hisal

  
71.Find the total sal given to the ‘MGR’
SQL>select sum(sal) from emp where job = ‘MANAGER’
  
72.Find the total annual sal to distribute job wise in the year 81
SQL>select Job,sum(sal*12) from emp GROUP BY JOB ;
  
73.Display the total sal of emps belong to Grade 3.
SQL>select grade, sum(sal) from emp,salgrade where

  sal between losal and hisal group by grade ;

 
 
74.Display the avg salaries of all CLERKS
SQL>select avg(sal) from emp where job = ‘CLERK’ ;
  
75.List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps.
SQL>select * from emp where sal > (select avg(sal) from emp

 where deptno=10) and deptno=20 ;

  
76.Display the number of emps for each job group deptno wise
SQL>select dname,job,count(empno) from emp,dept

 where dept.deptno = emp.deptno

 group by dname,job ;

  
77.List the Mgr no & no. of emps working for those Mgrs in the asc Mgrno.
SQL>select mgr,count(*) from emp group by mgr order by mgr asc ;
  
78.List the dept details where at least two emps are working.
SQL>select dname, count(empno) from emp,dept

 where dept.deptno = emp.deptno

  group by dname,job

   having count(empno) >= 2 ;

  
79.Display the grade, number of emps, max sal of each grade.
SQL>select grade, count(empno),max(sal) from emp,dept,salgrade

 where dept.deptno = emp.deptno and

  sal between losal and hisal

   group by grade ;

  
80.Display dname, grade, no of emps where atleast two emps are ‘CLERKS’
SQL>select dname,grade, count(empno) from emp,dept,salgrade

 where dept.deptno = emp.deptno and

  sal between losal and hisal

   group by dname,grade

    having count(‘CLERK’) >= 2 ;

     
81.List the details of the dept where the max no of emps are working
QL>select dname, count(empno) from emp,dept

 where dept.deptno = emp.deptno group by dname

   having count(empno) = (select max(count(empno)) from emp group by deptno) ;

  
082.Display the emps whose Mgr name is Jones
SQL>Select * from emp where mgr = (select empno from emp where ename = ‘JONES’) ;
 
83.List the emps whose salary is more than 3000 after giving 20% increment
QL>Select EMP.*,SAL+(SAL*.2) incrsal from emp where SAL+(SAL*.2) > 3000 ;
  
84.List the emps with their dept names.
SQL>Select emp.*, dname from emp, dept where

 emp.deptno = dept.deptno ;

  
85.List the emps who are not working in sales dept
SQL>Select emp.*, dname from emp, dept where

 Dname <> ‘SALES’ and

  emp.deptno = dept.deptno ;

  
86.List the emps name, dept, sal & comm. For those whose salary is between 2000 and 5000 while loc is CHICAGO
SQL>Select ename, dname, sal, comm from emp, dept where

 sal between 2000 and 5000 and loc = ‘CHICAGO’ and

  emp.deptno = dept.deptno ;

  
87.List the emps whose salary is > his Manager’s salary.
SQL>select A.* from emp A,emp B where

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

  
88.List the grade, employee name for the deptno 10 or deptno 30 but salgrade is not 4 while they joined the company before ’31-DEC-82’
SQL>select grade, ename, hiredate from emp,salgrade where

deptno in (10,30) and grade <> 4 and hiredate

in (select hiredate from emp where hiredate

< ’31-DEC-82′) and sal between losal and hisal

  
89.List the name, job, dname, loc for those who are working as a ‘MGRs’
SQL>select empno,ename, job, dname, loc from emp,dept where

 job = ‘MANAGER’ and emp.deptno = dept.deptno ;

  
90.List the emps whose mgr name is ‘JONES’ and also list their manager name
SQL>select A.*, B.ename from emp A, emp B where

 A.mgr = B.empno and b.ename=’JONES’ ;

     
91.List the name and salary of FORD if his salary is equal to hisal of his Grade
SQL>select ename, sal from emp where ename = ‘FORD’

and sal = (select hisal from emp, salgrade where ename = ‘FORD’

and sal between losal and hisal)

 
92.List the name, job, dname, Manager, salary, Grade dept wise.
SQL>Select ename, job, Mgr, sal, dname, grade from

emp, dept, salgrade where emp.deptno=dept.deptno

and sal between losal and hisal and emp.deptno in

(select distinct deptno from emp

group by emp.deptno) ;

  
93.List the emps name, job, salary, grade and dname except ‘CLERK’s and sort on the basis of highest salary.
SQL>select ename, job, sal, grade, dname from emp, dept, salgrade

where job <> ‘CLERK’ and dept.deptno = emp.deptno and

sal between losal and hisal order by sal desc ;

  
94.List the emps name, job and Manager who are without Manager.
SQL>select ename, job, mgr from emp where mgr is NULL ;
     
95.List the names of emps who are getting the highest salary dept wise.
SQL>Select ename, job, sal, dname from emp, dept

where emp.deptno=dept.deptno and emp.deptno in

(select distinct deptno from emp group by emp.deptno)

and sal in (select max(sal) from emp group by deptno) ;

 
96.List the emps whose salary is equal to average of maximum and minimum.
SQL>

TO RETRIEVE SAL WHICH IS = TO AVG. SAL

Select ename, job, sal from emp where

sal = (select avg(sal) from emp) ;

 

TO RETRIEVE SAL WHICH IS = TO AVG. OF MAX SAL AND MIN SAL

select * from emp where sal = (select (max(sal)+min(sal))/2 from emp) ;

  
97.List the no of emps in each dept where the number is more than 3.
SQL>Select deptno, count(deptno) from emp

group by deptno having count(deptno) > 3 ;

     
98.List the names of depts. Where at least 3 emps are working in each dept.
SQL>Select dname, count(emp.deptno) from emp,dept

where emp.deptno = dept.deptno 

group by dname having count(emp.deptno) > 3 ;

  
99.List the managers whose salary is more than his emps avg sal.
SQL>Select distinct A.* from emp A, emp B

where A.empno = B.mgr and

A.sal > (select avg(B.sal) from emp group by B.Mgr) ;

  
100.List the name, sal, comm. For those emps whose net pay is > or = any other employee salary of the company.
SQL>Select distinct A.ename, A.sal, A.comm from emp A, emp B where

 ((A.sal + A.sal*1.2) + (A.sal*1.2) -(A.sal*1.6 + 80)) >= (select avg(sal) from emp) ;

http://techhowdy.com/wp-content/uploads/2018/02/Practice-SQL-Queries-with-Solutions-For-Employee-Table-–-Part-2.pnghttp://techhowdy.com/wp-content/uploads/2018/02/Practice-SQL-Queries-with-Solutions-For-Employee-Table-–-Part-2-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,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,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 best practice,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 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 server practice queries pdf,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