SQL

Q: What are the three main types of SQL commands introduced in database management?

The three main types are DQL (Data Query Language) for querying data, DML (Data Manipulation Language) for changing data, and DDL (Data Definition Language) for defining and managing database structure.

Q: How do you select specific columns or all columns from a table in SQL, and how can duplicate rows be removed from the output?

Use SELECT column1, column2 FROM TableName; for specific columns, SELECT * FROM TableName; for all columns, and add the DISTINCT keyword ( SELECT DISTINCT column FROM TableName; ) to eliminate duplicate rows.

Q: What is the purpose of the WHERE clause in SQL and what special operators can be used within it?

The WHERE clause filters records based on specified conditions using comparison operators, logical operators ( AND , OR , NOT ), and special operators like BETWEEN , IN , LIKE , and IS NULL .

Q: How does the ORDER BY clause sort SQL query results?

ORDER BY sorts query results in ascending order ( ASC , default) or descending order ( DESC ) based on one or more specified columns.

Q: What are the standard aggregate functions in SQL and what do they compute?

SQL aggregate functions perform calculations on multiple row values to return a single summary value: COUNT() , SUM() , AVG() , MAX() , and MIN() .

Q: What is the difference between GROUP BY and HAVING in SQL?

GROUP BY divides rows into summary groups to calculate aggregate functions for each group, while HAVING filters those groups based on aggregate conditions (unlike WHERE , which filters individual rows before grouping).

Q: Which SQL commands are used in DML to insert, update, and delete records?

DML uses INSERT INTO ... VALUES (...) to add new records, UPDATE ... SET ... WHERE to modify existing data, and DELETE FROM ... WHERE to remove records.

Q: What is the difference between an INNER JOIN and outer joins ( LEFT JOIN / RIGHT JOIN )?

An INNER JOIN returns only rows with matching values in both tables, whereas a LEFT JOIN (or RIGHT JOIN ) returns all records from the specified side table plus matched records from the other, filling non-matching fields with NULL .

Q: What is a sub-query in SQL and how does it execute?

A sub-query is a query nested inside another query; the inner query executes first and its calculated result is passed to the outer query as a condition.

Q: What are the key table integrity constraints used in a CREATE TABLE statement?

Key integrity constraints include PRIMARY KEY , FOREIGN KEY ... REFERENCES , NOT NULL , UNIQUE , CHECK , and DEFAULT .

Practice

Information and Communication Technology · Senior Secondary (HKDSE) · 11 min read · Updated: 29 Aug 2026

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".

Eliminating Duplicates with DISTINCT:

If you want to view unique values without repetition, use the DISTINCT keyword.

List all unique classes represented in the student table.

SELECT DISTINCT Class FROM Students;

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 ____';
- IS NULL / IS NOT NULL: Checks for missing or undefined data (never use = NULL).
  Example: WHERE Score IS NOT NULL;

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 Structure

For standard queries, the clauses must follow this strict order:

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

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';

Grouping Data with GROUP BY and HAVING

When you want aggregate statistics for subgroups rather than the entire table (such as average scores for each class), use GROUP BY.

Find the average score for each class:

SELECT Class, AVG(Score) FROM Students GROUP BY Class;

To filter groups based on aggregate values, use the HAVING clause (WHERE filters individual records before grouping, while HAVING filters summary groups after grouping):

Find classes where the average score is greater than 80:

SELECT Class, AVG(Score) FROM Students GROUP BY Class HAVING AVG(Score) > 80;

String Functions: Working with Text

SQL has functions to work with text data:

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 UPPER(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, E for Enrollments, 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 (ICT and ENG), Lee Siu Ming (ICT) and Wong Ka Wai (MATH) with their courses, but Ng Mei Ling and Cheung Pui Sze 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.

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., 81.6).
2. The outer query then runs as: SELECT FirstName, LastName FROM Students WHERE Score > 81.6;

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 Tables with DDL & Integrity Constraints

To define a new table and enforce business rules, we use the CREATE TABLE statement along with column constraints:

PRIMARY KEY: Uniquely identifies each record and prevents duplicates and NULL values.
FOREIGN KEY ... REFERENCES: Enforces referential integrity with another table.
NOT NULL: Ensures a column cannot contain NULL values.
UNIQUE: Ensures all values in a column are distinct.
CHECK: Enforces a specific condition on values (e.g., CHECK (Score >= 0 AND Score <= 100)).
DEFAULT: Assigns a default value when no value is provided.

