Middle SQL & Databases Interview Questions (50 MCQs)

Question 1

Why does `users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid'` drop users with no orders?

Accepted Answer

The `WHERE` condition isn't `TRUE` for `NULL`s, effectively turning it into an `INNER` join

Answer

`LEFT JOIN` isn't supported together with `WHERE`

Answer

The planner always rewrites `LEFT JOIN` as `RIGHT JOIN`

Answer

Because `status` has no index

Question 2

`SELECT u.id, SUM(o.total), COUNT(p.id) FROM users u JOIN orders o ON ... JOIN payments p ON p.user_id = u.id GROUP BY u.id` overstates order totals. Why?

Accepted Answer

Joining two independent one-to-many relations multiplies rows (fan-out)

Answer

`SUM` counts `NULL` values twice

Answer

`GROUP BY` must be on `o.id`

Answer

`COUNT` and `SUM` can't be in the same query

Question 3

What is a correlated subquery?

Accepted Answer

A subquery that references outer query columns and is logically evaluated per row

Answer

A subquery that runs exactly once

Answer

A subquery placed inside `FROM`

Answer

A subquery that uses `UNION`

Question 4

In modern PostgreSQL, how does `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)` compare to `WHERE u.id IN (SELECT user_id FROM orders)`?

Accepted Answer

Both are usually planned as a semi-join with similar performance

Answer

`IN` always re-executes the whole subquery for every row

Answer

`EXISTS` always returns duplicate rows

Answer

`EXISTS` can't use an index

Question 5

Which approach correctly gets each user's latest order?

Accepted Answer

`ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)` and filter `rn = 1`

Answer

`SELECT * FROM orders GROUP BY user_id`

Answer

`SELECT * FROM orders ORDER BY created_at DESC LIMIT 1`

Answer

`SELECT user_id, MAX(created_at), total FROM orders GROUP BY user_id`

Question 6

For scores `100, 90, 90, 80`, what do `RANK()` and `DENSE_RANK()` (descending) return?

Accepted Answer

`RANK`: `1,2,2,4` and `DENSE_RANK`: `1,2,2,3`

Answer

`RANK`: `1,2,2,3` and `DENSE_RANK`: `1,2,2,4`

Answer

Both `1,2,3,4`

Answer

Both `1,2,2,3`

Question 7

What does `revenue - LAG(revenue) OVER (ORDER BY month)` compute?

Accepted Answer

Change in revenue versus the previous month (`NULL` for the first month)

Answer

Change in revenue versus the next month

Answer

Cumulative revenue total

Answer

Difference from overall average revenue

Question 8

Which expression produces a running total of `amount` ordered by `id`?

Accepted Answer

`SUM(amount) OVER (ORDER BY id)`

Answer

`SUM(amount) OVER ()`

Answer

`SUM(amount) GROUP BY id`

Answer

`SUM(amount) OVER (PARTITION BY id)`

Question 9

Why does `SUM(amount) OVER (ORDER BY day)` show the same running total for rows with duplicate days?

Accepted Answer

The default frame is `RANGE`, which includes all peer rows with equal values

Answer

`SUM` doesn't work correctly with dates

Answer

Window functions remove duplicate values

Answer

A `GROUP BY day` must be added

Question 10

What is the main benefit of a CTE (`WITH ... AS (...)`)?

Accepted Answer

Better readability by naming intermediate query steps

Answer

It is always faster than a subquery

Answer

It stores the result permanently

Answer

It removes the need for indexes

Question 11

To fetch all descendants of a category in a tree with a `parent_id` column, what do you use?

Accepted Answer

`WITH RECURSIVE` with an anchor part and a recursive part combined by `UNION ALL`

Answer

A single self join

Answer

`GROUP BY parent_id`

Answer

The `LAG` window function

Question 12

If hierarchical data contains a cycle, what does a recursive CTE with `UNION ALL` do?

Accepted Answer

It loops forever unless you detect cycles or limit depth

Answer

It automatically detects the cycle and stops

Answer

