A-Level WJEC Computer Science: SQL Key Points Explained | A-Level WJEC 计算机：SQL 考点精讲

📚 A-Level WJEC Computer Science: SQL Key Points Explained | A-Level WJEC 计算机：SQL 考点精讲

Structured Query Language (SQL) is the backbone of all relational database systems and a core topic in the WJEC A-Level Computer Science specification. Mastery of SQL not only secures marks in the written paper but is also vital for coursework and practical problem-solving. This revision guide breaks down every essential SQL concept you need for your exam, from creating tables to complex queries, ensuring you can approach any database question with confidence.

结构化查询语言（SQL）是所有关系数据库系统的基石，也是 WJEC A-Level 计算机科学考试大纲中的核心主题。掌握 SQL 不仅能确保笔试得分，对课程作业和实际问题解决也至关重要。本复习指南将拆解你需要掌握的每一个基本 SQL 概念，从创建表到复杂查询，确保你能自信地应对任何数据库问题。

1. Introduction to SQL in WJEC A-Level | WJEC A-Level 中的 SQL 介绍

SQL is divided into two main categories: Data Definition Language (DDL) for defining database structures, and Data Manipulation Language (DML) for handling data within those structures. WJEC papers test both, with a strong emphasis on retrieval queries using SELECT, joins, and aggregate functions.

SQL 分为两大类：用于定义数据库结构的数据定义语言（DDL），以及用于处理结构内数据的数据操作语言（DML）。WJEC 试卷对两者都会考查，重点在于使用 SELECT、连接和聚合函数的检索查询。

You must be able to write correct SQL syntax from memory. The exam expects standard SQL, so avoid product-specific extensions. Case sensitivity for keywords is not enforced, but the convention is to write SQL keywords in uppercase for readability.

你必须能够凭记忆写出正确的 SQL 语法。考试要求标准 SQL，因此避免特定产品的扩展语法。关键字不强制区分大小写，但习惯上为了可读性会用大写书写 SQL 关键字。

2. Data Definition Language (DDL): CREATE, ALTER, DROP | 数据定义语言：创建、修改、删除

DDL commands are used to create and modify the schema of the database. The most frequent exam question asks you to write a CREATE TABLE statement with appropriate data types and constraints.

DDL 命令用于创建和修改数据库模式。最常见的考题要求你写出一条带有适当数据类型和约束的 CREATE TABLE 语句。

CREATE TABLE: This command defines a new table, its columns, data types, and any constraints. Example:
CREATE TABLE Student (StudentID INT PRIMARY KEY, FirstName VARCHAR(30) NOT NULL, LastName VARCHAR(30), DOB DATE);

CREATE TABLE：此命令定义新表、其列、数据类型以及约束。例如：
CREATE TABLE Student (StudentID INT PRIMARY KEY, FirstName VARCHAR(30) NOT NULL, LastName VARCHAR(30), DOB DATE);

ALTER TABLE: Used to add, modify, or drop columns in an existing table.
Example to add a column: ALTER TABLE Student ADD Email VARCHAR(50);

ALTER TABLE：用于在现有表中添加、修改或删除列。
添加列的例子：ALTER TABLE Student ADD Email VARCHAR(50);

DROP TABLE: Removes the entire table structure and all its data permanently. Use with caution. DROP TABLE Student;

DROP TABLE：永久删除整个表结构及其所有数据，使用时要格外小心。DROP TABLE Student;

3. Data Types and Constraints | 数据类型与约束

WJEC expects you to choose correct data types: INT for whole numbers, DECIMAL(m,n) for exact decimals, VARCHAR(n) for variable-length strings, CHAR(n) for fixed-length strings, DATE for dates, and BOOLEAN for true/false values.

WJEC 要求你选择正确的数据类型：INT 用于整数，DECIMAL(m,n) 用于精确小数，VARCHAR(n) 用于变长字符串，CHAR(n) 用于定长字符串，DATE 用于日期，BOOLEAN 用于真/假值。

Constraints ensure data integrity. Key constraints are:

