SQL Exam Essentials for CCEA GCSE Computer Science | CCEA GCSE 计算机：SQL 考点精讲

📚 SQL Exam Essentials for CCEA GCSE Computer Science | CCEA GCSE 计算机：SQL 考点精讲

Structured Query Language (SQL) is the standard language for managing and querying relational databases. For your CCEA GCSE Computer Science examination, you need to be able to write accurate SQL statements that create tables, insert data, retrieve specific information using filters and joins, and maintain data integrity through keys. This guide breaks down every essential command, offering clear examples and exam-focused explanations to help you answer even the trickiest database questions with confidence.

结构化查询语言 (SQL) 是用于管理和查询关系数据库的标准语言。在 CCEA 的 GCSE 计算机科学考试中，你需要准确编写 SQL 语句,包括创建数据表、插入数据、通过筛选和连接检索特定信息,以及通过键来维护数据完整性。本指南拆解了每个重要的命令,给出了清晰的示例和紧扣考点的解释,帮助你自信地应对最棘手的数据库考题。

---

1. Relational Databases and the Role of SQL | 关系数据库与 SQL 的作用

A relational database organises data into one or more tables, where each table consists of rows (records) and columns (fields). Tables are linked through common fields, reducing redundancy and improving consistency. SQL allows users to define the structure of these tables (using Data Definition Language, DDL) and to manipulate the data they contain (using Data Manipulation Language, DML).

关系数据库将数据组织到一个或多个表中,每个表由行(记录)和列(字段)组成。表之间通过公共字段相互关联,从而减少冗余、提高一致性。SQL 使用户能够定义这些表的结构(使用数据定义语言 DDL),以及操作表中所包含的数据(使用数据操作语言 DML)。

---

2. Data Definition Language: CREATE TABLE | 数据定义语言：CREATE TABLE

The CREATE TABLE command sets up a new table, specifying column names, data types, and any constraints. Common data types include VARCHAR(n) for variable-length text, INTEGER for whole numbers, DATE for dates, and BOOLEAN for true/false values. You must also declare which column acts as the primary key; this uniquely identifies each row and cannot be null.

CREATE TABLE 命令用于建立新表,需要指定列名、数据类型以及各种约束。常见的数据类型包括适用于可变长度文本的 VARCHAR(n)、整数的 INTEGER、日期的 DATE 和布尔值的 BOOLEAN。你还必须声明哪一列作为主键；主键能够唯一标识每一行且不能为空。

A typical CREATE TABLE statement looks like this:

典型的 CREATE TABLE 语句如下所示：

CREATE TABLE Student (
StudentID INTEGER PRIMARY KEY,
FirstName VARCHAR(30),
LastName VARCHAR(30),
DateOfBirth DATE
);

Always remember to end the statement with a semicolon. In the exam, you might be asked to choose suitable data types or to write the full table definition from a given description.

请务必以分号结束语句。在考试中,你可能会被要求选择合适的数据类型,或者根据给定的描述写出完整的表定义。

---

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

Tables are not set in stone. The ALTER TABLE command can add a new column, modify an existing column’s data type, or add a constraint. For example, to add an ‘Email’ column to the Student table you would write:

表的结构并非一成不变。ALTER TABLE 命令可以添加新列、修改现有列的数据类型或添加约束。例如,要向 Student 表添加一个 ‘Email’ 列,你可以这样写：

ALTER TABLE Student
ADD Email VARCHAR(50);

To remove a column (if supported by the database system) you could use DROP COLUMN:

若要删除某列(若数据库系统支持),可以使用 DROP COLUMN：

ALTER TABLE Student
DROP COLUMN Email;

The DROP TABLE command permanently deletes an entire table and all its data. Use it carefully, as the action cannot be undone: DROP TABLE Student;

DROP TABLE 命令会永久删除整个表及其所有数据。请谨慎使用,因为该操作无法撤消：DROP TABLE Student;

---

4. Data Manipulation: INSERT, UPDATE, DELETE | 数据操作：INSERT、UPDATE、DELETE

Once tables exist, you need to populate them with data using INSERT INTO. Specify the table name, the columns you are filling, and the corresponding values. String and date values must be enclosed in single quotes.

表创建之后,你需要使用 INSERT INTO 向其填充数据。你需要指定表名、要填充的列以及相应的值。字符串和日期值必须用单引号括起来。

INSERT INTO Student (StudentID, FirstName, LastName, DateOfBirth)
VALUES (101, ‘Aoife’, ‘Murphy’, ‘2008-05-14’);

To change existing data, use UPDATE with SET to specify new values and WHERE to target the correct row. Omitting WHERE updates every row — a common exam pitfall.

要修改现有数据,需要使用 UPDATE 搭配 SET 来指定新值,并用 WHERE 定位到正确的行。遗漏 WHERE 会更新每一行——这是考试中常见的陷阱。

