Common SQL Interview Questions

Going into a SQL interview, you will be asked a small set of questions that barely changes between companies. Interviewers want to know whether you can be trusted with the data behind a product, so they ask about tables, joins, nulls, indexes, and transactions.

That predictability is good news for anyone preparing, because it means the basics are worth knowing properly rather than approximately. An interviewer usually knows within two answers whether a candidate has read about SQL or written it.

Knowing what a foreign key does is one thing. Saying what happens to the child rows when the parent is deleted is the thing that gets you the offer.

It helps to treat an answer the way the database treats a query. A planner states the result it expects, shows the path it will take, and can be checked against reality.

A good interview answer does the same: state the result in plain language, show a small query or table, name the way it usually goes wrong, and say how you would verify it.

The questions below are grouped by level. Start at the beginner section for a first developer or analyst role, and read the later sections for roles where you own schema, performance, or data integrity.

Examples use PostgreSQL syntax; the concepts are portable.

How to Answer a SQL Interview Question

Start with the shape of the data before naming syntax. Define the result grain, the relationships, the constraints, and the database engine in play.

A practical answer usually has four parts:

1. State the concept in plain language.
2. Show a small query or table definition.
3. Explain the failure mode.
4. Name how you would verify the answer.

An index answer should mention the query pattern and EXPLAIN. A transaction answer should name the rollback boundary. A join answer should predict row multiplication. Those details expose the reasoning plan instead of presenting a memorized result.

Beginner Questions

These are the questions asked first in almost every screen. They look simple, and that is the trap: the interviewer is listening for whether the definition comes with a consequence attached.

1. What is SQL, and what is it used for?

SQL is the language for working with relational facts: rows, tables, relationships, constraints, and sets. It is not only a way to fetch records for an application.

A strong answer covers SQL's role in reading, changing, relating, and protecting stored facts. Tables keep different facts separate, while queries and constraints connect them without forcing everything into one record.

2. What is the difference between a table, a row, and a column?

A table describes one kind of fact. A row is one instance of that fact. A column is one attribute of that fact.

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Here users is the table, each user is a row, and email is a column. The important interview detail is table meaning. If a table tries to describe two different facts at once, queries become harder and constraints become weaker.

3. What is the difference between SQL and NoSQL?

A relational SQL database stores data in tables with defined columns. Primary keys identify rows, foreign keys connect tables, constraints reject invalid values, and transactions protect related changes.

NoSQL is an umbrella label rather than one product. It covers document databases, key-value stores, graph databases, wide-column stores, and search engines, each with a different API and different guarantees, so a claim about one family does not automatically apply to another.

The answer an interviewer wants is a workload answer, not a winner. Relational SQL suits stable entities whose relationships must stay valid, such as customers and orders. A specific NoSQL model suits a clear access pattern, such as reading one nested document or fetching a value by key. The SQL vs NoSQL comparison works through the criteria in full.

4. What is the difference between MySQL, MariaDB, PostgreSQL, and SQLite?

All four are relational databases and all four run SQL, so the differences that matter in an interview are the operational ones.

• MySQL is a client/server database, the long-time default for web applications, and widely hosted and documented.
• MariaDB is an open-source database in the MySQL family. It speaks the MySQL client protocol, while keeping its own releases, features, storage engines, and compatibility rules, so compatibility is something you test rather than assume.
• PostgreSQL is an object-relational database that combines SQL with a broad set of types, constraints, indexes, and extensions.
• SQLite is an embedded engine. The application reads and writes a database file directly, without a separate database server.

Name the shape first, server or file, then say which engine the role actually uses and where its syntax differs from the others. The database engines guide covers each one on its own page.

5. What is a primary key?

A primary key identifies a row and should be stable, unique, and present. In most application tables, a generated integer or UUID primary key is a practical choice.

Business identifiers can still be unique without becoming the relationship key:

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL
);

