SQL LIMIT Clause Practice Questions with Solutions

The SQL LIMIT clause is used to restrict the number of rows returned by an SQL query. Instead of displaying every record from a table, you can retrieve only the required number of rows. SQL LIMIT Clause practice questions with solutions help to understand the concepts.

The LIMIT clause is extremely useful when working with large databases because it improves query performance and makes reports easier to read.

It is commonly used for:

• Showing the Top 10 Students
• Displaying the Latest Products
• Pagination in Websites
• Leaderboards
• Dashboard Reports
• Data Analysis

Note: The LIMIT clause is supported by MySQL, MariaDB, PostgreSQL, and SQLite. Microsoft SQL Server uses TOP, while Oracle uses FETCH FIRST or ROWNUM.

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 returns only the first five records from the table.

Sample Table Used Throughout This Chapter

students

Why Use LIMIT?

The LIMIT clause is useful for:

• Returning only the first few records
• Improving query performance
• Creating pagination
• Showing Top N results
• Dashboard reporting
• Previewing table data

1. SQL Query to Display the First 3 Students

Problem Statement

Write an SQL query to display the first three students from the students table.

SQL Solution

SELECT *

FROM students

LIMIT 3;

Sample Output

Explanation

The LIMIT 3 statement tells SQL to return only the first three rows from the table.

If the table contains thousands of records, only the first three will be displayed.

Concepts Covered

• LIMIT Clause
• Restricting Records
• Query Optimization

2. SQL Query to Display the First 5 Students

Problem Statement

Write an SQL query to display the first five students.

SQL Solution

SELECT *

FROM students

LIMIT 5;

Sample Output

Explanation

The LIMIT clause simply restricts the number of rows returned.

It does not change the data stored in the table.

Concepts Covered

• LIMIT
• Data Preview
• Query Results

3. SQL Query to Display Only Student Names Using LIMIT

Problem Statement

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

SQL Solution

SELECT name

FROM students

LIMIT 4;

Sample Output

Explanation

You can combine the LIMIT clause with selected columns.

Instead of displaying the entire table, only the name column is returned for the first four records.

This improves readability and reduces unnecessary data retrieval.

Concepts Covered

• LIMIT
• SELECT Specific Columns
• Query Optimization

4. SQL Query Using LIMIT 1

Problem Statement

Write an SQL query to display only the first student from the students table.

Sample Table

SQL Solution

SELECT *

FROM students

LIMIT 1;

Sample Output

Explanation

The LIMIT 1 statement tells SQL to return only one row.

It is commonly used when:

• Retrieving the first record
• Checking sample data
• Fetching the latest or highest record (when combined with ORDER BY)

Concepts Covered

• LIMIT
• LIMIT 1
• Query Optimization

5. SQL LIMIT with ORDER BY

Problem Statement

Write an SQL query to display the top three highest-scoring students.

Sample Table

SQL Solution

SELECT *

FROM students

ORDER BY marks DESC

LIMIT 3;

Sample Output

Explanation

This query performs two operations:

1. ORDER BY marks DESC sorts students from highest to lowest marks.
2. LIMIT 3 returns only the first three rows from the sorted result.

This is one of the most common SQL query patterns for creating:

• Top Performers List
• Leaderboards
• Highest Salary Reports
• Best Selling Products

Concepts Covered

• ORDER BY
• DESC
• LIMIT
• Top N Records

6. SQL LIMIT with WHERE Clause

Problem Statement

Write an SQL query to display the first two students who scored more than 85 marks.

Sample Table

SQL Solution

SELECT *

FROM students

WHERE marks > 85

LIMIT 2;

Sample Output

Explanation

The query first filters students whose marks are greater than 85.

After filtering, LIMIT 2 returns only the first two matching records.

Concepts Covered

• WHERE
• LIMIT
• Filtering Records

7. SQL Query to Display the First Two Java Students

Problem Statement

Write an SQL query to display the first two students enrolled in the Java course.

SQL Solution

SELECT *

FROM students

WHERE course = 'Java'

LIMIT 2;

Sample Output

Explanation

The query filters all Java students and then limits the result to two records.

This approach is commonly used in applications where only a few records are displayed initially.

Concepts Covered

• WHERE
• LIMIT
• Text Filtering

8. SQL Query to Display the First Delhi Student

Problem Statement

Write an SQL query to display the first student from Delhi.

SQL Solution

SELECT *

FROM students

WHERE city = 'Delhi'

LIMIT 1;

Sample Output

Explanation

The query filters students whose city is Delhi and returns only the first matching record.

This is useful when checking sample records from a particular location.

Concepts Covered

• LIMIT 1
• WHERE
• Text Filtering

9. SQL LIMIT with ASC Sorting

Problem Statement

Write an SQL query to display the three students with the lowest marks.

SQL Solution

SELECT *

FROM students

ORDER BY marks ASC

LIMIT 3;

Sample Output

Explanation

The query first sorts students by marks in ascending order.

Then, LIMIT 3 returns only the first three records from the sorted result.

This is useful for identifying low-performing students.

Concepts Covered

• ORDER BY
• ASC
• LIMIT

10. SQL LIMIT with Multiple Conditions

Problem Statement

Write an SQL query to display two Python students who scored more than 80 marks.

SQL Solution

SELECT *

FROM students

WHERE course = 'Python'
AND marks > 80

LIMIT 2;

Sample Output

Explanation

The query performs three operations:

