LIMIT and OFFSET for pagination in MySQL - Mini Project: Build & Apply

LIMIT and OFFSET for pagination

📖 Scenario: You are working on a website that shows a list of books. The list is very long, so you want to show only a few books at a time on each page. This way, visitors can click to see the next set of books without loading all of them at once.

🎯 Goal: Build a SQL query that uses LIMIT and OFFSET to show only 3 books per page from a table called books.

📋 What You'll Learn

Create a table called books with columns id and title.

Insert exactly 6 books with given titles into the books table.

Write a variable page_size set to 3 to control how many books show per page.

Write a variable page_number set to 2 to select the second page of books.

Write a SQL query that selects id and title from books using LIMIT and OFFSET based on page_size and page_number.

💡 Why This Matters

🌍 Real World

Pagination is used on websites and apps to show large lists in small parts, improving speed and user experience.

💼 Career

Knowing how to paginate data with LIMIT and OFFSET is essential for backend developers and database administrators working with user interfaces.

1

Create the books table and insert data

Create a table called books with columns id (integer) and title (text). Then insert these 6 books exactly: 'The Hobbit', '1984', 'Pride and Prejudice', 'To Kill a Mockingbird', 'The Great Gatsby', and 'Moby Dick'.

MySQL

-- Create the books table
-- Your code here

Need a hint?

Use CREATE TABLE to make the table. Use INSERT INTO books (title) VALUES (...) to add the books.

2

Set pagination variables

Create two variables: page_size set to 3 and page_number set to 2. These will control how many books show per page and which page to show.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL
);

INSERT INTO books (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird'),
('The Great Gatsby'),
('Moby Dick');

-- Set page_size and page_number variables
-- Your code here

Need a hint?

Use SET @variable_name = value; to create variables in MySQL.

3

Calculate OFFSET for pagination

Create a variable offset that calculates the starting row for the page. Use the formula: (page_number - 1) * page_size. Use SET to assign this to @offset.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL
);

INSERT INTO books (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird'),
('The Great Gatsby'),
('Moby Dick');

SET @page_size = 3;
SET @page_number = 2;

-- Calculate offset
-- Your code here

Need a hint?

Use parentheses to ensure correct order of operations in the calculation.

4

Write the paginated SELECT query

Write a SQL query that selects id and title from books. Use LIMIT with @page_size and OFFSET with @offset to get the correct page of books.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL
);

INSERT INTO books (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird'),
('The Great Gatsby'),
('Moby Dick');

SET @page_size = 3;
SET @page_number = 2;

SET @offset = (@page_number - 1) * @page_size;

-- Write the SELECT query with LIMIT and OFFSET
-- Your code here

Need a hint?

Use LIMIT and OFFSET together to get the correct page.