The id is the stable relationship key, while the unique sku remains a business identifier that can follow its own change rules.

6. What is a foreign key?

A foreign key enforces that values in one table match values in another table.

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users (id),
    total_cents INTEGER NOT NULL
);

Now an order cannot point at a missing user. That is the database doing useful work instead of leaving the rule as a comment in application code.

A complete answer also names the delete rule: restrict the parent delete, cascade it to dependent children, or set an optional reference to null according to the relationship's meaning.

7. What does NULL mean in SQL?

NULL means the value is unknown, missing, or not applicable. It does not mean an empty string, zero, or false.

That distinction matters because comparisons with NULL use different syntax:

SELECT id, email
FROM users
WHERE deleted_at IS NULL;

This is not the same as deleted_at = NULL. A strong answer also notes that nulls affect aggregates, left joins, and engine-specific uniqueness behavior.

8. What is the difference between an inner join and a left join?

An inner join returns only rows where the relationship matches. A left join returns every row from the left table and fills unmatched right-side columns with NULL.

SELECT users.email, orders.id
FROM users
JOIN orders ON orders.user_id = users.id;

That query excludes users with no orders because there is no matching order row.

SELECT users.email, orders.id
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

That query keeps users with no orders, and the order columns are NULL for those rows.

Use an inner join when the match must exist and a left join when missing relationships belong in the result. The SQL joins guide covers the reporting mistakes around that choice.

9. What is the difference between WHERE and HAVING?

The WHERE clause filters rows before grouping, while HAVING filters grouped results after aggregation.

SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING COUNT(*) >= 3;

The WHERE clause limits the input rows to paid orders. The HAVING clause then keeps only users with at least three paid orders.

Use WHERE for an input-row condition and HAVING for a condition on the grouped result.

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

DELETE removes rows from a table, usually with a WHERE clause. TRUNCATE removes all rows quickly in engines that support it. A DROP statement removes the table object itself.

Interview-relevant details are engine-dependent: TRUNCATE typically bypasses per-row DELETE triggers, and in most engines it resets auto-increment counters, although some engines require an option or behave differently.

A strong answer includes caution. Check the target rows first (the interval literal below is PostgreSQL syntax; MySQL writes it as INTERVAL 90 DAY):

SELECT id, email
FROM users
WHERE deleted_at < CURRENT_DATE - INTERVAL '90 days';

Then apply the same condition to the destructive statement inside the appropriate operational safeguards.

Intermediate Questions

From here the interviewer stops asking what things are and starts asking what happens. The answers that land are the ones that predict a result before writing the query.

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

A COUNT(*) expression counts rows, while COUNT(column) counts non-null values in that column.

SELECT
    COUNT(*) AS total_rows,
    COUNT(deleted_at) AS deleted_rows
FROM users;

If deleted_at is null for active users, COUNT(deleted_at) counts only deleted rows. A strong answer states that null behavior.

12. Why did this count get inflated?

Because a join multiplied rows before the aggregate ran.

SELECT users.id, COUNT(*) AS row_count
FROM users
JOIN orders ON orders.user_id = users.id
JOIN order_items ON order_items.order_id = orders.id
GROUP BY users.id;

If one order has five items, the joined result has five rows for that order. Counting rows at that point counts items, not orders.

A small row count makes that multiplication visible:

Stage	Rows
Orders before join	3
Rows after item join	5

Fix it by counting the thing you mean:

SELECT users.id, COUNT(DISTINCT orders.id) AS order_count
FROM users
JOIN orders ON orders.user_id = users.id
JOIN order_items ON order_items.order_id = orders.id
GROUP BY users.id;

Name the result grain before choosing the aggregate: one row per user, order, or item.

13. What is normalization?

Normalization is the discipline of separating facts so each table records one kind of thing, and repeated facts do not need to be updated in several places.