UPDATE Student
SET LastName = ‘O’Brien’
WHERE StudentID = 101;

The DELETE FROM statement removes rows. Again, always include a WHERE clause unless you intend to delete all records:

DELETE FROM 语句用于删除行。同样,除非你打算删除所有记录,否则务必加上 WHERE 子句：

DELETE FROM Student
WHERE StudentID = 101;

---

5. Basic Queries: SELECT and FROM | 基本查询：SELECT 与 FROM

The SELECT command retrieves data from a database. The simplest form extracts all columns using the asterisk wildcard: SELECT * FROM Student; However, for better control and efficiency, you should list specific column names separated by commas.

SELECT 命令用于从数据库中检索数据。最简单的形式是使用星号通配符提取所有列：SELECT * FROM Student; 然而,为了更好地控制和提高效率,你应该列出具体的列名,并用逗号分隔。

To fetch only first names and dates of birth, the query would be:

若要只提取名字和出生日期,查询语句如下：

SELECT FirstName, DateOfBirth
FROM Student;

In CCEA exam questions, you are often provided with a table structure and asked to write a query that returns specified fields. Always double-check the column names given in the question.

在 CCEA 的考题中,通常会给出一个表结构,然后要求你编写返回指定字段的查询。请务必再检查题目中给出的列名。

---

6. Filtering with WHERE and Comparison Operators | 使用 WHERE 和比较运算符进行筛选

To narrow down results, add a WHERE clause followed by a condition. SQL supports the comparison operators =, <>, <, >, <=, and >=. Logical operators AND, OR, and NOT can combine multiple conditions.

要缩小结果范围,可以添加 WHERE 子句并附上条件。SQL 支持 =、<>、<、>、<= 和 >= 等比较运算符。使用逻辑运算符 AND、OR 和 NOT 可以组合多个条件。

Find all students born after 1 January 2008 whose first name is ‘Sean’:

找出所有出生于 2008 年 1 月 1 日之后且名字为 ‘Sean’ 的学生：

SELECT * FROM Student
WHERE DateOfBirth > ‘2008-01-01’
AND FirstName = ‘Sean’;

The BETWEEN operator is useful for checking a range of values, and IN checks if a value matches any item in a list:

BETWEEN 运算符适用于检查值的范围,而 IN 用于检查某个值是否与列表中的任何一项匹配：

SELECT * FROM Student
WHERE StudentID IN (101, 105, 110);

---

7. Pattern Matching: LIKE and Wildcards | 模式匹配：LIKE 与通配符

When you do not need an exact match, LIKE works with two wildcard characters: the percent sign % represents zero, one, or multiple characters, while the underscore _ represents exactly one character. This is invaluable for searching surnames that begin with ‘O’ or contain ‘Mac’.

当你不需要精确匹配时,可以使用 LIKE 和两个通配符：百分号 % 表示零个、一个或多个字符,而下划线 _ 代表恰好一个字符。这对于搜索以 ‘O’ 开头或包含 ‘Mac’ 的姓氏非常有用。

Select all students whose last name starts with ‘O’:

选择所有姓氏以 ‘O’ 开头的学生：

SELECT * FROM Student
WHERE LastName LIKE ‘O%’;

Find students whose first name has exactly four letters and ends with ‘an’:

查找名字恰好由四个字母组成且以 ‘an’ 结尾的学生：

SELECT * FROM Student
WHERE FirstName LIKE ‘__an’;

Always use single quotes around the pattern. Make sure you can distinguish between the % and _ wildcards for the exam.

请务必用单引号将模式括起来。确保在考试中能够区分 % 和 _ 这两个通配符。

---

8. Sorting Results with ORDER BY | 使用 ORDER BY 对结果进行排序

The ORDER BY clause sorts the retrieved rows by one or more columns. By default, sorting is ascending (ASC), but you can specify DESC for descending order. Sorting can be applied to text columns alphabetically or to numeric and date columns.

ORDER BY 子句可按照一个或多个列对检索到的行进行排序。默认情况下,排序为升序(ASC),但你也可以指定 DESC 进行降序排序。排序既可以按字母顺序应用于文本列,也可以应用于数字列和日期列。

To list students from oldest to youngest, and then alphabetically by last name for those born on the same day:

按年龄从大到小列出学生,对于同一天出生的学生,再按姓氏字母顺序排列：

SELECT FirstName, LastName, DateOfBirth
FROM Student
ORDER BY DateOfBirth ASC, LastName ASC;

If you want the most recent date first, use ORDER BY DateOfBirth DESC;. This is a common requirement in reporting tasks.

如果想要最近的日期排在前面,可以使用 ORDER BY DateOfBirth DESC;。这是报表任务中的常见要求。