1. Filters only Python students.
2. Keeps only students with marks greater than 80.
3. Returns only the first two matching records using LIMIT.

This type of query is widely used in real-world reporting and analytics.

Concepts Covered

• WHERE
• AND
• LIMIT
• Multiple Conditions

11. SQL Query to Display the Top 5 Highest Scoring Students

Problem Statement

Write an SQL query to display the top five highest-scoring students.

Sample Table

SQL Solution

SELECT *

FROM students

ORDER BY marks DESC

LIMIT 5;

Sample Output

Explanation

The query first sorts students by marks in descending order and then returns only the top five records.

This query is commonly used for:

• Merit Lists
• Top Performers
• Leaderboards
• Employee Rankings

Concepts Covered

• ORDER BY
• DESC
• LIMIT
• Top N Records

12. SQL LIMIT with Multiple Selected Columns

Problem Statement

Write an SQL query to display only the name and marks of the first four students.

SQL Solution

SELECT name,
       marks

FROM students

LIMIT 4;

Sample Output

Explanation

The query retrieves only the required columns instead of the entire table.

Using LIMIT with selected columns improves query performance and readability.

Concepts Covered

• SELECT
• LIMIT
• Query Optimization

13. SQL LIMIT with DISTINCT

Problem Statement

Write an SQL query to display the first three unique cities.

SQL Solution

SELECT DISTINCT city

FROM students

LIMIT 3;

Sample Output

Explanation

The query first removes duplicate city names using DISTINCT.

Then, LIMIT 3 returns only the first three unique cities.

Concepts Covered

• DISTINCT
• LIMIT
• Duplicate Removal

14. SQL LIMIT After Sorting by Course

Problem Statement

Write an SQL query to display the first four students after sorting by course name.

SQL Solution

SELECT *

FROM students

ORDER BY course ASC

LIMIT 4;

Sample Output

Explanation

SQL first sorts the table alphabetically by course, then displays only the first four rows.

This type of sorting is useful for generating department-wise or course-wise reports.

Concepts Covered

• ORDER BY
• LIMIT
• ASC
• Sorting Records

15. SQL LIMIT Using WHERE and ORDER BY Together

Problem Statement

Write an SQL query to display the top two SQL students based on marks.

SQL Solution

SELECT *

FROM students

WHERE course = 'SQL'

ORDER BY marks DESC

LIMIT 2;

Sample Output

Explanation

This query executes in the following order:

1. Filters only students enrolled in the SQL course.
2. Sorts them by marks in descending order.
3. Returns only the top two students using LIMIT.

This pattern is frequently used in reporting systems where filtered rankings are required.

Concepts Covered

• WHERE
• ORDER BY
• DESC
• LIMIT
• Multiple Clauses

Chapter Summary

In this chapter, you learned how to use the SQL LIMIT clause to control the number of rows returned by a query. Instead of retrieving every record from a table, the LIMIT clause allows you to display only the required number of rows.

You explored how LIMIT works with SELECT, WHERE, ORDER BY, and DISTINCT to create efficient and readable SQL queries. These techniques are commonly used in dashboards, reports, search results, and web applications.

Throughout this chapter, you covered:

• Introduction to the LIMIT Clause
• Displaying the First N Records
• Using LIMIT with ORDER BY
• Using LIMIT with WHERE
• Using LIMIT with DISTINCT
• Displaying Top Performers
• Retrieving Filtered Records
• Real-world SQL Examples

The LIMIT clause is an essential SQL feature that helps improve performance and makes large datasets easier to manage.

Key Takeaways

• LIMIT restricts the number of rows returned by a query.
• It is supported by MySQL, PostgreSQL, MariaDB, and SQLite.
• LIMIT is commonly combined with ORDER BY.
• LIMIT can also be used with WHERE and DISTINCT.
• It helps improve query performance.
• It is widely used for pagination.
• It is useful for dashboards and reports.
• LIMIT does not modify the original data.
• Sorting should usually be performed before applying LIMIT.
• It is one of the most commonly used SQL clauses in real-world projects.

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. Can LIMIT be used with ORDER BY?

Yes.

Example:

SELECT *

FROM students

ORDER BY marks DESC

LIMIT 3;

This displays the top three highest-scoring students.

3. Can LIMIT be used with WHERE?

Yes.

Example:

SELECT *

FROM students

WHERE course = 'Python'

LIMIT 2;

SQL filters the data first and then limits the result.

4. Can LIMIT be used with DISTINCT?

Yes.

Example:

SELECT DISTINCT city

FROM students

LIMIT 3;

This returns the first three unique cities.

5. Does LIMIT change the original table?

No.

LIMIT only affects the query result. The data stored in the table remains unchanged.

6. Why is LIMIT useful?

LIMIT is useful for:

• Showing Top 10 Records
• Dashboard Reports
• Pagination
• Previewing Data
• Improving Performance
• Reducing Data Transfer

7. Is LIMIT available in every database?

No.

• MySQL → LIMIT
• PostgreSQL → LIMIT
• SQLite → LIMIT
• SQL Server → TOP
• Oracle → FETCH FIRST or ROWNUM

8. Where is LIMIT used in real-world applications?

The LIMIT clause is commonly used in:

• E-commerce Websites
• Banking Systems
• CRM Software
• Inventory Management
• Student Management Systems
• Hospital Databases
• HR Applications
• Analytics Dashboards
• Reporting Systems
• Business Intelligence Tools

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

Rishabh Kumar