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 3

 Q. NoPractice SQL Queries with Solutions For Employee Table  
101.List the emps whose sal < his Manager but more than other Manager
SQL>Select distinct A.* from emp A, emp B, emp C

 where A.sal < B.sal and A.mgr = B.empno and

  A.sal > C.sal and A.mgr not in (select empno

   from emp where empno <> B.Empno and job = ‘MANAGER’) ;

Also Read: Sample Hadoop HDFS Interview Questions and Answers
102.List the employee names and his annual salary dept wise.
SQL>Select ename, dname, sal*12 from emp, dept where

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

and emp.deptno = dept.deptno ;

OR

Select deptno, ename, sal*12 from emp group by deptno,ename,sal ;

103.Find out least 5 earners of the company.
SQL>select * from emp where rownum < 6 order by sal asc
104.Find out the no of emps whose salary is > their Manager salary.
SQL>select count(A.empno) from emp A, emp B where

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

105.List the Mgrs who are not working under ‘PRESIDENT’ but are working under other Manager.
SQL>select A.ENAME, A.JOB from emp A, emp B where

A.Mgr = B.empno and B.ename <> ‘PRESIDENT’ AND A.job = ‘MANAGER’ ;

106.List the records from emp whose deptno is not in dept
SQL>HOW CAN IT BE POSSIBLE???

[A REFERENCE KEY VALUE WITHOUT A PRIMARY KEY VALUE.]

 

select * from emp where deptno not in (select deptno from dept)

107.List the name, sal, comm. And net pay is more than any other employee.
SQL>select empno, sal, comm, sal+sal*1.2+800 Net from emp where

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

108.List the enames who are retiring after ’31-DEC-89’ the max job period is 20Y.
SQL>Select empno, ename, hiredate, sysdate,

months_between(sysdate,hiredate)/12 Exp from emp

where months_between(sysdate,hiredate)/12 > 20

order by hiredate ;

 

ABOVE QUERY IS WORKING BUT BELOW QUERY IS NOT. IN BELOW QUERY I  GAVE DATE() IN PLACE OF SYSDATE.

 

select emp.*, months_between(’31-DEC-89′,hiredate)/12 Retirement_Date

from emp where months_between(’31-DEC-89′,hiredate) / 12 > 20 ;

months_between(…)/12 giving o/p greater than 100.

109.List those emps whose sal is odd value.
SQL>Select * from emp where mod(sal,2) <> 0 ;
110.List the emps whose sal contain 3 digits
SQL>Select * from emp where length(sal) = 3 ;
 
111.List the emps who joined in the month of ‘DEC’
SQL>Select * from emp where hiredate like ‘%DEC%’ ;
112.List the emps whose names contains ‘A’
SQL>select ename from  emp where ename like ‘%A%’ ;
113.List the emps whose deptno is available in his salary.
SQL>select ename, sal, deptno from emp

where substr(sal,length(sal)-1,2) = deptno

or substr(sal,length(sal)-2,2) = deptno

or substr(sal,length(sal)-3,2) = deptno ;

 
114.List the emps whose first 2 chars from hiredate = last 2 chars of salary.
SQL>select ename, sal, hiredate from emp

                 where substr(hiredate,1,2) = substr(sal,length(sal)-1,2) ;

115.

List the emps whose 10% of sal is equal to year of Joining

SQL>select ename,sal , sal * .1, hiredate from emp

where sal * .1 like substr(hiredate,length(hiredate)-1,2) ;

116.List first 50% of chars of ename in lower case and remaining are upper case.
SQL>select ename, lower(substr(ename,1,length(ename)/2)),

upper(substr(ename,(length(ename)/2)+1,length(ename)))  from emp ;

117.List the dname whose no of emps is = to no of chars in the dname.
SQL>select dname, length(dname) Len from dept where deptno =

