100 SQL Interview Questions with Queries and Answers (Freshers to Experienced)

SQL (Structured Query Language) is the standard language for storing, reading and changing data in relational databases such as MySQL, PostgreSQL, Oracle, SQL Server and SQLite. You describe what data you want, and the database engine works out how to fetch it, which is why SQL is called a declarative language.

With SQL you can create tables, insert, update and delete rows, query and combine data across tables, and control access and transactions. Each database adds its own extensions (MySQL, T-SQL in SQL Server, PL/SQL in Oracle), but the core syntax is standardised by ANSI/ISO and works almost everywhere.

SELECT name, salary
FROM employees
WHERE salary > 100000
ORDER BY salary DESC;

Output:
name         | salary
-------------+-------
Rajesh Kumar | 250000
Priya Sharma | 150000
Karan Mehta  | 120000
Kavya Reddy  | 120000
Pooja Nair   | 110000
(5 rows)

A DBMS (Database Management System) is any software that stores and manages data, which could be files, documents or key-value pairs. An RDBMS (Relational DBMS) stores data in tables of rows and columns, links tables through keys, enforces integrity rules, and is queried with SQL.

Some textbooks put SELECT under DML. Either answer is accepted if you explain it.

A primary key is a column, or set of columns, that uniquely identifies each row in a table. It cannot contain duplicates and cannot be NULL, and a table can have only one primary key. Most databases automatically create an index on it, so lookups by primary key are fast. In our sample database, emp_id is the primary key of employees, so inserting a second row with an existing id fails:

INSERT INTO employees (emp_id, name, salary)
VALUES (1, 'Duplicate Person', 50000);

Output:
Error: UNIQUE constraint failed: employees.emp_id

A foreign key is a column in one table that refers to the primary key of another table. It enforces referential integrity: you cannot insert a value that does not exist in the parent table, or delete a parent row that children still point to (unless you specify an action like ON DELETE CASCADE). Here employees.dept_id references departments.dept_id, so department 42 is rejected.

Note: SQLite only enforces foreign keys after PRAGMA foreign_keys = ON. MySQL (InnoDB), PostgreSQL and SQL Server enforce them by default.

PRAGMA foreign_keys = ON;
INSERT INTO employees (emp_id, name, dept_id, salary)
VALUES (50, 'New Joiner', 42, 60000);

Output:
Error: FOREIGN KEY constraint failed

Example: emp_id is the primary key, while email or pan_number would be unique keys.

Constraints are rules on columns that the database enforces automatically, so bad data never gets in:

• NOT NULL: a value is required.
• UNIQUE: no duplicates.
• PRIMARY KEY: NOT NULL plus UNIQUE, identifies each row.
• FOREIGN KEY: value must exist in the referenced table.
• CHECK: value must satisfy a condition.
• DEFAULT: value used when none is given.

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  holder  TEXT NOT NULL,
  balance INTEGER DEFAULT 0 CHECK (balance >= 0)
);
INSERT INTO accounts (id, holder) VALUES (1, 'Riya');
INSERT INTO accounts (id, holder, balance) VALUES (2, 'Kabir', -500);

Output:
Error: CHECK constraint failed: balance >= 0

SQLite has no TRUNCATE; DELETE FROM table without a WHERE clause is optimised to do the same job.

DELETE FROM orders WHERE status = 'Cancelled';     -- removes 2 rows
TRUNCATE TABLE orders;                            -- removes all rows (MySQL / PostgreSQL)
DROP TABLE orders;                                -- removes the table itself

WHERE filters individual rows before grouping, so it cannot use aggregate functions. HAVING filters groups after GROUP BY, so it can use aggregates like COUNT() or AVG(). You can use both in one query: here WHERE keeps only employees earning above 60,000, then HAVING keeps only departments that still have at least 3 of them.

SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 60000
GROUP BY dept_id
HAVING COUNT(*) >= 3;

Output:
dept_id | headcount | avg_salary
--------+-----------+-----------
1       | 5         | 142000
2       | 4         | 81750
(2 rows)

NULL means “unknown” or “missing”. It is not zero and not an empty string. Any comparison with NULL, including NULL = NULL, gives UNKNOWN, which WHERE treats as false. That is why WHERE dept_id = NULL returns nothing. Always use IS NULL or IS NOT NULL.

SELECT COUNT(*) AS using_equals FROM employees WHERE dept_id = NULL;
SELECT name, dept_id FROM employees WHERE dept_id IS NULL;

Output:
using_equals
------------
0
(1 row)

name          | dept_id
--------------+--------
Manish Tiwari | NULL
(1 row)

CHAR(n) is fixed length: every value takes exactly n characters, padded with spaces. It suits codes of a fixed size, like country codes ('IN') or PIN codes. VARCHAR(n) is variable length: it stores only the characters you use (up to n) plus a small length prefix, so it saves space for names, emails and addresses.

CHAR can be slightly faster for truly fixed-size data, but VARCHAR is the right default. In Oracle the type is VARCHAR2, and PostgreSQL also offers TEXT with no length limit.

A JOIN combines rows from two or more tables based on a related column, usually a foreign key matching a primary key.

• INNER JOIN: only rows that match in both tables.
• LEFT JOIN: all rows from the left table, plus matches from the right (NULL where none).
• RIGHT JOIN: all rows from the right table, plus matches from the left.
• FULL OUTER JOIN: all rows from both tables, matched where possible.
• CROSS JOIN: every combination of rows (Cartesian product).
• SELF JOIN: a table joined to itself, such as employees to their managers.

Each one has its own question below with a query you can run.

INNER JOIN keeps only rows where the join condition matches in both tables. Manish Tiwari has no department (dept_id is NULL), so he disappears from the result, and so does the Marketing department, which has no employees. Table aliases (e, d) keep the query short.

SELECT e.name, d.dept_name, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.salary DESC;

Output:
name         | dept_name   | salary
-------------+-------------+-------
Rajesh Kumar | Engineering | 250000
Priya Sharma | Engineering | 150000
Kavya Reddy  | Engineering | 120000
Amit Verma   | Engineering | 95000
Sneha Iyer   | Engineering | 95000
Pooja Nair   | Finance     | 110000
Arjun Patel  | Finance     | 75000
Ananya Rao   | HR          | 85000
Vikram Joshi | HR          | 55000
Karan Mehta  | Sales       | 120000
Divya Menon  | Sales       | 72000
Neha Gupta   | Sales       | 70000
Rohit Singh  | Sales       | 65000
(13 rows)

LEFT JOIN returns every row from the left table, and fills the right table’s columns with NULL when there is no match. Here all 5 departments appear, including Marketing with zero employees. Note that COUNT(e.emp_id) counts only non-NULL values, which is why Marketing shows 0 instead of 1.

SELECT d.dept_name, COUNT(e.emp_id) AS employees
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY employees DESC;

Output:
dept_name   | employees
------------+----------
Engineering | 5
Sales       | 4
HR          | 2
Finance     | 2
Marketing   | 0
(5 rows)

Normalization organises tables to reduce duplicate data and avoid update anomalies (for example, a customer’s city stored in 50 order rows and updated in only 49).

• 1NF: each column holds atomic (single) values, there are no repeating groups, and each row is unique. A phone_numbers column holding “98xx, 99xx” breaks 1NF.
• 2NF: 1NF plus no partial dependency: every non-key column depends on the whole primary key, not just part of a composite key.
• 3NF: 2NF plus no transitive dependency: non-key columns depend only on the key, not on other non-key columns. Storing dept_name in employees breaks 3NF, because it depends on dept_id, so we keep it in a separate departments table.

BCNF, 4NF and 5NF go further, but 3NF is the usual target for transactional systems.

