Mastering SQL for IGCSE WJEC Computer Science | IGCSE WJEC 计算机：SQL 考点精讲

📚 Mastering SQL for IGCSE WJEC Computer Science | IGCSE WJEC 计算机：SQL 考点精讲

Structured Query Language (SQL) is the standard language for managing relational databases. For the IGCSE WJEC Computer Science specification, a solid understanding of SQL is essential—not just for the exam but for real‑world data handling. This article covers every key SQL concept you need, from creating tables to joining them, with practical examples and exam tips.

结构化查询语言（SQL）是管理关系数据库的标准语言。在 IGCSE WJEC 计算机科学考试中，扎实掌握 SQL 至关重要——不仅为了考试，也为了真实世界的数据处理。本文涵盖所有核心 SQL 考点，从建表到表连接，配有实用示例和应试技巧。

1. Introduction to SQL & Databases | SQL 与数据库简介

SQL is used to interact with relational databases, where data is stored in tables made up of rows (records) and columns (fields). A database can contain multiple tables linked by primary and foreign keys.

SQL 用于与关系数据库交互，数据存储在由行（记录）和列（字段）组成的表中。一个数据库可以包含多张通过主键和外键关联的表。

WJEC expects you to understand how to write SQL commands to define, manipulate, and query data. All SQL keywords are case‑insensitive, but it is conventional to write them in UPPERCASE for readability.

WJEC 考试要求你理解如何编写 SQL 命令来定义、操作和查询数据。所有 SQL 关键字不区分大小写，但习惯上用大写以增强可读性。

---

2. Data Types & Table Creation | 数据类型与建表

Before you can store data, you must create a table with appropriate data types. The main data types you need to know are: INTEGER (whole number), VARCHAR(n) (variable‑length text, max n characters), CHAR(n) (fixed‑length text), BOOLEAN (true/false), DATE, and REAL/FLOAT (decimal numbers).

存储数据前，必须先创建一个具有合适数据类型的表。你需要掌握的主要数据类型有：INTEGER（整数）、VARCHAR(n)（可变长度文本，最多 n 个字符）、CHAR(n)（固定长度文本）、BOOLEAN（真/假）、DATE 和 REAL/FLOAT（小数）。

The CREATE TABLE command defines the table name, column names, data types, and constraints. Common constraints include PRIMARY KEY, NOT NULL, and UNIQUE.

CREATE TABLE 命令用于定义表名、列名、数据类型和约束。常见的约束包括 PRIMARY KEY、NOT NULL 和 UNIQUE。

CREATE TABLE Pupil (

  PupilID INTEGER PRIMARY KEY,

  Name VARCHAR(50) NOT NULL,

  DateOfBirth DATE,

  YearGroup INTEGER

);

Here, PupilID is the primary key—every value must be unique and not null. Name cannot be left empty. DateOfBirth uses the DATE type, and YearGroup is an integer.

这里，PupilID 是主键——每个值必须唯一且不为空。Name 不能留空。DateOfBirth 使用 DATE 类型，YearGroup 是整数。

---

3. SELECT Statements: Retrieving Data | SELECT 语句：数据查询

The SELECT command is used to fetch data from a database. The simplest form returns all columns and all rows:

SELECT 命令用于从数据库获取数据。最简单的形式返回所有列和所有行：

SELECT * FROM Pupil;

To retrieve only specific columns, list them instead of *:

要只检索特定列，列出列名而不是 * ：

SELECT Name, DateOfBirth FROM Pupil;

You can also give columns a temporary alias using AS. This does not rename the column in the table, only in the output.

你还可以用 AS 给列起一个临时别名。这不会重命名表中的列，只会影响输出。

SELECT Name AS PupilName FROM Pupil;

---

4. Filtering with WHERE Clauses | WHERE 子句过滤

filter records based on a condition. Comparison operators include =, <>, <, >, <=, and >=.

WHERE 子句允许你根据条件筛选记录。比较运算符包括 =、<>、<、>、<= 和 >=。

To find all pupils in Year 10:

查找所有 10 年级的学生：

SELECT * FROM Pupil WHERE YearGroup = 10;

Text values must be enclosed in single quotes: WHERE Name = 'Smith'. Note that SQL uses single quotes for strings, unlike many programming languages.

文本值必须用单引号括起来：WHERE Name = 'Smith'。注意，SQL 使用单引号表示字符串，这与许多编程语言不同。

---

5. Using Wildcards & Logical Operators | 通配符与逻辑运算符

The LIKE operator is used for pattern matching. It uses two wildcards: % represents zero or more characters, and _ represents exactly one character.

LIKE 运算符用于模式匹配。它使用两种通配符：% 代表零个或多个字符，_ 代表恰好一个字符。

To find names starting with 'J': WHERE Name LIKE 'J%'. To find names with 'a' as the second letter: WHERE Name LIKE '_a%'.

查找以 'J' 开头的名字：WHERE Name LIKE 'J%'。查找第二个字母为 'a' 的名字：WHERE Name LIKE '_a%'。

Logical operators AND, OR, and NOT combine conditions. Use parentheses to avoid ambiguity.

逻辑运算符 AND、OR 和 NOT 可以组合条件。使用括号避免歧义。