A normalized order model separates users, orders, products, and order items, then connects them with keys. Denormalize deliberately for a measured report, cache, or historical snapshot with a defined refresh rule. The schema design guide shows the full process.

14. What is a many-to-many relationship?

A many-to-many relationship means each row on one side can relate to many rows on the other side, and the reverse is also true. The reliable implementation is a join table.

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL REFERENCES posts (id),
    tag_id BIGINT NOT NULL REFERENCES tags (id),
    PRIMARY KEY (post_id, tag_id)
);

A comma-separated list cannot receive ordinary foreign keys or support clean joins and indexes. The join table makes each relationship a row.

15. What does an index do?

An index gives the database a faster path to matching rows for a query pattern. It can help filters, joins, ordering, uniqueness, and some covering reads.

A strong answer covers both sides: an index can speed specific reads, but it consumes storage, adds write work, and should match a real query pattern.

16. How would you index this query?

SELECT id, total_cents, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

A reasonable first index starts with the filter and then supports the sort:

CREATE INDEX orders_user_created_idx
ON orders (user_id, created_at);

The equality filter leads the index, followed by the ordering column. Verify the choice with EXPLAIN and representative data. The indexes and query optimization guide covers composite and covering indexes.

17. What is a transaction?

A transaction groups database steps into one all-or-nothing operation.

<?php

$debit = $pdo->prepare(
    "UPDATE accounts
     SET balance_cents = balance_cents - :debit_amount
     WHERE id = :account_id
       AND balance_cents >= :minimum_balance"
);
$credit = $pdo->prepare(
    "UPDATE accounts
     SET balance_cents = balance_cents + :amount
     WHERE id = :account_id"
);

$pdo->beginTransaction();

try {
    $debit->execute(["debit_amount" => 5000, "account_id" => 1, "minimum_balance" => 5000]);

    if ($debit->rowCount() !== 1) {
        throw new RuntimeException("Debit account was not updated.");
    }

    $credit->execute(["amount" => 5000, "account_id" => 2]);

    if ($credit->rowCount() !== 1) {
        throw new RuntimeException("Credit account was not updated.");
    }

    $pdo->commit();
} catch (Throwable $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $error;
}

Check both affected-row counts and roll back explicitly because InnoDB can leave earlier successful statements active after a later statement error, while a zero-row update is not an SQL error.

ACID names atomicity, consistency, isolation, and durability. The transactions and ACID guide covers isolation and deadlocks in detail.

18. What is SQL injection?

SQL injection happens when untrusted input becomes part of SQL syntax instead of a value bound to a parameter.

This is the unsafe pattern most PHP code reviews should reject:

$sql = "SELECT * FROM users WHERE email = '$email'";

The safer version uses a prepared statement:

$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = ?');
$stmt->execute([$email]);

Prepared statements keep values out of SQL grammar. The PHP PDO guide shows the complete application boundary.

19. What is the difference between UNION and UNION ALL?

UNION combines compatible result sets and removes duplicates. UNION ALL combines compatible result sets and keeps duplicates.

SELECT email FROM newsletter_signups
UNION ALL
SELECT email FROM account_users;

Use UNION ALL when duplicates are meaningful or impossible by design. Use UNION when duplicate removal is required. The UNION guide shows the result sets and column-shape rules.

Advanced Questions

These appear when the role owns performance, concurrency, or the review of somebody else's SQL. The interviewer is no longer checking knowledge; they are checking judgment under a trade-off.

20. What is a covering index?

A covering index is an index that contains everything the query needs, so the database can answer the query from the index without reading the table row in the ordinary way.

SELECT user_id, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

An index on (user_id, created_at) may cover that query. If the query also selects total_cents, the database may need either the table row or an index design that includes that value.

Covering indexes can help read-heavy paths, but they make the index larger and writes more expensive.

21. What does EXPLAIN show?

EXPLAIN shows the plan the database expects to use for a query. It helps you see scan types, join order, index usage, estimated rows, sorts, and other planning choices.

