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