---

9. Aggregate Functions and GROUP BY | 聚合函数与 GROUP BY

SQL provides built-in functions to perform calculations on a set of values. The five key aggregate functions are COUNT, SUM, AVG, MAX, and MIN. They are often used together with GROUP BY, which groups rows that have the same values in specified columns.

SQL 提供了内置函数,用于对一组值执行计算。五个关键的聚合函数是 COUNT、SUM、AVG、MAX 和 MIN。它们通常与 GROUP BY 一起使用,后者按指定列中相同的值对行进行分组。

If you have a Bookings table with columns BookingID, StudentID and Cost, you could find the total cost per student:

假如有一个 Bookings 表,包含 BookingID、StudentID 和 Cost 列,你可以找出每位学生预订的总费用：

SELECT StudentID, SUM(Cost) AS TotalSpent
FROM Bookings
GROUP BY StudentID;

Use the HAVING clause to filter groups after aggregation, because WHERE filters rows before grouping. For instance, to show only students whose total spend exceeds £100, you would add HAVING SUM(Cost) > 100;

使用 HAVING 子句可以在聚合之后对分组进行筛选,因为 WHERE 会在分组之前先对行进行筛选。例如,要只显示总消费超过 100 英镑的学生,可以添加 HAVING SUM(Cost) > 100;

---

10. Eliminating Duplicates with DISTINCT | 使用 DISTINCT 消除重复值

When a column contains repeated values, SELECT DISTINCT returns only unique instances. This is especially helpful when you need a list of all the different subjects offered by a school from an Enrolment table, without seeing each subject listed multiple times.

当列包含重复值时,SELECT DISTINCT 只返回唯一的值。当你需要从 Enrolment 表中获取某学校提供的所有不同科目列表,而不希望看到每门科目被多次列出时,这一点尤其有用。

SELECT DISTINCT Subject
FROM Enrolment;

You can apply DISTINCT to multiple columns; the database will then return unique combinations of those columns. For CCEA GCSE, you may be asked to write a query that avoids listing the same town or category twice.

你可以将 DISTINCT 应用于多个列；此时数据库将返回这些列的唯一组合。在 CCEA 的 GCSE 考试中,你可能会被要求编写一个查询,避免将同一个城镇或类别列出两次。

---

11. Joining Tables with INNER JOIN | 使用 INNER JOIN 连接表

Data is usually spread across several related tables to avoid duplication. An INNER JOIN combines rows from two tables based on a matching condition, typically where a foreign key in one table references the primary key of another. Only rows that satisfy the condition are included in the result.

数据通常会分散在几个相互关联的表中以避免重复。INNER JOIN 根据匹配条件将两个表中的行组合起来,通常是一张表中的外键引用另一张表的主键。只有满足条件的行才会包含在结果中。

Consider a Library database with Book (BookID, Title, AuthorID) and Author (AuthorID, Name). To list every book with its author’s name:

设想一个图书馆数据库,包含 Book (BookID、Title、AuthorID) 和 Author (AuthorID、Name) 两张表。要列出每本书及其作者姓名：

SELECT Book.Title, Author.Name
FROM Book
INNER JOIN Author ON Book.AuthorID = Author.AuthorID;

If the exam provides a schema diagram, identify which columns link the tables. Always use the tableName.columnName notation when columns have the same name in both tables.

如果考试提供了模式图,要确定哪些列连接了表。当两表中有同名的列时,请始终使用 表名.列名 的表示法。

---

12. Primary Keys, Foreign Keys and Referential Integrity | 主键、外键与参照完整性

A primary key is a column (or combination of columns) that uniquely identifies each record. A foreign key is a column in one table that matches the primary key of another table, creating a relationship. Together, these keys enforce referential integrity, ensuring that data across tables remains consistent.

主键是唯一标识每条记录的一列(或多列的组合)。外键是一个表中的列,它与另一个表的主键相匹配,从而建立起关系。这些键共同执行参照完整性,确保跨表数据保持一致。

When defining a table, you can add a foreign key constraint explicitly. For instance, in a Booking table that links to the Student table:

在定义表时,你可以显式添加外键约束。例如,在链接到 Student 表的 Booking 表中：

CREATE TABLE Booking (
BookingID INTEGER PRIMARY KEY,
StudentID INTEGER,
TripDate DATE,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

This constraint prevents the insertion of a Booking with a StudentID that does not exist in the Student table, and it stops the deletion of a Student who still has bookings. Expect exam questions that ask you to explain why primary and foreign keys are necessary in a database system.

此约束会阻止插入包含不在 Student 表中的 StudentID 的 Booking 记录,也会阻止删除仍有预订记录的学生。考试中可能会出现要求你解释为什么在数据库系统中主键和外键是必不可少的问题。

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

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