SQL Interview Questions and Answers

SELECT reads rows from a table. The basic shape is:

• SELECT which columns you want
• FROM which table
• WHERE which rows (a filter)
• ORDER BY in what order

SELECT name, city
FROM employees
WHERE city = 'Bengaluru'
ORDER BY name;

Avoid SELECT * in application code: it fetches columns you don't need, breaks when columns are added or reordered, and prevents some index optimisations. It's fine for quick exploration.

Text values use single quotes ('Bengaluru'). Double quotes are for identifiers like column names in standard SQL and PostgreSQL.

Likely follow-up: Why is SELECT * discouraged in production queries?

We write SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT, but the database logically processes them in this order:

1. FROM (and JOINs): get the rows
2. WHERE: filter rows
3. GROUP BY: form groups
4. HAVING: filter groups
5. SELECT: compute the output columns and aliases
6. DISTINCT: remove duplicates
7. ORDER BY: sort
8. LIMIT / OFFSET: cut the result

This explains common errors. A column alias defined in SELECT can be used in ORDER BY (which runs later), but not in WHERE in most databases (which runs earlier):

SELECT name, salary / 100000 AS lakhs
FROM employees
WHERE salary >= 1500000
ORDER BY lakhs DESC, name;

Writing WHERE lakhs >= 15 fails in PostgreSQL, MySQL and SQL Server, because the alias doesn't exist yet when WHERE runs.

Likely follow-up: Why can't you use an aggregate function like COUNT in a WHERE clause?

A join combines rows from two tables using a related column, usually a foreign key like employees.department_id → departments.id.