Denormalization deliberately adds redundant data, for example storing dept_name directly in a reporting table, to avoid expensive joins and speed up reads. The cost is extra storage and the risk of inconsistent data, because the same fact lives in several places and must be kept in sync.

It is common in reporting databases, data warehouses (star schemas), caches and read-heavy dashboards, where fast reads matter more than write efficiency. Transactional systems (OLTP) usually stay normalized.

An index is a separate data structure, usually a B-tree, that keeps a column’s values sorted with pointers to the matching rows, like the index at the back of a book. Without one, the database scans every row (a full table scan). With one, it can jump to the matching rows in O(log n).

The trade-off: indexes use extra storage and slow down INSERT, UPDATE and DELETE, because every index must be updated too. Index the columns you filter, join and sort on often. EXPLAIN QUERY PLAN (SQLite) or EXPLAIN (MySQL, PostgreSQL) shows whether an index is used:

CREATE INDEX idx_emp_city ON employees(city);
EXPLAIN QUERY PLAN
SELECT name FROM employees WHERE city = 'Mumbai';

Output:
id | parent | notused | detail
---+--------+---------+---------------------------------------------------
3  | 0      | 0       | SEARCH employees USING INDEX idx_emp_city (city=?)
(1 row)

A view is a saved SELECT query that behaves like a virtual table. It stores no data itself (unless it is a materialized view); every time you query it, the underlying query runs. Views are used to simplify complex joins, to give users access to only some columns (hiding salary, for example), and to keep a stable interface while underlying tables change.

CREATE VIEW employee_directory AS
SELECT e.name, d.dept_name, e.city
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

SELECT * FROM employee_directory WHERE city = 'Mumbai';

Output:
name        | dept_name | city
------------+-----------+-------
Karan Mehta | Sales     | Mumbai
Neha Gupta  | Sales     | Mumbai
Pooja Nair  | Finance   | Mumbai
Divya Menon | Sales     | Mumbai
(4 rows)

Both stack the results of two SELECT queries with the same number of columns and compatible types. UNION removes duplicate rows, which requires a sort or hash step. UNION ALL keeps every row, so it is faster. Use UNION ALL unless you actually need de-duplication. Below, UNION returns each city once, while UNION ALL keeps all 8 rows, duplicates included.

SELECT city FROM employees WHERE city IN ('Mumbai', 'Pune')
UNION
SELECT location FROM departments WHERE location = 'Mumbai';

SELECT COUNT(*) AS union_all_rows FROM (
  SELECT city FROM employees WHERE city IN ('Mumbai', 'Pune')
  UNION ALL
  SELECT location FROM departments WHERE location = 'Mumbai'
);

Output:
city
------
Mumbai
Pune
(2 rows)

union_all_rows
--------------
8
(1 row)

Aggregate functions calculate a single value from many rows: COUNT(), SUM(), AVG(), MIN() and MAX(). They ignore NULL values (except COUNT(*)), and they are usually combined with GROUP BY to get one result per group.

SELECT COUNT(*)     AS total_orders,
       SUM(amount)  AS revenue,
       AVG(amount)  AS avg_order,
       MIN(amount)  AS smallest,
       MAX(amount)  AS largest
FROM orders
WHERE status = 'Delivered';

Output:
total_orders | revenue | avg_order | smallest | largest
-------------+---------+-----------+----------+--------
12           | 33200   | 2766.6667 | 600      | 5600
(1 row)

GROUP BY splits rows into groups that share the same value in the listed columns, and aggregate functions then run once per group. Every column in the SELECT list must either be in the GROUP BY or be inside an aggregate function. MySQL can relax this when ONLY_FULL_GROUP_BY is off, but relying on that gives unpredictable results.

SELECT dept_id,
       COUNT(*)            AS headcount,
       ROUND(AVG(salary))  AS avg_salary,
       MAX(salary)         AS max_salary
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY avg_salary DESC;

Output:
dept_id | headcount | avg_salary | max_salary
--------+-----------+------------+-----------
1       | 5         | 142000     | 250000
4       | 2         | 92500      | 110000
2       | 4         | 81750      | 120000
3       | 2         | 70000      | 85000
(4 rows)

Sort with ORDER BY, then limit the rows. The syntax differs by database:

• MySQL, PostgreSQL, SQLite: LIMIT 3 (with OFFSET for paging)
• SQL Server: SELECT TOP 3 ...
• Oracle 12c+ and standard SQL: FETCH FIRST 3 ROWS ONLY

Without ORDER BY, the database may return any 3 rows. Also note that two employees tie at 120000, so “top 3” can be ambiguous; window functions (later questions) handle ties properly.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Output:
name         | salary
-------------+-------
Rajesh Kumar | 250000
Priya Sharma | 150000
Karan Mehta  | 120000
(3 rows)

DISTINCT removes duplicate rows from the result. With several columns, it applies to the combination of those columns. It is often used with COUNT: COUNT(DISTINCT city). Do not use DISTINCT to hide duplicate rows caused by a wrong join; fix the join instead.

SELECT DISTINCT city FROM employees ORDER BY city;
SELECT COUNT(DISTINCT city) AS unique_cities FROM employees;

Output:
city
---------
Ahmedabad
Bengaluru
Delhi
Hyderabad
Lucknow
Mumbai
Pune
(7 rows)

unique_cities
-------------
7
(1 row)

LIKE matches text patterns: % means any number of characters and _ means exactly one character. 'A%' is starts with A, '%a' is ends with a, and '%an%' is contains an.

Case sensitivity varies: MySQL and SQLite (for ASCII letters) are case-insensitive by default, PostgreSQL is case-sensitive and offers ILIKE. A pattern starting with % cannot use a normal index, so it is slow on large tables.

SELECT name FROM employees WHERE name LIKE 'A%';
SELECT name FROM employees WHERE name LIKE '_a%';

Output:
name
-----------
Amit Verma
Ananya Rao
Arjun Patel
(3 rows)

name
-------------
Rajesh Kumar
Karan Mehta
Kavya Reddy
Manish Tiwari
(4 rows)

BETWEEN a AND b is inclusive on both ends and works for numbers, dates and text. IN (...) is a cleaner way to write several OR conditions. Both have NOT versions. With dates stored as datetimes, be careful: BETWEEN '2025-01-01' AND '2025-01-31' misses times later on 31 January, so prefer >= start AND < next_day.

SELECT name, salary FROM employees
WHERE salary BETWEEN 70000 AND 95000
  AND city IN ('Mumbai', 'Bengaluru', 'Hyderabad')
ORDER BY salary;

Output:
name        | salary
------------+-------
Neha Gupta  | 70000
Divya Menon | 72000
Amit Verma  | 95000
Sneha Iyer  | 95000
(4 rows)

An alias is a temporary name for a column or table within one query, written with AS (optional for tables, and not allowed before table aliases in Oracle). Column aliases make output readable; table aliases shorten joins and are required for self joins. In standard SQL you cannot use a column alias in WHERE, because WHERE runs before SELECT, but you can use it in ORDER BY.

SELECT e.name AS employee,
       e.salary * 12 AS annual_ctc
FROM employees AS e
ORDER BY annual_ctc DESC
LIMIT 3;

Output:
employee     | annual_ctc
-------------+-----------
Rajesh Kumar | 3000000
Priya Sharma | 1800000
Karan Mehta  | 1440000
(3 rows)

• COUNT(*) counts all rows, including those with NULLs.
• COUNT(column) counts rows where that column is not NULL.
• COUNT(DISTINCT column) counts unique non-NULL values.

Manish Tiwari has a NULL dept_id, so the counts differ:

SELECT COUNT(*)                AS all_rows,
       COUNT(dept_id)          AS with_dept,
       COUNT(DISTINCT dept_id) AS unique_depts
FROM employees;

