A-Level CCEA Computer Science: SQL Exam Essentials | A-Level CCEA 计算机：SQL 考点精讲

📚 A-Level CCEA Computer Science: SQL Exam Essentials | A-Level CCEA 计算机：SQL 考点精讲

SQL (Structured Query Language) is the standard language for interacting with relational databases. For the CCEA A-Level Computer Science specification, you are expected to master both data definition and data manipulation commands, understand how to query multiple tables with joins and subqueries, and appreciate transaction control. This article distils the essential SQL topics, provides clear syntax examples, and highlights common pitfalls that examiners love to test.

SQL（结构化查询语言）是操作关系数据库的标准语言。在 CCEA A-Level 计算机科学考纲中，你需要熟练掌握数据定义和数据操纵命令，理解如何使用连接和子查询进行多表查询，并了解事务控制。本文提炼了所有重要 SQL 考点，提供清晰的语法示例，并指出考官常考的那些易错点。

1. What is SQL? | SQL 简介

SQL stands for Structured Query Language. It is a declarative language used to communicate with relational database management systems (RDBMS). Rather than telling the system how to retrieve data, you specify what you want, and the database engine optimises the execution. The language is divided into several sublanguages: DDL (Data Definition Language) for defining schema objects, DML (Data Manipulation Language) for working with data rows, DCL (Data Control Language) for permissions, and TCL (Transaction Control Language) for managing transactions. In CCEA exams, the focus is on DDL and DML, with basic TCL awareness.

SQL 全称是结构化查询语言。它是一种声明式语言，用于与关系数据库管理系统（RDBMS）交互。你不需要告诉系统如何检索数据，只需要指定你想要什么，数据库引擎会优化执行过程。该语言分为几个子语言：DDL（数据定义语言）用于定义模式对象，DML（数据操纵语言）用于处理数据行，DCL（数据控制语言）用于权限管理，TCL（事务控制语言）用于管理事务。在 CCEA 考试中，重点是 DDL 和 DML，并要求对 TCL 有基本了解。

2. SQL Data Types | SQL 数据类型

When you create a table, each column must be assigned a data type. Common SQL data types you need to know for the exam include:

创建表时，每一列都必须指定数据类型。你需要掌握的常见 SQL 数据类型包括：

INTEGER or INT – stores whole numbers.
INTEGER 或 INT – 存储整数。
DECIMAL(p, s) or NUMERIC(p, s) – exact numeric values with precision p and scale s.
DECIMAL(p, s) 或 NUMERIC(p, s) – 具有精度 p 和小数位 s 的精确数值。
VARCHAR(n) – variable-length character string with a maximum length of n.
VARCHAR(n) – 最大长度为 n 的可变长度字符串。
CHAR(n) – fixed-length character string; shorter values are padded with spaces.
CHAR(n) – 固定长度字符串；较短的值会用空格填充。
DATE – stores a date (year, month, day) in YYYY-MM-DD format.
DATE – 以 YYYY-MM-DD 格式存储日期（年、月、日）。
BOOLEAN – stores TRUE or FALSE (not always directly available, but relevant in MySQL and PostgreSQL).
BOOLEAN – 存储 TRUE 或 FALSE（并非所有 DBMS 直接支持，但 MySQL 和 PostgreSQL 中常用）。

Choosing the correct data type ensures data integrity and affects storage efficiency. For instance, storing a person’s age should use INTEGER, while a name uses VARCHAR. The exam may ask you to justify your choice of data type for a given attribute.

选择正确的数据类型可以确保数据完整性，并影响存储效率。例如，人的年龄应使用 INTEGER，而姓名应使用 VARCHAR。考试可能会要求你为给定属性说明选择某种数据类型的理由。

3. Defining a Database Schema: CREATE TABLE | 定义数据库模式：CREATE TABLE

The CREATE TABLE statement is the core DDL command. It defines the table name, column names, data types, and optional constraints. The basic syntax is:

CREATE TABLE 语句是核心的 DDL 命令。它定义了表名、列名、数据类型以及可选的约束。基本语法如下：

CREATE TABLE Student ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), DateOfBirth DATE, ClassID INT );

In CCEA papers, you may be asked to write a CREATE TABLE statement based on a given entity description or an ER diagram. Always remember to include a primary key and any foreign keys if relationships are specified. Use sensible data types and lengths. For example, a product code might be CHAR(8) rather than VARCHAR(8) when the code always has a fixed length, to enforce consistency.

在 CCEA 试卷中，你可能需要根据给定的实体描述或 ER 图写出 CREATE TABLE 语句。请始终记住要包含主键，如果指定了关系，还要包括外键。使用合理的数据类型和长度。例如，如果产品代码始终是固定长度，使用 CHAR(8) 而不是 VARCHAR(8) 可以强制一致性。

