Lesson Notes By Weeks and Term v5 - Grade 11

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

• Define and explain relational database components.
• Construct basic SQL SELECT statements to retrieve data.
• Use the ORDER BY clause to sort data.
• Use INSERT, UPDATE, and DELETE statements to modify data.
• Explain the importance of data integrity.

Lesson summary

This week, we delve deeper into the world of data and information management, specifically focusing on relational databases and the Structured Query Language (SQL). Relational databases are the backbone of almost every data-driven application you use daily, from social media platforms to online banking systems. Understanding how they work and how to interact with them using SQL is crucial for anyone interested in IT. Imagine a local spaza shop needing to manage its inventory efficiently, or a hospital keeping track of patient records securely – relational databases make these tasks possible.

Lesson notes

2.1 What is a Relational Database? A relational database is a structured way of storing data in tables. These tables are related to each other through common fields, establishing relationships between them. Think of it as a collection of spreadsheets, each representing a specific entity, linked together.

Tables: A table is a collection of related data organized in rows and columns. Each table represents an entity, such as "Customers," "Products," or "Orders." Rows (Records): A row represents a single instance of the entity in the table. For example, a row in the "Customers" table might represent a specific customer with their name, address, and contact details.

Columns (Fields/Attributes): A column represents a specific attribute of the entity. In the "Customers" table, columns might include "CustomerID," "FirstName," "LastName," "Address," and "PhoneNumber." Primary Key: A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It must be unique and cannot be NUL

L. For instance, "CustomerID" is often used as the primary key in the "Customers" table.

Foreign Key: A foreign key is a column in one table that refers to the primary key of another table. It establishes a relationship between the two tables. For example, the "Orders" table might have a "CustomerID" column as a foreign key, linking each order to the customer who placed it.

Relationships: Relational databases establish relationships between tables using primary and foreign keys. These relationships can be one-to-one, one-to-many, or many-to-many. 2.2 SQL Basics: SELECT Statements SQL (Structured Query Language) is the standard language for interacting with relational databases. The most fundamental statement is the `SELECT` statement, used to retrieve data.

Basic Syntax: ```sql SELECT column1, column2, ... FROM table_name WHERE condition; -- Optional WHERE clause for filtering ``` `SELECT `: To select all columns from a table, use the asterisk (`*`). ```sql SELECT * FROM Customers; ``` `WHERE` Clause: The `WHERE` clause is used to filter the data based on a specific condition.

Comparison Operators: `=`, ` `, ` =`, `!=` (not equal to)

Logical Operators: `AND`, `OR`, `NOT`

Example: Retrieving customers from Cape Town: ```sql SELECT FirstName, LastName, PhoneNumber FROM Customers WHERE Address LIKE '%Cape Town%'; -- Using LIKE for partial matches ``` 2.3 SQL Basics: ORDER BY Clause The `ORDER BY` clause is used to sort the retrieved data based on one or more columns.

Syntax: ```sql SELECT column1, column2, ... FROM table_name ORDER BY column_name ASC | DESC; ``` `ASC`: Ascending order (default). `DESC`: Descending order.

Example: Retrieving products sorted by price in descending order: ```sql SELECT ProductName, Price FROM Products ORDER BY Price DESC; ``` 2.4 SQL Basics: Data Manipulation (INSERT, UPDATE, DELETE) SQL allows us to modify the data stored in the database using the `INSERT`, `UPDATE`, and `DELETE` statements. `INSERT`: To add new data to a table. ```sql INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); ```

Example: Adding a new product to the "Products" table: ```sql INSERT INTO Products (ProductName, Price, Category) VALUES ('Umqombothi 5L', 35.00, 'Beverages'); ``` `UPDATE`: To modify existing data in a table. ```sql UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; -- Crucial WHERE clause to avoid updating all rows ```

Example: Updating the price of a product: ```sql UPDATE Products SET Price = 38.00 WHERE ProductName = 'Umqombothi 5L'; ``` `DELETE`: To remove data from a table. ```sql DELETE FROM table_name WHERE condition; -- Crucial WHERE clause to avoid deleting all rows ```

Example: Deleting a product from the "Products" table: ```sql DELETE FROM Products WHERE ProductName = 'Umqombothi 5L'; ``` 2.5 Data Integrity and Relationships Data integrity refers to the accuracy and consistency of data in the database. Primary and foreign keys play a crucial role in maintaining data integrity by enforcing relationships between tables. They prevent orphaned records (records that reference non-existent primary keys) and ensure that data remains consistent. For example, you can't add an order for a `CustomerID` that doesn't exist in the `Customers` table, thanks to the foreign key constraint. 2.6 SQL Injection (Introduction) SQL injection is a common security vulnerability where malicious SQL code is injected into user input fields, potentially allowing attackers to bypass security measures and gain unauthorized access to the database.