sqladvanced
SQL Advanced Quiz
Subqueries, window functions, CTEs, indexes, and advanced SQL techniques.
7 questions
By EZ4Code Team
1. What does a window function use to define the working set of rows?
SELECT name, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employeesOVER (...) clause
GROUP BY
WHERE
JOIN
Explanation: Window functions use the `OVER (...)` clause. `PARTITION BY` divides rows into groups, `ORDER BY` defines ordering within each partition. Unlike GROUP BY, window functions don't collapse rows — each row keeps its identity but gets an aggregated/ranked value.
2. What is a CTE?
WITH active_users AS (
SELECT id, name FROM users WHERE active = true
)
SELECT * FROM active_users;A Common Table Expression — a temporary named result set
A permanent table
A type of JOIN
A constraint
Explanation: CTE (Common Table Expression) is a temporary named result set defined with `WITH`. It exists only for the duration of the query. CTEs improve readability and can be recursive (using `WITH RECURSIVE`).
3. What does `ROW_NUMBER()` do?
SELECT name,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM playersAssigns a unique sequential integer to each row
Counts total rows
Returns the row's primary key
Generates random numbers
Explanation: `ROW_NUMBER()` assigns a unique sequential integer starting at 1, based on the ORDER BY in the OVER clause. Unlike `RANK()`, it never produces ties — each row gets a distinct number even when values are equal.
4. What is the purpose of an index?
To speed up data retrieval
To enforce uniqueness only
To compress data
To validate data types
Explanation: Indexes speed up data retrieval (like a book's index). They trade write speed and storage for read performance. `UNIQUE` indexes also enforce uniqueness, but the primary purpose of an index is fast lookup. Over-indexing slows down INSERTs/UPDATEs.
5. What does a correlated subquery do?
SELECT name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users uReferences the outer query, executing once per row
Runs once and caches the result
Is the same as a JOIN
Is illegal in SQL
Explanation: A correlated subquery references columns from the outer query, so it executes once per row of the outer query (which can be slow). The example counts orders per user. Non-correlated subqueries run once and can be cached.
6. What does `COALESCE(a, b, c)` return?
The first non-NULL value among a, b, c
The first NULL value
The average of a, b, c
The maximum value
Explanation: `COALESCE` returns the first non-NULL value from its arguments, left to right. If all are NULL, it returns NULL. Useful for providing defaults: `COALESCE(nickname, full_name, 'Anonymous')`.
7. What is the difference between `DELETE` and `TRUNCATE`?
They are identical
DELETE is row-by-row with WHERE; TRUNCATE removes all rows quickly and cannot use WHERE
TRUNCATE is slower than DELETE
DELETE resets auto-increment counters
Explanation: `DELETE` removes rows one at a time, can use WHERE, fires triggers, and is logged row-by-row (slower). `TRUNCATE` removes all rows at once, can't use WHERE, doesn't fire triggers, resets auto-increment counters, and is much faster for emptying a table.