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

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

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

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

Hint

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

Practice

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

easy

A. It restricts the number of rows returned by the query.

B. It sorts the rows in ascending order.

C. It groups rows based on a column.

D. It deletes rows from the table.

Solution

1. Step 1: Understand the purpose of LIMIT

   The LIMIT clause is used to restrict the number of rows returned by a query.

2. Step 2: Compare with other options

   Sorting is done by ORDER BY, grouping by GROUP BY, and deleting by DELETE statement, not LIMIT.

3. Final Answer:

   It restricts the number of rows returned by the query. -> Option A

4. Quick Check:

   LIMIT = restrict rows [OK]

Hint: LIMIT controls how many rows you get back [OK]

Common Mistakes:

• Confusing LIMIT with ORDER BY
• Thinking LIMIT deletes rows
• Mixing LIMIT with GROUP BY

2. Which of the following is the correct syntax to get the first 5 rows from a table named employees?

easy

A. SELECT * FROM employees LIMIT 5;

B. SELECT * FROM employees TOP 5;

C. SELECT * FROM employees FETCH FIRST 5 ROWS;

D. SELECT * FROM employees WHERE LIMIT = 5;

Solution

1. Step 1: Identify correct LIMIT syntax

   The standard SQL syntax to limit rows is using LIMIT followed by the number, e.g., LIMIT 5.

2. Step 2: Check other options

   TOP 5 is SQL Server syntax, FETCH FIRST 5 ROWS is standard SQL but typically requires 'ONLY', and WHERE LIMIT = 5 is invalid.

3. Final Answer:

   SELECT * FROM employees LIMIT 5; -> Option A

4. Quick Check:

   LIMIT 5 = first 5 rows [OK]

Hint: Use LIMIT number after FROM to restrict rows [OK]

Common Mistakes:

• Using TOP instead of LIMIT in standard SQL
• Misplacing LIMIT inside WHERE clause
• Using FETCH without OFFSET incorrectly

3. Given the table products with rows:
id | name
1 | Apple
2 | Banana
3 | Cherry
4 | Date
5 | Elderberry
What is the result of the query:
SELECT name FROM products ORDER BY id DESC LIMIT 3;?

medium

A. ["Apple", "Banana", "Cherry"]

B. ["Elderberry", "Date", "Cherry"]

C. ["Cherry", "Date", "Elderberry"]

D. ["Date", "Elderberry", "Cherry"]

Solution

1. Step 1: Understand ORDER BY id DESC

   Ordering by id descending means rows are sorted from highest id to lowest: 5,4,3,2,1.

2. Step 2: Apply LIMIT 3

   LIMIT 3 returns the first 3 rows after sorting: ids 5,4,3 which correspond to Elderberry, Date, Cherry.

3. Final Answer:

   ["Elderberry", "Date", "Cherry"] -> Option B

4. Quick Check:

   ORDER BY DESC + LIMIT 3 = top 3 highest ids [OK]

Hint: ORDER BY DESC then LIMIT gets top N rows [OK]

Common Mistakes:

• Ignoring ORDER BY and taking first rows as is
• Mixing ascending and descending order
• Confusing LIMIT number with offset

4. What is wrong with this query?
SELECT * FROM orders LIMIT;

medium

A. LIMIT only works with numeric columns.

B. LIMIT cannot be used without ORDER BY.

C. LIMIT must be placed before FROM clause.

D. LIMIT requires a number to specify how many rows to return.

Solution

1. Step 1: Check LIMIT syntax

   LIMIT must be followed by a number indicating how many rows to return.

2. Step 2: Validate other options

   LIMIT can be used without ORDER BY, it comes after FROM, and works on any query regardless of column types.

3. Final Answer:

   LIMIT requires a number to specify how many rows to return. -> Option D

4. Quick Check:

   LIMIT needs a number [OK]

Hint: Always put a number after LIMIT [OK]

Common Mistakes:

• Omitting the number after LIMIT
• Thinking ORDER BY is mandatory with LIMIT
• Placing LIMIT before FROM

5. You want to show page 3 of a product list, showing 10 products per page, ordered by price ascending. Which query correctly uses LIMIT and OFFSET to achieve this?

hard

A. SELECT * FROM products ORDER BY price ASC LIMIT 10 OFFSET 30;

B. SELECT * FROM products ORDER BY price ASC LIMIT 30 OFFSET 10;

C. SELECT * FROM products ORDER BY price ASC LIMIT 10 OFFSET 20;

D. SELECT * FROM products ORDER BY price ASC LIMIT 20 OFFSET 10;

Solution

1. Step 1: Calculate OFFSET for page 3 with 10 rows per page

   Page 1 shows rows 0-9, page 2 shows 10-19, so page 3 starts at offset 20.

2. Step 2: Use LIMIT 10 to get 10 rows for the page

   LIMIT 10 returns 10 rows starting from OFFSET 20.

3. Final Answer:

   SELECT * FROM products ORDER BY price ASC LIMIT 10 OFFSET 20; -> Option C

4. Quick Check:

   Page 3 offset = (3-1)*10 = 20 [OK]

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

Common Mistakes:

• Using wrong OFFSET calculation
• Mixing LIMIT and OFFSET values
• Forgetting ORDER BY for consistent paging