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

• Define key concepts of a relational database (tables, records, keys).
• Describe the purpose and functionality of SQL.
• Write basic SQL queries using SELECT, FROM, and WHERE.
• Use comparison and logical operators to filter data.
• Order a result set using the ORDER BY clause.

Lesson summary

This week, we delve into the crucial area of data and information management, focusing specifically on relational databases and the basics of Structured Query Language (SQL). In today's digitally driven world, understanding how data is organised, stored, and retrieved is no longer just for IT professionals. From managing your school records to accessing government services online, relational databases are silently working behind the scenes, ensuring the information you need is available efficiently and securely.

Lesson notes

This week, we delve into the crucial area of data and information management, focusing specifically on relational databases and the basics of Structured Query Language (SQL). In today's digitally driven world, understanding how data is organised, stored, and retrieved is no longer just for IT professionals. From managing your school records to accessing government services online, relational databases are silently working behind the scenes, ensuring the information you need is available efficiently and securely. In South Africa, with the increasing adoption of technology across various sectors, from agriculture to healthcare, the skills you learn in this module will be highly valuable, contributing to the effective management and analysis of data for informed decision-making. Imagine helping a local spaza shop manage its inventory more effectively using a database system – that's the power of relational databases! By the end of this week, you will be able to: Define and explain the key concepts of a relational database: This includes understanding tables, records (rows), fields (columns), primary keys, and foreign keys. Describe the purpose and functionality of SQL: You will be able to articulate how SQL is used to interact with relational databases. Write basic SQL queries to retrieve data from a single table: This includes using the `SELECT`, `FROM`, and `WHERE` clauses. Use comparison operators and logical operators in SQL `WHERE` clauses: You will learn to filter data effectively using operators like `=`, `>`, ` `: Greater than ` =`: Greater than or equal to ` ` or `!=`: Not equal to Logical Operators: Used to combine multiple conditions in the `WHERE` clause. `AND`: Returns true if both conditions are true. `OR`: Returns true if either condition is true. `NOT`: Negates a condition.

Example: Example 5: Retrieve learners who are NOT in Grade 11: ```sql SELECT * FROM Learners WHERE Grade != 11; ``` Example 6: Retrieve learners who are in Grade 10 OR live in the Western Cape: ```sql SELECT * FROM Learners WHERE Grade = 10 OR Province = 'Western Cape'; ``` 2.5 ORDER BY Clause The `ORDER BY` clause is used to sort the result set based on one or more columns. `ORDER BY column_name`: Sorts the data in ascending order (default). `ORDER BY column_name ASC`: Sorts the data in ascending order (explicitly specified). `ORDER BY column_name DESC`: Sorts the data in descending order.

Example: Example 7: Retrieve all learners, sorted by last name in ascending order: ```sql SELECT * FROM Learners ORDER BY LastName; ``` Example 8: Retrieve all learners, sorted by grade in descending order and then by first name in ascending order: ```sql SELECT * FROM Learners ORDER BY Grade DESC, FirstName ASC; ``` Guided Practice (With Solutions)

Question 1: Write an SQL query to retrieve the `FirstName` and `Province` of all learners from the `Learners` table.

Solution: ```sql SELECT FirstName, Province FROM Learners; ```

Commentary: This is a straightforward query using the `SELECT` and `FROM` clauses. We specify the columns we want (`FirstName` and `Province`) after the `SELECT` keyword and the table we want to retrieve data from (`Learners`) after the `FROM` keyword.

Question 2: Write an SQL query to retrieve all information about learners who have a `LearnerID` greater than

2. Solution: ```sql SELECT * FROM Learners WHERE LearnerID > 2; ```

Commentary: This query introduces the `WHERE` clause. We use the `>` (greater than) comparison operator to filter the results, retrieving only the rows where the `LearnerID` is greater than

2. Question 3: Write an SQL query to retrieve the `FirstName` and `LastName` of learners who are in Grade 10 and live in Gauteng.

Solution: ```sql SELECT FirstName, LastName FROM Learners WHERE Grade = 10 AND Province = 'Gauteng'; ```

Commentary: This query uses both the `WHERE` clause and the `AND` logical operator. We retrieve only the `FirstName` and `LastName` columns, and we filter the results to include only learners who satisfy both conditions: being in Grade 10 and living in Gauteng.

Question 4: Write an SQL query to retrieve all information about learners, ordered by `LastName` in descending order.

Solution: ```sql SELECT * FROM Learners ORDER BY LastName DESC; ```

Commentary: This query uses the `ORDER BY` clause to sort the results. We specify the column to sort by (`LastName`) and the sorting order (`DESC` for descending). Independent Practice (Questions Only) Write an SQL query to retrieve the `LearnerID`, `FirstName`, and `Grade` of all learners. Write an SQL query to retrieve all information about learners who live in KwaZulu-Natal. Write an SQL query to retrieve the `FirstName` and `LastName` of learners who are in Grade 11 OR live in the Western Cape. Write an SQL query to retrieve all information about learners whose `LastName` starts with the letter 'K'.