Learning of SQL Day 19

 

Day 19: LIMIT and OFFSET

Introduction:

• The LIMIT clause is used to specify the number of rows to return in a query. The OFFSET clause is used to skip a specific number of rows before starting to return the rows.

Key Concepts:

• LIMIT Clause: Restrict the number of rows returned.

• OFFSET Clause: Skip a specified number of rows before returning the rows.

• Pagination: Combine LIMIT and OFFSET to retrieve a subset of rows for paging through results.

SQL Commands and Examples:

1. Basic LIMIT Clause:

   • Retrieve a limited number of rows from a table.

sql

-- Select the first 5 students
SELECT * FROM Students
LIMIT 5;

2. LIMIT with OFFSET:

   • Skip a number of rows before returning the results.

sql

-- Select 5 students starting from the 3rd student (offset by 2)
SELECT * FROM Students
LIMIT 5 OFFSET 2;

3. Pagination Example:

   • Useful for applications that need to display a subset of results at a time.

sql

-- Select 10 courses starting from the 11th course (offset by 10)
SELECT * FROM Courses
LIMIT 10 OFFSET 10;

Practice Exercise:

1. Use the Students table with columns StudentID, FirstName, LastName, Age.

2. Write a query to select the first 3 students.

3. Write a query to select 3 students starting from the 4th student (offset by 3).

4. Use the Courses table and write a query to select the first 5 courses.

5. Write a query to select 5 courses starting from the 6th course (offset by 5).

sql

-- Select the first 3 students
SELECT * FROM Students
LIMIT 3;

-- Select 3 students starting from the 4th student
SELECT * FROM Students
LIMIT 3 OFFSET 3;

-- Select the first 5 courses
SELECT * FROM Courses
LIMIT 5;

-- Select 5 courses starting from the 6th course
SELECT * FROM Courses
LIMIT 5 OFFSET 5;

Using the LIMIT and OFFSET clauses effectively allows you to control the number of rows returned by your queries and implement pagination.