Databases

table_a has 3 rows; for a given a, b has 0, 1, 2 matches respectively. How many rows does `a LEFT JOIN b` produce?

• 3
• 0+1+2 = 3, plus the unmatched a → 4
• 6
• 9

Which lets you rank rows WITHOUT collapsing them into groups?

• GROUP BY
• A window function (e.g. RANK() OVER ...)
• DISTINCT
• HAVING

Given a composite index on (country, city, age), which queries can use it efficiently (leftmost-prefix rule)?

• WHERE country = ?
• WHERE city = ?
• WHERE country = ? AND city = ?
• WHERE city = ? AND age = ?

An ORM lazily loads each parent's children, firing 1 + N queries. This is the:

• Cartesian explosion
• N+1 query problem
• deadlock
• phantom read

T1 reads a row; T2 updates+commits it; T1 re-reads and sees a different value. This anomaly is a:

• Dirty read
• Non-repeatable read
• Phantom read
• Lost update

Which isolation level prevents dirty reads but still allows non-repeatable reads?

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Sharding primarily scales ___, while replication primarily scales ___.

• reads / writes
• writes & storage / reads & availability
• nothing / everything
• latency / nothing

Which aggregate ignores NULLs when counting a specific column, e.g. COUNT(col)?

• It counts NULLs too
• It skips NULLs
• It errors on NULLs
• It returns NULL

You can reference a column alias defined in SELECT inside the WHERE clause.

• True
• False

You LEFT JOIN customers→orders and add `WHERE orders.status='shipped'`. Unexpectedly, customers with no orders disappear. Why?

• LEFT JOIN is broken
• The WHERE filters out the NULL rows, turning it into an INNER JOIN
• You need a RIGHT JOIN
• orders has no index

To list employees alongside their manager (same table), you use a:

• CROSS JOIN
• SELF JOIN with aliases
• FULL OUTER JOIN
• UNION

A CTE (WITH …) primarily improves:

• raw speed
• readability / composability of complex queries
• index usage
• transaction isolation