Read the plan with these questions in mind:

• Is the database reading far more rows than expected?
• Is it using the index you thought would help?
• Is a sort happening after the filter?
• Did a join multiply the work?
• Are row estimates wildly wrong?

A strong answer names both EXPLAIN and the evidence to inspect after running it.

22. What is a deadlock?

A deadlock happens when transactions wait on each other in a cycle, so none of them can proceed. The database breaks the cycle by aborting one transaction.

Deadlocks can happen in a correct busy system. These habits keep them smaller and easier to retry:

• Keep transactions short.
• Touch rows in a consistent order.
• Retry the failed transaction when the operation is safe to retry.
• Avoid doing network calls or slow application work while a transaction is open.

A strong answer treats the aborted transaction as an expected retry case when the operation is safe to repeat.

23. How would you find the second highest salary?

Clarify whether the question asks for a distinct salary value or an employee row, and define how ties should behave.

If the question asks for the second distinct salary:

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

If the question asks for the employee row with the second-ranked salary, a window function is usually clearer:

SELECT id, name, salary
FROM (
    SELECT
        employees.*,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
) ranked
WHERE salary_rank = 2;

DENSE_RANK() keeps tied salaries at the same rank.

24. What is a window function?

A window function computes a value across related rows without collapsing them into one grouped row.

SELECT
    user_id,
    created_at,
    total_cents,
    SUM(total_cents) OVER (
        PARTITION BY user_id
        ORDER BY created_at
    ) AS running_total_cents
FROM orders;

The query keeps one row per order and adds a running total per user. A normal GROUP BY would collapse those rows.

25. How do you review SQL generated by an ORM or AI assistant?

Read the SQL, not only the application method name. The generated query is the contract the database has to execute.

A practical review checks these points:

• Does it use parameters instead of string concatenation?
• Does the join match the actual relationship?
• Does it accidentally select every column?
• Does pagination happen in SQL rather than in memory?
• Does the filter match an index?
• Does it run one query or one query per row?
• Does a write sequence need a transaction?

The review should end with the same reasoning plan used throughout this page: expected row grain, constraints, access path, parameters, and transaction boundary.

Mistakes Candidates Make

Three habits cost more offers than missing knowledge does. Each one is a preparation problem rather than a SQL problem.

Reciting Syntax Without Naming the Result Grain

Symptom: a familiar query is offered immediately, but duplicate rows or ties change the answer. Cause: preparation focused on syntax instead of the required row shape. Fix: state whether the result is one row per user, order, value, or group before writing SQL.

Treating One Engine's Behavior as Portable SQL

Symptom: an answer relies on one engine's identifier quoting, date arithmetic, auto-increment syntax, or LIMIT behavior without naming it. Cause: vendor behavior was presented as universal SQL. Fix: name the engine, give the portable concept first, and identify the syntax that changes elsewhere.

Claiming Performance Without Reading a Plan

Symptom: an index or rewrite is declared faster without evidence. Cause: the answer stops at a plausible rule. Fix: describe the expected access path, run EXPLAIN on representative data, and compare rows read, estimates, sorts, and execution time where the engine supports it.

What to Focus on the Night Before

With one evening left, revise the handful of questions that turn up in almost every screen rather than the whole page: joins, NULL behavior, primary and foreign keys, what an index actually does, and the difference between DELETE, TRUNCATE, and DROP. For each one, practise the habit the rest of this page rests on — say what happens, not only what the thing is: name the row grain, the failure mode, and how you would check the result. Pick three questions from above and answer them out loud tonight, because speaking an answer is much closer to the interview than recognising it on a page.

Frequently Asked Questions

Should an interview answer begin with SQL or plain language?

Begin with one plain-language sentence that defines the concept or result. Add a small query when syntax makes the reasoning clearer, then explain the important row shape, failure mode, or verification step. This keeps the answer understandable before implementation details appear.

