List of SQL Queries Related To Employee Table in Database For Practice contains most common SQL Queries related to manipulate employee table. We have tried to visit many forums and blogs to gather Questions related to SQL Queries Related on Employee Table and provide them to you in this blog post on SQL Queries Related To Employee Table in Database.

These Queries are put together from Internet based research and do not belong to particular individual. Our aim is to help people explore different methods of doing tasks in SQL. We thank the SQL Developer Community to constantly providing solutions to such Complex SQL Questions.

SQL QUERIES RELATED TO EMPLOYEE TABLE

1) Select all the Information Regarding the Employee Table

select * from empm

select * from deptm


2) List all the employees who have a sal between 1000 and 2000.

select ename from empm

where sal between 1000 and 2000


3) Display all the different job types.

select distinct job

from empm


4) List the details of the employees in departments 10 and 20 in alphabetical order.


select *

from empm

where deptno in (10,20)


5) List names and jobs of all clerks in department 20.

select ename,job

from empm

where job=’clerk’ and deptno=20

Also Read: Most Popular WhatsApp Display Picture for Your Phone


6) Display all employee names, which have TH and LL in them.

select ename

from empm

where ename like ‘%TH%’ or ename like ‘%LL%’


7) List the following details for all employees who have a manager.


select *

from empm

where mgr is not null


8) Display name and total remuneration for all employees.

select ename,sal+isnull(comm,0) “total salary”

from empm

select ename,hiredate from empm

Also Read: Practice SQL Queries with Solutions For Employee Table – Part 1


9) Display all employees who were hired during 1993.

select ename

from empm

where year(hiredate)=’1993′– between 1980/01/01 and 1980/12/31


10) Display name, annual sal and commission of all salespeople whose monthly salary is greater than their commission. The output should be order by salary, highest first. If two employees have the same sal sort by employee names, within the highest salary order.


select e.ename,e.sal*12 “annual sal”,e.comm

from empm e

inner join deptm d on e.deptno=d.deptno

where dname=’sales’ and sal>comm

order by sal desc,ename


11) List the employees name and salary incremented by 15% and express as whole number of Rupees.

select ename,round(sal+sal*(15/100),5) “new salary”

from empm


12) Display each employees name and hire date from dept 20. Make sure that you specify the alias ‘Date Hired’ after your expression otherwise the formatted column will wrap; it uses a width of 80 characters which is the default for character columns.


select ename,hiredate “Date Hired”

from empm

where deptno=20


13) Display each employee name with hire date, and salary review data. Assume review date is one year after hire date. Order the Output in Ascending.

select ename,hiredate “date hired”,dateadd(yy,+1,hiredate) “Review Date”

from empm


14) Print a list of employees displaying ‘just salary if more than 1500’. If exactly 1500 display ‘On Target’, if less than 1500 display ‘Below Target’


select ename,empno,

case when sal>1500 then ‘just salary’

when sal=1500 then ‘on target’

when sal<1500 then ‘below target’

end

as “position”

from empm


15) Write a query that will return the Day of the week (i.e. Monday) for any date entered in the format DD: MM.YY.

select datename(dw,hiredate)

from empm

Also Read: Practice SQL Queries with Solutions For Employee Table – Part 3


16) Find the minimum sal of the Employees.

select min(sal)

from empm


17) Find the minimum, maximum and avg sal of all employees.


select min(sal) “minimum sal”,max(sal) “max sal”,avg(sal) “avg sal”

from empm


18) List the minimum and maximum sal for each job type.

select job, min(sal) “min sal”,max(sal) “max sal”

from empm

group by job


19) Find out how many managers there are without listing them.

select count(mgr) “total managers”

from empm


20) Find the avg sal and avg total remuneration for each job type.

select job,avg(sal) “avg sal”,avg(sal+isnull(comm,0)) “avg total remuneration”

from empm

group by job


21) Find out the diff between highest and lowest sal.

select max(sal)-min(sal) “difference”

from empm


22) Find all dept, which have more than 3 employees.


select d.dname

from deptm d

inner join empm e

on d.deptno=e.empno

Also Read: Practice SQL Queries with Solutions For Employee Table – Part 4


23) Check whether all employee numbers are indeed unique.

select isnumeric(empno)

from empm


24) List lowest paid employees working for each manager, exclude any group Where the minimum sal is less than 1000. Sort the output by sal.

select min(sal)

from empm

except select min(sal) from empm

where sal<200

group by mgr


25) Display all EMP names and their dept names in dept name order.


select e.ename,d.dname

from empm e,deptm d

order by d.dname


26) Display all EMP names, dept number and name.

select e.ename,d.dname,d.deptno

from empm e,deptm d


27) Display the name, location and dept of employees whose sal is more than 1500 a month.


select e.ename,d.loc,d.deptno

from empm e,deptm d

where e.sal>1500


28) Produce a list showing employees’ sal grades.

select e.ename,g.grade

