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 and implement advanced queries using SQL.
• Create parameterized queries for dynamic data retrieval.
• Develop sophisticated reports with calculated fields and summaries.
• Apply database integrity constraints to ensure data accuracy.
• Understand and apply data normalization principles.

Lesson summary

This week, we delve deeper into advanced database techniques and report generation, crucial skills for analyzing data and presenting information effectively. In a world increasingly driven by data, being able to extract meaningful insights and communicate them clearly is invaluable, whether you're analyzing election results, tracking business performance, or even managing personal finances. In South Africa, these skills are highly sought after across various sectors, from government to business and NGOs. Mastering these concepts will empower you to contribute to data-driven decision-making and problem-solving within your communities and beyond.

Lesson notes

2.1 Advanced SQL Queries Subqueries: A subquery (or inner query) is a query nested inside another SQL query. It's used to retrieve data that will be used in the main query. Why use them? Subqueries allow for more complex selection criteria, enabling you to filter data based on conditions derived from other tables or calculations.

Example: Imagine you have a database for a local spaza shop. You want to find all the products that are more expensive than the average price of all products. ```sql SELECT ProductName, Price FROM Products WHERE Price > (SELECT AVG(Price) FROM Products); ``` Explanation: The inner query `(SELECT AVG(Price) FROM Products)` calculates the average price of all products. The outer query then selects the product name and price of all products where the price is greater than that average.

Aggregate Functions: These functions perform calculations on a set of values and return a single value.

Common Functions: `SUM`, `AVG`, `COUNT`, `MAX`, `MIN`.

Example: In the spaza shop database, you want to calculate the total value of all stock on hand. ```sql SELECT SUM(Price * QuantityOnHand) AS TotalStockValue FROM Products; ``` Explanation: `SUM(Price * QuantityOnHand)` calculates the sum of the product of price and quantity on hand for each product. `AS TotalStockValue` gives the resulting column a meaningful name.

GROUP BY Clause: Used to group rows that have the same value in one or more columns into a summary row. Often used with aggregate functions.

Example: You want to find the number of products from each supplier in your spaza shop database. ```sql SELECT SupplierID, COUNT(*) AS NumberOfProducts FROM Products GROUP BY SupplierID; ``` Explanation: This query groups the products by `SupplierID` and then counts the number of products within each group using `COUNT(*)`. 2.2 Parameterized Queries Parameterized queries allow you to create queries where certain values are replaced with parameters. This makes the query more flexible and prevents SQL injection attacks. Why use them? Allows users to dynamically input criteria for data retrieval. Safer and more efficient than directly embedding user input into SQL statements.

Example (MS Access): In your spaza shop database, you want to create a query that allows the user to enter a minimum price and display all products with prices greater than or equal to that minimum. Create a new query in design view. Add the `Products` table to the query. Add the `ProductName` and `Price` fields to the query grid. In the criteria row for the `Price` field, enter `[Enter Minimum Price:]`. This creates a parameter. Save the query. When you run the query, Access will prompt the user to enter a minimum price. The SQL equivalent (although parameter handling varies across DBMS): ```sql SELECT ProductName, Price FROM Products WHERE Price >= [Enter Minimum Price:]; ``` Important

Note: The way parameters are handled varies depending on the database management system (DBMS). In some systems, you might use question marks (?) or named parameters (@parameterName) in the SQL statement, and then bind the parameter values programmatically through code (e.g., using PHP or Python). 2.3 Advanced Report Generation Reports are formatted presentations of data, often incorporating calculated fields, grouping, sorting, and summary statistics.

Calculated Fields: Fields that are derived from other fields using formulas.

Example: A calculated field in a sales report that calculates the total revenue for each product by multiplying the quantity sold by the price per unit.

Grouping: Organizing data into logical groups based on a common field.

Example: Grouping sales data by month or product category.

Sorting: Arranging data in a specific order (ascending or descending) based on one or more fields.

Example: Sorting customers alphabetically by last name or sales transactions by date.

Summary Statistics: Aggregate functions (SUM, AVG, COUNT, MAX, MIN) applied to grouped data to provide summary information.

Example: Calculating the total sales revenue for each product category. 2.4 Database Integrity Constraints These are rules that ensure the accuracy and consistency of data in a database.

Primary Key: A field or set of fields that uniquely identifies each record in a table.

Foreign Key: A field in one table that refers to the primary key in another table. Used to establish relationships between tables.

Data Validation Rules: Rules that restrict the values that can be entered into a field.

Example: Ensuring that the "QuantityOnHand" field is always a non-negative number. You can achieve this using a validation rule like `>=-0` (greater than or equal to zero) and a validation text message "Quantity on Hand cannot be negative." 2.5 Data Normalization Data normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, more manageable tables and defining relationships between them. Why Normalize?