How should you handle an ambiguous SQL question?

State the ambiguity and ask which result the question requires. Clarify duplicate handling, null behavior, database engine, expected row grain, and tie rules before choosing syntax. If clarification is unavailable, name the assumption and show how another assumption would change the answer.

Should you mention trade-offs in every answer?

Mention a trade-off when the choice changes correctness, write cost, portability, concurrency, or maintainability. A basic definition does not need a forced downside. Indexes, denormalization, isolation levels, destructive operations, and generated SQL usually do need a clear cost or boundary.

How can you practise without memorizing scripts?

Create tiny tables, predict the rows each query will return, run the query, and explain any difference. Change one condition at a time. Practise naming result grain, null behavior, duplicate behavior, constraints, and the evidence from EXPLAIN instead of rehearsing one fixed sentence.

Should you write SQL exactly as it would run in production during a whiteboard interview?

Close is enough. The reasoning behind the query matters more than exact quoting, escaping, or dialect punctuation on a whiteboard. State any shortcut taken for the format, such as skipping parameter binding, so the interviewer knows it was deliberate.

Is it acceptable to say you do not know the answer to a SQL interview question?

Yes, when paired with a plan: naming what to check, test, or look up to find the answer shows more capability than a confident guess. A wrong answer stated with certainty is worse than an honest gap paired with a clear next step.

Do junior candidates need to know window functions and CTEs?

Not usually. Interviewers generally expect solid joins, grouping, and basic performance reasoning at every level, while window functions, recursive queries, and advanced query patterns matter more as the role's scope and seniority grow.

Self-Check

Answer these out loud before reading the answers. Speaking the answer is closer to the interview than recognizing it on a page.

Use this small grouped query for the first question:

WITH employees (department_id) AS (
    VALUES (10), (10), (20)
)
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 2;

1. Predict the output: which department row and count does the query return?
2. Multiple choice: which clause filters groups after aggregation: WHERE, HAVING, or ORDER BY?
3. Multiple choice: which join keeps every row from the left table: inner join, left join, or cross join?
4. Multiple choice: which database runs without a separate server process: MySQL, PostgreSQL, or SQLite?
5. Predict the output: a table holds four users, and deleted_at is null for three of them. What do COUNT(*) and COUNT(deleted_at) return?
6. Multiple choice: which tool checks the planned access path: EXPLAIN, COMMIT, or UNION?
7. Short answer: a join between users, orders, and order items inflates an order count. What causes it, and what fixes it?
8. Short answer: an interviewer asks whether to use SQL or NoSQL for a new checkout system. What do you ask before answering?

Answers

1. Department 10 with a count of 2. Department 20 has one row, so HAVING removes that group.
2. HAVING. It runs against grouped results after the aggregate is calculated.
3. Left join. Unmatched right-side columns become NULL.
4. SQLite. It is embedded, so the application reads and writes the database file directly.
5. 4 and 1. COUNT(*) counts rows, while COUNT(deleted_at) counts only the non-null values.
6. EXPLAIN. It reports the access plan selected by the database.
7. The item join multiplies rows before the aggregate runs. Count the thing you mean with COUNT(DISTINCT orders.id), after naming the result grain.
8. Ask about the data and the access pattern. Checkout records have relationships and money movement, so the burden of proof sits with any non-relational alternative.

Next Steps

The fastest preparation is a small PHP and MySQL application, because a rough one exposes every topic on this page at once:

• It pastes values into query strings.
• It joins tables and accidentally duplicates rows.
• It has no indexes on foreign keys.
• It writes to several tables without a transaction.
• It leaves referential rules in comments instead of constraints.

Fix those five things and you will have answered most of the beginner and intermediate questions with your hands rather than your memory.

Continue with SQL joins, SQL schema design, SQL indexes and query optimization, and SQL transactions and ACID. The PHP guide connects those database decisions to application code.