4. Constraints: PRIMARY KEY, FOREIGN KEY, and More | 约束：主键、外键等

Constraints enforce rules at the database level. The key constraints you must know are:

约束在数据库级别执行规则。你必须掌握的关键约束有：

PRIMARY KEY – uniquely identifies each row; implies NOT NULL and UNIQUE.
PRIMARY KEY – 唯一标识每一行；隐含 NOT NULL 和 UNIQUE。
FOREIGN KEY – establishes a link between two tables; the values must match the primary key in the referenced table or be NULL.
FOREIGN KEY – 建立两个表之间的链接；值必须与被引用表中的主键匹配或为 NULL。
NOT NULL – ensures a column cannot have a NULL value.
NOT NULL – 确保列不能有 NULL 值。
UNIQUE – guarantees all values in a column are distinct, but allows one NULL.
UNIQUE – 保证列中的所有值都不同，但允许一个 NULL。
CHECK – validates that a column value satisfies a logical expression.
CHECK – 验证列值是否满足逻辑表达式。

When creating a table, you can define constraints inline or at the end of the column list. For example:

创建表时，可以在列定义内联或列列表末尾定义约束。例如：

CREATE TABLE Enrolment ( StudentID INT, CourseID INT, EnrolDate DATE, PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (CourseID) REFERENCES Course(CourseID) );

Composite primary keys are common in junction tables like the one above. Examiners often test your ability to choose correct foreign key columns and to understand referential integrity – you cannot delete a parent row if child rows exist unless ON DELETE CASCADE is specified.

复合主键在上例这样的连接表中很常见。考官经常测试你选择正确外键列的能力，以及对引用完整性的理解——如果子行存在，你就不能删除父行，除非指定了 ON DELETE CASCADE。

5. Modifying Tables: ALTER and DROP | 修改表：ALTER 与 DROP

The structure of an existing table can be changed using the ALTER TABLE command. Typical operations include adding a new column, modifying a column’s data type, adding or dropping a constraint, and renaming a column. For example, to add a column email to the Student table: ALTER TABLE Student ADD COLUMN Email VARCHAR(100);

可以使用 ALTER TABLE 命令更改现有表的结构。典型操作包括添加新列、修改列的数据类型、添加或删除约束以及重命名列。例如，向 Student 表添加 email 列：ALTER TABLE Student ADD COLUMN Email VARCHAR(100);。

To remove a table completely, use DROP TABLE TableName;. This deletes both the structure and all its data. The command TRUNCATE TABLE removes all rows but keeps the table structure; however, it is less commonly tested in CCEA, but you should know the difference. Be careful: DROP TABLE cannot be undone in most DBMS unless a transaction is active.

要完全删除表，使用 DROP TABLE 表名;。这会删除表结构和所有数据。命令 TRUNCATE TABLE 会删除所有行但保留表结构；不过 CCEA 考试中较少考到，但你应了解其区别。注意：在大多数 DBMS 中，DROP TABLE 无法撤销，除非事务处于活动状态。

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

DML operations allow you to change the data stored in tables. INSERT INTO adds one or more rows. You can specify values for all columns (in the order they were defined) or list specific columns:

DML 操作允许你更改存储在表中的数据。INSERT INTO 可添加一行或多行。你可以为所有列指定值（按定义顺序），也可以列出特定列：

INSERT INTO Student (StudentID, FirstName, LastName, DateOfBirth) VALUES (101, 'Alice', 'Brown', '2006-04-15');

UPDATE modifies existing rows. Always include a WHERE clause to target specific rows; omitting it updates every row in the table – a catastrophic mistake examiners will mark as incorrect. Example: UPDATE Student SET ClassID = 5 WHERE StudentID = 101;

UPDATE 修改现有行。务必包含 WHERE 子句以指定目标行；省略它会更新表中的每一行——这是一个灾难性错误，考官会扣分。例如：UPDATE Student SET ClassID = 5 WHERE StudentID = 101;。

DELETE FROM removes rows. Again, you must use a WHERE clause to avoid wiping the entire table: DELETE FROM Student WHERE StudentID = 101;

DELETE FROM 删除行。同样，你必须使用 WHERE 子句，以免清空整个表：DELETE FROM Student WHERE StudentID = 101;。

Examiners often combine DML with referential integrity questions: for instance, what happens if you try to delete a student who has enrolment records? You would need to explain that the delete will fail unless CASCADE is configured.

考官经常将 DML 与引用完整性问题结合起来提问：例如，如果尝试删除已有选课记录的学生，会发生什么？你需要解释，除非配置了 CASCADE，否则删除操作会失败。

7. Querying Data with SELECT | 使用 SELECT 查询数据

The SELECT statement retrieves data from one or more tables. The simplest form is SELECT * FROM TableName; but using * in exam answers is discouraged unless specifically asked; always list the required columns to show you understand the schema.