• INNER JOIN: only rows that have a match in both tables
• LEFT JOIN: all rows from the left table, plus matches (NULLs where there's no match)
• RIGHT JOIN: all rows from the right table, plus matches
• FULL OUTER JOIN: all rows from both, matched where possible
• CROSS JOIN: every combination of rows
• SELF JOIN: a table joined to itself

With A = employees and B = departments, here's how many rows each join gives:

SELECT
  (SELECT COUNT(*) FROM employees e INNER JOIN departments d ON d.id = e.department_id) AS inner_rows,
  (SELECT COUNT(*) FROM employees e LEFT JOIN departments d ON d.id = e.department_id) AS left_rows,
  (SELECT COUNT(*) FROM employees e RIGHT JOIN departments d ON d.id = e.department_id) AS right_rows,
  (SELECT COUNT(*) FROM employees e FULL OUTER JOIN departments d ON d.id = e.department_id) AS full_rows;

Inner gives 9 (Aditya has no department). Left adds Aditya (10). Right adds Marketing, which has no employees (10). Full adds both (11).

Likely follow-up: Venn diagrams are a simplification of joins. When are they misleading?

An INNER JOIN returns one row for every pair of rows where the ON condition is true. Rows without a match on either side are dropped.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON d.id = e.department_id
ORDER BY d.name, e.name;

Aditya Joshi (no department) and Marketing (no employees) don't appear. JOIN on its own means INNER JOIN.

Table aliases (e, d) keep queries short, and prefixing columns (e.name, d.name) is required when both tables have a column with the same name; otherwise you get an "ambiguous column" error.

Likely follow-up: What is the difference between writing the join condition in ON and listing tables with commas plus a WHERE condition?

Aggregate functions take many rows and return one value:

• COUNT(): number of rows or non-NULL values
• SUM(): total
• AVG(): average
• MIN() / MAX(): smallest and largest

SELECT COUNT(*) AS employees,
       COUNT(department_id) AS with_department,
       SUM(salary) AS total,
       MIN(salary) AS lowest,
       MAX(salary) AS highest,
       AVG(salary) AS average
FROM employees;

The company spends ₹1.3 crore a year on salaries, an average of ₹13 lakh per employee.

All aggregates except COUNT(*) ignore NULLs: COUNT(department_id) is 9 because Aditya's department is NULL. Without GROUP BY, the whole table is one group, so you get one row.

Likely follow-up: What does SUM return when every value is NULL, or there are no rows?

GROUP BY splits rows into groups that share the same value(s), then aggregate functions run once per group.

Headcount and salary cost per department:

SELECT department_id, COUNT(*) AS headcount, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;

All the NULLs form one group of their own (Aditya, without a department).

The rule: every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT department_id, name, COUNT(*) ... GROUP BY department_id is an error in PostgreSQL and SQL Server (and in MySQL with the default ONLY_FULL_GROUP_BY mode), because there are several names per department and the database can't know which one to show.

Likely follow-up: Why does MySQL sometimes allow non-grouped columns in SELECT, and why is that dangerous?

A subquery is a query inside another query. In WHERE, it often supplies a value to compare against.

Employees earning more than the company average:

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

The subquery runs once and returns ₹13,00,000. A subquery that returns one value is called a scalar subquery; if it returned several rows, you'd need IN, ANY or EXISTS instead of >.

You can't write WHERE salary > AVG(salary) directly, because aggregates aren't allowed in WHERE.

Likely follow-up: What happens if a scalar subquery returns more than one row?

A correlated subquery refers to a column from the outer query, so it's evaluated for each outer row.

Employees who earn more than the average of their own department:

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

Engineering's average is ₹15.25 lakh and Sales' is ₹12 lakh. Rahul and Meera are alone in their departments, so they equal their own average, not exceed it.

Correlated subqueries can be slow on big tables if the database really runs them once per row, though optimisers often rewrite them as joins. The same result can be written with a window function: AVG(salary) OVER (PARTITION BY department_id).

Likely follow-up: Rewrite this query using a window function.

An index is a separate data structure that lets the database find rows without scanning the whole table, like the index at the back of a book.

Most indexes are B-trees: balanced trees that keep values sorted. Finding one value in a million rows takes about 3 or 4 page reads instead of reading every row. Because the values are sorted, B-trees also speed up range queries (BETWEEN, >, <), ORDER BY and MIN/MAX.

CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Now this doesn't need to scan every order:
SELECT * FROM orders WHERE customer_id = 3;

Other index types: hash indexes (equality only), GIN in PostgreSQL (for JSON, arrays and full-text search), and full-text indexes for word search.

Check whether your index is used with EXPLAIN: a "Seq Scan" (PostgreSQL) or "type: ALL" (MySQL) on a big table means a full table scan.

Likely follow-up: Why does an index help ORDER BY?

• A clustered index determines the physical order of the rows: the table data itself is stored in the index's order. A table can have only one. In SQL Server and MySQL (InnoDB), the primary key is the clustered index by default.
• A non-clustered (secondary) index is a separate structure holding the indexed values plus a pointer to the row (in InnoDB, the primary key value). A table can have many.

-- SQL Server syntax
CREATE CLUSTERED INDEX ix_orders_date ON orders (order_date);
CREATE NONCLUSTERED INDEX ix_orders_customer ON orders (customer_id);

A lookup through a non-clustered index needs an extra step to fetch the row, unless the index contains every column the query needs. That's called a covering index (SQL Server and PostgreSQL support INCLUDE (amount) for this).

PostgreSQL doesn't keep tables permanently clustered; its tables are "heaps", and every index is secondary.

Likely follow-up: Why is an auto-incrementing integer often a good clustered key, and a random UUID a poor one?

SELECT name, hire_date
FROM employees
WHERE hire_date >= '2023-01-01'
  AND hire_date < '2024-01-01'
ORDER BY hire_date DESC;

A half-open date range (>= the start, < the next year) works for both dates and timestamps, and can use an index on hire_date, unlike YEAR(hire_date) = 2023.

SELECT c.name, c.signup_date, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.signup_date >= '2024-02-01'
  AND c.signup_date < '2024-03-01'
GROUP BY c.id, c.name, c.signup_date
ORDER BY c.signup_date;

The LEFT JOIN and COUNT(o.id) mean a customer with no orders would still appear, with 0.