约束确保数据完整性。关键约束包括：

PRIMARY KEY – uniquely identifies each row. Cannot be NULL.
FOREIGN KEY – links two tables, referencing a primary key in another table.
NOT NULL – prevents empty values in a column.
UNIQUE – ensures all values in a column are distinct.
CHECK – restricts the range of allowed values, e.g., CHECK (Age >= 0).

PRIMARY KEY – 唯一标识每一行，不可为 NULL。
FOREIGN KEY – 连接两个表，引用另一个表中的主键。
NOT NULL – 防止列中出现空值。
UNIQUE – 确保列中所有值都是唯一的。
CHECK – 限制允许值的范围，例如 CHECK (Age >= 0)。

A correct CREATE TABLE with constraints might be: CREATE TABLE Enrolment (EnrolmentID INT PRIMARY KEY, StudentID INT, CourseID INT, EnrolDate DATE NOT NULL, FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (CourseID) REFERENCES Course(CourseID));

一条带有约束的正确 CREATE TABLE 可能是：CREATE TABLE Enrolment (EnrolmentID INT PRIMARY KEY, StudentID INT, CourseID INT, EnrolDate DATE NOT NULL, FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (CourseID) REFERENCES Course(CourseID));

4. Data Manipulation Language (DML): INSERT, UPDATE, DELETE | 数据操作语言：插入、更新、删除

DML commands handle the data stored in tables. Questions often give you a scenario and ask you to write a statement to add, modify, or remove records.

DML 命令处理表中存储的数据。题目常给出一个场景，要求你写出添加、修改或删除记录的语句。

INSERT INTO: Adds new rows. Two forms: specify values for all columns in order, or list specific columns.
Example: INSERT INTO Student (StudentID, FirstName, LastName, DOB) VALUES (101, 'Alice', 'Brown', '2005-03-21');

INSERT INTO：添加新行。有两种形式：按顺序为所有列指定值，或者列出特定列。
例如：INSERT INTO Student (StudentID, FirstName, LastName, DOB) VALUES (101, ‘Alice’, ‘Brown’, ‘2005-03-21’);

UPDATE: Modifies existing data. Always use a WHERE clause to target specific rows, otherwise all rows will be updated.
UPDATE Student SET LastName = 'White' WHERE StudentID = 101;

UPDATE：修改现有数据。务必使用 WHERE 子句定位特定行，否则所有行都会被更新。
UPDATE Student SET LastName = ‘White’ WHERE StudentID = 101;

DELETE: Removes rows. Again, WHERE is critical.
DELETE FROM Student WHERE StudentID = 101;

DELETE：删除行。同样，WHERE 子句至关重要。
DELETE FROM Student WHERE StudentID = 101;

5. SELECT Queries: Basic Retrieval and WHERE Clause | SELECT 查询：基本检索与 WHERE 子句

SELECT is the most examined SQL statement. Its general form: SELECT column1, column2 FROM table WHERE condition;

SELECT 是考查最多的 SQL 语句。其一般形式为：SELECT 列1, 列2 FROM 表 WHERE 条件;

Use * to retrieve all columns: SELECT * FROM Student; This is useful for quick checks but should be avoided in final solutions when only specific columns are needed.

使用 * 可检索所有列：SELECT * FROM Student; 这在快速检查时很有用，但在只需特定列的最终解答中应避免使用。

The WHERE clause filters rows using comparison operators: =, <>, <, >, <=, >=, and logical operators AND, OR, NOT.
Example: SELECT FirstName, LastName FROM Student WHERE Year = 12 AND TutorGroup = 'A';

WHERE 子句使用比较运算符（=、<>、<、>、<=、>=）和逻辑运算符（AND、OR、NOT）过滤行。
例如：SELECT FirstName, LastName FROM Student WHERE Year = 12 AND TutorGroup = ‘A’;

Pattern matching with LIKE: WHERE LastName LIKE 'S%' finds names starting with ‘S’. The wildcard % matches any sequence of characters; underscore _ matches a single character.