SELECT 语句从一个或多个表中检索数据。最简单的形式是 SELECT * FROM 表名;，但在考试答案中不鼓励使用 *，除非有特别要求；应始终列出所需的列，以表明你理解模式结构。

You can rename output columns using an alias with the AS keyword: SELECT FirstName AS 'First Name' FROM Student;. The column heading in the result set will then be “First Name”. Aliases are especially useful in reports and when joining tables to avoid ambiguity.

你可以使用 AS 关键字为输出列指定别名：SELECT FirstName AS 'First Name' FROM Student;。结果集中的列标题将变为 “First Name”。别名在报表和连接表以避免歧义时特别有用。

8. Filtering and Sorting: WHERE and ORDER BY | 过滤与排序：WHERE 与 ORDER BY

The WHERE clause filters rows based on a condition. Comparison operators include =, <> (or !=), <, >, <=, >=, BETWEEN, LIKE, and IN. Logical operators AND, OR, and NOT can combine conditions.

WHERE 子句根据条件过滤行。比较运算符包括 =、<>（或 !=）、<、>、<=、>=、BETWEEN、LIKE 和 IN。逻辑运算符 AND、OR 和 NOT 可以组合条件。

Pattern matching with LIKE uses the wildcard % (any sequence of characters) and _ (exactly one character). For example, WHERE LastName LIKE 'Sm%' matches ‘Smith’, ‘Smythe’, etc. To find students born after 2005: WHERE DateOfBirth > '2005-12-31'.

使用 LIKE 进行模式匹配时，通配符 % 表示任意字符序列，_ 表示恰好一个字符。例如，WHERE LastName LIKE 'Sm%' 会匹配 ‘Smith’、’Smythe’ 等。要查找 2005 年后出生的学生：WHERE DateOfBirth > '2005-12-31'。

The ORDER BY clause sorts the result set. You can sort by one or more columns in ascending (ASC, default) or descending (DESC) order: SELECT * FROM Student ORDER BY LastName ASC, FirstName DESC;. Note that ORDER BY always comes after WHERE (but before LIMIT, if used).

ORDER BY 子句对结果集进行排序。你可以按一列或多列升序（ASC，默认）或降序（DESC）排序：SELECT * FROM Student ORDER BY LastName ASC, FirstName DESC;。注意 ORDER BY 总是位于 WHERE 之后（如果使用 LIMIT，则在其之前）。

9. Grouping and Aggregating: GROUP BY and HAVING | 分组与聚合：GROUP BY 与 HAVING

Aggregate functions perform calculations on a set of rows and return a single value. The standard ones are COUNT, SUM, AVG, MIN, and MAX. For example, SELECT COUNT(*) FROM Student; returns the number of students. COUNT(*) counts all rows, while COUNT(column) ignores NULLs.

聚合函数对一组行执行计算并返回单个值。标准函数有 COUNT、SUM、AVG、MIN 和 MAX。例如，SELECT COUNT(*) FROM Student; 返回学生人数。COUNT(*) 计算所有行，而 COUNT(column) 忽略 NULL。

The GROUP BY clause groups rows that have the same values in specified columns, often used with aggregate functions. For instance, to find the number of students in each class: SELECT ClassID, COUNT(*) AS NumStudents FROM Student GROUP BY ClassID;

GROUP BY 子句将指定列中具有相同值的行分成一组，常与聚合函数一起使用。例如，查找每个班级的学生人数：SELECT ClassID, COUNT(*) AS NumStudents FROM Student GROUP BY ClassID;。

To filter groups, use HAVING, which is like WHERE but for aggregated data. HAVING COUNT(*) > 20 would show only classes with more than 20 students. Remember: WHERE filters rows before grouping; HAVING filters groups after aggregation. A common exam mistake is trying to use an aggregate function inside a WHERE clause.

要过滤分组，请使用 HAVING，它类似于 WHERE 但用于聚合数据。HAVING COUNT(*) > 20 将仅显示学生人数超过 20 的班级。请记住：WHERE 在分组前过滤行；HAVING 在聚合后过滤分组。一个常见的考试错误是试图在 WHERE 子句中使用聚合函数。

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

Relational databases store data in multiple linked tables; joins combine rows from two or more tables based on a related column. The most common is the INNER JOIN, which returns only rows where there is a match in both tables. Syntax:

关系数据库将数据存储在多个关联的表中；连接基于相关列将两个或多个表中的行组合在一起。最常见的连接是 INNER JOIN，它仅返回两个表中匹配的行。语法：

SELECT Student.FirstName, Class.ClassName FROM Student INNER JOIN Class ON Student.ClassID = Class.ClassID;

