In this tutorial, we will learn the step by step approach to solving complex SQL Queries.
We will solve 10 SQL queries , ranging from intermediate to complex levels of difficulty.
Here is the link to the video tutorial.
We are going to work on the Employee data. Below are the Create and Insert scripts for this table.
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FullName] [nvarchar](250) NOT NULL,
[DeptID] [int] NULL,
[Salary] [int] NULL,
[HireDate] [date] NULL,
[ManagerID] [int] NULL
) ;
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (1, 'Owens, Kristy', 1, 35000, '2018-01-22' , 3);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (2, 'Adams, Jennifer', 1, 55000, '2017-10-25' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (3, 'Smith, Brad', 1, 110000, '2015-02-02' , 7);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (4, 'Ford, Julia', 2, 75000, '2019-08-30' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (5, 'Lee, Tom', 2, 110000, '2018-10-11' , 7);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (6, 'Jones, David', 3, 85000, '2012-03-15' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (7, 'Miller, Bruce', 1, 100000, '2014-11-08' , NULL);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (9, 'Peters, Joe', 3, 11000, '2020-03-09' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (10, 'Joe, Alan', 3, 11500, '2020-03-09' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (11, 'Clark, Kelly', 2, 11500, '2020-03-09' , 5);
1 -
Problem Statement - Find employees with highest salary in a department.
Method 1 -
Hint - 1) Find maximum salary in each department and then find the employee whose salary is equal to the maximum salary.
2) Use of sub-query and Inner Join
SQL Query -
Select EmployeeID, Emp.DeptID, Salary
from dbo.Employees Emp
INNER JOIN
(Select DeptID, Max(Salary) as MaxSalary from dbo.EMployees
Group BY DeptID ) MaxSalEMp
ON Emp.DeptID = MaxSalEmp.DeptID
AND Emp.Salary = MaxSalEmp.MaxSalary
Method 2 -
Hint - 1) Use aggregate functions to rank the employees in order of their salary.
2) Filter on the salary with the highest rank.
SQL Query -
Select EmployeeID, FullName, DeptID, Salary from
(Select EmployeeID, FullName, DeptID, Salary ,
rank() OVER (Partition by DeptID Order BY Salary desc) as MaxSal
from dbo.Employees ) as Emp
Where Emp.MaxSal = 1
2 -
Problem Statement - Find employees with salary lesser than department average
This is similar to the above Problem Statement with minor differences. Instead of maximum salary, here we need to calculate average salary.
Hint - 1) Find average salary in each department and then find the employees whose salary is less than the average salary.
2) Use of sub-query and Inner Join
SQL Query -
Select EmployeeID, Emp.DeptID, Salary
from dbo.Employees Emp
INNER JOIN
(Select DeptID, Avg(Salary) as AvgSalary from dbo.EMployees
Group BY DeptID ) AvgSalEMp
ON Emp.DeptID = AvgSalEmp.DeptID
AND Emp.Salary < AvgSalEmp.AvgSalary
3
Problem Statement - Find employees with less than average salary in dept but more than average of ANY other Depts
Hint - 1) First part of the query is same as Problem Statement 2.
Find employees with less than average salary in dept but more than average of ANY other Depts
2) Use of ANY to calculate the second logic.
Find employees with less than average salary in dept but more than average of ANY other Depts
SQL Query -
Select EmployeeID, Emp.DeptID, Salary
from dbo.Employees Emp
INNER JOIN
(Select DeptID, Avg(Salary) as AvgSalary from dbo.Employees
Group BY DeptID ) AvgSalEMp
ON Emp.DeptID = AvgSalEmp.DeptID
AND Emp.Salary < AvgSalEmp.AvgSalary
WHERE Emp.Salary > ANY (Select Avg(Salary) from dbo.Employees Group By DeptID)
4
Problem Statement - Find employees with same salary
Hint - 1) Create two instances of Employees table and self join to compare salaries between different rows.
2) Fetch rows with same salary from both instances.
3) Eliminate records for same employeeid from both instances.
SQL Query -
SELECT s1.EmployeeID, s1.Salary
FROM dbo.Employees s1
INNER JOIN dbo.Employees s2
ON s1.Salary = s2.Salary
AND s1.EmployeeID <> s2.EmployeeID
5
Problem Statement - Find Dept where none of the employees has salary greater than their manager's salary
Hint - 1) Use self join to find employee and manager's salary.
2) Find departments where any employee has salary greater than his manager's salary.
3) Use NOT IN to find departments which are not part of the list in Step -2. This gives the remaining list of departments which do not have any employee whose salary is greater than his manager's salary.
SQL Query -
SELECT DISTINCT (DeptID) FROM dbo.Employees Employee
WHERE DeptID NOT IN
(SELECT Emp.DeptID FROM dbo.Employees AS Emp,
dbo.Employees AS Mgr WHERE Emp.ManagerID = Mgr.EmployeeID AND Emp.Salary > Mgr.Salary)
6
Problem Statement - Find difference between employee salary and average salary of department
Hint - 1) Use aggregate functions to find average salary for each department.
2) Use the above calculation in Select query and do a final calculation by subtracting it from employee salary.
This query can be written as a Single Select Statement.
SQL Query -
SELECT EMPLOYEEID, SALARY - AVG(salary)
OVER ( PARTITION BY DEPTID ) salary_diff
FROM Employees;
7
Problem Statement - Find Employees whose salary is in top 2 percentile in department
Hint - 1) Use aggregate function - Percent_Rank().
2) Filter employees whose Percent_Rank() is greater than 0.98.
SQL Query -
Select EmployeeID, FullName, DeptID, Salary from
(Select EmployeeID, FullName, DeptID, Salary ,
PERCENT_RANK() OVER (Partition by DeptID Order BY Salary desc) as Percentile
from dbo.Employees ) as Emp
Where Emp.Percentile > .98
8
Problem Statement - Find Employees who earn more than every employee in dept no 2
Method 1 -
Hint - 1) List salaries of all employees in the department.
2) Use ALL to compare employee salary with all values in the list above.
SQL Query -
Select * from Employees where salary > ALL (select salary from Employees where deptid=2);
Method 2 -
Hint - 1) Find maximum salary in each department.
2) Filter records where employee salary is greater than maximum salary calculated in Step 1 above.
SQL Query -
Select * from employees where salary > (select max(salary) from employees where deptid=2)
9
Problem Statement - Department names(with employee name) with more than or equal to 2 employees whose salary greater than 90% of respective department average salary
Hint - 1) Find average salary of department.
2) Count employees whose salary is greater then 90% of average salary calculated in Step 1 above.
Use CASE and SUM to implement this conditional count.
3) Filter departments where the count in step 2 above is greater than or equal to 2.
SQL Query -
select *
from (
select deptid,
employeeid,
sum(case when salary > avg_dept_sal * 0.9 then 1 else 0 end) over (partition by deptid) as empcnt
from (
select
e.deptid,
e.employeeid,
e.salary,
avg(salary) over (partition by e.deptid) as avg_dept_sal
from employees e
) t1
) t2
where empcnt >= 2
10
Problem Statement - Select Top 3 departments with at least two employees and rank them according to the percentage of their employees making over 100K in salary
Hint - 1) Count employees making over 100K salary.
2) Calculate percentage by dividing by total number of employees in the relative department.
3) Order by the percentage calculated in Step 2 above.
4) Use TOP to filter top 2 departments.
SQL Query -
SELECT
deptid,
SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END)/COUNT(employeeid),
COUNT(employeeid)
FROM employees
GROUP BY deptid
HAVING COUNT(*) > 2
ORDER BY 2 DESC