Example: Defining the Students table with constraints.

CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Class VARCHAR(5), Score INT CHECK (Score >= 0 AND Score <= 100) );

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);

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 is decades old, it remains the standard language for relational databases worldwide!

Final Summary

You've learned the fundamentals of SQL! From simple queries with SELECT...FROM...WHERE and aggregate groupings with GROUP BY / HAVING, to table creation with CREATE TABLE, and multi-table JOINs. Practice is key, so try writing your own queries for different scenarios. Well done!

References

Information and Communication Technology Curriculum and Assessment Guide (Secondary 4–6) (2021) — Curriculum Development Council & HKEAA
Technology Education – Curriculum Documents — Education Bureau (EDB)

Quick check

Can you answer these now?

Open each question to check the key ideas from this chapter.

What are the three main types of SQL commands introduced in database management?

The three main types are DQL (Data Query Language) for querying data, DML (Data Manipulation Language) for changing data, and DDL (Data Definition Language) for defining and managing database structure.

How do you select specific columns or all columns from a table in SQL, and how can duplicate rows be removed from the output?

Use SELECT column1, column2 FROM TableName; for specific columns, SELECT * FROM TableName; for all columns, and add the DISTINCT keyword (SELECT DISTINCT column FROM TableName;) to eliminate duplicate rows.

What is the purpose of the WHERE clause in SQL and what special operators can be used within it?

The WHERE clause filters records based on specified conditions using comparison operators, logical operators (AND, OR, NOT), and special operators like BETWEEN, IN, LIKE, and IS NULL.

How does the ORDER BY clause sort SQL query results?

ORDER BY sorts query results in ascending order (ASC, default) or descending order (DESC) based on one or more specified columns.

What are the standard aggregate functions in SQL and what do they compute?

SQL aggregate functions perform calculations on multiple row values to return a single summary value: COUNT(), SUM(), AVG(), MAX(), and MIN().

What is the difference between GROUP BY and HAVING in SQL?

GROUP BY divides rows into summary groups to calculate aggregate functions for each group, while HAVING filters those groups based on aggregate conditions (unlike WHERE, which filters individual rows before grouping).

Which SQL commands are used in DML to insert, update, and delete records?

DML uses INSERT INTO ... VALUES (...) to add new records, UPDATE ... SET ... WHERE to modify existing data, and DELETE FROM ... WHERE to remove records.

What is the difference between an INNER JOIN and outer joins (LEFT JOIN / RIGHT JOIN)?

An INNER JOIN returns only rows with matching values in both tables, whereas a LEFT JOIN (or RIGHT JOIN) returns all records from the specified side table plus matched records from the other, filling non-matching fields with NULL.

What is a sub-query in SQL and how does it execute?

A sub-query is a query nested inside another query; the inner query executes first and its calculated result is passed to the outer query as a condition.

What are the key table integrity constraints used in a CREATE TABLE statement?

Key integrity constraints include PRIMARY KEY, FOREIGN KEY ... REFERENCES, NOT NULL, UNIQUE, CHECK, and DEFAULT.

What is a SQL VIEW and why is it useful?

A VIEW is a virtual table defined by a saved SQL query (created using CREATE VIEW ... AS SELECT ...) that simplifies complex queries and enhances data security.

How is the ALTER TABLE command used to modify the structure of an existing table?

ALTER TABLE is a DDL command used to modify existing table schemas by adding columns (ADD), deleting columns (DROP COLUMN), or changing data types (MODIFY COLUMN / ALTER COLUMN).

Ready to test yourself?

10 practice questions on this topic, with worked solutions. Multiple choice is marked as you go. No account needed.

Practise This Topic

More Information and Communication Technology chapters

* The content provided by thinka is generated by AI and may not always be accurate or up-to-date. Please use it as a supplementary resource and verify with official materials.

Put These Notes into Practice

Reading the notes is a great start. Now practise with unlimited AI-generated questions and get instant feedback. 100,000+ students are already improving their grades.

Start Practising Now View Pricing

Done reading? Test yourself with AI practice questions

Practise This Topic Now