If you need all rows from the left table regardless of matches, use LEFT JOIN (or LEFT OUTER JOIN). Right join and full outer join are less frequently tested but you should be aware they exist. In CCEA, you must be able to write an SQL query that joins two or even three tables, selecting appropriate columns and using aliases for clarity.

如果你需要左表中的所有行，无论是否匹配，请使用 LEFT JOIN（或 LEFT OUTER JOIN）。右连接和全外连接考得较少，但你应该知道它们的存在。在 CCEA 中，你必须能够编写连接两个甚至三个表的 SQL 查询，选择合适的列，并使用别名以提高清晰度。

Note that older SQL syntax uses a comma-separated list of tables with the join condition in WHERE, e.g., FROM Student, Class WHERE Student.ClassID = Class.ClassID. This implicit join is equivalent to an INNER JOIN, but explicit JOIN notation is now preferred and expected in exam answers.

请注意，较旧的 SQL 语法在 WHERE 子句中使用逗号分隔的表列表和连接条件，例如 FROM Student, Class WHERE Student.ClassID = Class.ClassID。这种隐式连接等同于 INNER JOIN，但现在更推荐使用显式的 JOIN 表示法，这也是考试答案所期望的。

11. Subqueries | 子查询

A subquery is a SELECT statement nested inside another query. It can appear in the WHERE clause (as a filtering condition), the FROM clause (as a derived table), or the SELECT clause (as a scalar value). The most frequent exam scenario uses a subquery with IN, EXISTS, or comparison operators. Example: find students who achieved a grade higher than the average:

子查询是嵌套在另一个查询中的 SELECT 语句。它可以出现在 WHERE 子句中（作为过滤条件）、FROM 子句中（作为派生表）或 SELECT 子句中（作为标量值）。最常见的考试场景是在 IN、EXISTS 或比较运算符中使用子查询。例如：查找成绩高于平均水平的学生：

SELECT StudentID, Score FROM Result WHERE Score > (SELECT AVG(Score) FROM Result);

Correlated subqueries refer to columns from the outer query and are re-evaluated for each row. For instance, finding students who have taken all required courses might involve a correlated NOT EXISTS subquery. CCEA questions may ask you to explain why a subquery is necessary and to rewrite a query using a join as an alternative, or vice versa.

关联子查询引用外部查询的列，并对每一行重新求值。例如，查找已修完所有必修课程的学生可能会涉及关联的 NOT EXISTS 子查询。CCEA 题目可能会要求你解释为什么需要使用子查询，并让你用连接重写该查询作为替代方案，反之亦然。

12. Managing Transactions: COMMIT and ROLLBACK | 管理事务：COMMIT 与 ROLLBACK

A transaction is a sequence of database operations that must succeed or fail as a single atomic unit. SQL provides COMMIT to permanently save changes made during the transaction, and ROLLBACK to undo them and return the database to its previous consistent state. The ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable transaction processing.

事务是一系列数据库操作，必须作为一个原子单元全部成功或全部失败。SQL 提供 COMMIT 来永久保存事务中所做的更改，提供 ROLLBACK 来撤销这些更改并将数据库恢复到之前的一致状态。ACID 属性（原子性、一致性、隔离性、持久性）确保了可靠的事务处理。

In the CCEA specification, you need to understand when to use COMMIT and ROLLBACK. For example, when transferring funds between two accounts, you would deduct from one account and add to another. If the second update fails, a ROLLBACK must be issued to revert the first deduction; otherwise, the data becomes inconsistent. You should also be aware that some statements (like CREATE TABLE) often cause an implicit commit in many DBMS.

在 CCEA 考纲中，你需要理解何时使用 COMMIT 和 ROLLBACK。例如，在两个账户之间转账时，你会从一个账户扣款，然后给另一个账户加款。如果第二个更新失败，必须发出 ROLLBACK 来撤销第一次扣款；否则数据会变得不一致。你还应知道，某些语句（如 CREATE TABLE）在许多 DBMS 中通常会导致隐式提交。

Exam answers might require you to describe a scenario where transaction control preserves data integrity, or to write a short script with explicit COMMIT and ROLLBACK around DML operations. Indexes, while not directly a SQL command in CCEA focus, can be mentioned in the context of performance – they speed up searches but slow down data modification, and are created with CREATE INDEX ... ON ....

考试答案可能要求你描述一个事务控制保持数据完整性的场景，或者编写一个简短的脚本，在 DML 操作周围显式使用 COMMIT 和 ROLLBACK。索引虽然不直接是 CCEA 重点的 SQL 命令，但可以在性能上下文中提及——它们加快搜索速度但会减慢数据修改，可以使用 CREATE INDEX ... ON ... 创建。

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

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

A-Level CCEA Computer Science: SQL Exam Essentials | A-Level CCEA 计算机：SQL 考点精讲