Thursday, August 1, 2013

SQL Queries: with solutings

TABLES

CASE STUDY

Create the following Tables:

LOCATION
Location_ID	Regional_Group
122	NEW YORK
123	DALLAS
124	CHICAGO
167	BOSTON

DEPARTMENT
Department_ID	Name	Location_ID
10	ACCOUNTING	122
20	RESEARCH	124
30	SALES	123
40	OPERATIONS	167

JOB
Job_ID	Function
667	CLERK
668	STAFF
669	ANALYST
670	SALESPERSON
671	MANAGER
672	PRESIDENT

EMPLOYEE
EMPLOYEE_ID	LAST_NAME	FIRST_NAME	MIDDLE_NAME	JOB_ID	MANAGER_ID	HIREDATE	SALARY	COMM	DEPARTMENT_ID
7369	SMITH	JOHN	Q	667	7902	17-DEC-84	800	NULL	20
7499	ALLEN	KEVIN	J	670	7698	20-FEB-85	1600	300	30
7505	DOYLE	JEAN	K	671	7839	04-APR-85	2850	NULL	30
7506	DENNIS	LYNN	S	671	7839	15-MAY-85	2750	NULL	30
7507	BAKER	LESLIE	D	671	7839	10-JUN-85	2200	NULL	40
7521	WARK	CYNTHIA	D	670	7698	22-FEB-85	1250	500	30

SALARY_GRADE

GRADE	LOWER_BOUND	UPPER_BOUND
I	4000.00	5000.00
II	3999.00	3000.00
III	2999.00	2000.00
IV	1999.00	1000.00
V	999.00	300.00

Queries based on the above tables:

Simple Queries:

List all the employee details

SELECT * FROM EMPLOYEE

List all the department details

SELECT * FROM DEPARTMENT

List all job details

SELECT * FROM JOB

List all the locations

SELECT * FROM LOCATION

List out first name,last name,salary, commission for all employees

SELECT LAST_NAME,FIRST_NAME,COMM FROM EMPLOYEE

List out employee_id,last name,department id for all employees and rename employee id as “ID of the employee”, last name as “Name of the employee”, department id as “department ID”

SELECT EMPLOYEE_ID AS "ID OF EMPLOYEE",

LAST_NAME AS "EMPLOYEE NAME",

DEPARTMENT_ID AS "DEPARTMENT ID"

FROM EMPLOYEE

List out the employees anuual salary with their names only.

SELECT LAST_NAME AS "EMPLOYEE NAME",

SALARY*12 AS "ANUAL SALARY"

FROM EMPLOYEE

Where Conditions:

List the details about “SMITH”

SELECT * FROM EMPLOYEE

WHERE LAST_NAME='SMITH'

List out the employees who are working in department 20

SELECT * FROM EMPLOYEE

WHERE DEPARTMENT_ID=20

List out the employees who are earning salary between 3000 and 4500

SELECT * FROM EMPLOYEE

WHERE SALARY BETWEEN 3000 AND 4500

List out the employees who are working in department 10 or 20

SELECT * FROM EMPLOYEE

WHERE DEPARTMENT_ID IN(20,30)

Find out the employees who are not working in department 10 or 30

SELECT * FROM EMPLOYEE

WHERE DEPARTMENT_ID NOT IN(10,30)

List out the employees whose name starts with “S”

SELECT * FROM EMPLOYEE

WHERE LAST_NAME LIKE 'S%'

List out the employees whose name start with “S” and end with “H”

SELECT * FROM EMPLOYEE

WHERE LAST_NAME LIKE 'S%H'

List out the employees whose name length is 4 and start with “S”

SELECT * FROM EMPLOYEE

WHERE LAST_NAME LIKE 'S____'

List out the employees who are working in department 10 and draw the salaries more than 3500

SELECT * FROM EMPLOYEE

WHERE DEPARTMENT_ID=10

AND SALARY >3500

list out the employees who are not receiving commission.

SELECT * FROM EMPLOYEE

WHERE COMM IS NULL

Order By Clause:

List out the employee id, last name in ascending order based on the employee id.

SELECT EMPLOYEE_ID,LAST_NAME

FROM EMPLOYEE

ORDER BY EMPLOYEE_ID

List out the employee id, name in descending order based on salary column

SELECT EMPLOYEE_ID,LAST_NAME

FROM EMPLOYEE

ORDER BY SALARY DESC

list out the employee details according to their last_name in ascending order and salaries in descending order

SELECT * FROM EMPLOYEE

ORDER BY LAST_NAME,SALARY DESC

list out the employee details according to their last_name in ascending order and then on department_id in descending order.

