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

• Design advanced SQL queries using aggregate functions and GROUP BY.
• Create and use parameterized queries for dynamic reports.
• Develop formatted reports with calculated fields and visualizations.
• Understand the use of database views and stored procedures.

Lesson summary

This week, we delve deeper into advanced database techniques and sophisticated report generation. These skills are crucial for effective data analysis and informed decision-making, especially in the South African context where access to and analysis of data is increasingly important for addressing socio-economic challenges. From understanding crime statistics to managing agricultural yields, databases and reports provide the tools to turn raw data into actionable insights. Mastering these skills will not only boost your performance in CAT but also equip you with valuable tools for your future careers and civic engagement.

Lesson notes

2. 1. Aggregate Functions and GROUP BY Aggregate functions allow you to perform calculations on groups of rows in a database table.

Common aggregate functions include: SUM(column): Calculates the sum of values in a specified column.

AVG(column): Calculates the average of values in a specified column.

COUNT(column): Counts the number of rows (or non-null values in a column).

MAX(column): Finds the maximum value in a specified column.

MIN(column): Finds the minimum value in a specified column. The `GROUP BY` clause is used in conjunction with aggregate functions to group rows based on one or more columns. This allows you to calculate aggregates for each group separately.

Example: Imagine a database table `Learners` with columns `Name`, `Grade`, and `Marks`. We want to find the average marks for each grade. ```sql SELECT Grade, AVG(Marks) AS AverageMarks FROM Learners GROUP BY Grade; ``` Explanation: `SELECT Grade, AVG(Marks) AS AverageMarks`: We are selecting the `Grade` and the average of the `Marks`. The `AS AverageMarks` part renames the calculated average column to `AverageMarks` for clarity. `FROM Learners`: Specifies the table to retrieve data from. `GROUP BY Grade`: Groups the rows based on the `Grade` column. This means the `AVG(Marks)` function will calculate the average separately for each unique grade value. South African Context

Example: Suppose a database tracks crime statistics for different provinces. The table `CrimeStats` includes columns like `Province`, `CrimeType`, and `Incidents`. To find the total number of incidents for each crime type in each province, you would use: ```sql SELECT Province, CrimeType, SUM(Incidents) AS TotalIncidents FROM CrimeStats GROUP BY Province, CrimeType; ``` 2.

2. Parameterized Queries Parameterized queries (also known as prepared statements) are SQL queries that use placeholders (parameters) for values. These parameters are then supplied when the query is executed. This is crucial for security (preventing SQL injection attacks) and efficiency (queries can be reused with different values).

How it Works: Instead of directly embedding values in the SQL query string, you use placeholders (e.g., `?` or named parameters like `:value`). The database system then handles the proper escaping and quoting of the values, preventing malicious code from being injected.

Example: Let's say you have a table called `Products` with columns `ProductID`, `ProductName`, and `Price`. You want to retrieve information about a product based on user input for the `ProductID`. In a programming language (like Python with a database connector), you would do something like this (conceptual example): ```python product_id = input("Enter Product ID: ") # Get user input Assuming a database connection object named 'conn' cursor = conn.cursor() sql = "SELECT ProductName, Price FROM Products WHERE ProductID = ?" cursor.execute(sql, (product_id,)) # Pass the product_id as a parameter result = cursor.fetchone() if result: product_name, price = result print(f"Product Name: {product_name}, Price: {price}") else: print("Product not found.") ``` Explanation: `sql = "SELECT ProductName, Price FROM Products WHERE ProductID = ?"`: Defines the SQL query with a placeholder `?` for the `ProductID`. `cursor.execute(sql, (product_id,))`: Executes the query, passing the `product_id` as a parameter. The database system handles the safe substitution of the value into the query.

Benefits: Security: Prevents SQL injection vulnerabilities.

Performance: Prepared statements can be compiled and reused multiple times, improving performance.

Readability: Makes code cleaner and easier to understand. 2.

3. Calculated Fields in Reports Calculated fields allow you to create new fields in your reports based on existing data. This enables you to perform calculations and present derived information that isn't directly stored in the database.

Example: Suppose you have a table `Sales` with columns `Quantity` and `UnitPrice`. You want to create a report that shows the total value of each sale (Quantity * UnitPrice). Most reporting tools (like those integrated into database management systems or standalone report designers) allow you to define a calculated field using an expression or formula. In the report designer, you would create a new field (e.g., "TotalValue") and set its expression to `[Quantity] * [UnitPrice]`. The reporting tool would then automatically calculate this value for each row in the report. South African Context

Example: Imagine a database containing data about electricity consumption in different households (`ElectricityUsage`). You have fields for `HouseID`, `Month`, and `UnitsConsumed`. You also have a table (`ElectricityTariffs`) that stores the electricity price per unit for each month. To calculate the electricity bill for each household in the report, you would: Join the `ElectricityUsage` and `ElectricityTariffs` tables on the `Month` column.