It fails immediately with a syntax error

Answer

It returns only the first level

Question 13

What is the main difference between a materialized view and a regular view?

Accepted Answer

It physically stores the result and must be updated with `REFRESH`

Answer

It is always in sync with the base tables

Answer

It can't be indexed

Answer

It is kept only in memory

Question 14

Table `order_items(order_id, product_id, quantity, product_name)` with primary key `(order_id, product_id)` violates which normal form?

Accepted Answer

2NF, because `product_name` depends on only part of the key

Answer

1NF, because the key is composite

Answer

3NF, because `quantity` is numeric

Answer

None; the table is fully normalized

Question 15

Table `employees(id, dept_id, dept_name)` keyed by `id` violates which normal form?

Accepted Answer

3NF, due to the transitive dependency `id → dept_id → dept_name`

Answer

1NF

Answer

2NF

Answer

None

Question 16

When does denormalization (e.g. storing `comments_count` on `posts`) make sense?

Accepted Answer

When reads are very frequent, computing is expensive, and you can manage keeping it in sync

Answer

Always, because joins are always slow

Answer

When data changes a lot and consistency is critical

Answer

Only when the database has no foreign keys

Question 17

What is PostgreSQL's default isolation level?

Accepted Answer

Read Committed

Answer

Repeatable Read

Answer

Serializable

Answer

Read Uncommitted

Question 18

What is a dirty read?

Accepted Answer

Reading another transaction's uncommitted changes

Answer

Reading two different values of a row within one transaction

Answer

New rows appearing on a re-run of a query

Answer

Reading data from a corrupted index

Question 19

Under Read Committed, a transaction reads `SELECT balance FROM accounts WHERE id = 1` and gets `100`; another transaction changes it to `50` and commits. What does re-running the SELECT return?

Accepted Answer

`50` (a non-repeatable read)

Answer

`100`, because the snapshot is fixed

Answer

A serialization error

Answer

The SELECT blocks until the other transaction ends

Question 20

What is a phantom read?

Accepted Answer

Re-running a query with a condition shows rows that were inserted or deleted

Answer

Reading data that was rolled back

Answer

Losing an update due to a concurrent update

Answer

Reading a row that is still in the WAL

Question 21

Two concurrent requests each read `stock` (`10`), subtract one in the app, and write `UPDATE products SET stock = 9`. What's the result?

Accepted Answer

`9` instead of `8` (a lost update)

Answer

`8`, because the database merges them automatically

Answer

A deadlock rolling one of them back

Answer

A unique constraint error

Question 22

What does `SELECT * FROM accounts WHERE id = 1 FOR UPDATE` do inside a transaction?

Accepted Answer

Row-locks it so other transactions can't modify or lock it until this transaction ends

Answer

Locks the whole table for reads and writes

Answer

Also blocks other sessions' plain SELECTs

Answer

Updates the row immediately

Question 23

To build a PostgreSQL job queue where multiple workers grab different jobs concurrently, what fits best?

Accepted Answer

`SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1`

Answer

`SELECT ... FOR UPDATE NOWAIT` with no retry

Answer

`LOCK TABLE jobs` in each worker

Answer

`SELECT ... LIMIT 1` with no locking

Question 24

Transaction A updates row 1 then row 2, while B concurrently updates row 2 then row 1. What does PostgreSQL do?

Accepted Answer

Detects the deadlock and aborts one transaction with an error

Answer

Both wait forever

Answer

Automatically runs both one after the other

Answer

Silently ignores the second update in both

Question 25

How is optimistic locking with a `version` column typically implemented?

Accepted Answer

`UPDATE ... SET version = version + 1 WHERE id = ? AND version = ?` and check the affected row count

Answer

`SELECT ... FOR UPDATE` before every read

Answer

Locking the table at the start of every request

Answer

Using the Read Uncommitted isolation level

Question 26

With a B-tree index on `(last_name, first_name)`, which query can't use it efficiently?

Accepted Answer

`WHERE first_name = 'Sara'`

Answer