Output:
all_rows | with_dept | unique_depts
---------+-----------+-------------
14       | 13        | 4
(1 row)

A subquery is a query nested inside another query. It can appear in WHERE (the most common), in FROM (as a derived table), or in SELECT (as a scalar value). Here the inner query computes the company-wide average salary once, and the outer query keeps employees above it.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Output:
name         | salary
-------------+-------
Rajesh Kumar | 250000
Priya Sharma | 150000
Karan Mehta  | 120000
Kavya Reddy  | 120000
Pooja Nair   | 110000
(5 rows)

A transaction is a group of SQL statements that must succeed or fail as one unit, like debiting one bank account and crediting another. ACID describes the guarantees:

• Atomicity: all statements succeed, or none do.
• Consistency: the database moves from one valid state to another, with all constraints satisfied.
• Isolation: concurrent transactions do not see each other’s half-finished work (the degree depends on the isolation level).
• Durability: once committed, changes survive crashes, because they are written to a log on disk.

BEGIN (or START TRANSACTION in MySQL) starts a transaction. COMMIT makes its changes permanent. ROLLBACK undoes everything since BEGIN. SAVEPOINT name marks a point you can roll back to without undoing the whole transaction. Below, the salary hike for Neha is undone, so her salary is unchanged.

BEGIN;
UPDATE employees SET salary = salary * 2 WHERE emp_id = 6;
SELECT name, salary FROM employees WHERE emp_id = 6;
ROLLBACK;
SELECT name, salary FROM employees WHERE emp_id = 6;

Output:
name       | salary
-----------+-------
Neha Gupta | 140000
(1 row)

name       | salary
-----------+-------
Neha Gupta | 70000
(1 row)

A schema is a named container for database objects such as tables, views, indexes and procedures, and it also describes their structure. Schemas group related objects and control permissions, for example sales.orders and hr.employees in the same database.

Meaning differs slightly by product: in MySQL, “schema” and “database” are synonyms; in PostgreSQL and SQL Server a database contains many schemas (public and dbo are the defaults); in Oracle each user owns a schema.

Data usually flows from OLTP systems into an OLAP warehouse through ETL or ELT pipelines.

CASE is SQL’s if-else. It returns a value based on conditions checked in order, and the first true one wins. Use it to create categories, map codes to labels, and for conditional aggregation (see the pivot question later). Always add an ELSE, otherwise unmatched rows get NULL.

SELECT name, salary,
       CASE
         WHEN salary >= 150000 THEN 'Senior band'
         WHEN salary >= 90000  THEN 'Mid band'
         ELSE 'Junior band'
       END AS pay_band
FROM employees
ORDER BY salary DESC
LIMIT 6;

Output:
name         | salary | pay_band
-------------+--------+------------
Rajesh Kumar | 250000 | Senior band
Priya Sharma | 150000 | Senior band
Karan Mehta  | 120000 | Mid band
Kavya Reddy  | 120000 | Mid band
Pooja Nair   | 110000 | Mid band
Amit Verma   | 95000  | Mid band
(6 rows)

The most asked SQL query question. Two common approaches: take the maximum salary below the overall maximum, or use DISTINCT with LIMIT 1 OFFSET 1. Both handle ties correctly, because two people with the top salary still leave the second distinct value. The DENSE_RANK() approach for any Nth salary appears in the intermediate section.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Output:
second_highest
--------------
150000
(1 row)

second_highest
--------------
150000
(1 row)

Date functions differ between databases, which interviewers like to check:

• MySQL: YEAR(hire_date) = 2020
• PostgreSQL / standard SQL: EXTRACT(YEAR FROM hire_date) = 2020
• SQL Server: YEAR(hire_date) or DATEPART(year, hire_date)
• SQLite: strftime('%Y', hire_date) = '2020'

Wrapping the column in a function prevents index use, so on large tables a range is faster: hire_date >= '2020-01-01' AND hire_date < '2021-01-01'.

SELECT name, hire_date
FROM employees
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

Output:
name        | hire_date
------------+-----------
Sneha Iyer  | 2020-03-22
Arjun Patel | 2020-08-17
(2 rows)

Group by city, count, and filter the groups with HAVING. This pattern (GROUP BY plus HAVING COUNT) is the basis for many interview questions, such as departments with more than N employees or customers with more than N orders.

SELECT city, COUNT(*) AS employees
FROM employees
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY employees DESC, city;

Output:
city      | employees
----------+----------
Mumbai    | 4
Bengaluru | 3
Hyderabad | 2
Pune      | 2
(4 rows)

Group by the columns that define a duplicate and keep groups with more than one row. Here the same customer (same name and email) signed up twice. To see the full duplicate rows, join this result back to the table or use a window function.

SELECT name, email, COUNT(*) AS copies
FROM customers
GROUP BY name, email
HAVING COUNT(*) > 1;

Output:
name       | email          | copies
-----------+----------------+-------
Aarav Shah | aarav@mail.com | 2
(1 row)

Classic “anti-join”. Three correct ways: LEFT JOIN ... WHERE right.id IS NULL, NOT EXISTS, or NOT IN. Prefer NOT EXISTS or the LEFT JOIN: NOT IN returns no rows at all if the subquery contains a NULL (explained in the intermediate section).

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Output:
customer_id | name
------------+----------
108         | Tara Bose
(1 row)

name
---------
Tara Bose
(1 row)

Join customers to orders, filter only delivered orders, group by customer and sort by the total. Group by the id as well as the name, because two customers can share a name (Aarav Shah appears twice in our data).

SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS orders,
       SUM(o.amount)     AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Delivered'
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

Output:
customer_id | name          | orders | total_spent
------------+---------------+--------+------------
101         | Aarav Shah    | 4      | 11800
102         | Isha Kapoor   | 3      | 7200
104         | Meera Pillai  | 1      | 5600
106         | Riya Sen      | 1      | 4300
103         | Yash Malhotra | 1      | 2700
107         | Aarav Shah    | 1      | 600
(6 rows)

Keep the row with the smallest id in each duplicate group and delete the rest. This version works in SQLite, PostgreSQL and SQL Server. MySQL does not allow selecting from the same table in a DELETE subquery, so there you use a self join: DELETE c1 FROM customers c1 JOIN customers c2 ON c1.email = c2.email AND c1.customer_id > c2.customer_id;. A ROW_NUMBER() version appears in the experienced section.

DELETE FROM customers
WHERE customer_id NOT IN (
  SELECT MIN(customer_id)
  FROM customers
  GROUP BY name, email
);
SELECT customer_id, name, email FROM customers ORDER BY customer_id;

Output:
customer_id | name          | email
------------+---------------+---------------
101         | Aarav Shah    | aarav@mail.com
102         | Isha Kapoor   | isha@mail.com
103         | Yash Malhotra | yash@mail.com
104         | Meera Pillai  | meera@mail.com
105         | Dev Choudhary | dev@mail.com
106         | Riya Sen      | riya@mail.com
108         | Tara Bose     | NULL
(7 rows)

A RIGHT JOIN keeps every row from the right table and fills the left side with NULL when there is no match. A RIGHT JOIN B gives the same result as B LEFT JOIN A, so most teams simply write LEFT JOINs for readability. RIGHT JOIN is supported in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite 3.39+.

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

Output:
name | dept_name
-----+----------
NULL | Marketing
(1 row)

A FULL OUTER JOIN returns all rows from both tables: matched rows side by side, plus unmatched rows from either side with NULLs. Below, customers with no orders appear with a NULL order, and order 15 (from customer 999, who does not exist) appears with a NULL customer. That makes FULL JOIN handy for reconciliation, such as comparing two systems’ records.

MySQL has no FULL OUTER JOIN. Emulate it with LEFT JOIN ... UNION ... RIGHT JOIN.

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL OR o.order_id IS NULL;

