LIMIT clause behavior in SQL - Mini Project: Build & Apply

LIMIT Clause Behavior in SQL

📖 Scenario: You are managing a small bookstore database. You want to learn how to retrieve a limited number of book records from your database to display on your website's homepage.

🎯 Goal: Build SQL queries step-by-step to understand how the LIMIT clause works to restrict the number of rows returned.

📋 What You'll Learn

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

Insert exactly 5 book records with specified values

Write a query to select all books

Write a query to select only the first 3 books using LIMIT

Write a query to select 2 books starting from the 3rd book using LIMIT with offset

💡 Why This Matters

🌍 Real World

Limiting query results is useful when displaying data on websites or apps where showing all data at once is impractical.

💼 Career

Understanding LIMIT and OFFSET is essential for database querying, data analysis, 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'), (5, 'The Great Gatsby', 'F. Scott Fitzgerald').

SQL

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

Need a hint?

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

2

Select all books

Write a SQL query to select all columns from the books table without any limit.

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');

-- Select all books
-- Your code here

Need a hint?

Use SELECT * FROM books; to get all rows and columns.

3

Select the first 3 books using LIMIT

Write a SQL query to select all columns from the books table but only return the first 3 rows using the LIMIT clause.

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');

-- Select first 3 books
-- Your code here

Need a hint?

Use LIMIT 3 after the SELECT statement to get only 3 rows.

4

Select 2 books starting from the 3rd book using LIMIT with offset

Write a SQL query to select all columns from the books table but return only 2 rows starting from the 3rd row. Use LIMIT with an offset of 2.

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');

-- Select 2 books starting from the 3rd book
-- Your code here

Need a hint?

Use LIMIT 2 OFFSET 2 to skip the first 2 rows and get the next 2 rows.