`WHERE last_name = 'Ahmadi'`

Answer

`WHERE last_name = 'Ahmadi' AND first_name = 'Sara'`

Answer

`WHERE last_name = 'Ahmadi' ORDER BY first_name`

Question 27

For `WHERE status = 'active' AND created_at > now() - interval '7 days'`, which composite index is better?

Accepted Answer

`(status, created_at)`: equality column first, range column second

Answer

`(created_at, status)`: range column first

Answer

Two separate indexes are always better than a composite one

Answer

Column order in a composite index doesn't matter

Question 28

What does a covering index (e.g. `CREATE INDEX ON orders(user_id) INCLUDE (total)`) provide?

Accepted Answer

Enables an index-only scan without visiting the table for needed columns

Answer

Guarantees `total` is unique

Answer

Lets `WHERE total > 100` search as efficiently as a key column

Answer

Reduces write cost on the table

Question 29

When is a partial index like `CREATE INDEX ON orders(created_at) WHERE status = 'pending'` useful?

Accepted Answer

When queries usually target a small subset of rows (e.g. pending ones)

Answer

When you want the index to cover only some columns

Answer

For all queries on `orders` regardless of condition

Answer

Only for partitioned tables

Question 30

With an index on `email`, why doesn't `WHERE LOWER(email) = 'a@b.com'` use it?

Accepted Answer

The index stores raw values; this query needs an expression index on `LOWER(email)`

Answer

`LOWER` isn't allowed in `WHERE`

Answer

B-tree doesn't work with strings

Answer

You must use `LIKE` instead

Question 31

Why might the planner ignore an index on `is_active` when 95% of rows are `true`?

Accepted Answer

Selectivity is low, and a seq scan is cheaper when reading most of the table

Answer

Indexes on booleans aren't supported

Answer

The index is corrupted and needs `REINDEX`

Answer

The planner never uses indexes for `WHERE`

Question 32

What is the difference between `EXPLAIN` and `EXPLAIN ANALYZE`?

Accepted Answer

`EXPLAIN` shows the estimated plan; `EXPLAIN ANALYZE` actually runs the query and reports real timings and row counts

Answer

`EXPLAIN ANALYZE` only refreshes table statistics

Answer

`EXPLAIN` runs the query but hides the result

Answer

No difference; only the output format differs

Question 33

In `EXPLAIN ANALYZE`, a node estimates `rows=10` but actually returns `rows=500000`. What's the most likely first step?

Accepted Answer

Run `ANALYZE` on the table to refresh statistics

Answer

Drop all indexes

Answer

Increase `max_connections`

Answer

Convert the table to unlogged

Question 34

When does PostgreSQL usually choose a Bitmap Heap Scan?

Accepted Answer

When too many rows for an index scan but too few for a seq scan, or when combining several indexes

Answer

Only for tables with fewer than 100 rows

Answer

When no index exists

Answer

Only for boolean columns

Question 35

In an ORM, looping over 50 posts and accessing `post.author` each iteration generates 51 queries. What's the standard fix?

Accepted Answer

Eager loading (e.g. `include`/`joinedload`/`with`) to load authors in one or two queries

Answer

Increase the connection pool size

Answer

Cache each post separately in Redis

Answer

Add an index on `posts.title`

Question 36

Which is a common ORM performance pitfall?

Accepted Answer

Loading all columns and rows into memory when only a count or a few fields are needed

Answer

Automatic use of parameterized queries

Answer

Supporting transactions

Answer

Generating migrations from models

Question 37

Why is opening a new PostgreSQL connection per HTTP request a bad idea?

Accepted Answer

Each connection is a separate process with setup and memory cost, and their count is limited

Answer

PostgreSQL accepts only one concurrent connection

Answer

New connections see uncommitted data

Answer

It causes SQL injection

Question 38

In PgBouncer with `pool_mode = transaction`, which session-level feature is traditionally problematic?

Accepted Answer

Session-level `SET`, session advisory locks, and `LISTEN`

Answer

Multi-statement transactions with `BEGIN`/`COMMIT`

