Kyurious Minds Computer Academy SQL SQL : Sorting and Limiting Results

SQL : Sorting and Limiting Results

Objectives:

  • Learn how to sort query results using ORDER BY
  • Understand how to limit the number of results using LIMIT
  • Use ASC and DESC to specify sort order

1. Introduction to ORDER BY (5 minutes)

Syntax Overview:

  • The ORDER BY clause is used to sort the result set in ascending or descending order.
  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Example:

SELECT * FROM TestTable ORDER BY Name ASC;

2. Sorting in Ascending and Descending Order (10 minutes)

Ascending Order (ASC):

  • Default order, sorts values from A to Z, or 0 to 9.

Descending Order (DESC):

  • Sorts values from Z to A, or 9 to 0.

Examples:

  • Sort by Name in ascending order:
  SELECT * FROM TestTable ORDER BY Name ASC;
  • Sort by DateOfBirth in descending order:
  SELECT * FROM TestTable ORDER BY DateOfBirth DESC;

Exercise:

  • Write SQL queries to:
  1. Sort Students by FirstName in ascending order.
  2. Sort Students by EnrollmentDate in descending order.

3. Limiting Results with LIMIT (10 minutes)

Syntax Overview:

  • The LIMIT clause is used to specify the number of records to return.
  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1
  LIMIT number;

Example:

SELECT * FROM TestTable ORDER BY Name ASC LIMIT 5;

Exercise:

  • Write SQL queries to:
  1. Retrieve the first 3 rows from Students table, sorted by EnrollmentDate.
  2. Select the top 5 students by StudentID in ascending order.

4. Combining ORDER BY and LIMIT (10 minutes)

Combining Syntax:

  • Use ORDER BY and LIMIT together to sort and restrict results.
  SELECT * FROM Students ORDER BY EnrollmentDate DESC LIMIT 5;

Example:

SELECT * FROM TestTable ORDER BY DateOfBirth ASC LIMIT 10;

Exercise:

  • Write SQL queries to:
  1. Select the first 5 students ordered by LastName in descending order.
  2. Retrieve the last 3 students by StudentID in ascending order.

5. Practical Exercise: Sorting and Limiting Results (5 minutes)

  • Task:
  • Continue using the Students table.
  • Insert additional sample data:
  INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
  VALUES
      (6, 'Charlie', 'Davis', '2024-01-20'),
      (7, 'Dana', 'Evans', '2024-01-21');
  • Exercises:
  1. Retrieve all columns from Students ordered by FirstName in ascending order.
  2. Select FirstName and LastName from Students where StudentID is between 4 and 6, ordered by EnrollmentDate in descending order.
  3. Limit the number of rows returned to 3, sorted by LastName.

Exercises

  1. Sorting with ORDER BY:
  • Exercise: Write SQL queries to:
    • Sort TestTable by Name in ascending order.
    • Sort TestTable by DateOfBirth in descending order.
  1. Using LIMIT:
  • Exercise: Write SQL queries to:
    • Retrieve the first 3 rows from Students table, sorted by EnrollmentDate.
    • Select the top 5 students by StudentID in ascending order.
  1. Combining ORDER BY and LIMIT:
  • Exercise: Write SQL queries to:
    • Select the first 5 students ordered by LastName in descending order.
    • Retrieve the last 3 students by StudentID in ascending order.
  1. Sample Queries for Students Table:
  • Exercise: Execute the following queries:
    1. Retrieve all columns from Students ordered by FirstName in ascending order.
    2. Select FirstName and LastName from Students where StudentID is between 4 and 6, ordered by EnrollmentDate in descending order.
    3. Limit the results to 3 rows, sorted by LastName.

Homework:

  • Practice Questions:
  • Write SQL queries to:
    • Select the first 10 rows from Students ordered by EnrollmentDate in descending order.
    • Retrieve the last 4 students by StudentID, sorted by LastName in ascending order.