codewithamod
Top 10 Advanced SQL Interview Ques ons
This document contains the most frequently asked and practically important SQL interview
questions for professionals. Each question is explained in depth with business logic, SQL
syntax, edge cases, and performance considerations. All examples are based on MySQL and
commonly used Employee–Department schemas.
Assumed Database Tables
EMPLOYEE
- emp_id (Primary Key)
- emp_name
- salary
- department_id (Foreign Key)
- manager_id
- hire_date
DEPARTMENT
- department_id (Primary Key)
- department_name
1) Create department table
a) CREATE TABLE department (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL
);
2) Create Employee table
a) CREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
department_id INT,
manager_id INT,
hire_date DATE,
CONSTRAINT _department
FOREIGN KEY (department_id)
REFERENCES department(department_id),
CONSTRAINT _manager
FOREIGN KEY (manager_id)
REFERENCES employee(emp_id));
3) INSERT DATA INTO DEPARTMENT
a) INSERT INTO department (department_id, department_name) VALUES
(1, 'Engineering'),
(2, 'HR'),
(3, 'Finance'),
(4, 'Sales'),
(5, 'Marke ng');
4) INSERT DATA INTO EMPLOYEE
a) INSERT INTO employee (emp_id, emp_name, salary, department_id,
manager_id, hire_date) VALUES
(1, 'Amod', 150000, 1, NULL, '2018-01-10'),
(2, 'Bittu', 140000, 2, NULL, '2019-03-15'),
(3, 'Ravi', 145000, 3, NULL, '2017-06-01');
1. Find the Nth Highest Salary
Problem:
Find the Nth highest salary from the employee table.
Query (MySQL):
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT N-1, 1;
Explanation:
- DISTINCT removes duplicate salary values.
- ORDER BY salary DESC sorts salaries from highest to lowest.
- LIMIT N-1,1 skips the irst N-1 rows and fetches the Nth row.
Edge Cases:
- If N is greater than the number of distinct salaries, the query returns no result.
- This approach is MySQL-speci ic and not portable to all databases.
Performance Notes:
- Ensure salary column is indexed for faster sorting.
2. Find Highest Salary in Each Department
Problem:
Retrieve the maximum salary paid in each department.
Query:
SELECT d.department_name, MAX([Link]) AS max_salary
FROM employee e
JOIN department d ON e.department_id = d.department_id
GROUP BY d.department_name;
Explanation:
- GROUP BY groups employees by department.
- MAX() inds the highest salary per group.
- JOIN is required to fetch department name.
Interview Follow-up:
To fetch employee name along with salary, use a correlated subquery.
3. Employees Earning More Than Their Manager
Problem:
Find employees whose salary is greater than their manager’s salary.
Query:
SELECT e.emp_name
FROM employee e
JOIN employee m ON e.manager_id = m.emp_id
WHERE [Link] > [Link];
Explanation:
- Self join is used because manager details are stored in the same table.
- Comparison happens between employee salary and manager salary.
Business Use Case:
- Used to detect hierarchy anomalies in payroll systems.
4. Departments With No Employees
Problem:
Identify departments that currently have no employees.
Query:
SELECT d.department_name
FROM department d
LEFT JOIN employee e ON d.department_id = e.department_id
WHERE e.emp_id IS NULL;
Explanation:
- LEFT JOIN ensures all departments are included.
- NULL employee means no matching employee exists.
Why LEFT JOIN:
INNER JOIN would eliminate departments without employees.
5. Find Duplicate Employees
Problem:
Identify duplicate employee records based on name and department.
Query:
SELECT emp_name, department_id, COUNT(*)
FROM employee
GROUP BY emp_name, department_id
HAVING COUNT(*) > 1;
Explanation:
- GROUP BY clusters similar records.
- HAVING ilters aggregated results.
- COUNT(*) > 1 indicates duplication.
Follow-up:
Often followed by questions on deleting duplicates safely.
6. Second Highest Salary in Each Department
Problem:
Fetch the second highest salary employee per department.
Query (MySQL 8+):
SELECT department_id, emp_name, salary
FROM (
SELECT emp_name, department_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS
rnk
FROM employee
)t
WHERE rnk = 2;
Explanation:
- Window function avoids complex subqueries.
- DENSE_RANK ensures no salary gaps.
- PARTITION BY processes each department independently.
7. Employees Hired in the Last 6 Months
Problem:
Retrieve employees hired in the last six months.
Query:
SELECT *
FROM employee
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
Explanation:
- CURDATE() returns current date.
- DATE_SUB subtracts 6 months.
Real-world Usage:
Used in HR dashboards and probation tracking.
8. Top 3 Salaries in Each Department
Problem:
Fetch top 3 highest-paid employees per department.
Query:
Uses DENSE_RANK() with rank <= 3.
Explanation:
- Window functions scale better than subqueries.
- Commonly used in performance reviews.
9. Employees Without Managers
Problem:
Identify employees who do not report to anyone.
Query:
SELECT emp_name
FROM employee
WHERE manager_id IS NULL;
Explanation:
- NULL manager_id indicates top-level roles.
- Usually CEO or department heads.
10. Departments With Average Salary Greater Than Company Average
Problem:
Find departments whose average salary is higher than the company-wide average.
Query:
SELECT department_id, AVG(salary)
FROM employee
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employee);
Explanation:
- Subquery computes company average.
- HAVING ilters aggregated values.
Business Insight:
Useful for compensation benchmarking.