SQL LIMIT and Pagination Practice Questions with Solutions

The SQL LIMIT clause is used to control the number of rows returned by a query. Instead of retrieving every record from a table, LIMIT allows you to fetch only the required number of rows.

When working with large databases containing thousands or millions of records, displaying every row is inefficient. The LIMIT clause helps improve performance and provides a better user experience. SQL LIMIT and Pagination Practice questions with solutions help to understand the concepts.

One of the most common real-world uses of LIMIT is pagination, where records are divided into multiple pages.

Examples include:

• E-commerce product listings
• Google search results
• Student portals
• Banking transactions
• Employee records
• Blog posts
• CRM dashboards
• Admin panels

What is SQL LIMIT?

The LIMIT clause restricts the number of rows returned by a query.

Basic Syntax

SELECT column_name

FROM table_name

LIMIT number;

Example

SELECT *

FROM students

LIMIT 5;

This query displays only the first five records.

What is Pagination?

Pagination means dividing large amounts of data into smaller pages.

Instead of displaying:

1000 Students

Websites display:

Page 1 → 10 Students

Page 2 → Next 10 Students

Page 3 → Next 10 Students

This improves:

• Website Speed
• User Experience
• Database Performance

Sample Table Used Throughout This Chapter

students

1. SQL Query to Display Only the First 3 Records

Problem Statement

An administrator wants to preview only the first three student records without loading the entire table.

Write an SQL query to display only the first three records.

SQL Solution

SELECT *

FROM students

LIMIT 3;

Sample Output

Explanation

The LIMIT clause tells SQL to stop after returning the first three rows.

This is useful when:

• Previewing data
• Testing queries
• Displaying sample records
• Improving performance

Concepts Covered

• LIMIT Clause
• Record Restriction
• Query Performance

2. SQL Query to Display the First 2 Java Students

Problem Statement

A Java trainer wants to see only the first two Java students for a classroom attendance list.

Write an SQL query to display only the first two Java students.

SQL Solution

SELECT *

FROM students

WHERE course = 'Java'

LIMIT 2;

Sample Output

Explanation

The query first filters students enrolled in the Java course.

Then, the LIMIT clause returns only the first two matching records.

This approach is useful when displaying a small subset of filtered data.

Concepts Covered

• LIMIT
• WHERE
• Filtering Records

3. SQL Query to Display Only Student Names for the First 5 Records

Problem Statement

The school administration needs a quick list of student names for printing visitor badges.

Write an SQL query to display only the names of the first five students.

SQL Solution

SELECT name

FROM students

LIMIT 5;

Sample Output

Explanation

Instead of retrieving every column, the query selects only the name column and limits the result to the first five records.

Selecting only required columns improves query efficiency.

Concepts Covered

• LIMIT
• Column Selection
• Query Optimization

4. SQL Query to Display the First 4 Students from Delhi

Problem Statement

A school administrator wants to quickly view the first four students from Delhi for a city-wise attendance report.

Write an SQL query to display only the first four students from Delhi.

Sample Table

SQL Solution

SELECT *

FROM students

WHERE city = 'Delhi'

LIMIT 4;

Sample Output

Explanation

The query first filters students whose city is Delhi.

The LIMIT clause ensures that only the first four matching records are returned.

Concepts Covered

• LIMIT
• WHERE
• Filtering by City

5. SQL Query to Display the First 3 Students Scoring Above 85

Problem Statement

A teacher wants to review only the first three students who scored more than 85 marks.

Write an SQL query to retrieve these students.

SQL Solution

SELECT *

FROM students

WHERE marks > 85

LIMIT 3;

Sample Output

Explanation

The query filters students with marks greater than 85.

The LIMIT clause then restricts the output to the first three matching students.

This is useful for quickly reviewing high-performing students.

Concepts Covered

• LIMIT
• WHERE
• Comparison Operators

6. SQL Query to Display the First 2 SQL Course Students

Problem Statement

A database instructor wants to prepare lab attendance for the SQL batch. Display only the first two students enrolled in the SQL course.

Sample Table

SQL Solution

SELECT *

