SQL Mastery Quiz — Quiz

Subqueries, CTEs, window functions, transactions and query tuning. · 10 questions

Question 1 of 10

Which keyword starts a Common Table Expression?

aWITHbUSINGcDEFINEdAS

Question 2 of 10

Which statement permanently saves the changes made in a transaction?

aSAVEbROLLBACKcCOMMITdEND

Question 3 of 10

Which window function gives the value from the previous row?

aLEADbFIRST_VALUEcPREVdLAG

Question 4 of 10

In `EXPLAIN` output, what does `type = ALL` indicate?

aAll indexes were usedbA full table scancThe query returns all columnsdThe result is cached

Question 5 of 10

Scores are 90, 80, 80, 70. What `DENSE_RANK()` (ORDER BY score DESC) does 70 get?

a3b4c2d1

Question 6 of 10

Why can this query return zero rows even though some customers never ordered?

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

aNOT IN only works with numbersbThe subquery must use DISTINCTcIf `orders.customer_id` contains a NULL, NOT IN evaluates to unknown for every rowdSubqueries cannot be used in WHERE

Question 7 of 10

Which index can MySQL use for `WHERE status = 'PAID'` alone?

a(customer_id, status)b(created_at, status)c(customer_id, created_at, status)d(status, created_at)

Question 8 of 10

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 MySQLbApplying a function to the column hides it from the index; use a date range insteadcIndexes never work on DATETIME columnsdSELECT * disables all indexes

Question 9 of 10

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

aSELECT ... FOR UPDATE before the UPDATEbAdding DISTINCT to the SELECTcUsing READ UNCOMMITTED isolationdRunning the UPDATE twice

Question 10 of 10

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

aTotal revenue of the branch on every rowbRevenue of the current month onlycA running total from the branch's first month up to the current monthdTotal revenue across all branches