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

Need a 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

Need a 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

Need a 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

Need a hint?

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