Answer

Plain `SELECT` queries

Answer

Foreign key constraints

Question 39

A service has 20 instances, each with a pool of 50. The database has `max_connections = 200`. What's the problem?

Accepted Answer

Up to 1000 connections are requested, exceeding the database limit

Answer

No problem; pools coordinate automatically

Answer

Smaller pools are always slower

Answer

`max_connections` only applies to replicas

Question 40

Why is `ORDER BY id LIMIT 20 OFFSET 1000000` slow?

Accepted Answer

The database must read and discard a million rows to reach the 20

Answer

`OFFSET` disables indexes and sorts the entire table

Answer

`LIMIT` over 10 isn't supported

Answer

A large OFFSET causes an overflow

Question 41

In keyset pagination by `created_at`, why add `id` as a tiebreaker?

Accepted Answer

Several rows can share a `created_at`, and without it rows get duplicated or skipped

Answer

Because `created_at` can't be indexed

Answer

So that `LIMIT` works

Answer

Because comparing timestamps isn't allowed in SQL

Question 42

With soft delete (a `deleted_at` column), which common problem arises?

Accepted Answer

A unique constraint on `email` blocks re-registering with a deleted user's email

Answer

Rows are physically removed from disk

Answer

Foreign keys are automatically disabled

Answer

Queries no longer need `WHERE`

Question 43

What's one advantage and one drawback of UUID v4 primary keys versus auto-increment?

Accepted Answer

Generated client-side and unguessable, but randomness causes scattered B-tree inserts and larger indexes

Answer

Smaller than `bigint`, but can't be indexed

Answer

Time-ordered, but may collide

Answer

Faster to insert, but doesn't work in distributed systems

Question 44

In PostgreSQL 11+, how does `ALTER TABLE big ADD COLUMN flag boolean NOT NULL DEFAULT false` behave on a large table?

Accepted Answer

Fast, since the constant default is stored in metadata without rewriting the table

Answer

Rewrites the whole table, holding a lock for hours

Answer

Always fails because `NOT NULL` conflicts with `DEFAULT`

Answer

Existing rows get `NULL`

Question 45

Why use `CREATE INDEX CONCURRENTLY` in production?

Accepted Answer

To build the index without blocking writes on the table

Answer

Because it's faster than a normal `CREATE INDEX`

Answer

To build the index on all replicas simultaneously

Answer

Because it runs inside a transaction and can be rolled back

Question 46

A user updates their profile, the page immediately reads from a read replica, and shows the old value. Why?

Accepted Answer

Replication lag with asynchronous replication

Answer

The transaction was rolled back on the primary

Answer

Replicas only hold data older than one day

Answer

The user's browser cache

Question 47

What does partitioning an `events` table by date (e.g. one partition per month) provide?

Accepted Answer

Partition pruning for time-range queries and fast removal of old data via `DROP`/`DETACH`

Answer

Automatic distribution of data across multiple servers

Answer

Eliminates the need for indexes

Answer

Guarantees global uniqueness on any column

Question 48

Why does PostgreSQL need `VACUUM`?

Accepted Answer

Because `UPDATE` and `DELETE` leave old row versions (dead tuples) that must be reclaimed

Answer

To compress the WAL file

Answer

To rebuild all indexes

Answer

To close idle connections

Question 49

Why is `SELECT COUNT(*) FROM big_table` slow on a very large PostgreSQL table?

Accepted Answer

Under MVCC the exact count differs per snapshot, so rows (or an index) must be scanned

Answer

Because `COUNT(*)` reads and decodes every column from disk

Answer

Because PostgreSQL never estimates row counts

Answer

Because `COUNT(*)` locks the table

Question 50

What's the common way to add a foreign key to a large production table without a long lock?

Accepted Answer

Add it with `NOT VALID`, then run `VALIDATE CONSTRAINT` separately

Answer

Copy the table, add the constraint, then swap

Answer

Only check the constraint in application code

Answer

`LOCK TABLE` and add the constraint directly in off-peak hours