SQL : Sorting Data with ORDER BY

Objectives:

• Learn how to sort query results using ORDER BY
• Understand ascending and descending order
• Practice sorting based on multiple columns

1. Introduction to ORDER BY Clause (5 minutes)

Definition:

• The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.

Syntax Overview:

• Sort rows by one or more columns.

  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Example:

SELECT FirstName, LastName, Age
FROM Students
ORDER BY LastName ASC, FirstName ASC;

2. Sorting Data in Ascending Order (10 minutes)

Syntax:

• Sort rows in ascending order (default behavior).

  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1, column2, ...;

Example:

SELECT CourseID, CourseName, Fee
FROM Courses
ORDER BY Fee;

Exercise:

• Write SQL queries to:

1. Sort students by FirstName in ascending order.
2. Order courses by CourseName alphabetically.

3. Sorting Data in Descending Order (10 minutes)

Syntax:

• Sort rows in descending order using DESC keyword.

  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1 DESC, column2 DESC, ...;

Example:

SELECT StudentID, FirstName, LastName, Age
FROM Students
ORDER BY Age DESC;

Exercise:

• Write SQL queries to:

1. Sort students by LastName in descending order.
2. Order courses by Fee in descending order.

4. Sorting by Multiple Columns (10 minutes)

Syntax:

• Sort rows by multiple columns, applying sorting rules in sequence.

  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Example:

SELECT StudentID, FirstName, LastName, Age
FROM Students
ORDER BY LastName ASC, FirstName ASC;

Exercise:

• Write SQL queries to:

1. Sort students first by LastName in ascending order, then by FirstName in descending order.
2. Order courses by CourseName alphabetically, then by Fee in descending order.

5. Practical Exercise: Sorting Data with ORDER BY (10 minutes)

• Task:
• Use the Students and Courses tables.
• Exercises:

1. List students ordered by Age in descending order.
2. Display courses sorted by CourseName in ascending order.
3. Sort students by LastName alphabetically, then by FirstName in ascending order.

Exercises

1. Sorting in Ascending Order:

• Exercise: Write SQL queries to order students by FirstName alphabetically.

1. Sorting in Descending Order:

• Exercise: Write SQL queries to sort courses by Fee in descending order.

1. Sorting by Multiple Columns:

• Exercise: Write SQL queries to order students first by LastName alphabetically, then by FirstName in descending order.

Homework:

• Practice Questions:
• Write SQL queries to:
  • Sort students by Age in ascending order.
  • Order courses by CourseName alphabetically, then by Fee in descending order.
  • Apply different sorting techniques using ORDER BY.