from empm e,salgradem g


29) Show only employees on Grade 3.

select e.ename,e.sal

from empm e,salgradem g

where g.grade=3

select * from salgradem


30) Show all employees in Dallas.


select e.ename “employees working in dallas”

from empm e,deptm d

where d.loc=’dallas’


31) List the Employee Name, job, sal, grade and dept name for everyone in the company except clerks. Sort on salary, displaying the highest sal first.

select e.ename,e.job,e.sal,g.grade,d.dname

from empm e,deptm d,salgradem g

except

select e.ename,e.job,e.sal,g.grade,d.dname from empm e,deptm d,salgradem g where e.job=’clerk’

where e.job not in (‘clerk’) order by e.sal desc


32) List the following details of EMP whose earn 36000 a year or who are clerks.


select ename,empno

from empm

where sal*12>36000 or job=’clerk’


33) Display the dept that has no employees.

select distinct(d.dname)

from deptm d left outer join empm e

on e.deptno=d.deptno


34) List all EMP by name and number along with their manager’s name and number.

select e.ename,e.empno,e.mgr,m.ename “manager name”

from empm e,empm m

where e.mgr=m.empno


35) Modify solution to above question to display KING who has no manager.

select e.ename

from emp e

where e.mgr is null


36) Find all employees who joined the company before their manager.

select e.ename

from empm e,empm m

where year(e.hiredate)>year(m.hiredate)


37) Find the Employees who earn the highest sal in each job type Sort in Desc Sal order.

select ename,job,max(sal) “maximum salary”

from empm

group by ename,job

order by max(sal) desc


38) Find the Employees who earn the minimum sal for their job. Display the result in ascending order of salary.

select ename,job,min(sal) “minimum salary”

from empm

group by ename,job

order by min(sal) desc


39) Find the most recently hired employees in each dept. Order by hiredates.

select deptno,min(hiredate)

from empm

group by deptno


40) Show the following details for any EMP who earns a sal greater than the avg for their dept. sort in deptno order.

select ename,deptno

from empm

where sal in (select avg(sal) from empm group by deptno)

order by deptno


41) Display the following information for the dept with the HIGHEST annual remuneration bill.

select ename,sal

from empm

where sal in (select max(sal) from empm group by deptno)


42) Who are the Top 3 Earners of the Company?

select top 3 sal ,ename from empm


43) Write a Query to display an ‘*’ against the row of the most recently hired employee. Display ENAME, HIREDATE and column.

SELECT CONVERT(VARCHAR(10),ENAME)+’*’ AS “MODIFIED NAME” ,HIREDATE FROM EMPm WHERE DATEDIFF(DD,HIREDATE,GETDATE()) <=(SELECT MIN(DATEDIFF(DD,HIREDATE,GETDATE()))FROM EMPm )


44) Display the Number of Months between getdate( ) and Hiredate of the EMP Table.

select datediff(mm,hiredate,getdate()) “number of months”

from empm


45) Add 2 Months for the existing Sysdate and Display the Result of the Employee Table and also display the Next Day of the Hiredate.

select dateadd(mm,+2,getdate()) “result”,dateadd(dd,+1,hiredate) “1st day”

from empm


46) Display all the Employees who earn less than their managers.

select e.ename

from empm e

where sal>(select sal from empm m where m.empno=e.mgr)


47) Display the Lowest Sal of Each Department using subquery.

select deptno,sal

from empm

where sal in (select min(sal) from empm group by deptno)


48) Display the Employees who earn more than the lowest sal in Dept 30

select ename,sal

from empm

where sal>(select min(sal) from empm where deptno=30)


49) Display the Employees who earn more than every employee in the Department 30.

select ename,sal

from empm

where sal>(select max(sal) from empm where deptno=30)


50) Display the name, job and hiredate for employees whose sal is greater than the highest sal in any sales department.

select ename,job,hiredate

from empm where sal>(select max(e.sal) from empm e,deptm d where d.dname=’sales’)


51) Display all the Employees whose department is not in Department table.

select e.ename,d.dname

from empm e,deptm d

where d.dname not in (select dname from deptm)

http://techhowdy.com/wp-content/uploads/2018/05/List-of-SQL-Queries-Related-To-Employee-Table-in-Database-1024x614.pnghttp://techhowdy.com/wp-content/uploads/2018/05/List-of-SQL-Queries-Related-To-Employee-Table-in-Database-150x150.pngDemonDatabase Programmingemployee table queries in sql,interview questions sql queries employee table,sql queries based on employee table,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 on employee and department table,sql queries on employee table pdf,sql queries using employee table,sql queries with employee table,sql query employee table,sql query to create employee table,sql server queries on employee tableList of SQL Queries Related To Employee Table in Database For Practice contains most common SQL Queries related to manipulate employee table. We have tried to visit many forums and blogs to gather Questions related to SQL Queries Related on Employee Table and provide them to you in this...Latest technology news