(select emp.deptno from emp, dept where

emp.deptno = dept.deptno having Count(empno) in

(select length(dname) from dept group by dname)

group by emp.deptno) ;

 

   THIS QUERY WILL WORK AFTER MODIFING EMP TABLE. AT PRESET THERE ARE NO SUCH DNAME WHERE NO. OF EMPS = NO OF CHARS IN THE DNAME. TO GET THE OUTPUT, I HAD TO CHANGE ONE SALES DEPT(DEPTNO 30) EMP TO SOME OTHER DEPT – SO THAT THE NO OF CHARS IN SALES DEPT(5) IS = NO OF EMPS OF THAT DEPT. [AT PRESENT THERE ARE 6 EMPS]

 

IF U DON’T CHANGE THE ABOVE, U GET THE WRONG O/P FOR THE SAME.

 

select dname, length(dname) Len from dept where deptno =

(select emp.deptno from emp, dept where

emp.deptno = dept.deptno having Count(empno) in

(select length(dname) from dept group by dname)

group by emp.deptno) ;

O/P : Reserch   ,   8   Which is a wrong O/P.

 

 

select dname, count(empno) from emp, dept

where emp.deptno = dept.deptno

group by dname ;

O/P : Dname, count of emps

118.List the emps those who joined in company before 15th of the month.
SQL>
 

 

 

select * from emp where hiredate in

(select hiredate from emp where hiredate > (select

hiredate from emp where hiredate like ’14-%’)) ;

 

 

     NO ROWS SELECTED

 

119.List the dname, no of chars of which is no of emp’s in any other dept
SQL>
select dname, length(dname) Len from dept where deptno IN

(select emp.deptno from emp, dept where emp.deptno = dept.deptno

having Count(empno) in (select count(empno) from

emp group by deptno) group by emp.deptno) ;

 

select dname, length(dname) from dept ;

 

120.List the emps who are working as managers
SQL>select * from emp where empno in (select mgr from emp) ;
 
121.List the name of dept where highest no of emps are working
SQL>select dname, emp.deptno, count(empno) from emp, dept

where emp.deptno = dept.deptno having count(empno) =

(select max(count(empno)) from emp group by deptno)

group by emp.deptno, dname ;

122.Count the no of emps who are working as ‘Managers’ (using set option)
SQL>Select * from emp where empno in (select empno from emp)

Union (select Mgr from emp)

GIVING ERROR : ‘Query block has incorrect number of result columns’

123.List the emps who joined in the company on the same date
SQL>select * from emp where hiredate in (select hiredate from emp

having count(hiredate) > 1 group by hiredate) ;

 
124.List the details of the emps whose grade is equal to one tenth of sales dept.
SQL>select * from emp, salgrade where grade = deptno/10

and deptno = (select deptno from dept where dname=’SALES’)

and sal between losal and hisal ;

 

OR

 

select * from emp, salgrade where grade = deptno/10

and sal between losal and hisal  ;

125.List the name of the dept where more than avg. no of emps are working
SQL>select dname, dept.deptno, count(empno) from emp2, dept

where emp2.deptno in (select deptno from emp group by emp.deptno)

and emp2.deptno = dept.deptno group by dept.deptno, dname

having count(empno) >= (select count(deptno) from dept) ;

126.List the managers name who is having max no of emps working under him
SQL>select ename from emp where empno =

(select mgr from emp having count(empno) =

(select max(count(empno)) from emp group by mgr) group by mgr) ;

127.List the ename and sal is increased by 15% and expressed as no of dollars.
SQL> select ename, (sal + sal * 0.15), decode(sal, sal + sal * 0.15,

(sal + sal * 0.15)/34) NewSal from emp  ;

128.Produce the output of EMP table ‘EMP_AND_JOB’ for ename and job
SQL>select ename || ‘_AND_’ || job from emp ;
 
129.Produce the following output from EMP

EMPLOYEE

—————-

SMITH(clerk)

ALLEN(salesman)

