SQL for Interviews: The 12 Query Patterns That Cover 95% of Questions

SQL is tested at virtually every Indian tech company — from TCS and Infosys screening tests to Freshworks and Razorpay technical rounds. Yet most CS students treat it as an afterthought.

This is a mistake. SQL questions are quick wins. You can master the interview-relevant patterns in a week. This guide covers all 12 of them.

We'll use a single sample database throughout so each query is immediately understandable.

---

The Sample Database

-- Employees
CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(100),
  dept_id    INT,
  salary     INT,
  manager_id INT,  -- references employees.id
  join_date  DATE
);

-- Departments
CREATE TABLE departments (
  id    INT PRIMARY KEY,
  name  VARCHAR(100),
  city  VARCHAR(100)
);

-- Orders (for e-commerce examples)
CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT,
  amount      INT,
  order_date  DATE,
  status      VARCHAR(20)
);

---

Pattern 1: Basic Filtering with WHERE

-- Employees with salary > 50,000 in the Engineering department
SELECT e.name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'Engineering'
  AND e.salary > 50000
ORDER BY e.salary DESC;

Key interview rules:

• WHERE filters rows before grouping
• HAVING filters rows after grouping (see Pattern 5)
• Use AND, OR, NOT to combine conditions
• BETWEEN 40000 AND 60000 is inclusive on both ends
• IN ('Engineering', 'Marketing') is cleaner than multiple ORs

---

Pattern 2: Aggregate Functions

The 5 aggregates you must know cold:

SELECT
  dept_id,
  COUNT(*)          AS total_employees,   -- count all rows
  COUNT(manager_id) AS employees_with_mgr, -- count non-NULL values
  AVG(salary)       AS avg_salary,
  MAX(salary)       AS max_salary,
  MIN(salary)       AS min_salary,
  SUM(salary)       AS total_payroll
FROM employees
GROUP BY dept_id;

Common trap: COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values. Know which you need.

---

Pattern 3: GROUP BY and HAVING

-- Departments with more than 5 employees and average salary > 60,000
SELECT
  d.name,
  COUNT(*) AS headcount,
  AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.id, d.name
HAVING COUNT(*) > 5
   AND AVG(e.salary) > 60000
ORDER BY avg_salary DESC;

The filter order (frequently tested):

1. WHERE — filter individual rows before grouping
2. GROUP BY — group the filtered rows
3. HAVING — filter groups based on aggregate values
4. SELECT — compute what to show
5. ORDER BY — sort the result

---

Pattern 4: JOINs — All Four Types

-- INNER JOIN: only matching rows
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN: all employees, even those without a department
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN: all departments, even empty ones (rare in practice)
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER JOIN: all rows from both tables (not in MySQL by default)
SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

Interview tip: 90% of JOIN questions use INNER or LEFT. Always be able to explain: "I'm using LEFT JOIN here because I want to include employees who haven't been assigned to a department yet — INNER JOIN would drop them."

---

Pattern 5: Subqueries

Scalar subquery (returns one value)

-- Employees earning above the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery with IN

-- Employees in departments located in Mumbai
SELECT name
FROM employees
WHERE dept_id IN (
  SELECT id FROM departments WHERE city = 'Mumbai'
);

Correlated subquery (references outer query)

-- Employees earning above their department's average
SELECT e.name, e.salary, e.dept_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE dept_id = e.dept_id  -- references outer query's dept_id
);

When to use subquery vs JOIN: Subqueries are more readable for filtering. JOINs are faster for large datasets (the query planner can optimise them better).

---

Pattern 6: Window Functions

This is where candidates stand out. Window functions are tested at product companies.

-- Rank employees by salary within each department
SELECT
  name,
  dept_id,
  salary,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
FROM employees;

The 4 window functions you need:

-- ROW_NUMBER: unique sequential number (no ties)
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC)

-- RANK: same rank for ties, then skips (1, 2, 2, 4)
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)

-- DENSE_RANK: same rank for ties, no skips (1, 2, 2, 3)
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)

-- LAG/LEAD: access previous/next row's value
LAG(salary, 1) OVER (ORDER BY join_date) AS prev_salary
LEAD(salary, 1) OVER (ORDER BY join_date) AS next_salary

Classic interview question — Nth highest salary:

-- Second highest salary in each department
SELECT name, dept_id, salary
FROM (
  SELECT
    name,
    dept_id,
    salary,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

---

Pattern 7: Self Joins

A self join joins a table with itself. Classic use case: find hierarchies or compare rows within the same table.

-- Find all employees and their manager's name
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find employees earning more than their manager
SELECT e.name AS employee, e.salary, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

---

Pattern 8: CTEs (Common Table Expressions)

CTEs make complex queries readable. Interviewers love seeing them — they show you think in structured steps.

-- Find departments where average salary is above company average
WITH dept_averages AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
),
company_average AS (
  SELECT AVG(salary) AS overall_avg FROM employees
)
SELECT d.name, da.avg_salary
FROM dept_averages da
JOIN departments d ON da.dept_id = d.id
CROSS JOIN company_average ca
WHERE da.avg_salary > ca.overall_avg;

CTE syntax:

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

Multiple CTEs: separate with commas after the first.

---

Pattern 9: Finding Duplicates and Unique Records

-- Find duplicate emails
SELECT email, COUNT(*) AS count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

-- Find the employee with the highest salary per department (one row per dept)
SELECT e.name, e.salary, e.dept_id
FROM employees e
WHERE e.id IN (
  SELECT id
  FROM (
    SELECT id, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
  ) ranked
  WHERE rnk = 1
);

---

Pattern 10: Date Functions

-- Employees hired in the last 6 months
SELECT name, join_date
FROM employees
WHERE join_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH);

-- Count hires by year
SELECT
  YEAR(join_date) AS year,
  COUNT(*) AS hires
FROM employees
GROUP BY YEAR(join_date)
ORDER BY year;

-- Days since each employee joined
SELECT
  name,
  join_date,
  DATEDIFF(CURRENT_DATE, join_date) AS days_employed
FROM employees;

Key date functions:

• CURRENT_DATE / NOW() — today's date
• DATE_SUB(date, INTERVAL n unit) — subtract time
• DATE_ADD(date, INTERVAL n unit) — add time
• DATEDIFF(date1, date2) — days between dates
• YEAR(), MONTH(), DAY() — extract parts
• DATE_FORMAT(date, '%Y-%m') — format as string

---

Pattern 11: CASE WHEN (Conditional Logic)

-- Categorise employees by salary band
SELECT
  name,
  salary,
  CASE
    WHEN salary < 30000 THEN 'Junior'
    WHEN salary BETWEEN 30000 AND 70000 THEN 'Mid-level'
    WHEN salary > 70000 THEN 'Senior'
    ELSE 'Unknown'
  END AS level
FROM employees;

-- Count employees per band in one query
SELECT
  SUM(CASE WHEN salary < 30000 THEN 1 ELSE 0 END) AS junior,
  SUM(CASE WHEN salary BETWEEN 30000 AND 70000 THEN 1 ELSE 0 END) AS mid,
  SUM(CASE WHEN salary > 70000 THEN 1 ELSE 0 END) AS senior
FROM employees;

---

Pattern 12: UNION and INTERSECT

-- All cities with either a department OR an order customer
SELECT city FROM departments
UNION
SELECT city FROM customers;  -- removes duplicates

SELECT city FROM departments
UNION ALL
SELECT city FROM customers;  -- keeps duplicates (faster)

-- Employees who are ALSO customers (same name)
SELECT name FROM employees
INTERSECT
SELECT name FROM customers;

---

The 5 Questions Interviewers Almost Always Ask

1. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. You can't use aggregate functions in WHERE.

2. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes specific rows (can be rolled back, triggers fire). TRUNCATE removes all rows (faster, can't be rolled back, resets auto-increment). DROP removes the entire table including its structure.

3. What is an index and when would you add one?

An index is a data structure (usually a B-tree) that speeds up lookups on a column. Add an index on columns used frequently in WHERE, JOIN, or ORDER BY. Don't index every column — indexes slow down writes and take space.

4. What is normalisation?

Organising tables to reduce redundancy. 1NF: atomic values, no repeating groups. 2NF: no partial dependencies on composite keys. 3NF: no transitive dependencies. In practice: "split data into separate tables connected by foreign keys so each fact is stored once."

5. What is a transaction?

A sequence of operations treated as a single unit. Either all succeed or all fail (ACID). Example: transferring money — debit one account AND credit another must both succeed or neither should.

---

How to Practice SQL

Free resources:

• SQLZoo (sqlzoo.net) — best for practicing syntax
• LeetCode SQL problems — interview-style questions (sort by Acceptance rate, do top 30)
• Mode Analytics SQL Tutorial — good for window functions
• DB Fiddle (dbfiddle.uk) — run queries in your browser, no setup

Study schedule: 45 minutes daily for 2 weeks covers everything above. After that, do 2-3 LeetCode SQL problems weekly to stay sharp.

Next step: Every DSA interview also tests problem-solving. Use the AI Tutor to generate SQL problems based on your target company's known interview style.