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.

Practice

1. What does the OFFSET clause do in an SQL query?

easy

A. It skips a specified number of rows before returning the result set.

B. It limits the number of rows returned by the query.

C. It sorts the rows in ascending order.

D. It groups rows based on a column.

Solution

1. Step 1: Understand OFFSET purpose

   The OFFSET clause tells SQL to skip a certain number of rows before starting to return rows.

2. Step 2: Compare with other clauses

   LIMIT controls how many rows to return, ORDER BY sorts rows, GROUP BY groups rows. OFFSET specifically skips rows.

3. Final Answer:

   It skips a specified number of rows before returning the result set. -> Option A

4. Quick Check:

   OFFSET = skip rows [OK]

Hint: OFFSET skips rows; LIMIT controls count [OK]

Common Mistakes:

• Confusing OFFSET with LIMIT
• Thinking OFFSET sorts rows
• Assuming OFFSET groups rows

2. Which of the following is the correct syntax to skip the first 10 rows in a query?

easy

A. SELECT * FROM table OFFSET;

B. SELECT * FROM table SKIP 10;

C. SELECT * FROM table LIMIT 10 OFFSET;

D. SELECT * FROM table OFFSET 10;

Solution

1. Step 1: Recall OFFSET syntax

   The correct syntax is to write OFFSET followed by the number of rows to skip, e.g., OFFSET 10.

2. Step 2: Check each option

   SELECT * FROM table OFFSET 10; uses correct syntax. SELECT * FROM table SKIP 10; uses SKIP which is invalid. SELECT * FROM table LIMIT 10 OFFSET; misplaces OFFSET. SELECT * FROM table OFFSET; lacks a number.

3. Final Answer:

   SELECT * FROM table OFFSET 10; -> Option D

4. Quick Check:

   OFFSET number correct syntax [OK]

Hint: Use OFFSET number after FROM clause [OK]

Common Mistakes:

• Using SKIP instead of OFFSET
• Omitting the number after OFFSET
• Placing OFFSET before FROM

3. Given the table Employees with columns id and name, what will this query return?

SELECT id, name FROM Employees ORDER BY id ASC LIMIT 3 OFFSET 2;

medium

A. Rows with the first 3 employees ordered by id.

B. Rows 1, 2, and 3 ordered by id ascending.

C. Rows 3, 4, and 5 ordered by id ascending.

D. Rows 2, 3, and 4 ordered by id ascending.

Solution

1. Step 1: Understand OFFSET and LIMIT effect

   OFFSET 2 skips the first 2 rows. LIMIT 3 returns the next 3 rows after skipping.

2. Step 2: Apply ORDER BY

   Rows are ordered by id ascending, so rows with id 3, 4, and 5 are returned.

3. Final Answer:

   Rows 3, 4, and 5 ordered by id ascending. -> Option C

4. Quick Check:

   OFFSET 2 skips first 2, LIMIT 3 returns next 3 [OK]

Hint: OFFSET skips rows, LIMIT picks next rows [OK]

Common Mistakes:

• Ignoring OFFSET and returning first rows
• Confusing OFFSET number with LIMIT
• Not applying ORDER BY before OFFSET

4. Identify the error in this query:

SELECT * FROM Products LIMIT 5 OFFSET;

medium

A. LIMIT cannot be used with OFFSET.

B. OFFSET requires a number after it.

C. SELECT * is not allowed with OFFSET.

D. OFFSET must come before LIMIT.

Solution

1. Step 1: Check OFFSET syntax

   OFFSET must be followed by a number indicating how many rows to skip.

2. Step 2: Analyze the query

   The query ends with OFFSET but no number is given, causing a syntax error.

3. Final Answer:

   OFFSET requires a number after it. -> Option B

4. Quick Check:

   OFFSET needs number [OK]

Hint: Always put a number after OFFSET [OK]

Common Mistakes:

• Leaving OFFSET without a number
• Thinking OFFSET must come before LIMIT
• Believing SELECT * disallows OFFSET

5. You want to display page 4 of a product list with 20 items per page, ordered by created_at descending. Which query correctly uses OFFSET and LIMIT?

hard

A. SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 60;

B. SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 80;

C. SELECT * FROM Products ORDER BY created_at DESC LIMIT 4 OFFSET 20;

D. SELECT * FROM Products ORDER BY created_at DESC OFFSET 20 LIMIT 4;

Solution

1. Step 1: Calculate OFFSET for page 4

   Each page has 20 items, so page 4 starts after 3 pages: 3 * 20 = 60 rows skipped.

2. Step 2: Check LIMIT and OFFSET usage

   LIMIT 20 returns 20 rows, OFFSET 60 skips first 60 rows, so page 4 shows rows 61-80.

3. Step 3: Verify ORDER BY

   ORDER BY created_at DESC ensures newest products first, consistent paging.

4. Final Answer:

   SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 60; -> Option A

5. Quick Check:

   Page 4 offset = 60, limit = 20 [OK]

Hint: OFFSET = (page-1)*page_size, LIMIT = page_size [OK]

Common Mistakes:

• Calculating OFFSET as page*page_size
• Swapping LIMIT and OFFSET values
• Omitting ORDER BY for consistent paging