Databases & SQL Interview Questions — Practice Quiz for All Levels

DISTINCT removes duplicate rows from a SELECT result set. Writing SELECT DISTINCT collapses rows that are identical across all selected columns into a single row. UNIQUE is a column or table constraint rather than a query keyword, and GROUP BY aggregates rows instead of simply de-duplicating them.

Every orders column holds NULL. A LEFT JOIN keeps each left-hand row even when no right-hand row matches the ON condition, filling the unmatched right-hand columns with NULL rather than omitting the row or substituting 0 or empty strings. This is exactly why a WHERE orders.id IS NULL filter after a LEFT JOIN finds customers who have placed no orders.

COUNT(*) returns 10 and COUNT(manager_id) returns 7. COUNT(*) counts every row regardless of nulls, while COUNT(manager_id) counts only rows where that column is non-null, skipping the 3 nulls. Every standard aggregate except COUNT(*) ignores NULL inputs.

WHERE created_at >= '2024-01-01' is the one a B-tree can accelerate with a single range scan. A B-tree stores keys in sorted order, so range and equality predicates on the bare column map to a contiguous slice. Wrapping the column in a function such as EXTRACT or a cast, or using a leading-wildcard LIKE or <>, defeats the index because the stored key order no longer lines up with the predicate.

A FOREIGN KEY guarantees referential integrity: every value in the referencing column matches an existing value in the referenced column. You cannot insert a child row pointing at a non-existent parent, nor orphan children by deleting a parent. It does not impose uniqueness, and PostgreSQL does not auto-create an index on the referencing column, though one is often added to speed joins and cascade checks.

The trade-off is that every INSERT, UPDATE, and DELETE must also maintain each affected index, slowing writes and consuming extra storage. Indexes speed reads, but each one is an additional structure the database keeps consistent on every write, adding disk and cache pressure. The skill is indexing the columns that selective queries actually filter or join on, not indexing everything.

SERIALIZABLE is the lowest standard isolation level that prevents phantom reads. Under the ANSI definitions, READ UNCOMMITTED allows dirty reads, READ COMMITTED prevents those but allows non-repeatable reads, and REPEATABLE READ prevents non-repeatable reads but still permits phantoms. Only SERIALIZABLE forbids phantom reads, though PostgreSQL's stronger REPEATABLE READ happens to block them too.

NOT NULL, CHECK, and UNIQUE are integrity constraints, but ORDER BY is not. Those three, alongside PRIMARY KEY and FOREIGN KEY, are rules the database enforces on every write. ORDER BY is a query clause that sorts a result set and constrains nothing about the stored data.

Filtering on tenant_id alone, on tenant_id plus a created_at range, and an ORDER BY tenant_id, created_at after a tenant_id filter can all seek — but filtering on created_at alone cannot. A composite index is usable only on a left-to-right prefix of its columns, so skipping the leading tenant_id defeats it. This is the classic left-prefix rule.

The Atomicity, Durability, and Consistency descriptions are correct, but the Isolation one is wrong. Atomicity means all-or-nothing, Durability means committed changes survive crashes, and Consistency means constraints are preserved across the transaction. Isolation governs how concurrent transactions are kept from interfering via the chosen isolation level; it has nothing to do with running each transaction on its own physical server.

True. Third normal form forbids transitive dependencies, requiring every non-key attribute to depend on the key, the whole key, and nothing but the key. Storing a zip_code alongside a city that the zip_code functionally determines is the classic 3NF violation, because city depends on zip_code rather than directly on the primary key.

2

It is an N+1 query: 1 query for the posts plus one extra query per post to fetch its author

EXPLAIN shows the query planner's chosen execution plan — the access methods (sequential scan vs index scan), join strategies, and the planner's estimated row counts and costs — without running the query. EXPLAIN ANALYZE actually executes it and adds real timings and actual row counts, so you can spot a sequential scan that should be an index scan or a large gap between estimated and actual rows that points to bad statistics.

A covering index contains every column a query needs (in its key or, in PostgreSQL, an INCLUDE clause), so the query is answered entirely from the index. This gives an index-only scan that skips the extra heap/table fetch a normal index lookup needs to retrieve the remaining columns.