QuizzesORDER BY, LIMIT & OFFSET — Concept Check
intermediate
sql

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.

8 minutes 8 questions Pass at 70%
Time Limit
8 minutes - timer starts when you begin
Change Answers Freely
Revisit and change any answer before submitting
Full Review After Submit
Correct answers and explanations shown for every question
Auto-submit on timeout
Quiz submits automatically when time runs out

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. 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. 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. 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. 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. 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. 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. 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. 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.