使用 LIKE 进行模式匹配：WHERE LastName LIKE ‘S%’ 可找到以 ‘S’ 开头的姓氏。通配符 % 匹配任意字符序列；下划线 _ 匹配单个字符。

Range checks with BETWEEN: WHERE DOB BETWEEN '2005-01-01' AND '2005-12-31'. Set membership with IN: WHERE City IN ('Cardiff', 'Swansea').

使用 BETWEEN 进行范围检查：WHERE DOB BETWEEN ‘2005-01-01’ AND ‘2005-12-31’。使用 IN 检查集合成员：WHERE City IN (‘Cardiff’, ‘Swansea’)。

6. Sorting with ORDER BY and Distinct Values | 使用 ORDER BY 排序和去重

ORDER BY sorts the output. Ascending (ASC) is default; use DESC for descending.
Example: SELECT FirstName, LastName, Grade FROM Results ORDER BY Grade DESC, LastName ASC; This sorts by grade highest first, then by surname alphabetically for ties.

ORDER BY 对输出进行排序。默认升序（ASC）；使用 DESC 降序。
例如：SELECT FirstName, LastName, Grade FROM Results ORDER BY Grade DESC, LastName ASC; 这将先按成绩从高到低排序，成绩相同时再按姓氏字母升序排列。

DISTINCT removes duplicate rows from the result set.
SELECT DISTINCT City FROM Student; returns each city only once.

DISTINCT 从结果集中删除重复行。
SELECT DISTINCT City FROM Student; 每个城市只返回一次。

Both clauses are often combined: SELECT DISTINCT Subject FROM Results ORDER BY Subject;

这两个子句常组合使用：SELECT DISTINCT Subject FROM Results ORDER BY Subject;

7. Aggregate Functions: COUNT, SUM, AVG, MAX, MIN | 聚合函数：计数、求和、平均值、最大值、最小值

Aggregate functions perform calculations on a set of rows and return a single value. They are extensively tested in WJEC exams.

聚合函数对一组行执行计算并返回单个值。它们在 WJEC 考试中被广泛考查。

COUNT(*) – counts all rows including NULLs.
COUNT(column) – counts non-NULL values in a column.
SUM(column) – total of a numeric column.
AVG(column) – average of a numeric column.
MAX(column) – largest value.
MIN(column) – smallest value.

COUNT(*) – 统计所有行（包括 NULL）。
COUNT(column) – 统计列中非 NULL 值的数量。
SUM(column) – 数值列的总和。
AVG(column) – 数值列的平均值。
MAX(column) – 最大值。
MIN(column) – 最小值。

Example: SELECT AVG(Mark) AS AverageMark, MAX(Mark) AS Highest FROM ExamResult; Aliases (AS) make output more readable.

例如：SELECT AVG(Mark) AS AverageMark, MAX(Mark) AS Highest FROM ExamResult; 别名（AS）使输出更易读。

Be careful: aggregates ignore NULLs, which can affect averages. Use COUNT(*) to include all rows. When mixing aggregate functions with non-aggregate columns, you must use GROUP BY.

注意：聚合函数忽略 NULL 值，这会影响平均值。使用 COUNT(*) 可包含所有行。当混合使用聚合函数和非聚合列时，必须使用 GROUP BY。

8. Grouping Data with GROUP BY and HAVING | 使用 GROUP BY 和 HAVING 分组数据

GROUP BY groups rows that have the same values in specified columns, so aggregate functions can be applied to each group.
For example, find the average mark per subject: SELECT Subject, AVG(Mark) FROM Results GROUP BY Subject;

GROUP BY 将指定列中具有相同值的行分组，从而可以对每个组应用聚合函数。
例如，找出每门科目的平均分：SELECT Subject, AVG(Mark) FROM Results GROUP BY Subject;

Any column in the SELECT list that is not an aggregate must appear in the GROUP BY clause. This is a common exam pitfall.

SELECT 列表中任何未包含在聚合函数中的列都必须出现在 GROUP BY 子句中。这是常见的考试失分点。