SELECT * FROM Pupil WHERE YearGroup = 11 AND Name LIKE 'A%';

---

广告 / Sponsored

6. Sorting with ORDER BY | ORDER BY 排序

ORDER BY sorts the result set by one or more columns. The default order is ascending (ASC); use DESC for descending.

ORDER BY 按一列或多列对结果集进行排序。默认顺序为升序（ASC）；使用 DESC 降序。

List all pupils sorted by surname, then first name:

按姓排序，再按名排序列出所有学生：

SELECT * FROM Pupil ORDER BY Surname ASC, FirstName ASC;

You can also sort by column position, e.g., ORDER BY 2, but using column names is clearer and recommended.

也可以按列位置排序，例如 ORDER BY 2，但使用列名更清晰，推荐这样做。

---

7. Inserting, Updating & Deleting Data | 插入、更新与删除数据

To add new rows, use INSERT INTO. Specify the columns, then the VALUES. If you are inserting values for all columns in order, you can omit the column list, but it is safer to include it.

添加新行使用 INSERT INTO。指定列，然后给出 VALUES。如果要按顺序为所有列插入值，可以省略列清单，但为了安全建议包含。

INSERT INTO Pupil (PupilID, Name, DateOfBirth, YearGroup)

VALUES (101, 'Alice Brown', '2009-05-14', 10);

To modify existing data, use UPDATE with SET and a WHERE clause. Without WHERE, all rows will be updated!

修改现有数据使用 UPDATE、SET 和 WHERE 子句。若没有 WHERE，所有行都会被更新！

UPDATE Pupil SET YearGroup = 11 WHERE PupilID = 101;

DELETE removes rows. Again, always include a WHERE clause unless you intend to delete everything.

DELETE 删除行。同样，务必包含 WHERE 子句，除非你想删除所有数据。

DELETE FROM Pupil WHERE PupilID = 101;

---

8. Aggregate Functions & GROUP BY | 聚合函数与分组

Aggregate functions calculate a single value from multiple rows: COUNT, SUM, AVG, MIN, and MAX. They are often used with GROUP BY to split data into groups.

聚合函数从多行计算出一个值：COUNT、SUM、AVG、MIN 和 MAX。它们常与 GROUP BY 结合使用，将数据分组。

To count pupils per year group:

SELECT YearGroup, COUNT(*) AS NumberOfPupils

FROM Pupil

GROUP BY YearGroup;

You can filter groups using HAVING, which works like WHERE but for aggregated data.

你可以使用 HAVING 过滤分组，它的作用类似 WHERE，但针对聚合后的数据。

SELECT YearGroup, COUNT(*) FROM Pupil GROUP BY YearGroup HAVING COUNT(*) > 5;

Remember: WHERE filters rows before grouping; HAVING filters after grouping.

记住：WHERE 在分组前过滤行，HAVING 在分组后过滤。

---

9. JOINs: Combining Tables | 连接表

Relational databases store data across multiple tables to reduce redundancy. JOINs combine rows from two or more tables based on a related column.

关系数据库将数据存储在多个表中以减少冗余。JOIN 根据相关列连接两个或多个表的行。

The most common is INNER JOIN, which returns only rows where there is a match in both tables.

最常用的是 INNER JOIN，只返回两个表中匹配的行。

Suppose a Result table stores exam marks linked to Pupil via PupilID:

SELECT Pupil.Name, Result.Subject, Result.Mark

FROM Pupil

INNER JOIN Result ON Pupil.PupilID = Result.PupilID;

LEFT JOIN returns all rows from the left table, plus matching rows from the right table (NULL if no match). RIGHT JOIN does the reverse. WJEC mainly focuses on INNER JOIN, but you should recognise the others.

LEFT JOIN 返回左表的所有行，加上右表匹配的行（无匹配时为 NULL）。RIGHT JOIN 相反。WJEC 主要考察 INNER JOIN，但你应该认识其他类型。

---

10. SQL Exam-style Questions & Tips | 考题示例与技巧

Exam questions often provide a scenario (e.g., a library database) and ask you to write SQL statements. Read the table structure carefully, noting primary and foreign keys. Double‑check data types: text needs quotes, numbers do not.

考试题目通常会给出一个场景（例如图书馆数据库），要求你编写 SQL 语句。仔细阅读表结构，注意主键和外键。反复检查数据类型：文本需要引号，数字不需要。

Common mistakes: missing semicolons (though WJEC usually does not penalise a missing semi‑colon), forgetting WHERE in UPDATE/DELETE, and confusing HAVING with WHERE. Practice writing queries by hand, as you will do in the exam.

常见错误：漏写分号（尽管 WJEC 通常不扣分）、在 UPDATE/DELETE 中忘记 WHERE、混淆 HAVING 和 WHERE。练习手写查询语句，因为考试中你需要这样做。

When you finish, read your SQL aloud: does it make logical sense? Could it accidentally delete all data? Always include a WHERE clause when modifying or deleting.

完成后，通读你的 SQL：它逻辑上合理吗？会不会意外删除所有数据？在修改或删除时务必包含 WHERE 子句。

Published by TutorHao | Computer Science Revision Series | aleveler.com

更多咨询请联系16621398022（同微信）