Output:
name      | order_id | amount
----------+----------+-------
Tara Bose | NULL     | NULL
NULL      | 15       | 1000
(2 rows)

A self join joins a table to itself, using two different aliases. It is the standard way to work with hierarchical data stored in one table, like employees and managers or categories and parent categories. A LEFT JOIN keeps Rajesh, the CEO, who has no manager.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
ORDER BY m.name, e.name;

Output:
employee      | manager
--------------+-------------
Rajesh Kumar  | NULL
Vikram Joshi  | Ananya Rao
Manish Tiwari | Karan Mehta
Neha Gupta    | Karan Mehta
Rohit Singh   | Karan Mehta
Divya Menon   | Neha Gupta
Arjun Patel   | Pooja Nair
Amit Verma    | Priya Sharma
Kavya Reddy   | Priya Sharma
Sneha Iyer    | Priya Sharma
Ananya Rao    | Rajesh Kumar
Karan Mehta   | Rajesh Kumar
Pooja Nair    | Rajesh Kumar
Priya Sharma  | Rajesh Kumar
(14 rows)

A CROSS JOIN returns the Cartesian product: every row of the first table paired with every row of the second, so m × n rows. It is rarely what you want by accident (a missing join condition creates one), but it is useful on purpose to generate combinations, such as every department paired with every month for a report that must show zero rows too.

SELECT COUNT(*) AS combinations
FROM departments
CROSS JOIN (SELECT 'Q1' AS quarter UNION ALL SELECT 'Q2' UNION ALL SELECT 'Q3' UNION ALL SELECT 'Q4');

Output:
combinations
------------
20
(1 row)

A non-correlated subquery runs once and does not depend on the outer query. A correlated subquery refers to a column of the outer query (e.dept_id below), so logically it runs once per outer row. It is easy to read but can be slow on large tables; a join to a grouped derived table or a window function usually performs better.

SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id
)
ORDER BY e.dept_id;

Output:
name         | dept_id | salary
-------------+---------+-------
Rajesh Kumar | 1       | 250000
Priya Sharma | 1       | 150000
Karan Mehta  | 2       | 120000
Ananya Rao   | 3       | 85000
Pooja Nair   | 4       | 110000
(5 rows)

IN compares a value against a list returned by the subquery. EXISTS just checks whether the subquery returns any row, and can stop at the first match. Modern optimisers often run them the same way, but EXISTS is usually preferred for correlated checks.

The trap: NOT IN returns no rows at all if the subquery contains a NULL, because x NOT IN (1, NULL) evaluates to UNKNOWN. NOT EXISTS does not have this problem. Manish’s NULL dept_id breaks the first query below:

SELECT COUNT(*) AS not_in_result
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);

SELECT dept_name AS not_exists_result
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

Output:
not_in_result
-------------
0
(1 row)

not_exists_result
-----------------
Marketing
(1 row)

A CTE is a named temporary result defined with WITH at the start of a query and used like a table in the main query. CTEs make long queries readable by breaking them into steps, can be referenced more than once, and can be recursive (see the experienced section). They exist only for that one query. Supported by PostgreSQL, SQL Server, Oracle, SQLite and MySQL 8+.

WITH dept_stats AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT d.dept_name, ROUND(s.avg_sal) AS avg_salary
FROM dept_stats s
JOIN departments d ON d.dept_id = s.dept_id
ORDER BY avg_salary DESC;

Output:
dept_name   | avg_salary
------------+-----------
Engineering | 142000
Finance     | 92500
Sales       | 81750
HR          | 70000
(4 rows)

A window function computes a value across a set of related rows (the “window”) without collapsing them into one row. GROUP BY returns one row per group; a window function keeps every row and adds the calculated value next to it. The OVER() clause defines the window: PARTITION BY splits it into groups and ORDER BY sets the order inside each group. Common ones: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM/AVG OVER, NTILE.

SELECT name, dept_id, salary,
       ROUND(AVG(salary) OVER (PARTITION BY dept_id)) AS dept_avg,
       salary - ROUND(AVG(salary) OVER (PARTITION BY dept_id)) AS diff
FROM employees
WHERE dept_id IN (1, 2)
ORDER BY dept_id, salary DESC;

Output:
name         | dept_id | salary | dept_avg | diff
-------------+---------+--------+----------+-------
Rajesh Kumar | 1       | 250000 | 142000   | 108000
Priya Sharma | 1       | 150000 | 142000   | 8000
Kavya Reddy  | 1       | 120000 | 142000   | -22000
Amit Verma   | 1       | 95000  | 142000   | -47000
Sneha Iyer   | 1       | 95000  | 142000   | -47000
Karan Mehta  | 2       | 120000 | 81750    | 38250
Divya Menon  | 2       | 72000  | 81750    | -9750
Neha Gupta   | 2       | 70000  | 81750    | -11750
Rohit Singh  | 2       | 65000  | 81750    | -16750
(9 rows)

All three number rows in a given order, but treat ties differently:

• ROW_NUMBER(): always unique (1, 2, 3, 4), ties broken arbitrarily.
• RANK(): ties share a rank, then numbers are skipped (1, 2, 2, 4).
• DENSE_RANK(): ties share a rank with no gaps (1, 2, 2, 3).

Use DENSE_RANK for “Nth highest salary” questions and ROW_NUMBER when you need exactly one row per group.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC
LIMIT 8;

Output:
name         | salary | row_num | rnk | dense_rnk
-------------+--------+---------+-----+----------
Rajesh Kumar | 250000 | 1       | 1   | 1
Priya Sharma | 150000 | 2       | 2   | 2
Karan Mehta  | 120000 | 3       | 3   | 3
Kavya Reddy  | 120000 | 4       | 3   | 3
Pooja Nair   | 110000 | 5       | 5   | 4
Amit Verma   | 95000  | 6       | 6   | 5
Sneha Iyer   | 95000  | 7       | 6   | 5
Ananya Rao   | 85000  | 8       | 8   | 6
(8 rows)

Use DENSE_RANK() so that ties do not skip a level, then filter on the rank in an outer query (window functions cannot be used directly in WHERE). Change 3 to any N. Our data has two people tied at 120000, and both are returned for the 3rd highest salary.