HAVING filters groups after aggregation, unlike WHERE which filters rows before aggregation.
SELECT Subject, COUNT(*) AS StudentCount FROM Enrolment GROUP BY Subject HAVING COUNT(*) > 5; This returns only subjects with more than 5 enrolments.

HAVING 在聚合之后对分组进行过滤，这与在聚合前过滤行的 WHERE 不同。
SELECT Subject, COUNT(*) AS StudentCount FROM Enrolment GROUP BY Subject HAVING COUNT(*) > 5; 这仅返回选课人数超过 5 人的科目。

Order of clauses matters: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

子句的顺序很重要：SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

9. Joining Tables: INNER JOIN, LEFT JOIN, RIGHT JOIN | 连接表：内连接、左连接、右连接

Relational databases split data across multiple tables to reduce redundancy. Joins are used to combine them meaningfully. WJEC expects you to write explicit JOIN syntax, not old-style comma joins.

关系数据库将数据分散在多个表中以减少冗余。连接（Join）用于将数据有意义地组合起来。WJEC 要求你写出显式的 JOIN 语法，而不是旧式的逗号连接。

INNER JOIN returns only rows where there is a match in both tables.
SELECT Student.FirstName, Subject.Name FROM Student INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID INNER JOIN Subject ON Enrolment.SubjectID = Subject.SubjectID;

INNER JOIN 仅返回两个表中匹配的行。
SELECT Student.FirstName, Subject.Name FROM Student INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID INNER JOIN Subject ON Enrolment.SubjectID = Subject.SubjectID;

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, and matched rows from the right table; unmatched right columns appear as NULL.
This is useful for finding students who are not enrolled in any subject: SELECT Student.FirstName FROM Student LEFT JOIN Enrolment ON Student.StudentID = Enrolment.StudentID WHERE Enrolment.EnrolmentID IS NULL;

LEFT JOIN（或 LEFT OUTER JOIN）返回左表的所有行，右表匹配的行显示对应数据，不匹配时右表列为 NULL。
这对于查找未选修任何科目的学生很有用：SELECT Student.FirstName FROM Student LEFT JOIN Enrolment ON Student.StudentID = Enrolment.StudentID WHERE Enrolment.EnrolmentID IS NULL;

RIGHT JOIN works oppositely: all rows from the right table preserved. It is less common but examinable.

RIGHT JOIN 作用相反：保留右表的所有行。它不常用，但在考试范围内。

Use table aliases for complex joins: SELECT s.FirstName, su.Name FROM Student s INNER JOIN Enrolment e ON s.StudentID = e.StudentID INNER JOIN Subject su ON e.SubjectID = su.SubjectID;

在复杂连接中使用表别名：SELECT s.FirstName, su.Name FROM Student s INNER JOIN Enrolment e ON s.StudentID = e.StudentID INNER JOIN Subject su ON e.SubjectID = su.SubjectID;

10. Subqueries and Nested SELECT | 子查询与嵌套 SELECT

A subquery is a SELECT statement embedded inside another SQL statement, often in WHERE or FROM clauses. They can return a single value, a row, or a table.

子查询是嵌入在另一个 SQL 语句中的 SELECT 语句，常出现在 WHERE 或 FROM 子句中。它可以返回单个值、一行或一个表。

Example – find students whose marks are above the average: SELECT StudentID, Mark FROM Results WHERE Mark > (SELECT AVG(Mark) FROM Results); The inner query computes the average, and the outer query uses it for comparison.

例如 – 找出分数高于平均分的学生：SELECT StudentID, Mark FROM Results WHERE Mark > (SELECT AVG(Mark) FROM Results); 内部查询计算平均分，外部查询用它进行比较。

Subqueries with IN: SELECT FirstName FROM Student WHERE StudentID IN (SELECT StudentID FROM Enrolment WHERE SubjectID = 5);

子查询与 IN 配合使用：SELECT FirstName FROM Student WHERE StudentID IN (SELECT StudentID FROM Enrolment WHERE SubjectID = 5);

