Chapter Notes: Mastering SQL (Structured Query Language)

Hey everyone! Welcome to the world of databases. Think about all the data around you: your friends' contact info, your school's student records, the products on an online shopping site. How is all this information stored, managed, and retrieved? The answer is often a database, and the language we use to "talk" to it is SQL.

In these notes, we'll learn how to use SQL to ask questions, change information, and even build parts of a database. It's like learning the secret language that powers many of the apps and websites you use every day. Don't worry if it seems tricky at first – we'll break it down step-by-step with lots of examples!


What is SQL? A Simple Analogy

Imagine your school library has a super-organised digital catalogue. SQL is the special language you use to ask the librarian (the Database Management System or DBMS) to find, add, or update book information in that catalogue (the database).

  • Asking for data: "Show me all books written by J.K. Rowling."
  • Adding data: "Add this new book, 'The Adventures of ICT', to the system."
  • Updating data: "Change the status of 'Harry Potter' from 'Available' to 'Checked Out'."

SQL has a few main types of commands. Let's think of them as different kinds of requests:

  • Data Query Language (DQL): Used for asking questions and getting data. (The most common!)
  • Data Manipulation Language (DML): Used for changing the data (adding, updating, deleting records).
  • Data Definition Language (DDL): Used for building and managing the database structure itself (creating or changing tables).


Part 1: Querying Data with SELECT (DQL)

This is the most important and frequently used part of SQL. It's all about retrieving information. The basic structure is simple and easy to remember.

Let's use a sample table called Students for our examples:

Students Table

StudentID | FirstName | LastName | Class | Score
101 | Chan | Tai Man | 5A | 88
102 | Lee | Siu Ming | 5B | 95
103 | Wong | Ka Wai | 5A | 72
104 | Ng | Mei Ling | 5C | 88
105 | Cheung | Pui Sze | 5B | 65

The Basic SELECT Statement

The two essential parts are SELECT (what columns you want) and FROM (what table to look in).

To get specific columns:

Find the first name and last name of all students.

SELECT FirstName, LastName FROM Students;

To get ALL columns (using the wildcard *):

Find all information about every student.

SELECT * FROM Students;

Memory Aid: Think of * as a wildcard meaning "everything".

Filtering Results with WHERE

What if you don't want everyone? The WHERE clause lets you set conditions to filter your results.

Find the names of students in Class 5A.

SELECT FirstName, LastName FROM Students WHERE Class = '5A';

Important: Text values (like '5A') are usually put inside single quotes ' '.

Operators for the WHERE Clause

You can use different operators to build powerful filters:

  • Comparison Operators:
    • = (Equal to)
    • <> (Not equal to)
    • > (Greater than)
    • < (Less than)
    • >= (Greater than or equal to)
    • <= (Less than or equal to)
  • Logical Operators:
    • AND: Both conditions must be true. (e.g., WHERE Class = '5A' AND Score > 80)
    • OR: At least one condition must be true. (e.g., WHERE Score < 70 OR Score > 90)
    • NOT: Reverses the result of a condition. (e.g., WHERE NOT Class = '5A')
  • Special Operators:
    • BETWEEN: Checks if a value is within a range (inclusive).
      Example: WHERE Score BETWEEN 70 AND 90; (This is the same as Score >= 70 AND Score <= 90)
    • IN: Checks if a value matches any value in a list.
      Example: WHERE Class IN ('5A', '5C'); (This is a shortcut for Class = '5A' OR Class = '5C')
    • LIKE: Used for pattern matching in text. It uses wildcards:
      • %: Represents zero, one, or multiple characters.
      • _: Represents a single character.
      Example 1: Find students whose last name starts with 'W'.
      WHERE LastName LIKE 'W%';
      Example 2: Find students whose first name is 'Siu' followed by exactly four characters.
      WHERE FirstName LIKE 'Siu ____';

Sorting Results with ORDER BY

The ORDER BY clause sorts your results. By default, it sorts in ascending order (A-Z, 1-100).

  • ASC: Ascending order (the default).
  • DESC: Descending order.

List all students, sorted by their score from highest to lowest.

SELECT FirstName, LastName, Score FROM Students ORDER BY Score DESC;

Quick Review: The DQL Golden Trio

For most queries, you'll use this structure:

SELECT [columns]
FROM [table]
WHERE [conditions]
ORDER BY [column to sort by];

You don't always need `WHERE` or `ORDER BY`, but this is the order they must appear in!



Part 2: Summarising and Manipulating Data (Elective Content)

Now we move into more advanced topics covered in the Database elective. This is where SQL gets really powerful!

Aggregate Functions: The Big Picture

These functions perform a calculation on a set of rows and return a single summary value.

  • COUNT(): Counts the number of rows.
  • SUM(): Adds up all the values in a column.
  • AVG(): Calculates the average of the values in a column.
  • MAX(): Finds the highest value in a column.
  • MIN(): Finds the lowest value in a column.

How many students are there in total?

SELECT COUNT(*) FROM Students;

What is the average score of students in Class 5A?

SELECT AVG(Score) FROM Students WHERE Class = '5A';

String Functions: Working with Text

SQL has functions to work with text data. The exact names can vary, but the concepts are similar.

  • LENGTH() or LEN(): Gets the number of characters in a string.
  • UCASE() or UPPER(): Converts text to uppercase.
  • LCASE() or LOWER(): Converts text to lowercase.

Show all student last names in uppercase.

SELECT UCASE(LastName) FROM Students;

Changing Data with DML

These commands modify the data *inside* your tables.

WATCH OUT! A mistake with DML can permanently change or delete your data. Always be careful with the WHERE clause!

INSERT INTO: Add New Data

