SQL Interview Questions and Answers

A join combines rows from two tables on a related column. Take two tables:

employees(id, name, dept_id) and departments(id, dept_name).

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id;

This lists every employee, and dept_name is NULL for an employee with no department. With INNER JOIN that employee would disappear. A self join joins a table to itself, for example to find an employee's manager.

What they are checking: Whether you know what happens to unmatched rows, not just the names

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY and can use aggregate functions.

SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 20000          -- row filter, runs first
GROUP BY dept_id
HAVING COUNT(*) > 5;          -- group filter, runs after aggregation

• WHERE cannot contain aggregates such as COUNT(*) or AVG().
• Put conditions in WHERE whenever possible, because it reduces rows before grouping and is faster.
• Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

What they are checking: Understanding of SQL's logical execution order

A primary key uniquely identifies each row in its own table. A foreign key is a column that points to the primary key of another table. It enforces the link between them.

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
);

The foreign key blocks adding an employee with a department that does not exist. It also controls what happens on delete (ON DELETE CASCADE, SET NULL, or restrict).

What they are checking: Referential integrity basics

DELETE removes selected rows, TRUNCATE empties the whole table, DROP removes the table itself.

DELETE FROM orders WHERE status = 'cancelled';
TRUNCATE TABLE temp_logs;
DROP TABLE temp_logs;

TRUNCATE also resets auto-increment counters in MySQL.

What they are checking: DML vs DDL and the side effects

Use DENSE_RANK so ties are handled correctly, or LIMIT with OFFSET on distinct values.

-- 3rd highest salary (change 3 to N)
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 3
LIMIT 1;

Alternative without window functions:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;   -- OFFSET is N - 1

• ROW_NUMBER would count tied salaries as separate ranks, which is wrong here.
• RANK leaves gaps after ties, so the 3rd rank may not exist.
• If fewer than N distinct salaries exist, both queries return no row.

What they are checking: Handling of ties and knowing rank functions

Group by department to get just the value. Or join back, or use a window function, to get the full row.

-- Only the max value
SELECT dept_id, MAX(salary) AS max_salary
FROM employees
GROUP BY dept_id;

-- Whole row of the top earner (ties return all)
SELECT e.*
FROM employees e
JOIN (
  SELECT dept_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY dept_id
) m ON m.dept_id = e.dept_id AND m.max_salary = e.salary;

-- Same with a window function
SELECT * FROM (
  SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees e
) t WHERE rnk = 1;

The common mistake is selecting name together with MAX(salary) in a GROUP BY query. Name is not aggregated, so PostgreSQL rejects it and MySQL with ONLY_FULL_GROUP_BY does too.

What they are checking: The classic 'greatest-n-per-group' pattern

Keep the row with the smallest id for each duplicate key and delete the rest.

DELETE FROM contacts
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id
    FROM contacts
    GROUP BY email
  ) keep
);

The extra derived table (keep) is needed in MySQL. MySQL refuses to read and delete from the same table in one subquery. It also works in PostgreSQL.

First find duplicates to verify:

SELECT email, COUNT(*) FROM contacts GROUP BY email HAVING COUNT(*) > 1;

Run it inside a transaction or on a backup first. To prevent the problem permanently, add ALTER TABLE contacts ADD CONSTRAINT uq_email UNIQUE (email);.

What they are checking: Safe data cleanup and prevention

An index is a separate sorted structure (usually a B-tree). It lets the database find rows without scanning the whole table, like a book index.

CREATE INDEX idx_orders_customer ON orders (customer_id);

Benefits: fast lookups, joins, sorting and range queries on the indexed column.

Costs:

• Extra disk space.
• Slower INSERT, UPDATE and DELETE, since every index must be maintained.
• Too many indexes confuse the planner and waste memory.

Good candidates are columns used in WHERE, JOIN, ORDER BY and foreign keys with high selectivity. Poor candidates are tiny tables and low-cardinality columns such as a boolean. A composite index (a, b) helps queries that filter on a, or on a and b. It does not help a filter on b alone (leftmost prefix rule).

What they are checking: Costs and benefits, not just definitions

ACID is the set of guarantees of a database transaction.

• Atomicity: all steps happen or none do.
• Consistency: a transaction moves the database from one valid state to another, respecting constraints.
• Isolation: concurrent transactions do not see each other's half-finished work.
• Durability: once committed, data survives a crash (through the write-ahead log).

Example, transferring 500 from account A to B:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

If the second update fails, ROLLBACK restores the first, so money never disappears (atomicity). Constraints such as balance >= 0 keep it consistent. Another session reading mid-transfer does not see a partial state (isolation). After COMMIT a power cut does not lose it (durability).

