OFFSET for pagination in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows does this query return?

Consider a table Employees with columns id and name. It has 10 rows with id from 1 to 10. What rows will this query return?

SELECT * FROM Employees ORDER BY id ASC LIMIT 3 OFFSET 4;

SQL

SELECT * FROM Employees ORDER BY id ASC LIMIT 3 OFFSET 4;

A Rows with id 7, 8, 9

B Rows with id 5, 6, 7

C Rows with id 1, 2, 3

D Rows with id 4, 5, 6

💡 Hint

OFFSET skips the first N rows, LIMIT picks how many rows after that.

OFFSET 4 skips the first 4 rows (id 1 to 4). LIMIT 3 then returns the next 3 rows (id 5, 6, 7).

🧠 Conceptual

intermediate

Why use OFFSET with LIMIT for pagination?

Which of these best explains why OFFSET is used with LIMIT in pagination queries?

A OFFSET specifies how many rows to skip before starting to return rows, LIMIT controls how many rows to return.

B OFFSET limits the total number of rows returned, LIMIT skips rows from the start.

C OFFSET sorts the rows, LIMIT filters rows by condition.

D OFFSET and LIMIT are synonyms and do the same thing.

💡 Hint

Think about skipping rows and how many to show.

OFFSET skips a number of rows before returning results, LIMIT controls the maximum number of rows returned.

📝 Syntax

advanced

Which query correctly paginates results?

You want to get the 3rd page of results from a table Products, showing 5 rows per page, ordered by price ascending. Which query is correct?

A SELECT * FROM Products ORDER BY price ASC LIMIT 5 OFFSET 10;

B SELECT * FROM Products ORDER BY price ASC OFFSET 10 LIMIT 5;

C SELECT * FROM Products LIMIT 5 OFFSET 15 ORDER BY price ASC;

D SELECT * FROM Products ORDER BY price ASC LIMIT 15 OFFSET 5;

💡 Hint

Page 3 means skipping 10 rows (2 pages * 5 rows).

Page 3 with 5 rows per page means OFFSET = (3-1)*5 = 10, LIMIT = 5. The correct syntax is LIMIT then OFFSET after ORDER BY.

❓ optimization

advanced

What is a downside of using large OFFSET values?

When paginating a large table with millions of rows, what is a common performance issue caused by using a large OFFSET value?

A OFFSET causes the database to return duplicate rows.

B LIMIT with OFFSET disables indexes, causing full table scans.

C The database must scan and skip many rows before returning results, causing slow queries.

D Using OFFSET automatically locks the entire table.

💡 Hint

Think about how the database processes OFFSET internally.

Large OFFSET values make the database scan and discard many rows before returning the requested page, which slows down queries.

🔧 Debug

expert

Why does this pagination query return fewer rows than expected?

Given a table Orders with 50 rows, you run this query to get page 5 with 10 rows per page:

SELECT * FROM Orders LIMIT 10 OFFSET 50;

But it returns only 0 rows. Why?

A OFFSET 50 skips the first 50 rows, so page 5 should start at row 51, but page 5 with 10 rows per page should start at row 41.

B The query is correct; the table has fewer than 60 rows, so no rows are returned.

C LIMIT must come before OFFSET in the query for it to work.

D OFFSET 50 skips the first 50 rows, so page 5 with 10 rows per page should use OFFSET 40 instead.

💡 Hint

Calculate OFFSET as (page_number - 1) * rows_per_page.

Page 5 with 10 rows per page means OFFSET = (5-1)*10 = 40. Using OFFSET 50 skips too many rows, returning no rows.