WITH ranked AS (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, salary FROM ranked WHERE rnk = 3;

Output:
name        | salary
------------+-------
Karan Mehta | 120000
Kavya Reddy | 120000
(2 rows)

“Top N per group” is one of the most frequent scenario questions. Partition by department, rank inside each partition, then keep ranks up to N. Use ROW_NUMBER for exactly N rows per group, or DENSE_RANK to include ties (Engineering has a tie at 95000 for 4th place, which would matter for a top 4).

WITH ranked AS (
  SELECT d.dept_name, e.name, e.salary,
         ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
  FROM employees e
  JOIN departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, name, salary
FROM ranked
WHERE rn <= 2
ORDER BY dept_name, salary DESC;

Output:
dept_name   | name         | salary
------------+--------------+-------
Engineering | Rajesh Kumar | 250000
Engineering | Priya Sharma | 150000
Finance     | Pooja Nair   | 110000
Finance     | Arjun Patel  | 75000
HR          | Ananya Rao   | 85000
HR          | Vikram Joshi | 55000
Sales       | Karan Mehta  | 120000
Sales       | Divya Menon  | 72000
(8 rows)

SUM(...) OVER (ORDER BY ...) adds each row’s value to all the previous ones. Adding PARTITION BY customer_id would restart the total for each customer. When the window has an ORDER BY, the default frame is “from the start to the current row”.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status = 'Delivered'
ORDER BY order_date
LIMIT 8;

Output:
order_date | amount | running_total
-----------+--------+--------------
2025-01-15 | 2500   | 2500
2025-02-05 | 1200   | 3700
2025-02-18 | 3400   | 7100
2025-03-20 | 5600   | 12700
2025-03-25 | 2100   | 14800
2025-04-22 | 1800   | 16600
2025-05-11 | 4300   | 20900
2025-05-28 | 2700   | 23600
(8 rows)

LAG(col, n) reads the value from n rows before the current row; LEAD(col, n) reads from n rows after. They are ideal for comparing with the previous period, like growth, churn or time between events. The first row has no previous value, so LAG returns NULL there (or a default if you pass a third argument).

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'Delivered'
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

Output:
month   | revenue | prev_month | change
--------+---------+------------+-------
2025-01 | 2500    | NULL       | NULL
2025-02 | 4600    | 2500       | 2100
2025-03 | 7700    | 4600       | 3100
2025-04 | 1800    | 7700       | -5900
2025-05 | 7000    | 1800       | 5200
2025-06 | 4500    | 7000       | -2500
2025-07 | 5100    | 4500       | 600
(7 rows)

• COALESCE(a, b, c) returns the first non-NULL argument. It is standard SQL and works everywhere.
• IFNULL(a, b) in MySQL and SQLite, ISNULL(a, b) in SQL Server and NVL(a, b) in Oracle are two-argument versions.
• NULLIF(a, b) returns NULL if a equals b, otherwise a. Its classic use is avoiding division by zero: x / NULLIF(y, 0).

SELECT name, COALESCE(email, 'not given') AS email
FROM customers
WHERE customer_id IN (106, 108);

SELECT name, COALESCE(dept_id, 0) AS dept, 100 / NULLIF(0, 0) AS safe_division
FROM employees
WHERE emp_id = 13;

Output:
name      | email
----------+--------------
Riya Sen  | riya@mail.com
Tara Bose | not given
(2 rows)

name          | dept | safe_division
--------------+------+--------------
Manish Tiwari | 0    | NULL
(1 row)

The common ones: UPPER/LOWER, LENGTH (LEN in SQL Server), SUBSTR/SUBSTRING, TRIM, REPLACE, INSTR/POSITION, and concatenation, which differs: || in PostgreSQL, Oracle and SQLite, CONCAT() in MySQL, + or CONCAT() in SQL Server. A favourite question: extract the first name from a full name.

SELECT name,
       UPPER(name) AS upper_name,
       LENGTH(name) AS len,
       SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name,
       city || ' (' || SUBSTR(city, 1, 3) || ')' AS city_code
FROM employees
LIMIT 4;

Output:
name         | upper_name   | len | first_name | city_code
-------------+--------------+-----+------------+----------------
Rajesh Kumar | RAJESH KUMAR | 12  | Rajesh     | Bengaluru (Ben)
Priya Sharma | PRIYA SHARMA | 12  | Priya      | Bengaluru (Ben)
Amit Verma   | AMIT VERMA   | 10  | Amit       | Hyderabad (Hyd)
Sneha Iyer   | SNEHA IYER   | 10  | Sneha      | Bengaluru (Ben)
(4 rows)

Grouping by month is an everyday reporting task, but the function changes per database:

• MySQL: DATE_FORMAT(order_date, '%Y-%m')
• PostgreSQL: TO_CHAR(order_date, 'YYYY-MM') or DATE_TRUNC('month', order_date)
• SQL Server: FORMAT(order_date, 'yyyy-MM')
• SQLite: strftime('%Y-%m', order_date)

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY month
ORDER BY month;

Output:
month   | orders | revenue
--------+--------+--------
2025-01 | 1      | 2500
2025-02 | 2      | 4600
2025-03 | 2      | 7700
2025-04 | 2      | 2750
2025-05 | 2      | 7000
2025-06 | 2      | 4500
2025-07 | 3      | 7300
(7 rows)

Put a CASE expression inside an aggregate to turn row values into columns. This works in every database, unlike the PIVOT keyword, which exists only in SQL Server and Oracle. Here each order status becomes its own column.

SELECT strftime('%Y-%m', order_date) AS month,
       SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status = 'Pending'   THEN 1 ELSE 0 END) AS pending
FROM orders
GROUP BY month
ORDER BY month;

Output:
month   | delivered | cancelled | pending
--------+-----------+-----------+--------
2025-01 | 1         | 0         | 0
2025-02 | 2         | 0         | 0
2025-03 | 2         | 1         | 0
2025-04 | 1         | 0         | 1
2025-05 | 2         | 0         | 0
2025-06 | 2         | 1         | 0
2025-07 | 2         | 0         | 1
(7 rows)

A classic self-join question, also on LeetCode (181). Join the table to itself on manager_id = emp_id so each row has the employee and their manager side by side, then compare the two salaries. In our data, Divya was hired as a senior specialist and earns slightly more than her team lead Neha.

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

Output:
employee    | salary | manager    | manager_salary
------------+--------+------------+---------------
Divya Menon | 72000  | Neha Gupta | 70000
(1 row)

An anti-join again: LEFT JOIN and keep rows where the employee side is NULL, or use NOT EXISTS. Remember the NOT IN NULL trap from an earlier question: NOT IN (SELECT dept_id FROM employees) returns nothing here because one dept_id is NULL.

SELECT d.dept_id, d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

Output:
dept_id | dept_name
--------+----------
5       | Marketing
(1 row)

A simple GROUP BY dept_id with MAX(salary) gives the salary but not the name, and adding name to the SELECT list is invalid in standard SQL. Two correct ways: join back to the grouped maximum, or rank with a window function. The join version also returns ties.

SELECT d.dept_name, e.name, e.salary
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS max_sal
      FROM employees GROUP BY dept_id) m
  ON e.dept_id = m.dept_id AND e.salary = m.max_sal
JOIN departments d ON d.dept_id = e.dept_id
ORDER BY e.salary DESC;

Output:
dept_name   | name         | salary
------------+--------------+-------
Engineering | Rajesh Kumar | 250000
Sales       | Karan Mehta  | 120000
Finance     | Pooja Nair   | 110000
HR          | Ananya Rao   | 85000
(4 rows)

Combine WHERE (filter rows), GROUP BY (one row per customer) and HAVING with two aggregate conditions. Scenario questions like this appear constantly in data analyst interviews.

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE status = 'Delivered'
GROUP BY customer_id
HAVING COUNT(*) > 2 AND SUM(amount) > 5000;

Output:
customer_id | orders | total
------------+--------+------
101         | 4      | 11800
102         | 3      | 7200
(2 rows)

Compute the department total with GROUP BY, then divide by the grand total, which a window function gives without a second query: SUM(SUM(salary)) OVER () means “sum the grouped sums across the whole result”. Multiply by 100.0 rather than 100 to avoid integer division in databases that truncate integers.

SELECT d.dept_name,
       SUM(e.salary) AS dept_salary,
       ROUND(100.0 * SUM(e.salary) / SUM(SUM(e.salary)) OVER (), 1) AS pct_of_total
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY pct_of_total DESC;

Output:
dept_name   | dept_salary | pct_of_total
------------+-------------+-------------
Engineering | 710000      | 52.1
Sales       | 327000      | 24
Finance     | 185000      | 13.6
HR          | 140000      | 10.3
(4 rows)

MIN and MAX on a date column give the first and last order. Date arithmetic differs by database: DATEDIFF(last, first) in MySQL, last - first for DATE values in PostgreSQL, DATEDIFF(day, first, last) in SQL Server, and julianday(last) - julianday(first) in SQLite.

SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order,
       CAST(julianday(MAX(order_date)) - julianday(MIN(order_date)) AS INTEGER) AS days_between
FROM orders
WHERE customer_id IN (101, 102, 103)
GROUP BY customer_id;

