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

• Construct complex SQL queries using subqueries and joins.
• Implement data validation to ensure data integrity.
• Design professional reports with grouping, sorting, and calculated fields.
• Use parameterised queries to create dynamic, user-filtered reports.

Lesson summary

This week, we delve into the advanced aspects of databases and report generation. Building on your existing knowledge of database design and SQL, we will explore advanced querying techniques, data validation, and the creation of sophisticated, user-friendly reports. This knowledge is crucial for building effective information systems that can be used in various sectors in South Africa, such as managing patient records in hospitals, tracking inventory in businesses, or analyzing census data for governmental planning. Efficient data management and reporting are essential for informed decision-making, contributing to improved services and economic growth.

Lesson notes

2.1 Subqueries A subquery is a query nested inside another SQL query. It allows you to perform complex data retrieval by using the results of one query to inform another. Subqueries can appear in the `SELECT`, `FROM`, `WHERE`, and `HAVING` clauses of a main query.

Types of Subqueries: Scalar Subqueries: Return a single value.

Multiple-Row Subqueries: Return multiple rows. These often use operators like `IN`, `ANY`, or `ALL`.

Correlated Subqueries: Depend on the outer query for their values. They execute once for each row processed by the outer query.

Example (Scalar Subquery): Let's say we have two tables: `Customers` (CustomerID, CustomerName, City) and `Orders` (OrderID, CustomerID, OrderDate, TotalAmount). To find customers whose total order amount is greater than the average order amount across all orders, we can use a scalar subquery: ```sql SELECT CustomerName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE TotalAmount > (SELECT AVG(TotalAmount) FROM Orders)); ``` Explanation: The innermost subquery `(SELECT AVG(TotalAmount) FROM Orders)` calculates the average order amount across all orders. This returns a single value (scalar). The middle subquery `(SELECT CustomerID FROM Orders WHERE TotalAmount > (SELECT AVG(TotalAmount) FROM Orders))` retrieves the CustomerID of all orders whose total amount is greater than the average calculated in the first step. This returns a list of CustomerIDs. The outer query selects the `CustomerName` from the `Customers` table for those `CustomerID` values returned by the subquery using the `IN` operator.

Example (Correlated Subquery): Suppose we want to find the names of all customers who have placed an order for an amount higher than the average order amount for their own city. ```sql SELECT c.CustomerName FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID AND o.TotalAmount > (SELECT AVG(TotalAmount) FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = c.City)) ); ``` Explanation: The outer query selects `CustomerName` from the `Customers` table aliased as `c`. The correlated subquery `(SELECT AVG(TotalAmount) FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = c.City))` calculates the average total amount from the `Orders` table for customers that are from the same city as `c`.city. The inner `Orders o` table checks to see that the `CustomerID` is the same as the `CustomerID` from the `Customers c` table and checks if the `TotalAmount` from the `Orders` table is greater than the average from the correlated subquery. `EXISTS` check if a row exists from the subquery. 2.2 Data Validation Data validation ensures the accuracy, integrity, and reliability of data stored in a database. It prevents invalid or inconsistent data from being entered, minimizing errors and maintaining data quality.

Techniques for Data Validation: Data Types: Enforcing appropriate data types (e.g., integer, text, date) for each field.

Constraints: Using database constraints to define rules for data values. `NOT NULL`: Ensures a field cannot be left empty. `UNIQUE`: Ensures a field contains unique values. `PRIMARY KEY`: Uniquely identifies each record in a table. `FOREIGN KEY`: Establishes relationships between tables, ensuring referential integrity. `CHECK`: Specifies a condition that must be true for a value to be accepted.

Validation Rules/Stored Procedures: Implementing custom validation logic using database functions or stored procedures.

Input Masks: Formatting data as it is entered (e.g., phone number format).

Example (Using Constraints): Let's modify our `Customers` table to include data validation constraints: ```sql CREATE TABLE Customers ( CustomerID INT PRIMARY KEY AUTO_INCREMENT, CustomerName VARCHAR(255) NOT NULL, City VARCHAR(255) DEFAULT 'Johannesburg', PhoneNumber VARCHAR(10) UNIQUE, DateOfBirth DATE CHECK (DateOfBirth 2; ``` Explanation: `SELECT c.CustomerName, COUNT(o.OrderID) AS NumberOfOrders`: Selects the customer name and counts the number of orders for each customer. The count is aliased as `NumberOfOrders`. `FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID`: Joins the `Customers` and `Orders` tables on the `CustomerID` field. `GROUP BY c.CustomerName`: Groups the results by customer name to count the orders for each customer. `HAVING COUNT(o.OrderID) > 2`: Filters the results to include only customers who have placed more than 2 orders. `HAVING` is used instead of `WHERE` because we are filtering based on an aggregate function (`COUNT`).

Question 2: A table called `Products` exists with columns `ProductID`, `ProductName`, and `UnitPrice`. Write a query to add a `CHECK` constraint to the `UnitPrice` column to ensure that the price is always greater than zero.

Solution: ```sql ALTER TABLE Products ADD CONSTRAINT CK_UnitPrice CHECK (UnitPrice > 0); ``` Explanation: `ALTER TABLE Products`: Modifies the existing `Products` table.