Lesson Notes By Weeks and Term v5 - Grade 12

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 SQL aggregate functions (COUNT, SUM, AVG) to summarize data.
• Create calculated fields in queries using arithmetic, functions, and logic.
• Design reports based on complex queries, including grouping and sorting.
• Apply filtering criteria using WHERE clauses and JOINs to analyse data.

Lesson summary

This week, we delve into advanced database techniques and report generation, crucial skills for any aspiring Computer Applications Technology (CAT) professional in South Africa. In today's data-driven world, businesses, government agencies, and even NGOs rely heavily on databases to store and manage information. Being able to manipulate this data effectively and present it in a clear, understandable format (reports) is invaluable. Imagine, for example, helping a local clinic in your community efficiently manage patient records and generate reports for health statistics tracking, or assisting a small business in analyzing sales data to improve their marketing strategies.

Lesson notes

2.1 Aggregate Functions Aggregate functions perform calculations on a set of values and return a single value. They are extremely powerful for summarizing data. `COUNT()`: Returns the number of rows that match a specified criterion. `SUM()`: Returns the sum of numeric values in a column. `AVG()`: Returns the average of numeric values in a column. `MIN()`: Returns the smallest value in a column. `MAX()`: Returns the largest value in a column.

Example: Consider a table named `Learners` with columns `LearnerID`, `Name`, `Surname`, and `Grade`. To find the total number of learners in Grade 12: ```sql SELECT COUNT(*) AS TotalLearners FROM Learners WHERE Grade = 12; ``` This query will return a single row with one column named `TotalLearners`, containing the number of Grade 12 learners. 2.2 Calculated/Derived Fields Calculated fields, also known as derived fields, are new fields created within a query based on existing fields and calculations. They enable us to perform on-the-fly calculations and transformations of data.

Arithmetic Operations: You can use standard arithmetic operators (+, -, \*, /) for calculations.

Functions: Databases provide built-in functions for various operations, such as: Date Functions: `DATE()`, `YEAR()`, `MONTH()`, `DAY()` (to extract date parts), `DATEDIFF()` (to calculate the difference between two dates).

String Functions: `UPPER()`, `LOWER()`, `SUBSTRING()`, `LEN()` (to manipulate text).

Conditional Logic (CASE Statements): Allows you to perform different calculations based on conditions.

Example: Suppose the `Learners` table has a `DateOfBirth` column.

To calculate each learner's age: ```sql SELECT LearnerID, Name, Surname, DateOfBirth, YEAR(CURDATE()) - YEAR(DateOfBirth) - (RIGHT(CURDATE(), 5) < RIGHT(DateOfBirth, 5)) AS Age FROM Learners; ``` This query calculates the age by subtracting the birth year from the current year. The `CASE` statement handles cases where the birthday hasn't occurred yet this year. This is a simplified age calculation. A more robust example using `DATEDIFF()`: ```sql SELECT LearnerID, Name, Surname, DateOfBirth, FLOOR(DATEDIFF(CURDATE(), DateOfBirth) / 365.25) AS Age -- Account for leap years FROM Learners; ``` This utilizes `DATEDIFF()` to find the days between today and the birthdate. Then divides by 365.25 (to account for leap years) and rounds down to the nearest whole number to give the age. Example using `CASE` statements: Consider a `Sales` table with `ProductID`, `Quantity`, and `UnitPrice` columns. To categorize sales amounts as "Low", "Medium", or "High": ```sql SELECT ProductID, Quantity, UnitPrice, Quantity * UnitPrice AS TotalAmount, CASE WHEN Quantity * UnitPrice < 100 THEN 'Low' WHEN Quantity * UnitPrice BETWEEN 100 AND 500 THEN 'Medium' ELSE 'High' END AS SalesCategory FROM Sales; ``` This query multiplies quantity and unit price to get the total amount and then uses a `CASE` statement to assign a category based on the amount. 2.3 Report Generation Reports are structured presentations of data extracted from a database. They allow for clear communication of insights.

Key elements of report generation include: Grouping: Grouping data based on one or more columns (e.g., grouping sales by region). This is achieved with the `GROUP BY` clause in SQ

L. Sorting: Sorting data in ascending or descending order based on one or more columns (e.g., sorting learners alphabetically by surname). This is achieved with the `ORDER BY` clause in SQ

L. Filtering: Selecting specific data based on criteria (e.g., showing only sales from the Gauteng region). This is achieved with the `WHERE` clause in SQ

L. Formatting: Formatting data for readability (e.g., displaying currency values with appropriate symbols and decimal places). This depends on the reporting tool you are using.

Example: Let's say you want to generate a report showing the average unit price for each product category in the `Products` table, sorted by average price in descending order. ```sql SELECT Category, AVG(UnitPrice) AS AveragePrice FROM Products GROUP BY Category ORDER BY AveragePrice DESC; ``` This query groups the products by category, calculates the average unit price for each category, and then sorts the results in descending order of average price. You would then typically take this data and present it visually within a reporting tool. 2.4 Joining Tables Often data we need is stored across multiple related tables. We must then combine the data from multiple tables by joining them.

INNER JOIN: returns only matching records in both tables based on common columns.

LEFT JOIN: returns all records from the left table, and the matched records from the right table. If no matches, columns from the right table will be `NULL`.

RIGHT JOIN: returns all records from the right table, and the matched records from the left table. If no matches, columns from the left table will be `NULL`.

Example: Let's imagine a `Learners` table with learner info and a `Grades` table with grades for each learner.