Question bank

Pick an answer to see if you're right — with an explanation.

SQL / MySQLMedium

Why does this filter usually prevent the use of an index on `created_at`?

SELECT * FROM orders WHERE YEAR(created_at) = 2026;

aYEAR() is not supported in MySQL

bApplying a function to the column hides it from the index; use a date range instead

cIndexes never work on DATETIME columns

dSELECT * disables all indexes

SQL / MySQLHard

Why does this query fail?

SELECT name FROM staff
WHERE salary > AVG(salary);

aAggregate functions are not allowed in WHERE; compare against a subquery such as (SELECT AVG(salary) FROM staff)

bAVG only works on integer columns

cThe query needs GROUP BY name first

dAVG must be spelled AVERAGE in MySQL

SQL / MySQLHard

Two cashiers may sell the last box at the same time. What prevents overselling inside a transaction?

aSELECT ... FOR UPDATE before the UPDATE

bAdding DISTINCT to the SELECT

cUsing READ UNCOMMITTED isolation

dRunning the UPDATE twice

SQL / MySQLHard

Why do customers with no orders disappear from this result?

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PAID';

aLEFT JOIN cannot be combined with WHERE

bThe WHERE test on the right table drops the NULL rows, so the join behaves like an INNER JOIN; move the condition into ON

cThe query is missing GROUP BY

dBecause `o.id` is selected

SQL / MySQLHard

With `SUM(revenue) OVER (PARTITION BY branch ORDER BY month)` and no explicit frame, what is computed?

aTotal revenue of the branch on every row

bRevenue of the current month only

cA running total from the branch's first month up to the current month

dTotal revenue across all branches