SQL & Relational Databases

Expected answer
Give each customer a stable primary key such as customer_id, store that key as a foreign key on orders, and keep mutable customer attributes on the customer row instead of copying identity fields into every order.

Why this answer works
The important idea is identity versus description. A name is a mutable attribute and is not a reliable identifier. The foreign key gives each order a durable relationship to exactly one customer while avoiding repeated customer data. A strong answer can also mention constraints, cascade behavior, and when deliberately denormalized historical snapshots may still be appropriate.

Review the relevant lesson →

Expected answer
A predicate on the right-side table was probably placed in WHERE, so rows with no match had NULL right-side values and were filtered out. Put the match-specific condition in the JOIN condition when unmatched left rows must remain.

Why this answer works
This is a row-set reasoning problem, not a keyword trick. LEFT JOIN first preserves unmatched left rows by filling right-side columns with NULL. A later WHERE predicate such as orders.status = paid rejects those NULL rows, making the result behave like an inner join for that condition. Explain the intended population before deciding whether the predicate belongs in ON or WHERE.

Review the relevant lesson →

Expected answer
WHERE filters input rows before grouping, while HAVING filters the groups after aggregate values such as AVG(salary) have been computed. The aggregate condition therefore belongs after grouping.

Why this answer works
A strong SQL answer explains logical processing order. First decide which individual rows are eligible, then form groups, then calculate aggregates, and only then decide which aggregate results survive. Mixing those stages causes both syntax errors and subtler business-logic mistakes, especially when a row-level filter changes which rows contribute to the average.

Review the relevant lesson →

Expected answer
If the predicate matches a large fraction of the table, using the index may require many lookups and random reads, so scanning the table can cost less. Index usefulness depends on selectivity, access pattern, covering columns, table size, and optimizer estimates.

Why this answer works
Indexes are not automatically faster. They add an alternate access path, and the optimizer compares its estimated cost with other plans. When many rows match, repeatedly walking an index and then fetching table pages can cost more than reading the table sequentially. A strong answer also mentions stale statistics, composite-index order, covering indexes, and measuring the actual execution plan.

Review the relevant lesson →

Expected answer
The design must make the stock transition atomic under concurrency so both requests cannot commit a sale based on the same stale quantity. Use an appropriate transaction or conditional update and verify the affected-row result before confirming the order.

Why this answer works
The bug is a concurrency invariant violation: inventory must never fall below the allowed quantity or be promised twice. Merely starting a transaction is not enough if both transactions can still read stale state and later overwrite it. Strong answers discuss conditional updates, locking or isolation choices, retry behavior, deadlocks, and keeping the transaction scope small around the invariant being protected.

Review the relevant lesson →