Correlated subqueries refer to a column from the outer query and are evaluated row by row. They appear with EXISTS or in the WHERE clause.
Example: SELECT s.FirstName FROM Student s WHERE EXISTS (SELECT 1 FROM Enrolment e WHERE e.StudentID = s.StudentID AND e.SubjectID = 5);

关联子查询引用了外部查询的列，逐行求值。它们常与 EXISTS 一起出现或在 WHERE 子句中使用。
例如：SELECT s.FirstName FROM Student s WHERE EXISTS (SELECT 1 FROM Enrolment e WHERE e.StudentID = s.StudentID AND e.SubjectID = 5);

Subqueries can also appear in the FROM clause as derived tables, but WJEC tends to focus on WHERE subqueries.

子查询也可以作为派生表出现在 FROM 子句中，但 WJEC 倾向于考查 WHERE 中的子查询。

11. Indexes and Performance | 索引与性能

While not a main DML topic, you should understand that indexes speed up data retrieval. An index is created on one or more columns of a table to allow faster searching, much like a book index.

尽管不是主要的 DML 主题，你应该理解索引能加快数据检索。索引创建在表的一个或多个列上，以加快搜索速度，就像书的索引一样。

Syntax: CREATE INDEX idx_student_lastname ON Student(LastName); This accelerates queries that search or sort by LastName. A unique index enforces uniqueness: CREATE UNIQUE INDEX idx_email ON Student(Email);

语法：CREATE INDEX idx_student_lastname ON Student(LastName); 这会加速按 LastName 搜索或排序的查询。唯一索引强制唯一性：CREATE UNIQUE INDEX idx_email ON Student(Email);

Indexes improve SELECT speed but slow down INSERT, UPDATE, and DELETE because the index must be maintained. The primary key automatically has an index. In WJEC questions, you may be asked to identify which columns to index for given query patterns.

索引能提高 SELECT 的速度，但会减慢 INSERT、UPDATE 和 DELETE，因为索引需要维护。主键自动带有一个索引。在 WJEC 考题中，可能会要求你根据给定的查询模式识别应创建索引的列。

12. Exam Tips and Common Mistakes | 考试技巧与常见错误

WJEC SQL questions often mix DDL and DML in a scenario. Read carefully: if asked to create a table, specify data types and constraints; if asked for a query, use correct join conditions and aggregation logic.

WJEC 的 SQL 题目经常在场景中混合 DDL 和 DML。仔细审题：如果要求创建表，则明确数据类型和约束；如果要求查询，则使用正确的连接条件和聚合逻辑。

Common pitfalls:

常见失分点：

Forgetting the semicolon at the end of statements (though some implementations accept without, examiners prefer completeness).
Using commas instead of AND in WHERE clauses.
Misplacing WHERE and HAVING: filter row data with WHERE, group results with HAVING.
Missing required columns in GROUP BY, leading to logical errors.
Confusing DELETE with DROP – DELETE removes rows, DROP removes the schema.
Not using table aliases or fully qualified column names when joins create ambiguity.

忘记语句末尾的分号（虽然有些实现允许不加，但阅卷人更倾向完整答案）。
在 WHERE 子句中使用逗号而不是 AND。
WHERE 和 HAVING 位置颠倒：用 WHERE 过滤行数据，用 HAVING 过滤分组结果。
GROUP BY 中遗漏必需的列，导致逻辑错误。
混淆 DELETE 与 DROP —— DELETE 删除行，DROP 删除模式。
当连接导致列名模糊时，未使用表别名或完全限定列名。

Practice by writing queries for given table structures, and always test your logic mentally: what would this return? Build complex queries incrementally—start with the core SELECT, add joins, then filters and grouping.

通过为给定表结构编写查询来练习，并始终在心里测试逻辑：这个查询会返回什么？逐步构建复杂查询——从核心 SELECT 开始，添加连接，然后添加过滤和分组。

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

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

A-Level WJEC Computer Science: SQL Key Points Explained | A-Level WJEC 计算机：SQL 考点精讲