Introduction to SQL: Talking to Your Data
In previous chapters, we learned how to design databases using ER diagrams and normalisation. But how do we actually "talk" to a database to get the information we need? We use SQL (Structured Query Language). Think of SQL as a professional waiter in a restaurant: you (the user) give an order using specific words, and the waiter fetches exactly what you asked for from the kitchen (the database).
In H2 Computing, we focus on SQLite, a lightweight but powerful version of SQL. Whether you are building a web application in Flask or managing a large dataset, these commands are your primary tools.
1. Setting the Foundation: Data Types and Constraints
Before we can store data, we need to tell the database what kind of data to expect and what rules to follow.
SQLite Data Types
INTEGER: Whole numbers (e.g., 1, 42, -5).
REAL: Numbers with decimal points (e.g., 3.14, 99.9).
TEXT: Strings of characters (e.g., 'Alice', 'Computing 9569').
NULL: Represents empty or missing data.
Constraints (The Rules)
Constraints ensure Data Integrity (making sure the data is accurate and reliable):
PRIMARY KEY: Uniquely identifies each record. No two rows can have the same Primary Key.
FOREIGN KEY: Links a record in one table to a record in another table.
NOT NULL: Ensures that a column cannot be left empty.
UNIQUE: Ensures all values in a column are different.
AUTOINCREMENT: Automatically increases the value (usually for IDs) every time a new record is added.
Key Takeaway: Choosing the right data type and constraints prevents "garbage data" from entering your system.
2. Creating and Deleting Tables (DDL)
To start a project, we first need to build the "containers" for our data.
CREATE TABLE
This command defines the table structure. Here is an example of creating a table for Students:
CREATE TABLE Students (
StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
Email TEXT UNIQUE,
Age INTEGER
);
DROP TABLE
If you need to delete an entire table and all its data, use this command. Be careful: there is no "undo" button!
DROP TABLE Students;
3. Managing Data: Insert, Update, and Delete (DML)
Once the table exists, we can manage the records inside it.
INSERT INTO
Adds a new row to a table.
INSERT INTO Students (Name, Email, Age) VALUES ('Alice Tan', 'alice@email.com', 17);
UPDATE
Changes existing data. Warning: Always use a WHERE clause, or you will update every single row in the table!
UPDATE Students SET Age = 18 WHERE Name = 'Alice Tan';
DELETE
Removes records. Again, always use WHERE to avoid deleting all your data.
DELETE FROM Students WHERE StudentID = 1;
Key Takeaway: INSERT adds, UPDATE changes, and DELETE removes. Always double-check your WHERE clause!
4. The SELECT Query: Finding Information
The SELECT statement is the most common command you will use. It follows a specific order: SELECT (what columns) FROM (which table) WHERE (what conditions) ORDER BY (what sequence).
Basic SELECT
To see everything in a table:
SELECT * FROM Students;
(The asterisk * is a wildcard meaning "all columns".)
Filtering with WHERE
We use operators to narrow down results:
Comparison: \( = \), \( > \), \( < \), \( >= \), \( <= \), \( <> \) (not equal to).
Logical: AND, OR, NOT.
Null checks: IS NULL or IS NOT NULL.
Example: Find students older than 16 who have an email listed:
SELECT Name FROM Students WHERE Age > 16 AND Email IS NOT NULL;
Sorting with ORDER BY
Use ASC for ascending (default) or DESC for descending.
SELECT Name, Age FROM Students ORDER BY Age DESC;
5. Working with Multiple Tables (Joins)
In relational databases, data is split into many tables. To combine them, we use a JOIN.
The WHERE Join (Multi-table SELECT)
You can join tables by listing them and matching their keys in the WHERE clause:
SELECT Students.Name, Results.Grade
FROM Students, Results
WHERE Students.StudentID = Results.StudentID;
INNER JOIN
This returns only the records that have matching values in both tables.
SELECT Students.Name, Results.Grade
FROM Students
INNER JOIN Results ON Students.StudentID = Results.StudentID;
LEFT OUTER JOIN
This returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.
Analogy: If you join "Students" (Left) and "CCA" (Right) using a LEFT JOIN, you get a list of every student. If a student doesn't have a CCA, the CCA column just stays empty.
6. Summary Statistics: Aggregate Functions
Sometimes we don't want a list of names; we want a summary, like an average or a total.
COUNT(): Returns the number of rows.
SUM(): Returns the total sum of a numeric column.
MAX(): Returns the largest value.
MIN(): Returns the smallest value.
Example: To find the highest score in a test:
SELECT MAX(Score) FROM TestResults;
7. SQL Security: Preventing SQL Injection
In the "Social, Ethical and Security Impact" module, you will learn about SQL Injection. This is where a hacker enters SQL commands into a login form to "trick" the database into giving them access.
The Solution: Prepared Statements
Instead of building a query string like "SELECT * FROM Users WHERE Name = " + userInput, we use a template. The database treats the user input strictly as data, not as code. In Python's sqlite3 library, this is done using the \( ? \) placeholder.
Quick Review:
1. Use CREATE and DROP to manage table structures.
2. Use INSERT, UPDATE, and DELETE to manage records.
3. Use SELECT with WHERE and JOIN to retrieve specific data.
4. Use Aggregate Functions (COUNT, SUM, etc.) for math.
5. Use Prepared Statements to keep your database secure.
Don't worry if Joins feel difficult at first! Practice drawing the tables side-by-side and drawing lines between matching IDs; it will make the logic much clearer.