Lesson Notes By Weeks and Term v5 - Grade 11

Lesson Video

This page supports the lesson note with a companion video and a short classroom-ready summary.

For class groups and homework, share this lesson page so learners also get the summary, objectives, and full lesson context.

Performance objectives

• Use the WHERE clause to filter data based on conditions.
• Use the ORDER BY clause to sort your results.
• Use the GROUP BY clause to summarize and aggregate data.
• Combine all three clauses in a single powerful query.

Lesson summary

This week, we delve deeper into the world of relational databases and Structured Query Language (SQL), the language used to interact with them. Relational databases are fundamental to how organizations manage and access information, from online shopping to banking and even the management of government services. Understanding relational databases and SQL is not just an IT skill; it's a vital skill for navigating the modern, data-driven world. Think about how your school manages student records, how cellular networks keep track of your usage, or how the IEC stores voting data – all likely rely on relational databases.

Lesson notes

2. 1. The `WHERE` Clause: Filtering Data The `WHERE` clause is a powerful tool in SQL that allows you to filter the rows returned by a `SELECT` statement. It acts as a condition, selecting only the rows that meet specific criteria.

The basic syntax is: ```sql SELECT column1, column2, ... FROM table_name WHERE condition; ``` The `condition` can involve: Comparison Operators: `=`, `>`, ` =`, `<=`, `!=` (not equal to)

Logical Operators: `AND`, `OR`, `NOT` `BETWEEN` Operator: Specifies a range. `WHERE column BETWEEN value1 AND value2` `LIKE` Operator: Used for pattern matching. `WHERE column LIKE 'pattern%'` (`%` represents zero or more characters, `_` represents a single character) `IN` Operator: Specifies a set of values. `WHERE column IN (value1, value2, value3)` `IS NULL` and `IS NOT NULL` Operators: Checks for null values.

Example 1: Finding students with a specific surname Let's say we have a table called `Students` with columns `StudentID`, `FirstName`, `Surname`, and `Grade`. To find all students with the surname "Dlamini", we would use the following query: ```sql SELECT StudentID, FirstName, Surname, Grade FROM Students WHERE Surname = 'Dlamini'; ``` Example 2: Finding students in a specific grade range To find all students in grades 11 and 12: ```sql SELECT StudentID, FirstName, Surname, Grade FROM Students WHERE Grade BETWEEN 11 AND 12; ``` Example 3: Using Logical Operators To find students named "Sipho" in grade 11: ```sql SELECT StudentID, FirstName, Surname, Grade FROM Students WHERE FirstName = 'Sipho' AND Grade = 11; ``` Example 4: Using LIKE for partial matches To find students whose surname starts with "M": ```sql SELECT StudentID, FirstName, Surname, Grade FROM Students WHERE Surname LIKE 'M%'; ``` 2.

2. The `ORDER BY` Clause: Sorting Data The `ORDER BY` clause is used to sort the result-set of a query in ascending or descending order.

The syntax is: ```sql SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...; ``` `ASC` (ascending) is the default sorting order. `DESC` (descending) sorts the results in reverse order.

Example 1: Sorting students by surname alphabetically ```sql SELECT StudentID, FirstName, Surname, Grade FROM Students ORDER BY Surname; -- Ascending order by default ``` Example 2: Sorting students by grade in descending order ```sql SELECT StudentID, FirstName, Surname, Grade FROM Students ORDER BY Grade DESC; ``` Example 3: Sorting by multiple columns Sorting first by grade (descending) and then by surname (ascending) for students with the same grade: ```sql SELECT StudentID, FirstName, Surname, Grade FROM Students ORDER BY Grade DESC, Surname ASC; ``` This is useful for grouping students by grade and then alphabetically listing them within each grade. 2.

3. The `GROUP BY` Clause: Grouping Data and Aggregate Functions The `GROUP BY` clause groups rows that have the same values in specified columns into summary rows, like "find the number of students in each grade". It's often used with aggregate functions like: `COUNT()`: Counts the number of rows in a group. `SUM()`: Calculates the sum of values in a group. `AVG()`: Calculates the average of values in a group. `MIN()`: Finds the minimum value in a group. `MAX()`: Finds the maximum value in a group.

The syntax is: ```sql SELECT column1, column2, ..., aggregate_function(column) FROM table_name WHERE condition GROUP BY column1, column2, ... ORDER BY column1, column2, ...; ``` Important: Any column listed in the `SELECT` clause that is not an aggregate function must be in the `GROUP BY` clause. The `WHERE` clause filters rows before the grouping occurs. The `ORDER BY` clause sorts the final grouped result.

Example 1: Counting the number of students in each grade ```sql SELECT Grade, COUNT(StudentID) AS NumberOfStudents FROM Students GROUP BY Grade; ``` This query will return the grade and the number of students in each grade. The `AS NumberOfStudents` gives the calculated column a meaningful name.

Example 2: Finding the average age of students in each class Assuming we have an 'Age' column in the Students table: ```sql SELECT Grade, AVG(Age) AS AverageAge FROM Students GROUP BY Grade; ``` Example 3: Grouping with a WHERE Clause To find the number of female students in each grade (assuming we have a 'Gender' column): ```sql SELECT Grade, COUNT(StudentID) AS NumberOfFemaleStudents FROM Students WHERE Gender = 'Female' GROUP BY Grade; ``` Guided Practice (With Solutions)

Question 1: Using the `Students` table (StudentID, FirstName, Surname, Grade, Province), write an SQL query to find all students from Gauteng (Province = 'Gauteng') who are in Grade

1

2. Solution: ```sql SELECT StudentID, FirstName, Surname, Grade, Province FROM Students WHERE Province = 'Gauteng' AND Grade = 12; ```

Commentary: This query uses the `WHERE` clause with two conditions connected by `AND`. Both the Province and Grade criteria must be met for a row to be included in the result.