A developer runs: SELECT department FROM employees ORDER BY salary DESC LIMIT 5. The column 'salary' is not in the SELECT list. What happens?
AThe query fails because you can only ORDER BY columns that appear in SELECT
BThe query succeeds and returns the 5 departments of the highest-paid employees
CThe query returns 5 rows but salary values are substituted with NULL
DThe query runs but ignores the ORDER BY clause since salary is not selected
In most databases, you can ORDER BY a column that does not appear in your SELECT list. ORDER BY operates on the row before projection (column selection), so the engine has access to all columns for sorting purposes. The query correctly returns the 5 departments corresponding to the highest-paid employees. This is a useful pattern for top-N queries where you want to sort by a criterion you don't need to display.
Question 2 Multiple Choice
A web app shows products 10 per page. Page 1 uses OFFSET 0, page 2 uses OFFSET 10, and so on. What is the fundamental performance problem with this approach at page 500?
AOFFSET 4990 is a syntax error in SQL
BThe database reads and discards 4990 rows internally before returning 10, making deep pages progressively slower
CLIMIT 10 with a large OFFSET returns duplicate rows across pages
DORDER BY becomes unreliable when combined with large OFFSET values
OFFSET-based pagination has a hidden cost: the database must process all the skipped rows before it can return the requested ones. At page 500 with OFFSET 4990, the database reads 5000 rows and discards 4990 of them to give you 10. This cost scales linearly with page depth, making deep pages much slower than shallow ones. More efficient alternatives — keyset pagination, cursor-based pagination — avoid this by using a 'last seen' value as a filter rather than an offset count.
Question 3 True / False
ORDER BY is applied after WHERE filtering, meaning rows are sorted from the already-filtered result set.
TTrue
FFalse
Answer: True
SQL has a defined logical execution order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/OFFSET. ORDER BY operates on the nearly-final result after all filtering and aggregation. This means ORDER BY sees only the rows that passed WHERE conditions, and LIMIT then trims from the sorted output. Understanding this order is essential for reasoning about what ORDER BY can access and what TOP-N queries guarantee.
Question 4 True / False
Using LIMIT without ORDER BY guarantees you receive the same set of rows each time you run the query.
TTrue
FFalse
Answer: False
Without ORDER BY, SQL result sets are unordered — the database returns rows in whatever sequence is internally convenient, which may vary between runs depending on query planning, caching, concurrency, or physical storage changes. LIMIT without ORDER BY picks an arbitrary subset of that unordered output. If you need deterministic pagination, you must always pair LIMIT with an ORDER BY on a unique or stable column. This is a common source of subtle bugs in applications that assume LIMIT alone produces consistent results.
Question 5 Short Answer
Why does OFFSET-based pagination become progressively slower on large datasets, and what does this imply about how it should be used?
Think about your answer, then reveal below.
Model answer: OFFSET instructs the database to skip N rows before returning results, but the database must still read and process those N rows internally — it cannot jump directly to row N+1 in the general case. At OFFSET 10,000 for a 10-row page, the engine processes 10,010 rows to return 10. Performance degrades linearly with page depth. This means OFFSET pagination is practical for shallow pages but problematic for deep pagination on large tables. For high-page-count use cases, keyset pagination (filtering on a 'last seen' ID or timestamp) is more efficient because it lets the database use an index to jump directly to the right starting point.
The core issue is that OFFSET is a 'skip count' instruction, not a direct pointer. Without a specialized index, the database has no way to know where row N starts without reading rows 1 through N-1 first. Understanding this limitation early prevents building systems that work in development (small data) but break in production (large data).