What they are checking: Concepts tied to a concrete example

Normalization organizes tables to reduce redundancy and update anomalies.

UNION combines result sets and removes duplicate rows. UNION ALL combines them and keeps every row.

A stored procedure is a named block of SQL, already compiled, saved in the database. You run it with CALL. A function returns a value and can be used inside queries.

A view is a saved query that behaves like a virtual table. It stores no data, and the query runs each time it is read.

• 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.

A self join joins a table to itself using two aliases. It fits hierarchies stored in one table.

Both enforce uniqueness, but they differ in purpose and NULL rules.

NULL means unknown or missing, not zero or an empty string. Any comparison with NULL (= NULL) yields unknown, so you must use IS NULL or IS NOT NULL.

SQL injection happens when user input is concatenated into a query, letting an attacker change its meaning.

Constraints enforce rules on data at the database level:

A trigger is code that the database runs by itself before or after an INSERT, UPDATE or DELETE on a table.

CHAR(n) is fixed length and pads shorter values with spaces. VARCHAR(n) is variable length and stores only what is needed plus a small length prefix.

• COMMIT makes all changes of the transaction permanent and visible to others.
• ROLLBACK discards all changes since BEGIN.
• SAVEPOINT marks a point inside a transaction so you can roll back only part of the work.

A window function computes a value across a set of rows related to the current row. Unlike GROUP BY, it does not collapse them. You define the set with OVER (PARTITION BY ... ORDER BY ...).

For salaries 90, 80, 80, 70 ordered descending:

• ROW_NUMBER: unique sequence, ties broken arbitrarily.
• RANK: ties share a rank, then a gap.
• DENSE_RANK: ties share a rank, no gap.

SELECT name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Other common ones: LAG, LEAD, SUM() OVER, NTILE, FIRST_VALUE.

What they are checking: Whether you know analytics SQL beyond GROUP BY

Isolation levels trade consistency for concurrency. The problems they prevent:

• Dirty read: reading uncommitted data of another transaction.
• Non-repeatable read: the same row read twice returns different values.
• Phantom read: the same query returns new rows the second time.

A clustered index sets the physical order of the table rows, so the table data is the index. A non-clustered index is a separate structure with pointers back to the rows.

A Common Table Expression (WITH) is a named temporary result you can reference in the following query. It improves readability and supports recursion.

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. The database detects it and aborts one transaction as the victim.

Example: T1 updates row A then wants row B, while T2 updated row B and wants row A.

Denormalize when read performance matters more than write simplicity and joins have become the bottleneck, after measuring.

Both test membership in a subquery result. IN compares a value against a list. EXISTS checks whether the correlated subquery returns at least one row.

A non-correlated subquery runs once and does not reference the outer query. A correlated subquery references outer columns, so conceptually it runs once per outer row.

A lost update happens when two transactions read the same value, both change it, and the second write overwrites the first.

Example: both read stock = 10, one sets 9 and the other sets 8, so one sale vanishes.

OLTP handles many small, concurrent transactions for running the business. OLAP handles few, heavy analytical queries over large history.

Relational databases use a fixed schema, SQL, joins and ACID transactions. NoSQL stores (document, key-value, wide-column, graph) trade some of that for flexible schemas and horizontal scale.

A foreign key constraint does not create an index on the child column in PostgreSQL. (MySQL InnoDB does create one.) Without it:

• Joins from parent to child scan the whole child table.
• Deleting or updating a parent row forces a scan of the child table to check references. This can be slow and cause lock contention.

Rank within each department using a window function, then filter.

Group by the columns that define a duplicate and keep groups with more than one row.

Measure before changing anything.

Make the check and the update atomic using row locking or a constrained update.

Option 1, pessimistic lock:

Start from the execution plan, then work through the options in order of effort.

Use an anti join: LEFT JOIN and keep rows with no match, or NOT EXISTS.

Truncate the date to the month, filter the range, group and sort.

Simple pagination uses LIMIT and OFFSET. But the database must read and throw away all skipped rows, so page 10,000 is slow.

Common reasons the planner ignores an index:

Avoid anything that locks or rewrites the whole table during peak traffic.

One giant DELETE holds locks for a long time, bloats the transaction log and can stall replication. Delete in small batches instead.

Use a junction (bridge) table. It holds the foreign keys of both sides, plus any attributes of the relationship itself.

Compare each salary to a per-department average, using a join or a window function.

Use SUM() OVER with an ordered window.

For simple conditional logic, CASE inside a query is enough and keeps everything portable. To pivot rows into columns, combine CASE with an aggregate.

Use a subquery that takes the maximum of everything below the overall maximum.