SQL Interview Cheat Sheet - SUBQUERY CHEAT SHEET

 

Single Row Subquery

Employees earning above average salary.

SELECT *
FROM Employee
WHERE Salary >
(
   SELECT AVG(Salary)
   FROM Employee
);

---

Multi Row Subquery

Employees in HR and IT.

SELECT *
FROM Employee
WHERE DeptID IN
(
   SELECT DeptID
   FROM Department
   WHERE DeptName IN ('HR','IT')
);

---

Correlated Subquery

Highest paid employee in each department.

SELECT *
FROM Employee e
WHERE Salary =
(
   SELECT MAX(Salary)
   FROM Employee
   WHERE DeptID=e.DeptID
);

Most Frequently Asked Interview Question

---

EXISTS

Returns TRUE if rows exist.

SELECT *
FROM Customers c
WHERE EXISTS
(
   SELECT 1
   FROM Orders o
   WHERE c.CustomerID=o.CustomerID
);

---

NOT EXISTS

Customers with no orders.

SELECT *
FROM Customers c
WHERE NOT EXISTS
(
   SELECT 1
   FROM Orders o
   WHERE c.CustomerID=o.CustomerID
);

---

2nd Highest Salary

SELECT MAX(Salary)
FROM Employee
WHERE Salary <
(
   SELECT MAX(Salary)
   FROM Employee
);

---

Better Approach

SELECT Salary
FROM
(
   SELECT Salary,
          DENSE_RANK() OVER
          (ORDER BY Salary DESC) rk
   FROM Employee
)x
WHERE rk=2;

---