OFFSET for pagination in SQL - Mini Project: Build & Apply

OFFSET for Pagination in SQL

📖 Scenario: You are working on a simple online bookstore database. The books table contains a list of books with their id, title, and author. You want to show the books to users in pages, each page showing a fixed number of books.

🎯 Goal: Build a SQL query that uses OFFSET to skip a certain number of rows and LIMIT to show a fixed number of books per page. This helps in creating pagination for the book list.

📋 What You'll Learn

Create a table called books with columns id, title, and author.

Insert exactly 5 books with given details into the books table.

Set a variable page_size to 2 to represent the number of books per page.

Write a SQL query to select title and author from books ordered by id, using LIMIT and OFFSET to get the second page of results.

💡 Why This Matters

🌍 Real World

Pagination is used in websites and apps to show large lists of items in smaller chunks, improving user experience and performance.

💼 Career

Knowing how to use <code>LIMIT</code> and <code>OFFSET</code> is essential for database querying and backend development roles.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer), title (text), and author (text). Then insert these 5 books exactly: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), (3, 'Pride and Prejudice', 'Jane Austen'), (4, 'To Kill a Mockingbird', 'Harper Lee'), and (5, 'The Great Gatsby', 'F. Scott Fitzgerald').

SQL

-- Create the books table and insert 5 books
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO with multiple rows to add the books.

2

Set the page size for pagination

Write a SQL statement to set a variable called page_size to 2. This variable will represent how many books to show per page.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Hobbit', 'J.R.R. Tolkien'),
  (2, '1984', 'George Orwell'),
  (3, 'Pride and Prejudice', 'Jane Austen'),
  (4, 'To Kill a Mockingbird', 'Harper Lee'),
  (5, 'The Great Gatsby', 'F. Scott Fitzgerald');

-- Set page_size to 2
-- Your code here

Hint

Use the \set command to define a variable in SQL clients like psql. If your SQL environment does not support variables, just write a comment -- page_size = 2.

3

Write the query to get the second page of books

Write a SQL query to select title and author from the books table ordered by id. Use LIMIT with the variable page_size and OFFSET to skip the first page of books. Calculate the offset as page_size * 1 to get the second page.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Hobbit', 'J.R.R. Tolkien'),
  (2, '1984', 'George Orwell'),
  (3, 'Pride and Prejudice', 'Jane Austen'),
  (4, 'To Kill a Mockingbird', 'Harper Lee'),
  (5, 'The Great Gatsby', 'F. Scott Fitzgerald');

-- Set page_size variable
\set page_size 2

-- Write query to get second page of books
-- Your code here

Hint

Use LIMIT to control how many rows to show and OFFSET to skip rows. The second page skips page_size * 1 rows.

4

Complete the pagination query for any page number

Add a variable called page_number set to 2. Modify the OFFSET in the query to use page_size * (page_number - 1) so it works for any page number.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Hobbit', 'J.R.R. Tolkien'),
  (2, '1984', 'George Orwell'),
  (3, 'Pride and Prejudice', 'Jane Austen'),
  (4, 'To Kill a Mockingbird', 'Harper Lee'),
  (5, 'The Great Gatsby', 'F. Scott Fitzgerald');

-- Set page_size variable
\set page_size 2
-- Set page_number variable to 2
-- Your code here

Hint

Define page_number and use it in the OFFSET calculation to skip the right number of rows for any page.

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