SQL interview questions and answers

What is SQL?

SQL (Structured Query Language) is the language used to read and change data in relational databases such as MySQL, PostgreSQL, SQL Server and Oracle. You describe what result you want, and the database decides how to get it.

What is the difference between DDL, DML, DCL and TCL?

DDL defines structure: CREATE, ALTER, DROP. DML works with rows: SELECT, INSERT, UPDATE, DELETE. DCL controls access: GRANT, REVOKE. TCL controls transactions: COMMIT, ROLLBACK, SAVEPOINT.

What is a primary key?

A primary key is the column, or set of columns, that uniquely identifies each row. It cannot be NULL and a table has only one. Most databases create an index on it automatically.

What is a foreign key?

A foreign key is a column that points to the primary key of another table. The database then refuses rows that point to something that does not exist, which keeps the data consistent.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id)
);

What is the difference between a primary key and a unique key?

Both stop duplicate values. A table has one primary key and it cannot be NULL. It can have many unique keys, and most databases allow NULL in a unique column.

What is NULL and how do you check for it?

NULL means the value is unknown or missing. It is not zero and not an empty string. Any comparison with NULL, even NULL = NULL, is unknown rather than true, so you must use IS NULL or IS NOT NULL.

-- wrong: returns no rows
SELECT * FROM users WHERE phone = NULL;

-- right
SELECT * FROM users WHERE phone IS NULL;

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows you choose with WHERE and can be rolled back inside a transaction. TRUNCATE removes all rows quickly and usually resets identity counters. DROP removes the whole table, structure included.

What is the difference between CHAR and VARCHAR?

CHAR has a fixed length and pads shorter values with spaces. VARCHAR stores only the characters you give it, up to the limit. Use CHAR for codes that always have the same length, such as a two-letter country code.

In what order does the database run the clauses of a SELECT query?

Logically it runs FROM and JOIN first, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. This is why you cannot use a column alias from SELECT inside WHERE: WHERE runs before SELECT.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregate functions such as COUNT or SUM. Put every condition that does not need an aggregate in WHERE, because it removes rows earlier.

SELECT department, COUNT(*) AS staff
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 10;

What is the difference between UNION and UNION ALL?

Both stack the results of two queries with the same columns. UNION removes duplicate rows, which needs a sort or hash step. UNION ALL keeps every row and is faster, so use it when duplicates are impossible or wanted.

What does DISTINCT do?

DISTINCT removes duplicate rows from the result, comparing all the selected columns together. SELECT DISTINCT city, state returns each city and state pair once, not each city once.

How does LIKE work?

LIKE matches text patterns. % matches any number of characters and _ matches exactly one. A pattern that starts with %, such as LIKE '%son', usually cannot use a normal index, so it scans the table.

SELECT name FROM customers
WHERE name LIKE 'Ra_%';  -- 'Ra', then one character, then anything

What is the difference between IN and EXISTS?

IN checks whether a value is in a list or in the result of a subquery. EXISTS checks whether a subquery returns at least one row and stops at the first match. Modern optimisers often run both the same way, but NOT IN returns no rows if the subquery contains a NULL, so prefer NOT EXISTS.

Is BETWEEN inclusive?

Yes. BETWEEN 10 AND 20 includes both 10 and 20. Be careful with dates and times: BETWEEN '2026-01-01' AND '2026-01-31' misses most of 31 January if the column stores a time. Use created_at >= start AND created_at < next_day instead.

What is a CASE expression?

CASE is SQL's if-else. It returns a value based on conditions and can be used in SELECT, ORDER BY and even inside aggregates.

SELECT name,
  CASE
    WHEN salary >= 100000 THEN 'high'
    WHEN salary >= 50000 THEN 'medium'
    ELSE 'low'
  END AS band
FROM employees;

What does COALESCE do?

COALESCE returns the first value in its list that is not NULL. It is the usual way to show a default, for example COALESCE(phone, 'not given').

What is a JOIN?

A JOIN combines rows from two tables using a condition, usually matching a foreign key to a primary key. The type of join decides what happens to rows that have no match.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table and fills the right side with NULL when there is no match.

-- customers with no orders still appear, with NULL order_id
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

What is a FULL OUTER JOIN?

A FULL OUTER JOIN keeps all rows from both tables, matched where possible and filled with NULL where not. MySQL does not support it directly, so you combine a LEFT JOIN and a RIGHT JOIN with UNION.

What is a CROSS JOIN?

A CROSS JOIN pairs every row of one table with every row of the other. Ten rows times twenty rows gives two hundred. It is useful for generating combinations, such as every size with every colour.

What is a self join?

A self join joins a table to itself using two aliases. The classic example is an employees table where manager_id points to another employee.

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

How do you find rows in one table that have no match in another?

Use a LEFT JOIN and keep the rows where the right side is NULL, or use NOT EXISTS. Both find, for example, customers who never placed an order.

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

Why can a LEFT JOIN suddenly behave like an INNER JOIN?

Because a condition on the right table was put in WHERE. Rows without a match have NULL there, the WHERE condition fails for them, and they disappear. Move that condition into the ON clause to keep the unmatched rows.

-- drops customers without paid orders
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'

-- keeps them
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'

What do aggregate functions do?

COUNT, SUM, AVG, MIN and MAX turn many rows into one value per group. They ignore NULL values, except COUNT(*), which counts rows.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts the different non-NULL values.

How does GROUP BY work?

