ORDER BY, LIMIT & OFFSET — Concept Check
Questions on ORDER BY, LIMIT & OFFSET. ORDER BY sorts result rows; LIMIT restricts the count returned; OFFSET skips rows — together they enable pagination, but naive OFFSET pagination degrades at scale.
Questions in this quiz
8 questions on ORDER BY, LIMIT & OFFSET — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.You have a query that uses OFFSET for pagination. As the page number increases, the performance degrades significantly. What is the primary reason for this slowdown?
- AThe database engine locks rows for each OFFSET, causing contention.
- BOFFSET requires sorting the entire dataset each time, increasing complexity.
- COFFSET causes the database to recompute indexes on each page request.
- DOFFSET scans and discards all preceding rows, causing O(n) work per page.
2.A developer uses the following query for pagination: `SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 1000;`. What is a more efficient alternative for deep pagination?
- AUse keyset pagination with a WHERE clause on the last seen price and ID.
- BIncrease the LIMIT to reduce the number of OFFSET scans.
- CUse a subquery to pre-filter the first 1000 rows.
- DAdd an index on the price column to optimize OFFSET.
3.Why is it important to include a unique tiebreaker column like `id` in an ORDER BY clause when paginating results?
- ATo prevent NULL values from affecting the order of results.
- BTo automatically index the results for faster retrieval.
- CTo allow the use of OFFSET without performance issues.
- DTo ensure stable and deterministic pagination across requests.
4.In PostgreSQL, what is the default behavior for sorting NULL values in an ORDER BY clause with ascending order?
- ANULL values are sorted last.
- BNULL values are randomly placed.
- CNULL values are sorted first.
- DNULL values are ignored in sorting.
5.A query uses `ORDER BY salary DESC, full_name ASC`. What happens if two employees have the same salary?
- AThey are returned in a non-deterministic order.
- BThey are sorted by full_name in descending order as a tiebreaker.
- CThey are grouped together without further sorting.
- DThey are sorted by full_name in ascending order as a tiebreaker.
6.A developer notices that a query with `LIMIT 5` returns inconsistent results across repeated executions. What might be missing in the query?
- AA DISTINCT clause to remove duplicates.
- BA WHERE clause to filter the dataset first.
- CAn index on the columns being queried.
- DAn ORDER BY clause to ensure deterministic sorting.
7.What is a potential drawback of using OFFSET-based pagination in a high-traffic application?
- AIt requires more memory as the offset increases.
- BIt causes data inconsistency due to skipped rows.
- CIt becomes slower as the offset increases due to scanning all preceding rows.
- DIt locks the table, preventing concurrent writes.
8.Which of the following is a key advantage of using keyset pagination over OFFSET pagination?
- AKeyset pagination prevents NULL values from affecting result order.
- BKeyset pagination maintains consistent performance regardless of page depth.
- CKeyset pagination automatically indexes the dataset for faster access.
- DKeyset pagination allows for more complex queries without performance loss.