Output:
customer_id | first_order | last_order | days_between
------------+-------------+------------+-------------
101         | 2025-01-15  | 2025-07-30 | 196
102         | 2025-02-05  | 2025-06-19 | 134
103         | 2025-03-02  | 2025-05-28 | 87
(3 rows)

A stored procedure is a named block of SQL (with variables, conditions and loops) saved inside the database and run with CALL or EXEC. Benefits: logic runs close to the data, fewer network round trips, reusable business rules, and permissions can be granted on the procedure instead of the tables. Downsides: harder to version-control and test, and the syntax is vendor-specific (PL/SQL, T-SQL, PL/pgSQL). SQLite does not support stored procedures; this MySQL example is not runnable here:

DELIMITER //
CREATE PROCEDURE give_raise(IN p_dept INT, IN p_pct DECIMAL(5,2))
BEGIN
  UPDATE employees
  SET salary = salary * (1 + p_pct / 100)
  WHERE dept_id = p_dept;
END //
DELIMITER ;

CALL give_raise(2, 10);

A trigger is code that runs automatically when an INSERT, UPDATE or DELETE happens on a table, either BEFORE or AFTER the change. Typical uses: audit logs, enforcing complex rules, and keeping derived columns in sync. Use them sparingly, because hidden logic makes bugs hard to trace. This trigger records every salary change:

CREATE TABLE salary_audit (emp_id INT, old_salary INT, new_salary INT);

CREATE TRIGGER trg_salary_change
AFTER UPDATE OF salary ON employees
BEGIN
  INSERT INTO salary_audit VALUES (OLD.emp_id, OLD.salary, NEW.salary);
END;

UPDATE employees SET salary = salary + 5000 WHERE dept_id = 3;
SELECT * FROM salary_audit;

Output:
emp_id | old_salary | new_salary
-------+------------+-----------
8      | 85000      | 90000
9      | 55000      | 60000
(2 rows)

A clustered index decides the physical order of rows on disk: the table data is the index’s leaf level. So a table can have only one clustered index, usually on the primary key. Range scans on it are very fast.

A non-clustered index is a separate structure holding the indexed column values plus a pointer (row locator or primary key) back to the table row. A table can have many of them, and a lookup may need an extra step to fetch the remaining columns, unless the index covers the query.

In MySQL InnoDB the primary key is always the clustered index. In PostgreSQL tables are heaps, and CLUSTER only reorders them once.

A composite index covers several columns, for example (dept_id, salary). It is sorted by the first column, then by the second within it. So it helps queries that filter on dept_id, or on dept_id and salary, but generally not queries filtering only on salary: that is the leftmost prefix rule. Put the column used most often in equality filters first.

CREATE INDEX idx_dept_salary ON employees(dept_id, salary);
EXPLAIN QUERY PLAN SELECT name FROM employees WHERE dept_id = 1 AND salary > 100000;
EXPLAIN QUERY PLAN SELECT name FROM employees WHERE salary > 100000;

Output:
id | parent | notused | detail
---+--------+---------+----------------------------------------------------------------------
3  | 0      | 0       | SEARCH employees USING INDEX idx_dept_salary (dept_id=? AND salary>?)
(1 row)

id | parent | notused | detail
---+--------+---------+---------------
2  | 0      | 0       | SCAN employees
(1 row)

• Super key: any set of columns that uniquely identifies a row, even with extra columns, like (emp_id, name).
• Candidate key: a minimal super key, with no unnecessary columns. A table may have several, like emp_id and email.
• Primary key: the candidate key you choose as the main identifier.
• Alternate key: the candidate keys not chosen as primary, usually enforced with UNIQUE.
• Composite key: a key made of two or more columns, like (order_id, product_id) in an order_items table.
• Surrogate key: an artificial id with no business meaning (auto-increment or UUID), as opposed to a natural key like a PAN number.

It defines what happens to child rows when the parent row is deleted. CASCADE deletes the children automatically. The other options are SET NULL, SET DEFAULT, and RESTRICT/NO ACTION (block the delete, the default). ON UPDATE works the same way for key changes. Cascades are convenient but dangerous: one delete can silently remove thousands of rows.

PRAGMA foreign_keys = ON;
CREATE TABLE teams (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE members (id INTEGER PRIMARY KEY, team_id INT REFERENCES teams(id) ON DELETE CASCADE, name TEXT);
INSERT INTO teams VALUES (1, 'Backend'), (2, 'Data');
INSERT INTO members VALUES (1, 1, 'Riya'), (2, 1, 'Kabir'), (3, 2, 'Sneha');
DELETE FROM teams WHERE id = 1;
SELECT * FROM members;

Output:
id | team_id | name
---+---------+------
3  | 2       | Sneha
(1 row)

• One-to-one: each row in A matches at most one row in B, such as employee and employee_bank_details. Implement with a foreign key that is also UNIQUE.
• One-to-many: one department has many employees. The foreign key goes on the “many” side (employees.dept_id).
• Many-to-many: students and courses. This needs a junction (bridge) table, like enrollments(student_id, course_id), with a composite primary key and two foreign keys.

INTERSECT returns rows present in both queries. EXCEPT returns rows in the first query that are not in the second; Oracle calls it MINUS. Both remove duplicates. MySQL added them only in 8.0.31, so older MySQL needs joins or EXISTS instead.

SELECT city FROM employees
INTERSECT
SELECT location FROM departments;

SELECT city FROM employees
EXCEPT
SELECT location FROM departments;

Output:
city
---------
Bengaluru
Delhi
Mumbai
Pune
(4 rows)

city
---------
Ahmedabad
Hyderabad
Lucknow
(3 rows)

SQL is written in one order but logically processed in another, which explains many errors:

1. FROM and JOIN: build the combined rows
2. WHERE: filter rows
3. GROUP BY: form groups
4. HAVING: filter groups
5. SELECT: compute columns and aliases (window functions run here)
6. DISTINCT: remove duplicates
7. ORDER BY: sort
8. LIMIT / OFFSET / TOP: cut rows

That is why a SELECT alias cannot be used in WHERE in standard SQL, why aggregates cannot appear in WHERE, and why window functions need a subquery or CTE to be filtered. The optimiser may physically execute steps differently, but results always match this logical order.

A JOIN combines tables horizontally: it adds columns from another table to each row, matched on a condition. A UNION combines query results vertically: it stacks rows from two queries that have the same number of columns with compatible types. Use a JOIN to see employees with their department names; use a UNION to merge last year’s and this year’s order tables into one list.

PostgreSQL and Oracle have PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary), but MySQL and SQLite do not, so interviewers ask for a portable version: number the rows in both directions, then average the middle one or two. With 14 employees, the median is the average of the 7th and 8th salaries.