GROUP BY puts rows with the same values in the listed columns into one group, and aggregates are calculated per group. Every selected column must either be in GROUP BY or be inside an aggregate.

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS spent
FROM orders
GROUP BY customer_id;

What is a correlated subquery?

A correlated subquery refers to a column of the outer query, so it is evaluated for each outer row. A normal subquery runs once on its own. Correlated subqueries can be slow on large tables; a join or a window function is often faster.

-- employees paid more than their department average
SELECT e.name
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees x
  WHERE x.department_id = e.department_id
);

What is a CTE?

A CTE (common table expression) is a named temporary result defined with WITH at the top of a query. It makes long queries readable by splitting them into steps. A recursive CTE can walk hierarchies such as an org chart.

WITH monthly AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM monthly WHERE total > 1000;

How do you find the second highest salary?

Rank the distinct salaries and pick rank 2. DENSE_RANK handles ties correctly. A shorter version uses MAX with a subquery.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

-- or
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

How do you find duplicate rows?

Group by the columns that should be unique and keep the groups with more than one row.

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

How do you delete duplicates but keep one row?

Number the rows inside each duplicate group with ROW_NUMBER and delete every row numbered above 1. The extra derived table lets this run in MySQL too, which does not allow selecting from the table you are deleting from directly.

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id,
      ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) numbered
  WHERE rn > 1
);

What is a window function?

A window function calculates a value across a set of related rows but, unlike GROUP BY, keeps every row in the result. You write it with OVER (...). Ranking, running totals and comparing a row with the previous one are all window-function problems.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

For the salaries 900, 800, 800, 700: ROW_NUMBER gives 1, 2, 3, 4 with no ties. RANK gives 1, 2, 2, 4 and skips a number after a tie. DENSE_RANK gives 1, 2, 2, 3 with no gaps.

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;

What does PARTITION BY do?

PARTITION BY splits the rows into groups for the window function, which then restarts for each group. RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) ranks employees inside each department.

How do you calculate a running total?

Use SUM as a window function ordered by date. Each row then shows the total of all rows up to and including it.

SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

What do LAG and LEAD do?

LAG reads a value from an earlier row and LEAD from a later row, in the window order. They are the simple way to compare each month with the month before.

SELECT month, revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

How do you get the top 3 rows in each group?

Number the rows inside each group with ROW_NUMBER (or DENSE_RANK if ties should be kept) and filter on that number in an outer query. You cannot filter on a window function in WHERE of the same query.

WITH ranked AS (
  SELECT name, department_id, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

What is an index and why does it make queries faster?

An index is a separate sorted structure, usually a B-tree, that points to rows. Instead of reading the whole table, the database walks the tree to the matching rows, like using the index at the back of a book.

When can an index hurt?

Every INSERT, UPDATE and DELETE must also update each index, so too many indexes slow writes and use storage. An index also helps little on a column with very few distinct values, such as a yes/no flag, when most rows match.

What is the difference between a clustered and a non-clustered index?

A clustered index decides the physical order of the rows, so a table has only one. In MySQL InnoDB it is the primary key. A non-clustered index is a separate structure that points back to the rows, and a table can have many.

What is a composite index and the leftmost prefix rule?

A composite index covers several columns in a fixed order. An index on (last_name, first_name) helps queries that filter on last_name, or on last_name and first_name, but usually not a query on first_name alone.

Why can a function on a column stop the index from being used?

The index stores the raw column values. If you wrap the column in a function, the database must calculate it for every row. Rewrite the condition as a range on the plain column.

-- cannot use an index on created_at
WHERE YEAR(created_at) = 2026

-- can
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

How do you find out why a query is slow?

Run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see the plan. Look for full table scans on large tables, a missing index on join or filter columns, and row estimates that are far from the real counts.

What is normalization?

Normalization organises tables to remove repeated data. 1NF: each column holds one value. 2NF: every non-key column depends on the whole key. 3NF: non-key columns depend only on the key, not on each other. The goal is to update a fact in exactly one place.

When would you denormalize?

When reads matter more than writes, for example in reporting tables or a dashboard that joins many tables on every request. You accept duplicated data and extra update work in exchange for simpler, faster queries.

What is a transaction and what does ACID mean?

A transaction groups statements so they succeed or fail together. Atomicity: all or nothing. Consistency: rules and constraints still hold afterwards. Isolation: concurrent transactions do not see each other's half-done work. Durability: committed data survives a crash.

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

What are isolation levels?

They decide how much concurrent transactions can see of each other. Read Uncommitted allows dirty reads. Read Committed prevents dirty reads. Repeatable Read also keeps rows you already read unchanged. Serializable behaves as if transactions ran one by one. Higher levels are safer but allow less concurrency.

What is a deadlock?

A deadlock happens when two transactions each hold a lock the other needs, so neither can continue. The database detects it and rolls one back. Taking locks in the same order everywhere and keeping transactions short makes deadlocks rare.

What is a view?

A view is a saved query that you can select from like a table. It hides complex joins and can limit which columns a user can see. A normal view stores no data; a materialized view stores the result and must be refreshed.

What is the difference between a stored procedure and a function?

A function returns a value and can be used inside a query. A stored procedure is called on its own with CALL or EXEC, can return several result sets, and is used for multi-step work such as a batch update.

How do you prevent SQL injection?

Never build SQL by joining strings with user input. Use parameterised queries or prepared statements, so the input is always treated as data and never as SQL.

// unsafe
query("SELECT * FROM users WHERE email = '" + email + "'");

// safe
query('SELECT * FROM users WHERE email = ?', [email]);