Adds a new row (record) to a table.

Add a new student to the table.

INSERT INTO Students (StudentID, FirstName, LastName, Class, Score) VALUES (106, 'Lau', 'Kit Ying', '5C', 81);

UPDATE: Modify Existing Data

Changes data in existing rows.

Common Mistake: Forgetting the `WHERE` clause will update ALL rows in the table!

Chan Tai Man (StudentID 101) retook a test and his score is now 91.

UPDATE Students SET Score = 91 WHERE StudentID = 101;

DELETE: Remove Data

Deletes rows from a table.

Common Mistake: Forgetting the `WHERE` clause will delete ALL rows in the table!

Student Cheung Pui Sze (StudentID 105) has left the school.

DELETE FROM Students WHERE StudentID = 105;

Key Takeaway: DML Safety

The WHERE clause is your safety net for UPDATE and DELETE. It tells the database exactly which record(s) to change or remove. Double-check it before you run the command!



Part 3: Advanced Queries with Joins & Sub-queries (Elective Content)

Real databases have data split across many tables to be efficient. How do we combine them? With JOINs!

Let's add another table, Courses:

Courses Table

CourseID | CourseName | Teacher
ICT | Information Tech | Mr. Lee
ENG | English Language | Ms. Davis
MATH| Mathematics | Mrs. Chan

Prerequisite: Primary & Foreign Keys

To link tables, we need special keys.

  • A Primary Key (PK) is a column that uniquely identifies each record in a table (like `StudentID` in the Students table).
  • A Foreign Key (FK) is a primary key from one table that you put into another table to create a link.

Joining Tables

A JOIN combines rows from two or more tables based on a related column between them.

Equi-Join (or INNER JOIN)

This is the most common type of join. It returns only the records that have matching values in both tables.

Let's imagine we have a third table, Enrollments, that links Students and Courses.

Enrollments Table

StudentID | CourseID
101 | ICT
101 | ENG
102 | ICT
103 | MATH

Show the first name of each student and the name of the course they are enrolled in.

SELECT S.FirstName, C.CourseName FROM Students S JOIN Enrollments E ON S.StudentID = E.StudentID JOIN Courses C ON E.CourseID = C.CourseID;

Explanation:
1. We use aliases (S for Students, C for Courses) to make the code shorter.
2. The ON keyword specifies the linking condition: `ON table1.column = table2.column`.

Outer Joins (LEFT and RIGHT)

What if you want to see all records from one table, even if they don't have a match in the other?

  • LEFT JOIN: Returns all records from the left table (the first one mentioned), and the matched records from the right table. If there's no match, the result is NULL on the right side.
  • RIGHT JOIN: Returns all records from the right table. It's the opposite of LEFT JOIN.

List ALL students and the courses they take. Students taking no courses should still be listed.

SELECT S.FirstName, E.CourseID FROM Students S LEFT JOIN Enrollments E ON S.StudentID = E.StudentID;

This would show Chan Tai Man and Lee Siu Ming with their courses, but Wong Ka Wai, Ng Mei Ling, etc., would also be listed with a NULL value for `CourseID` because they are in the left table (Students) but have no match in the Enrollments table.

Natural Join

A NATURAL JOIN is a type of equi-join where the database automatically joins tables based on columns that have the same name. It's convenient but can be unpredictable if tables have multiple columns with the same name.

(In our example, we'd rename the columns to `StudentID` in both tables to use it).

SELECT FirstName, CourseID FROM Students NATURAL JOIN Enrollments;

Sub-queries (One Level Deep)

A sub-query is a query nested inside another query. The result of the inner query is used by the outer query.

Find the names of students who have a score higher than the class average.

SELECT FirstName, LastName FROM Students WHERE Score > (SELECT AVG(Score) FROM Students);

Step-by-step:
1. The inner query (SELECT AVG(Score) FROM Students) runs first and calculates the average score (e.g., 82).
2. The outer query then runs as: SELECT FirstName, LastName FROM Students WHERE Score > 82;

Key Takeaway: Combining Data

JOINs are for combining columns from different tables. Sub-queries are for using the result of one query as a condition in another query.



Part 4: Views and Table Structure (Elective Content)

Creating a VIEW

A VIEW is a virtual table based on the result-set of an SQL statement. It's like a saved query that you can interact with like a real table. Views are useful for:

  • Simplifying complex queries.
  • Providing a layer of security by only showing certain columns or rows.

Create a view that only shows students from Class 5A.

CREATE VIEW Class5A_Students AS SELECT StudentID, FirstName, LastName, Score FROM Students WHERE Class = '5A';

Now, you can just query the view directly:

SELECT * FROM Class5A_Students;

Modifying Table Structure with DDL

Sometimes you need to change the structure (the "schema") of a table after it has been created. We use ALTER TABLE for this.

  • ADD Column: Adds a new column to a table.
  • DROP COLUMN: Removes a column.
  • MODIFY COLUMN or ALTER COLUMN: Changes the data type of a column.

Add a new column called 'Email' to the Students table.

ALTER TABLE Students ADD Email VARCHAR(255);

(VARCHAR is a common data type for variable-length text).

Remove the 'Email' column we just added.

ALTER TABLE Students DROP COLUMN Email;

Did you know?

SQL was first developed at IBM in the early 1970s. Its name was originally SEQUEL (Structured English Query Language). Even though it's nearly 50 years old, it remains one of the most in-demand skills for tech jobs worldwide!


Final Summary

You've learned the fundamentals of SQL! From simple queries with SELECT...FROM...WHERE to complex data manipulation and multi-table JOINs. Practice is key, so try to write your own queries for different scenarios. Well done!