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

• Apply advanced SQL with subqueries.
• Implement database transactions for data integrity.
• Understand and use stored procedures.
• Use database triggers to automate operations.
• Optimize SQL queries for better performance.

Lesson summary

This week, we delve deeper into advanced database concepts and SQL, focusing on manipulating and retrieving data effectively within applications. Databases are the backbone of almost every application you interact with daily, from social media platforms like TikTok and Instagram (managing user profiles, posts, and interactions) to online banking systems used by South African banks like FNB and ABSA (handling accounts, transactions, and customer data). Understanding how to efficiently access and manipulate this data is crucial for building robust and scalable applications.

Lesson notes

2.1 Subqueries and Correlated Subqueries A subquery is a query nested inside another SQL query. It can be used in the `WHERE`, `SELECT`, `FROM`, or `HAVING` clauses of the outer query.

Non-correlated Subqueries: These are independent queries. They execute once and pass their result to the outer query.

Example: Finding all customers who placed orders larger than the average order value. ```sql SELECT customer_id, customer_name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE order_total > (SELECT AVG(order_total) FROM Orders)); ``` Explanation: The inner query `SELECT AVG(order_total) FROM Orders` calculates the average order total. The next inner query `SELECT customer_id FROM Orders WHERE order_total > (average order total)` finds the customer IDs that have orders greater than the average. The outer query `SELECT customer_id, customer_name FROM Customers WHERE customer_id IN (list of customer IDs)` retrieves the customer details for those IDs.

Correlated Subqueries: These subqueries depend on the outer query for their values. They execute once for each row processed by the outer query. This can be slower than non-correlated subqueries but is essential for certain types of queries.

Example: Finding all customers who placed orders larger than the average order value for their specific region. (Assume each customer is associated with a 'region' in the `Customers` table) ```sql SELECT customer_id, customer_name FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id AND o.order_total > ( SELECT AVG(order_total) FROM Orders WHERE customer_id IN (SELECT customer_id FROM Customers WHERE region = c.region) ) ); ``` Explanation: The outer query iterates through each customer in the `Customers` table (aliased as `c`). For each customer, the correlated subquery calculates the average order total for customers in the same region as the current customer. The `EXISTS` clause checks if there is at least one order for the current customer whose order total is greater than the regional average. 2.2 Database Transactions A database transaction is a sequence of one or more SQL operations treated as a single logical unit of work. Transactions ensure data integrity by enforcing ACID properties: Atomicity: All operations within a transaction either succeed completely, or the entire transaction is rolled back as if it never happened.

Consistency: The transaction moves the database from one valid state to another.

Isolation: Transactions are isolated from each other, meaning one transaction cannot interfere with another.

Durability: Once a transaction is committed, its changes are permanent, even in the event of a system failure.

Example: Transferring money between bank accounts (using MySQL as the database): ```sql START TRANSACTION; UPDATE Accounts SET balance = balance - 1000 WHERE account_number = '12345'; UPDATE Accounts SET balance = balance + 1000 WHERE account_number = '67890'; -- Check if both updates were successful SELECT ROW_COUNT() INTO @rows_affected; IF @rows_affected = 2 THEN COMMIT; SELECT 'Transaction committed'; ELSE ROLLBACK; SELECT 'Transaction rolled back'; END IF; END; ``` Explanation: `START TRANSACTION` begins the transaction. The first `UPDATE` subtracts R1000 from account '12345'. The second `UPDATE` adds R1000 to account '67890'. `ROW_COUNT()` retrieves the number of rows affected by the last query. This is crucial for error checking in this scenario. If both updates were successful (2 rows affected in total), `COMMIT` makes the changes permanent. If either update failed, `ROLLBACK` undoes all changes. This ensures atomicity. 2.3 Stored Procedures A stored procedure is a precompiled SQL code stored within the database. Stored procedures offer several advantages: Reusability: Avoid writing the same SQL code multiple times.

Security: Grant permissions to execute the procedure without granting access to the underlying tables.

Performance: Precompiled code executes faster than dynamically generated SQ

L. Data Integrity: Enforce business rules within the procedure.

Example: Creating a stored procedure to add a new product to the Products table. (using MySQL): ```sql DELIMITER // CREATE PROCEDURE AddProduct( IN p_product_name VARCHAR(255), IN p_description TEXT, IN p_price DECIMAL(10, 2), IN p_category_id INT ) BEGIN INSERT INTO Products (product_name, description, price, category_id) VALUES (p_product_name, p_description, p_price, p_category_id); END // DELIMITER ; -- Calling the stored procedure: CALL AddProduct('New Smart Phone', 'Latest model with advanced features', 12999.99, 3); ``` Explanation: `DELIMITER //` changes the delimiter to `//` to avoid conflicts with semicolons within the procedure definition. `CREATE PROCEDURE AddProduct(...)` defines the procedure name and input parameters. The `BEGIN...END` block contains the SQL code to be executed. The `INSERT INTO Products...` statement adds the new product.