FROM students

WHERE course = 'SQL'

LIMIT 2;

Sample Output

Explanation

The query first filters students enrolled in the SQL course.

The LIMIT clause returns only the first two matching records.

Concepts Covered

• LIMIT
• WHERE
• Text Filtering

7. SQL Query to Display the First 5 Students Aged Above 21

Problem Statement

A college administrator wants to review the first five students whose age is greater than 21.

Write an SQL query to retrieve these students.

SQL Solution

SELECT *

FROM students

WHERE age > 21

LIMIT 5;

Sample Output

Explanation

The query filters students whose age is greater than 21.

Since only three students satisfy the condition, SQL returns those three records.

Concepts Covered

• LIMIT
• WHERE
• Greater Than Operator

8. SQL Query to Display the First 4 Students from Delhi or Noida

Problem Statement

A regional coordinator wants to review students from Delhi and Noida, but only needs the first four records.

SQL Solution

SELECT *

FROM students

WHERE city IN ('Delhi', 'Noida')

LIMIT 4;

Sample Output

Explanation

The IN operator filters students from Delhi and Noida.

The LIMIT clause restricts the output to the first four matching rows.

Concepts Covered

• LIMIT
• IN Operator
• Multiple Value Filtering

9. SQL Query to Display the First 3 Students Whose Name Starts with ‘A’

Problem Statement

The admission office wants to review only the first three students whose names start with the letter A.

SQL Solution

SELECT *

FROM students

WHERE name LIKE 'A%'

LIMIT 3;

Sample Output

Explanation

The LIKE 'A%' condition filters names beginning with A.

The LIMIT clause returns only the first matching records.

Concepts Covered

• LIMIT
• LIKE
• Pattern Matching

10. SQL Query to Display the First 5 Students Scoring Between 80 and 90

Problem Statement

A teacher wants to review only the first five students whose marks fall between 80 and 90.

SQL Solution

SELECT *

FROM students

WHERE marks BETWEEN 80 AND 90

LIMIT 5;

Sample Output

Explanation

The query filters students whose marks are between 80 and 90.

After filtering, SQL returns only the first five matching records.

This is useful when previewing a subset of a filtered report.

Concepts Covered

• LIMIT
• BETWEEN
• Range Filtering

11. SQL Query to Display the First Page of Student Records

Problem Statement

A student management portal displays 5 students per page.

Write an SQL query to display the first page of student records.

Sample Table

SQL Solution

SELECT *

FROM students

LIMIT 5 OFFSET 0;

Sample Output

Explanation

• LIMIT 5 → Display five records.
• OFFSET 0 → Start from the first record.

This query is used to display Page 1.

Concepts Covered

• LIMIT
• OFFSET
• Pagination

12. SQL Query to Display the Second Page of Student Records

Problem Statement

The website displays 5 students per page.

Write an SQL query to display the second page.

SQL Solution

SELECT *

FROM students

LIMIT 5 OFFSET 5;

Sample Output

Explanation

The first five records are skipped.

SQL starts reading from record number 6.

This displays Page 2.

Concepts Covered

• OFFSET
• LIMIT
• Pagination

13. SQL Query to Display the Third Page of Records

Problem Statement

Each page contains 5 records.

Write an SQL query to display the third page.

SQL Solution

SELECT *

FROM students

LIMIT 5 OFFSET 10;

Sample Output

Explanation

SQL skips the first 10 records.

Then it displays the next 5 records.

This represents Page 3.

Concepts Covered

• OFFSET
• LIMIT
• Large Dataset Navigation

14. Real-World Example: E-commerce Product Listing

Problem Statement

An online shopping website displays 20 products per page.

Write an SQL query to display the second page of products.

SQL Solution

SELECT *

FROM products

LIMIT 20 OFFSET 20;

Explanation

• The first 20 products belong to Page 1.
• SQL skips those products.
• The next 20 products become Page 2.

This technique is used by:

• Amazon
• Flipkart
• Myntra
• Meesho
• Shopify Stores

Concepts Covered

• LIMIT
• OFFSET
• Product Pagination