SELECT * FROM EMPLOYEE

ORDER BY LAST_NAME,DEPARTMENT_ID DESC

Group By & Having Clause:

How many employees who are working in different departments wise in the organization

SELECT DEPARTMENT_ID,COUNT(*) AS "NUMBER OF EMPLOYEE"

FROM EMPLOYEE

GROUP BY DEPARTMENT_ID

List out the department wise maximum salary, minimum salary, average salary of the employees

SELECT DEPARTMENT_ID,

MAX(SALARY) MAXUM_SALARY,

MIN(SALARY) MINMUM_SALARY,

AVG(SALARY) AVERAGE_SALARY

FROM EMPLOYEE

GROUP BY DEPARTMENT_ID

List out the job wise maximum salary, minimum salary, average salaries of the employees.

SELECT JOB_ID,

MAX(SALARY) MAXUM_SALARY,

MIN(SALARY) MINMUM_SALARY,

AVG(SALARY) AVERAGE_SALARY

FROM EMPLOYEE

GROUP BY JOB_ID

List out the no.of employees joined in every month in ascending order.

SELECT DATENAME(MM,HIREDATE) MONTH,COUNT(*) NoOfEmployee

FROM EMPLOYEE

GROUP BY DATENAME(MM,HIREDATE)

List out the no.of employees for each month and year, in the ascending order based on the year, month.

SELECT DATEPART(YYYY,HIREDATE) YEAR,

DATENAME(MM,HIREDATE) MONTH,

COUNT(*) NoOfEmployee

FROM EMPLOYEE

GROUP BY DATEPART(YYYY,HIREDATE),DATENAME(MM,HIREDATE)

List out the department id having at least four employees.

SELECT DEPARTMENT_ID,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE

GROUP BY DEPARTMENT_ID

HAVING COUNT(*)>4

How many employees in January month.

SELECT DATENAME(MM,HIREDATE) MONTH,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE

WHERE DATENAME(MM,HIREDATE)='FEBRUARY'

GROUP BY DATENAME(MM,HIREDATE)

How many employees who are joined in January or September month.

SELECT DATENAME(MM,HIREDATE) MONTH,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE

WHERE DATENAME(MM,HIREDATE)IN ('FEBRUARY','MARCH')

GROUP BY DATENAME(MM,HIREDATE)

How many employees who are joined in 1985.

SELECT DATEPART(YY,HIREDATE) YEAR,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE

WHERE DATEPART(YY,HIREDATE)=1985

GROUP BY DATEPART(YY,HIREDATE)

How many employees joined each month in 1985.

SELECT DATEPART(YY,HIREDATE) YEAR,

DATENAME(MM,HIREDATE) MONTH,

COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE

WHERE DATEPART(YY,HIREDATE)=1985

GROUP BY DATEPART(YY,HIREDATE),DATENAME(MM,HIREDATE)

How many employees who are joined in March 1985.

SELECT DATEPART(YY,HIREDATE) YEAR,

DATENAME(MM,HIREDATE) MONTH,

COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE

WHERE DATEPART(YY,HIREDATE)=1985

AND

DATENAME(MM,HIREDATE)='MARCH'

GROUP BY DATEPART(YY,HIREDATE),DATENAME(MM,HIREDATE)

Which is the department id, having greater than or equal to 3 employees joined in April 1985.

SELECT DEPARTMENT_ID,

COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE

WHERE DATEPART(YY,HIREDATE)=1985

AND

DATENAME(MM,HIREDATE)='APRIL'

GROUP BY DEPARTMENT_ID

HAVING COUNT(*)>=3

Sub-Queries

Display the employee who got the maximum salary.

SELECT * FROM EMPLOYEE

WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE

WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE))

Display the employees who are working in Sales department

SELECT * FROM EMPLOYEE

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENT

WHERE NAME='SALES')

Display the employees who are working as “Clerk”.

SELECT * FROM EMPLOYEE

WHERE JOB_ID IN (SELECT JOB_ID FROM JOB

WHERE FUNCTIONALITY='CLEARK')

Display the employees who are working in “New York”

SELECT * FROM EMPLOYEE

WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM DEPARTMENT

WHERE LOCATION_ID IN (SELECT LOCATION_ID FROM LOCATION

WHERE REGIONAL_GROUP='NEW YORK'))

Find out no.of employees working in “Sales” department.

SELECT * FROM EMPLOYEE

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENT

WHERE NAME='SALES')

Update the employees salaries, who are working as Clerk on the basis of 10%.

UPDATE EMPLOYEE SET SALARY=(SALARY+SALARY*10/100)

WHERE JOB_ID IN(SELECT JOB_ID FROM JOB

WHERE FUNCTIONALITY='CLERK')

Delete the employees who are working in accounting department.

DELETE FROM EMPLOYEE

WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM DEPARTMENT

WHERE NAME='ACCOUNTING')

Display the second highest salary drawing employee details.

SELECT * FROM EMPLOYEE

WHERE SALARY IN(SELECT MAX(SALARY) FROM EMPLOYEE

WHERE SALARY <(SELECT MAX(SALARY) FROM EMPLOYEE))

Display the Nth highest salary drawing employee details

SELECT A.SALARY FROM EMPLOYEE A

WHERE N-1=(SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B

WHERE A.SALARY<B.SALARY)

2^ND,3^RD AND 5^TH MAX SALARYS

SELECT A.SALARY FROM EMPLOYEE A

WHERE (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B

WHERE A.SALARY<B.SALARY)IN(1,2,4)

Sub-Query operators: (ALL,ANY,SOME,EXISTS)

List out the employees who earn more than every employee in department 30.

SELECT * FROM EMPLOYEE

WHERE SALARY>ALL(SELECT SALARY FROM EMPLOYEE

WHERE DEPARTMENT_ID=30)

(OR)

SELECT * FROM EMPLOYEE

WHERE SALARY>ALL(SELECT MAX(SALARY) FROM EMPLOYEE

WHERE DEPARTMENT_ID=30)

List out the employees who earn more than the lowest salary in department 30.

SELECT * FROM EMPLOYEE

WHERE SALARY>ANY(SELECT MIN(SALARY) FROM EMPLOYEE

WHERE DEPARTMENT_ID=30)

Find out which department does not have any employees.

SELECT DEPARTMENT_ID,NAME FROM DEPARTMENT

WHERE DEPARTMENT_ID NOT IN(SELECT DEPARTMENT_ID FROM EMPLOYEE)

Co-Related Sub Queries:

46. Find out the employees who earn greater than the average salary for their department.

SELECT * FROM EMPLOYEE E

WHERE SALARY>(SELECT AVG(SALARY) FROM EMPLOYEE

WHERE DEPARTMENT_ID=E.DEPARTMENT_ID

GROUP BY DEPARTMENT_ID)

Joins

Simple join

48.List our employees with their department names

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,E.DEPARTMENT_ID,NAME

FROM EMPLOYEE E

INNER JOIN DEPARTMENT D

ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

49.Display employees with their designations (jobs)

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,E.DEPARTMENT_ID,FUNCTIONALITY

FROM EMPLOYEE E

INNER JOIN JOB J

ON E.JOB_ID=J.JOB_ID

50.Display the employees with their department name and regional groups.

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,E.DEPARTMENT_ID,NAME,REGIONAL_GROUP

FROM EMPLOYEE E,DEPARTMENT D,LOCATION L

WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID

AND

D.LOCATION_ID=L.LOCATION_ID

(OR)

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,E.DEPARTMENT_ID,NAME,REGIONAL_GROUP

FROM EMPLOYEE E

INNER JOIN DEPARTMENT D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

INNER JOIN LOCATION L ON D.LOCATION_ID=L.LOCATION_ID

51.How many employees who are working in different departments and display with department name.

SELECT NAME AS "DEPARTMENT NAME",COUNT(*) AS "NUMBER OF EMPLOYEES"

FROM DEPARTMENT D

INNER JOIN EMPLOYEE E

ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

GROUP BY NAME

52.How many employees who are working in sales department.

SELECT NAME,COUNT(*) AS "NUMBER OF EMPLOYEES"

FROM DEPARTMENT D

INNER JOIN EMPLOYEE E

ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

GROUP BY NAME

HAVING NAME='SALES'

53.Which is the department having greater than or equal to 5 employees and display the department names in ascending order.

SELECT NAME AS "DEPARTMENT NAME",COUNT(*) AS "NUMBER OF EMPLOYEES"

FROM DEPARTMENT D

INNER JOIN EMPLOYEE E

ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

GROUP BY NAME

HAVING COUNT(*)>=5

54.How many jobs in the organization with designations.

SELECT FUNCTIONALITY,COUNT(*) AS "NO OF JOBS"

FROM JOB J

INNER JOIN EMPLOYEE E

ON E.JOB_ID=J.JOB_ID

GROUP BY FUNCTIONALITY

55.How many employees working in “New York”.

SELECT REGIONAL_GROUP,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE E

INNER JOIN DEPARTMENT D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

INNER JOIN LOCATION L ON D.LOCATION_ID=L.LOCATION_ID

GROUP BY REGIONAL_GROUP

HAVING REGIONAL_GROUP='DALLAS'

(OR)

SELECT REGIONAL_GROUP,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE E, DEPARTMENT D, LOCATION L

WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID

AND D.LOCATION_ID=L.LOCATION_ID

AND L.REGIONAL_GROUP='DALLAS'

GROUP BY REGIONAL_GROUP

Non – Equi Join:

56.Display employee details with salary grades.

SELECT LAST_NAME,SALARY,GRADE

FROM EMPLOYEE E,SALARY_GRADE S

WHERE SALARY BETWEEN LOWER_BOUND AND UPPER_BOUND

57.List out the no. of employees on grade wise.

SELECT GRADE,COUNT(*)

FROM SALARY_GRADE S,EMPLOYEE E

WHERE SALARY BETWEEN LOWER_BOUND AND UPPER_BOUND

GROUP BY GRADE

ORDER BY GRADE DESC

58.Display the employ salary grades and no. of employees between 2000 to 5000 range of salary.

SELECT GRADE,COUNT(*)

FROM SALARY_GRADE S,EMPLOYEE E

WHERE SALARY BETWEEN LOWER_BOUND AND UPPER_BOUND

AND

LOWER_BOUND>=2000 AND UPPER_BOUND<=5000

GROUP BY GRADE

ORDER BY GRADE

Self Join:

59.Display the employee details with their manager names.

SELECT E.EMPLOYEE_ID "EMPLOYEE ID",

E.LAST_NAME "EMPLOYEE NAME",

M.LAST_NAME "MANAGER NAME"

FROM EMPLOYEE E,EMPLOYEE M

WHERE M.EMPLOYEE_ID=E.MANAGER_ID

(OR)

SELECT E.EMPLOYEE_ID "EMPLOYEE ID",

E.LAST_NAME "EMPLOYEE NAME",

M.LAST_NAME "MANAGER NAME"

FROM EMPLOYEE E

INNER JOIN EMPLOYEE M

ON E.MANAGER_ID=M.EMPLOYEE_ID

60.Display the employee details who earn more than their managers salaries.

SELECT E.LAST_NAME "EMPLOYEE NAME",

E.SALARY "EMPLOYEE SALARY",

M.LAST_NAME "MANAGER NAME",

M.SALARY "MANAGER SALARY"

FROM EMPLOYEE E,EMPLOYEE M

WHERE M.EMPLOYEE_ID=E.MANAGER_ID

AND

E.SALARY>M.SALARY

(OR)

SELECT E.LAST_NAME "EMPLOYEE NAME",

E.SALARY "EMPLOYEE SALARY",

M.LAST_NAME "MANAGER NAME",

M.SALARY "MANAGER SALARY"

FROM EMPLOYEE E

INNER JOIN EMPLOYEE M

ON E.MANAGER_ID=M.EMPLOYEE_ID

AND

E.SALARY>M.SALARY

61.Show the no. of employees working under every manager.

SELECT M.LAST_NAME,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE E,EMPLOYEE M

WHERE E.MANAGER_ID=M.EMPLOYEE_ID

GROUP BY M.LAST_NAME

(OR)

SELECT M.LAST_NAME,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE E

INNER JOIN EMPLOYEE M

ON M.EMPLOYEE_ID=E.MANAGER_ID

GROUP BY M.LAST_NAME

(ABOVE TWO SOLUTIONS ARE BASED ON MANAGER NAME)

(BELOW SOLUTIONS IS BASED ON MANAGER ID)

SELECT M.MANAGER_ID,COUNT(*) "NUMBER OF EMPLOYEES"

FROM EMPLOYEE E

INNER JOIN EMPLOYEE M

ON M.EMPLOYEE_ID=E.MANAGER_ID

GROUP BY M.MANAGER_ID

Outer Join:

61.Display employee details with all departments.

SELECT EMPLOYEE_ID,LAST_NAME,HIREDATE,SALARY,E.DEPARTMENT_ID,NAME

FROM EMPLOYEE E

LEFT OUTER JOIN DEPARTMENT D

ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

62.Display all employees in sales or operation departments.

SELECT EMPLOYEE_ID,LAST_NAME,E.DEPARTMENT_ID,NAME

FROM EMPLOYEE E

LEFT OUTER JOIN DEPARTMENT D

ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

AND D.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENT

WHERE NAME IN('SALES','OPERATIONS'))

Set Operators:

63.List out the distinct jobs in Sales and Accounting Departments.

SELECT FUNCTIONALITY