WITH ordered AS (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS total
  FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((total + 1) / 2, (total + 2) / 2);

Output:
median_salary
-------------
90000
(1 row)

Isolation levels trade consistency for concurrency. The SQL standard defines four, by which anomalies they allow:

• Dirty read: reading another transaction’s uncommitted change, which may be rolled back.
• Non-repeatable read: reading the same row twice and getting different values because someone committed an update in between.
• Phantom read: running the same range query twice and getting new rows that someone inserted in between.

Defaults: READ COMMITTED in PostgreSQL, SQL Server and Oracle; REPEATABLE READ in MySQL InnoDB.

A deadlock happens when two transactions each hold a lock the other needs: T1 locks row A and waits for row B, while T2 locks row B and waits for row A. Neither can continue. The database detects the cycle and kills one transaction (the “victim”), which then gets an error and must retry.

Prevention: always access tables and rows in the same order across the application, keep transactions short, avoid user interaction inside a transaction, use proper indexes so fewer rows get locked, use SELECT ... FOR UPDATE deliberately, and add retry logic for deadlock errors.

1. Read the execution plan (EXPLAIN / EXPLAIN ANALYZE) to find full table scans, bad join orders and large sorts.
2. Add or fix indexes on columns used in WHERE, JOIN and ORDER BY. Consider composite and covering indexes.
3. Make filters sargable: avoid functions on indexed columns (WHERE YEAR(d) = 2025 → WHERE d >= '2025-01-01' AND d < '2026-01-01') and leading wildcards (LIKE '%x').
4. Select only the columns you need, not SELECT *.
5. Filter early, and avoid unnecessary DISTINCT, ORDER BY and subqueries that repeat work.
6. Rewrite correlated subqueries as joins or window functions.
7. Paginate with keyset pagination (WHERE id > last_id) instead of large OFFSETs.
8. Keep statistics fresh (ANALYZE), and for very large tables use partitioning, materialized views or caching.

The execution plan shows how the database will run a query: which tables it scans, which indexes it uses, the join algorithms (nested loop, hash, merge) and estimated costs. The key thing to spot is a full SCAN of a large table where you expected an index SEARCH. Here the same query goes from a full scan to an index search after the index is created. In PostgreSQL use EXPLAIN ANALYZE to see actual timings and row counts.

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 101;
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 101;

Output:
id | parent | notused | detail
---+--------+---------+------------
2  | 0      | 0       | SCAN orders
(1 row)

id | parent | notused | detail
---+--------+---------+--------------------------------------------------------------
3  | 0      | 0       | SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
(1 row)

Indexes are not free:

• Every INSERT, UPDATE and DELETE must also update every index, so write-heavy tables slow down.
• They use disk and memory.
• On low-selectivity columns (like gender or a boolean), the optimiser often ignores them, because reading most of the table directly is cheaper.
• Duplicate or unused indexes add cost with no benefit.

A covering index contains every column a query needs, so the database answers from the index alone without touching the table (an “index-only scan”). For example, CREATE INDEX idx ON orders(customer_id, order_date, amount) covers SELECT order_date, amount FROM orders WHERE customer_id = 101. SQL Server and PostgreSQL also support INCLUDE columns for this.

A recursive CTE has an anchor part (the starting rows, here the CEO) and a recursive part that joins back to the CTE itself to find the next level, repeating until no new rows appear. It is the standard way to walk org charts, category trees, bill-of-materials and graph paths. Use WITH RECURSIVE in PostgreSQL, MySQL and SQLite; SQL Server and Oracle accept plain WITH.

WITH RECURSIVE org AS (
  SELECT emp_id, name, manager_id, 1 AS level, name AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, o.level + 1, o.path || ' > ' || e.name
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT level, name, path FROM org
WHERE level <= 3 AND emp_id IN (1, 2, 4, 5, 6, 14)
ORDER BY path;

Output:
level | name         | path
------+--------------+-----------------------------------------
1     | Rajesh Kumar | Rajesh Kumar
2     | Karan Mehta  | Rajesh Kumar > Karan Mehta
3     | Neha Gupta   | Rajesh Kumar > Karan Mehta > Neha Gupta
2     | Priya Sharma | Rajesh Kumar > Priya Sharma
3     | Sneha Iyer   | Rajesh Kumar > Priya Sharma > Sneha Iyer
(5 rows)

Reports often need a row for every period, even ones with no data, and GROUP BY alone skips empty months. Generate the full series with a recursive CTE (PostgreSQL also has generate_series), then LEFT JOIN the data and COALESCE missing values to 0. Our data has no delivered orders outside January to July 2025, so August and September show 0.

WITH RECURSIVE months(m) AS (
  SELECT '2025-01-01'
  UNION ALL
  SELECT date(m, '+1 month') FROM months WHERE m < '2025-09-01'
)
SELECT strftime('%Y-%m', m) AS month,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM months
LEFT JOIN orders o
  ON strftime('%Y-%m', o.order_date) = strftime('%Y-%m', m)
 AND o.status = 'Delivered'
GROUP BY month
ORDER BY month;

Output:
month   | revenue
--------+--------
2025-01 | 2500
2025-02 | 4600
2025-03 | 7700
2025-04 | 1800
2025-05 | 7000
2025-06 | 4500
2025-07 | 5100
2025-08 | 0
2025-09 | 0
(9 rows)

A famous advanced pattern. For consecutive dates, date − ROW_NUMBER() stays constant within a streak, so it works as a group key for each “island”. Then group by it to get each streak’s start, end and length. The same idea finds consecutive seat numbers, uninterrupted subscriptions or missing invoice numbers (the “gaps”).

WITH logins(user_id, day) AS (
  VALUES (1,'2025-08-01'),(1,'2025-08-02'),(1,'2025-08-03'),
         (1,'2025-08-06'),(1,'2025-08-07'),(1,'2025-08-10')
),
grp AS (
  SELECT day,
         julianday(day) - ROW_NUMBER() OVER (ORDER BY day) AS island
  FROM logins
)
SELECT MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS days
FROM grp
GROUP BY island
ORDER BY streak_start;

Output:
streak_start | streak_end | days
-------------+------------+-----
2025-08-01   | 2025-08-03 | 3
2025-08-06   | 2025-08-07 | 2
2025-08-10   | 2025-08-10 | 1
(3 rows)

The frame clause controls exactly which rows the window covers relative to the current row. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the current order and the two before it. ROWS counts physical rows; RANGE groups rows with equal ORDER BY values together, which matters when there are ties. Moving averages smooth noisy metrics like daily sales.

SELECT order_id, order_date, amount,
       ROUND(AVG(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS moving_avg_3
FROM orders
WHERE status = 'Delivered'
ORDER BY order_date
LIMIT 7;

Output:
order_id | order_date | amount | moving_avg_3
---------+------------+--------+-------------
1        | 2025-01-15 | 2500   | 2500
2        | 2025-02-05 | 1200   | 1850
3        | 2025-02-18 | 3400   | 2367
5        | 2025-03-20 | 5600   | 3400
6        | 2025-03-25 | 2100   | 3700
8        | 2025-04-22 | 1800   | 3167
9        | 2025-05-11 | 4300   | 2733
(7 rows)

“First, latest, or Nth event per entity” is a top-N-per-group problem. Number the orders within each customer by date with ROW_NUMBER(), then keep row 1. Change the ORDER BY to DESC for the latest order. PostgreSQL also offers the shortcut SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, order_date.

WITH ranked AS (
  SELECT customer_id, order_id, order_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

Output:
customer_id | order_id | order_date | amount
------------+----------+------------+-------
101         | 1        | 2025-01-15 | 2500
102         | 2        | 2025-02-05 | 1200
103         | 4        | 2025-03-02 | 800
104         | 5        | 2025-03-20 | 5600
105         | 7        | 2025-04-10 | 950
106         | 9        | 2025-05-11 | 4300
107         | 13       | 2025-06-25 | 600
999         | 15       | 2025-07-12 | 1000
(8 rows)

Get each customer’s distinct order months, then use LAG to compare each month with the previous one. A difference of exactly one month means consecutive activity. This is the building block of retention and cohort analysis. Converting months to a number (year × 12 + month) makes the difference easy to compute in any database.

WITH months AS (
  SELECT DISTINCT customer_id,
         CAST(strftime('%Y', order_date) AS INT) * 12 + CAST(strftime('%m', order_date) AS INT) AS month_no
  FROM orders
),
diffs AS (
  SELECT customer_id, month_no,
         month_no - LAG(month_no) OVER (PARTITION BY customer_id ORDER BY month_no) AS gap
  FROM months
)
SELECT DISTINCT customer_id FROM diffs WHERE gap = 1 ORDER BY customer_id;

Output:
customer_id
-----------
101
102
(2 rows)

Aggregate to monthly revenue first, then use LAG to bring the previous month’s value onto the same row. Growth = (current − previous) / previous × 100. Use NULLIF(previous, 0) to avoid dividing by zero, and multiply by 100.0 to force decimal division.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
  FROM orders WHERE status = 'Delivered'
  GROUP BY month
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly;

Output:
month   | revenue | growth_pct
--------+---------+-----------
2025-01 | 2500    | NULL
2025-02 | 4600    | 84
2025-03 | 7700    | 67.4
2025-04 | 1800    | -76.6
2025-05 | 7000    | 288.9
2025-06 | 4500    | -35.7
2025-07 | 5100    | 13.3
(7 rows)

Add PARTITION BY to the running-total window, and the sum restarts for every customer. This answers questions like “when did each customer cross ₹5,000 lifetime spend?”: filter the running total in an outer query.

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS lifetime_spend
FROM orders
WHERE status = 'Delivered' AND customer_id IN (101, 102)
ORDER BY customer_id, order_date;

Output:
customer_id | order_date | amount | lifetime_spend
------------+------------+--------+---------------
101         | 2025-01-15 | 2500   | 2500
101         | 2025-02-18 | 3400   | 5900
101         | 2025-04-22 | 1800   | 7700
101         | 2025-07-30 | 4100   | 11800
102         | 2025-02-05 | 1200   | 1200
102         | 2025-03-25 | 2100   | 3300
102         | 2025-06-19 | 3900   | 7200
(7 rows)

Number the rows within each duplicate group, then delete every row with a number above 1. This version is flexible, because the ORDER BY decides which copy survives (here, the earliest signup). SQL Server can delete straight from the CTE (DELETE FROM cte WHERE rn > 1); PostgreSQL, MySQL and SQLite need the ids as below.

WITH ranked AS (
  SELECT customer_id,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY signup_date) AS rn
  FROM customers
  WHERE email IS NOT NULL
)
DELETE FROM customers
WHERE customer_id IN (SELECT customer_id FROM ranked WHERE rn > 1);

SELECT COUNT(*) AS customers_left FROM customers;

Output:
customers_left
--------------
7
(1 row)

A tricky classic (LeetCode 627 swaps ‘m’ and ‘f’). A single UPDATE with CASE does it in one pass, with no temporary value needed, because each row is evaluated against its original value. Here every Pending order becomes Delivered and every Delivered order becomes Pending.

UPDATE orders
SET status = CASE status
               WHEN 'Pending'   THEN 'Delivered'
               WHEN 'Delivered' THEN 'Pending'
               ELSE status
             END;
SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY status;

Output:
status    | orders
----------+-------
Cancelled | 2
Delivered | 2
Pending   | 12
(3 rows)

Interviewers sometimes ban LIMIT and window functions to test logic. A correlated subquery counts how many distinct salaries are higher than each salary. The Nth highest has exactly N − 1 distinct salaries above it. For N = 3:

SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
);

Output:
salary
------
120000
(1 row)

Boyce-Codd Normal Form (BCNF) is a stricter 3NF: for every functional dependency X → Y, X must be a super key. It catches rare cases 3NF allows, such as a table where a non-key column determines part of a composite key (for example, each instructor teaches one subject, but a subject has several instructors).

Denormalize when read performance matters more than write simplicity: reporting tables, data warehouses, search indexes, or pre-computed counters like order_count on a customers table. Do it deliberately, and keep the redundant data in sync with triggers, application code or scheduled jobs.

In a data warehouse, a fact table stores measurable events (sales amount, quantity, clicks) with foreign keys to dimension tables, which hold descriptive context (date, customer, product, store).

• Star schema: one central fact table joined directly to denormalized dimension tables. Fewer joins, simpler and faster queries, and the most common choice.
• Snowflake schema: dimensions are normalized into sub-tables (product → category → department). Less redundancy, but more joins.

Expect this question in data engineer and data analyst interviews, along with slowly changing dimensions (SCD Type 1 overwrites, Type 2 keeps history rows).

Partitioning splits one large table into smaller pieces within the same database server, by range (order_date by month), list (region) or hash. Queries that filter on the partition key read only the relevant partitions (“partition pruning”), and old data can be dropped quickly by partition.

Sharding splits data across multiple database servers, for example users with ids 1 to 1M on server A and the rest on server B. It scales writes and storage horizontally, but makes cross-shard joins, transactions and rebalancing much harder, so it is used only when one server cannot cope.

Many systems use both: PostgreSQL for orders and payments, Redis for caching and sessions.

MVCC (Multi-Version Concurrency Control), used by PostgreSQL, MySQL InnoDB and Oracle, keeps several versions of a row. Readers see a consistent snapshot without blocking writers, and writers do not block readers. Old versions are cleaned up later (VACUUM in PostgreSQL, the undo log purge in InnoDB).

• Pessimistic locking locks the row before changing it (SELECT ... FOR UPDATE), assuming conflicts are likely. Safe, but reduces concurrency.
• Optimistic locking does not lock. It checks at update time that nobody else changed the row, usually with a version column: UPDATE ... SET qty = 5, version = version + 1 WHERE id = 7 AND version = 3. If 0 rows are updated, retry. Best when conflicts are rare.

SQL injection happens when user input is pasted directly into a SQL string, letting an attacker change the query. If a login query is built as "... WHERE user = '" + input + "'" and the input is ' OR '1'='1, the condition is always true and the attacker logs in without a password. It can also be used to read or delete entire tables.

Prevention:

• Always use parameterised queries (prepared statements), where input is sent separately from the SQL text, for example cursor.execute("SELECT * FROM users WHERE email = %s", (email,)).
• Use an ORM or query builder that parameterises for you.
• Give the application database user the least privileges it needs.
• Validate input types and lengths, and never show raw database errors to users.

-- Vulnerable: input concatenated into the query
SELECT * FROM users WHERE email = '' OR '1'='1';

-- Safe: placeholder, value bound separately by the driver
SELECT * FROM users WHERE email = ?;

A normal view stores only the query, and runs it every time you read from the view. A materialized view stores the query’s result physically, like a table, so reads are very fast. The data can become stale and must be refreshed, on a schedule or on demand (REFRESH MATERIALIZED VIEW in PostgreSQL). Oracle can refresh automatically; SQL Server’s equivalent is an indexed view; MySQL has none, so people use summary tables updated by jobs. Use them for expensive aggregations behind dashboards.

NTILE(n) divides ordered rows into n roughly equal buckets and returns the bucket number. It is used for segmentation (top 25% customers), percentile-based pricing and A/B group assignment. Here bucket 1 holds the highest spenders.

WITH spend AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders WHERE status = 'Delivered'
  GROUP BY customer_id
)
SELECT customer_id, total,
       NTILE(4) OVER (ORDER BY total DESC) AS quartile
FROM spend
ORDER BY total DESC;

Output:
customer_id | total | quartile
------------+-------+---------
101         | 11800 | 1
102         | 7200  | 1
104         | 5600  | 2
106         | 4300  | 2
103         | 2700  | 3
999         | 1000  | 3
107         | 600   | 4
(7 rows)

Compare each order with the customer’s previous one using LAG, flag any order that did not increase, and keep customers with no such flag and at least two orders. Scenario questions like this test whether you can combine window functions with aggregation in clean steps.

WITH seq AS (
  SELECT customer_id, amount,
         LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev
  FROM orders
  WHERE status = 'Delivered'
)
SELECT customer_id
FROM seq
GROUP BY customer_id
HAVING COUNT(*) >= 2
   AND SUM(CASE WHEN prev IS NOT NULL AND amount <= prev THEN 1 ELSE 0 END) = 0;

Output:
customer_id
-----------
102
(1 row)