15. Real-World Example: Admin Dashboard Pagination

Problem Statement

An administrator wants to review website users.

Each page displays 50 users.

Write an SQL query to display the fourth page.

SQL Solution

SELECT *

FROM users

LIMIT 50 OFFSET 150;

Explanation

Calculation:

• Page Size = 50
• Page Number = 4

Formula:

OFFSET = (Page Number − 1) × Page Size

OFFSET = (4 − 1) × 50

OFFSET = 150

SQL skips the first 150 users and displays the next 50 users.

This technique is widely used in:

• Admin Dashboards
• CRM Software
• HR Management Systems
• Banking Portals
• Student Portals

Concepts Covered

• LIMIT
• OFFSET
• Pagination Formula
• Dashboard Development

Chapter Summary

In this chapter, you learned how to use the SQL LIMIT clause to control the number of records returned by a query. Instead of displaying an entire table, LIMIT helps retrieve only the required number of rows, making queries faster and improving the user experience.

You also explored pagination using the OFFSET clause, which is one of the most common techniques used in web applications to divide large datasets into multiple pages.

Throughout this chapter, you practiced:

• Displaying the first N records
• Using LIMIT with the WHERE clause
• Using LIMIT with the LIKE operator
• Using LIMIT with the BETWEEN operator
• Filtering records before applying LIMIT
• Displaying unique records with LIMIT
• Implementing pagination using LIMIT and OFFSET
• Real-world product listing pagination
• Admin dashboard pagination

The LIMIT clause is an essential SQL feature for building fast, scalable, and user-friendly applications.

Key Takeaways

• LIMIT restricts the number of rows returned by a query.
• It improves performance by reducing the amount of data retrieved.
• LIMIT is frequently combined with WHERE to display a subset of filtered records.
• LIMIT works well with LIKE, BETWEEN, and IN.
• LIMIT is commonly used with OFFSET to implement pagination.
• Pagination improves navigation through large datasets.
• The formula for pagination is:
  • OFFSET = (Page Number − 1) × Page Size
• LIMIT is widely used in dashboards, search results, blogs, and e-commerce websites.
• It is an important topic for SQL interviews and real-world database applications.
• Understanding LIMIT and OFFSET is essential for backend and full-stack development.

Frequently Asked Questions (FAQs)

1. What is the SQL LIMIT clause?

The LIMIT clause restricts the number of rows returned by a query.

Example:

SELECT *

FROM students

LIMIT 5;

2. Why is the LIMIT clause used?

The LIMIT clause is used to:

• Improve query performance
• Display only required records
• Reduce server load
• Implement pagination
• Preview data

3. Can LIMIT be used with WHERE?

Yes.

Example:

SELECT *

FROM students

WHERE city = 'Delhi'

LIMIT 3;

The query first filters students from Delhi and then displays only the first three records.

4. Can LIMIT be used with ORDER BY?

Yes.

Example:

SELECT *

FROM students

ORDER BY marks DESC

LIMIT 5;

This query displays the top five students based on marks.

5. What is OFFSET in SQL?

The OFFSET clause skips a specified number of rows before returning results.

Example:

SELECT *

FROM students

LIMIT 5 OFFSET 10;

SQL skips the first 10 rows and returns the next 5 rows.

6. How do LIMIT and OFFSET work together?

LIMIT specifies how many rows to return, while OFFSET specifies how many rows to skip.

Example:

SELECT *

FROM students

LIMIT 10 OFFSET 20;

This query skips the first 20 records and displays the next 10.

7. What is the pagination formula?

Pagination uses the following formula:

OFFSET = (Page Number − 1) × Page Size

Example:

• Page Number = 3
• Page Size = 10

OFFSET = (3 − 1) × 10 = 20

SQL Query:

SELECT *

FROM students

LIMIT 10 OFFSET 20;

8. Where are LIMIT and OFFSET used in real-world applications?

These clauses are commonly used in:

• E-commerce websites
• Blog pagination
• Search engine results
• CRM software
• Banking systems
• Student portals
• HR dashboards
• Inventory management
• Admin panels
• Mobile applications

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar