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 use subqueries
• Utilize aggregate functions and grouping
• Create advanced reports with calculated fields
• Implement data validation techniques

Lesson summary

This week, we delve deeper into advanced database techniques and report generation, essential skills for creating powerful and informative solutions using databases. We'll move beyond basic querying to explore more complex operations like subqueries, aggregate functions, and advanced reporting features. These skills are highly relevant in South Africa, where businesses and organizations rely heavily on data to make informed decisions, track progress, and improve efficiency. Consider, for example, a local municipality using a database to manage service delivery requests or a retail store analyzing sales data to optimize stock levels.

Lesson notes

Subqueries: A subquery (also known as an inner query or nested query) is a query embedded inside another SQL query. Subqueries are used to retrieve data that will be used in the main query. They're invaluable when you need to filter data based on conditions derived from another table or a calculated value.

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

Row Subqueries: Return a single row.

Column Subqueries: Return a single column of values.

Table Subqueries: Return a table (multiple rows and columns).

Example: Let's say we have two tables: `Customers` (CustomerID, CustomerName, City) and `Orders` (OrderID, CustomerID, OrderDate, TotalAmount). We want to find all customers who have placed an order with a total amount greater than R5000. ```sql SELECT CustomerName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE TotalAmount > 5000); ``` Explanation: The inner query `(SELECT CustomerID FROM Orders WHERE TotalAmount > 5000)` retrieves all CustomerIDs from the `Orders` table where the `TotalAmount` is greater than

5

0

0

0. The outer query then selects the `CustomerName` from the `Customers` table where the `CustomerID` matches any of the CustomerIDs returned by the inner query. The `IN` operator is used to compare the `CustomerID` in the outer query with the set of `CustomerID` values returned by the subquery.

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

Common aggregate functions include: COUNT: Returns the number of rows.

SUM: Returns the sum of values.

AVG: Returns the average of values.

MAX: Returns the maximum value.

MIN: Returns the minimum value.

Grouping (GROUP BY): The `GROUP BY` clause is used in conjunction with aggregate functions to group rows based on one or more columns. This allows you to perform calculations on each group separately.

Example: Consider a table `Sales` (ProductID, ProductName, QuantitySold, SalePrice). We want to find the total quantity sold for each product. ```sql SELECT ProductName, SUM(QuantitySold) AS TotalQuantitySold FROM Sales GROUP BY ProductName; ``` Explanation: This query groups the rows in the `Sales` table by `ProductName`. For each group (i.e., for each product), it calculates the sum of the `QuantitySold` and aliases the result as `TotalQuantitySold`.

Advanced Reporting Features: Database reporting tools offer features beyond basic querying, allowing you to create polished and informative reports.

These include: Calculated Fields: Fields derived from existing fields using formulas or expressions. For instance, calculating VAT (Value Added Tax) on a product price. If VAT is 15%, the calculated VAT field would be Price 0.

1

5. Grouping: Organizing data into hierarchical groups (e.g., grouping sales by region and then by product).

Sorting: Arranging data in ascending or descending order based on one or more fields.

Filtering: Displaying only specific records based on criteria (e.g., showing only sales from a particular quarter).

Report Headers and Footers: Adding titles, dates, page numbers, and other information to enhance readability.

Charts and Graphs: Visualizing data using charts and graphs to identify trends and patterns.

Example (Conceptual): Imagine creating a sales report from our `Sales` table. The report could group sales by region, calculate the total revenue for each region (calculated field: QuantitySold SalePrice), sort the regions by total revenue in descending order, and include a chart showing the revenue distribution across regions.

Data Validation: Ensuring the accuracy and integrity of data entered into a database. Validation rules can prevent incorrect or incomplete data from being stored.

Types of Validation: Required Fields: Ensuring that certain fields cannot be left blank.

Data Type Validation: Ensuring that data entered into a field matches the expected data type (e.g., a numeric field only accepts numbers).

Range Validation: Ensuring that values fall within a specific range (e.g., an age field must be between 0 and 120).

Format Validation: Ensuring that data conforms to a specific format (e.g., an email address must have a valid format).

Lookup Tables: Restricting values to a predefined list (e.g., a gender field can only be "Male" or "Female").

Example: In a `Students` table, you might require the `StudentID` and `Name` fields to be filled (required fields). The `Age` field might have a range validation rule (0-100). The `Email` field should have a format validation to ensure correct email syntax. The `Province` field might use a lookup table to ensure that only valid South African province names are entered. Guided Practice (With Solutions)

Question 1: Using the `Customers` and `Orders` tables from the previous example, write a query to find the names of all customers who have not placed any orders.