SQL>select ename || ‘(‘ || lower(job) ||’)’ EMPLOYEE from emp ;
130.List the emps with hiredate in format June 4,1988.
SQL>select ename, 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.
SQL>
select ename, sal, decode(sal,(sal>1500), ‘just salary’,

1500,’On target’,(sal<1500),’Below 1500′,sal) from emp ;

 

 MISSING RIGHT PARENTHESIS.

132.Write a query to return the day of the week for any date entered in format ‘DD-MM-YY’
SQL>select to_char(hiredate, ‘day’) from emp ;

OR

select to_char(sysdate, ‘day’) from dual

133.Wrote a query to calculate the length of service of any employee with the company, use DEFINE to avoid repetitive typing of functions
SQL>DEFINE ????
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/la’, ‘99/98’

SQL>
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 hiredate and the first pay date. Sort on hiredate.
SQL>
136.Count the no of characters without considering spaces for each name.
SQL>Select ename, length(ename) from emp ;
137.Find out the emps who are getting decimal value in their sal without using like operator.
SQL> 

Select ename, sal, to_char(substr(to_char(sal),3,1)) from emp ;

Select ename, sal from emp where

to_char(substr(to_char(sal),-3,1)) =’.’

 

138.List those emps whose salary contains first four digit of their deptno. ???
SQL>
139.List those managers who are getting less than his emps salary.
SQL>select distinct B.* from emp A, emp B where

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

140.Print the details of all the emps who are sub-ordinates to BLAKE.
SQL>select * from emp where mgr =

(select empno from emp where ename=’BLAKE’) ;

141.List the emps who are working as managers using co-related sub-query
SQL>Using Sub Query :

Select * from emp where empno in(select mgr from emp) ;  

 

Using Co-Releated Query :

Select distinct A.* from emp A, emp B where A.empno =B.Mgr ;

 

Using Co-Releated Sub Query :

Select distinct A.* from emp A, emp B

where A.empno in(select B.mgr from emp)

142.List the emps whose Mgr name is ‘JONES’ and also with his Manager Name.
SQL>select distinct A.*, B.ename from emp A, emp B where A.mgr =

(select empno from emp where ename=’JONES’) and B.ename=’JONES’ ;

 
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.
SQL>select emp.*, sal*12 Ann_Sal from emp where sal*12 >= 30000 ;
144.Find out how many managers are there in the company.
SQL>Select count(*) from emp where empno in(select mgr from emp) ;
145.Find avg sal and avg total remuneration for each job type. Remember salesman earn commission.
SQL>Select job, count(empno),avg(sal), sum(sal) from emp group by job ;

 

OR

 

Select job, avg(sal), sum(decode(comm,null,0)+sal) from emp group by job ;

 

OR

Select job, avg(decode(comm,null,0)+sal),

sum(decode(comm,null,0)+sal) from emp group by job ;

146.Check whether all the emps numbers are indeed unique.
SQL>
 
147.List the emps who are drawing  less than 1000. sort the output by salary.
SQL>Select * from emp where sal < 1000 order by sal ;
148.List the employee name, job, annual sal, deptno, dept name, and grade who earn 36000 a year or who are not CLERKS.
SQL>Select ename, job, sal*12 AnnSal, emp.deptno, dname, grade

from emp, dept, salgrade where sal*12 > 36000 and

job <> ‘CLERK’ and sal between losal and hisal and

dept.deptno = emp.deptno ;

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.
SQL>
select job from emp where hiredate in (select hiredate from

emp where hiredate > ’01-JUL-82′) and hiredate in (select hiredate

from emp where hiredate > ’01-JUL-84′)

NO ROWS SELECTED

150.Find out the emps who joined in the company before their managers.
SQL>select A.empno, A.hiredate, A.mgr from emp A, emp B

where A.mgr = B.empno and A.hiredate < B.hiredate ;

 

http://techhowdy.com/wp-content/uploads/2018/02/Practice-SQL-Queries-with-Solutions-For-Employee-Table-–-Part-3.pnghttp://techhowdy.com/wp-content/uploads/2018/02/Practice-SQL-Queries-with-Solutions-For-Employee-Table-–-Part-3-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