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

• Analyze and design complex SQL queries using subqueries and joins.
• Implement stored procedures and functions to encapsulate logic.
• Apply transaction management principles (ACID) to ensure data integrity.
• Design and implement views to simplify queries and control access.
• Optimize SQL queries for better performance.

Lesson summary

This week, we delve deeper into advanced database concepts and SQL (Structured Query Language) techniques within the context of real-world applications. Understanding these advanced concepts is crucial for developing robust, efficient, and secure database-driven systems. The skills acquired here are highly sought after in the South African IT job market, particularly in sectors like banking, telecommunications, and e-commerce, which heavily rely on database systems to manage large volumes of data.

Furthermore, as South Africa increasingly embraces digital solutions, the ability to manage and manipulate data effectively becomes essential for innovation and economic growth.

Lesson notes

2.1 Subqueries A subquery is a query nested inside another query. It can be used in the `WHERE`, `HAVING`, or `SELECT` clauses of the outer query. Subqueries are powerful for filtering data based on the results of another query.

Types of Subqueries: Scalar Subquery: Returns a single value. It can be used wherever a single value is allowed.

Multiple-Row Subquery: Returns multiple rows. It is used with operators like `IN`, `ANY`, `ALL`, and `EXISTS`.

Correlated Subquery: Depends on the outer query for its values. It is executed once for each row in the outer query.

Example (Scalar Subquery): Imagine a database of students and their exam scores. We want to find all students who scored higher than the average score for the entire class. ```sql SELECT student_name FROM Students WHERE score > (SELECT AVG(score) FROM Students); ``` In this example, the subquery `(SELECT AVG(score) FROM Students)` calculates the average score. The outer query then selects the names of students whose scores are greater than this average.

Example (Multiple-Row Subquery): Suppose we have a table of `Customers` and a table of `Orders`. We want to find all customers who have placed orders for a specific product (e.g., product ID 123). ```sql SELECT customer_name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id = 123); ``` The subquery `(SELECT customer_id FROM Orders WHERE product_id = 123)` returns a list of customer IDs who have ordered product

1

2

3. The outer query then selects the names of customers whose IDs are in this list.

Example (Correlated Subquery): Let's say we have `Departments` and `Employees` tables. We want to find all employees who earn more than the average salary for their respective department. ```sql SELECT employee_name FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE department_id = e.department_id); ``` The subquery is executed for each row in the `Employees` table (aliased as `e`). For each employee, it calculates the average salary of employees in the same department. The outer query then checks if the employee's salary is greater than this average. 2.2 Stored Procedures and Functions Stored procedures and functions are precompiled SQL code that can be stored in the database and executed by name.

They offer several benefits: Improved Performance: Because they are precompiled, they execute faster than ad-hoc SQL queries.

Code Reusability: They can be called from multiple applications, reducing code duplication.

Enhanced Security: They can control access to data by encapsulating logic and restricting direct access to tables.

Data Integrity: They can enforce business rules and data validation within the database.

Stored Procedures: A stored procedure is a named group of SQL statements that can accept input parameters and return output parameters. They are typically used to perform a specific task, such as inserting a new record, updating an existing record, or generating a report.

Example (Stored Procedure): Creating a stored procedure to add a new student to the `Students` table: ```sql CREATE PROCEDURE AddNewStudent ( @student_name VARCHAR(255), @student_id VARCHAR(20), @date_of_birth DATE ) AS BEGIN INSERT INTO Students (student_name, student_id, date_of_birth) VALUES (@student_name, @student_id, @date_of_birth); END; -- Executing the stored procedure EXEC AddNewStudent 'Thando Ndlovu', '2023001', '2005-03-15'; ``` Functions: A function is a named block of SQL code that accepts input parameters and returns a single value. They are typically used to perform calculations or data transformations.

Example (Function): Creating a function to calculate the age of a student based on their date of birth: ```sql CREATE FUNCTION CalculateAge (@date_of_birth DATE) RETURNS INT AS BEGIN DECLARE @age INT; SET @age = DATEDIFF(year, @date_of_birth, GETDATE()); RETURN @age; END; -- Using the function in a query SELECT student_name, dbo.CalculateAge(date_of_birth) AS age FROM Students; ``` 2.3 Database Transaction Management (ACID Properties) A transaction is a logical unit of work that must be either entirely completed or entirely rolled back. This is crucial for maintaining data consistency. The ACID properties are fundamental to transaction management: Atomicity: All operations within a transaction are treated as a single "atomic" unit. Either all operations succeed, or none do. If any operation fails, the entire transaction is rolled back to its original state.

Consistency: A transaction must maintain the integrity of the database. It must transition the database from one valid state to another valid state.

Isolation: Transactions must be isolated from each other. Concurrent transactions should not interfere with each other's results.

Durability: Once a transaction is committed (successfully completed), the changes are permanent and will survive even system failures (e.g., power outage).