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

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

SQL is the backbone of working with relational databases, and for AQA A-Level Computer Science it is a skill you must demonstrate with confidence. This article walks through the essential SQL concepts, from defining tables and inserting rows to building complex queries with joins, aggregations and subqueries, all aligned to the AQA 7517 specification. Expect clear examples, exam‑ready syntax, and paired bilingual explanations to support your revision.

SQL 是操作关系数据库的核心语言，也是 AQA A-Level 计算机科学中必须熟练掌握的关键技能。本文梳理 SQL 的核心考点，从定义表、插入数据到构建包含连接、聚合和子查询的复杂查询，完全对接 AQA 7517 考试大纲。每个知识点都配有清晰示例和考试认可的语法，并用中英双语对解释，帮助你高效备考。

1. SQL in the AQA Specification | AQA 大纲中的 SQL

The AQA A-Level Computer Science specification expects you to understand and use SQL to define, manipulate and query relational databases. You need to be able to write DDL statements for creating and altering table structures, DML statements for inserting, updating and deleting data, and complex SELECT queries that retrieve exactly the information required. The emphasis is on practical application, so exam questions often present a table structure and ask you to write SQL or predict the output of a given query.

AQA A-Level 计算机科学大纲要求你理解并使用 SQL 定义、操作和查询关系数据库。你需要能够编写创建和修改表结构的 DDL 语句，插入、更新和删除数据的 DML 语句，以及能够精确检索所需信息的复杂 SELECT 查询。考试侧重于实际应用，因此题目经常给出一组表结构，要求你写出 SQL 语句或预测所给查询的输出。

2. Data Definition Language (DDL) | 数据定义语言 (DDL)

DDL commands allow you to define and modify the schema of a database. The key statements you must master for the exam are CREATE TABLE, ALTER TABLE and DROP TABLE. When creating a table, you specify column names, data types (such as INTEGER, VARCHAR, DATE, BOOLEAN) and any constraints like PRIMARY KEY, FOREIGN KEY, NOT NULL and UNIQUE.

DDL 命令用来定义和修改数据库模式。考试中必须掌握的关键语句是 CREATE TABLE、ALTER TABLE 和 DROP TABLE。创建表时，需要指定列名、数据类型（如 INTEGER、VARCHAR、DATE、BOOLEAN）以及 PRIMARY KEY、FOREIGN KEY、NOT NULL、UNIQUE 等约束。

Example: creating a Student table with a primary key and a foreign key referencing a Class table.

示例：创建一个 Student 表，包含主键和引用 Class 表的外键。

CREATE TABLE Student (
  StudentID INTEGER PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  DateOfBirth DATE,
  ClassID INTEGER,
  FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
);

ALTER TABLE lets you add, modify or drop columns after creation. For instance, adding an email column:

ALTER TABLE 允许在创建后添加、修改或删除列。例如，添加 email 列：

ALTER TABLE Student ADD Email VARCHAR(100);

DROP TABLE permanently removes a table and its data, so use it carefully. Knowing these DDL statements is essential for database setup questions.

DROP TABLE 会永久删除表及其数据，因此使用时需谨慎。掌握这些 DDL 语句对回答数据库建立类题目至关重要。

3. Data Manipulation Language (DML) | 数据操作语言 (DML)

DML is used to work with the actual records inside tables. The three main statements are INSERT INTO to add new rows, UPDATE to modify existing rows, and DELETE to remove rows. In each case you must pay close attention to the WHERE clause to avoid unintentionally affecting all rows.

DML 用于操作表中的记录。三个主要的语句是：INSERT INTO 添加新行，UPDATE 修改现有行，DELETE 删除行。每种操作都需要特别注意 WHERE 子句，以避免意外影响所有行。

Insert example – adding a new student:

插入示例 – 添加一名新学生：

INSERT INTO Student (StudentID, FirstName, LastName, DateOfBirth, ClassID)
VALUES (101, ‘Alice’, ‘Smith’, ‘2006-05-12’, 3);

Update example – changing a student’s class:

更新示例 – 更改学生班级：

UPDATE Student
SET ClassID = 4
WHERE StudentID = 101;

Delete example – removing a student:

删除示例 – 删除一名学生：

DELETE FROM Student
WHERE StudentID = 101;

Be ready to write DML statements under exam conditions, and always think about referential integrity – deleting a row that is referenced by a foreign key may cause an error unless ON DELETE CASCADE is specified.

请准备好应对考试中的 DML 语句书写题，并始终考虑参照完整性 – 删除被外键引用的行可能会导致错误，除非指定了 ON DELETE CASCADE。

4. SELECT Queries and the FROM Clause | SELECT 查询与 FROM 子句

The most heavily tested part of SQL is undoubtedly the SELECT statement. At its simplest, it retrieves columns from one or more tables. The FROM clause specifies which table(s) to read. You can list multiple columns separated by commas, or use * to return all columns.

SQL 中考查最多的部分无疑是 SELECT 语句。其最基本的功能是从一个或多个表中检索列。FROM 子句指定从哪些表中读取数据。可以列出多个列（用逗号分隔），也可以使用 * 返回所有列。

Example: get first names and last names of all students.

示例：查询所有学生的名字和姓氏。

SELECT FirstName, LastName
FROM Student;

To make your queries more readable, you can alias column names using AS. For example:

为了让查询更具可读性，可以使用 AS 为列起别名。例如：

SELECT FirstName AS ‘First Name’, LastName AS ‘Surname’
FROM Student;

You will often combine SELECT with other clauses such as WHERE, ORDER BY and GROUP BY, so it is vital to get the basic structure right.

你常会需要将 SELECT 与 WHERE、ORDER BY、GROUP BY 等其他子句组合使用，因此掌握基本结构至关重要。

5. Filtering Rows with WHERE | 用 WHERE 过滤行

The WHERE clause filters rows that satisfy a specific condition. It can include comparison operators (=, <>, <, >, <=, >=) and logical operators (AND, OR, NOT). String values must be enclosed in single quotes.

WHERE 子句用于过滤满足特定条件的行。它可以使用比较运算符（=、<>、<、>、<=、>=）和逻辑运算符（AND、OR、NOT）。字符串值必须用单引号括起来。

Example: find students in class 3 born after 2005.

示例：查找 2005 年后出生且在 3 班的学生。

SELECT FirstName, LastName
FROM Student
WHERE ClassID = 3 AND DateOfBirth > ‘2005-01-01’;

Additional keywords that often appear with WHERE include:

以下几个常与 WHERE 一起使用的关键字：

BETWEEN for ranges: DateOfBirth BETWEEN ‘2006-01-01’ AND ‘2006-12-31’
LIKE for pattern matching (uses % as wildcard): LastName LIKE ‘S%’ (starts with S)
IN for a list of possible values: ClassID IN (1, 2, 3)
IS NULL to check for missing values

BETWEEN 用于范围：DateOfBirth BETWEEN ‘2006-01-01’ AND ‘2006-12-31’
LIKE 用于模式匹配（% 为通配符）：LastName LIKE ‘S%’（以 S 开头）
IN 用于一组可能的值：ClassID IN (1, 2, 3)
IS NULL 检查缺失值

Remember that exam questions often test the correct use of LIKE and wildcards, so be precise.

记住，考试常会测试 LIKE 和通配符的正确用法，因此要表述精准。

6. Sorting Results with ORDER BY | 用 ORDER BY 排序结果

The ORDER BY clause sorts the result set by one or more columns, either in ascending (ASC, the default) or descending (DESC) order. You can also sort by multiple columns – the result is sorted by the first column and then, for equal values, by the next.

ORDER BY 子句按一列或多列对结果集进行排序，可以使用升序（ASC，默认）或降序（DESC）。也可以按多列排序 – 结果先按第一列排序，相同值再按下一列排列。

Example: list all students ordered by class and then by last name.

示例：按班级、再按姓氏列出所有学生。

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

You may be asked to write a query that returns the top N rows. While AQA does not require a specific ‘LIMIT’ syntax (which is dialect-specific), you are expected to understand that ordering combined with a limiting clause (like FETCH FIRST or TOP in some environments) can achieve this. Always check the context of the question.

你可能会被要求编写返回前 N 行的查询。虽然 AQA 不要求特定的 ‘LIMIT’ 语法（该语法因方言而异），但你应该理解排序结合限制子句（如某些环境中的 FETCH FIRST 或 TOP）可以实现这一目的。务必根据题目的上下文来回答。

7. Joining Tables: INNER JOIN and Beyond | 连接表：INNER JOIN 及其它

When data is normalised, related information is split across multiple tables. JOINs bring them back together. The most commonly tested type is INNER JOIN, which returns only rows that have matching values in both tables. You should also know about LEFT JOIN (returns all rows from the left table, even if no match) and RIGHT JOIN.

数据在范式化后会分散在多个表中。JOIN 操作能将它们重新组合在一起。考试中最常见的是 INNER JOIN，它只返回两张表中匹配的行。你还需要了解 LEFT JOIN（返回左表所有行，即使没有匹配）和 RIGHT JOIN。

Typical AQA join scenario: retrieve each student’s class name from the Class table.

典型的 AQA 连接场景：从 Class 表中检索每位学生的班级名称。

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

Using table aliases can keep the SQL concise:

使用表别名可以让 SQL 更简洁：

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

Remember to qualify column names with the table name (or alias) if they exist in both tables to avoid ambiguity.

如果列名在两个表中都存在，请务必使用表名（或别名）限定，以避免歧义。

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

Aggregate functions perform a calculation on a set of values and return a single value. AQA expects you to know COUNT, SUM, AVG, MAX and MIN. They are often used together with GROUP BY, which groups rows sharing a common attribute.

聚合函数对一组值执行计算并返回单一值。AQA 要求你掌握 COUNT、SUM、AVG、MAX 和 MIN。它们常与 GROUP BY 配合使用，GROUP BY 按某个共同属性将行分组。

Example: count the number of students in each class.

示例：统计每班的学生人数。

SELECT ClassID, COUNT(*) AS StudentCount
FROM Student
GROUP BY ClassID;

To filter groups after aggregation, you need the HAVING clause (not WHERE). For instance, find classes with more than 25 students:

若要在聚合后对组进行过滤，需要使用 HAVING 子句（而不是 WHERE）。例如，查找学生人数超过 25 人的班级：

SELECT ClassID, COUNT(*) AS StudentCount
FROM Student
GROUP BY ClassID
HAVING COUNT(*) > 25;

A common exam mistake is confusing WHERE and HAVING. WHERE filters individual rows before grouping; HAVING filters the groups after aggregation.

一个常见的考试错误是混淆 WHERE 和 HAVING。WHERE 在分组前过滤单行；HAVING 在聚合后过滤组。

9. Subqueries and Nested Queries | 子查询与嵌套查询

A subquery is a SELECT statement nested inside another query. It can appear in the SELECT list, FROM clause, or WHERE clause. Subqueries are useful when you need to use the result of one query as a condition in another. AQA tends to test subqueries that return a single value (scalar), a list, or a table, often combined with IN, EXISTS, =, or > ALL / < ALL.

子查询是嵌套在另一条查询中的 SELECT 语句，可以出现在 SELECT 列表、FROM 子句或 WHERE 子句中。当你需要将一条查询的结果用作另一条查询的条件时，子查询就非常有用。AQA 常考查返回单个值（标量）、列表或表的子查询，通常搭配 IN、EXISTS、= 或 > ALL / < ALL 使用。

Example: find students who belong to the class with the fewest students. This requires a subquery to compute the minimum count.

示例：找出属于学生人数最少的班级的学生。这需要子查询先计算出最小计数值。

SELECT FirstName, LastName
FROM Student
WHERE ClassID = (
  SELECT ClassID
  FROM Student
  GROUP BY ClassID
  ORDER BY COUNT(*) ASC
  FETCH FIRST 1 ROW ONLY
);

Note that FETCH FIRST 1 ROW ONLY is a standard SQL:2008 way to limit results; if an exam question uses a different dialect it will specify the syntax. Alternatively you could use MIN with a subquery. The concept you must show is that you can nest queries.

注意，FETCH FIRST 1 ROW ONLY 是 SQL:2008 标准的限制结果方式；如果考题使用不同的方言，会说明相应的语法。你也可以用 MIN 结合子查询。关键在于展示嵌套查询的能力。

10. Indexes for Performance | 索引与性能优化

An index is a data structure that speeds up data retrieval on a database table. For AQA, you need to understand that CREATE INDEX can dramatically improve the performance of SELECT queries that filter or sort on the indexed column, at the cost of slightly slower INSERT/UPDATE/DELETE operations and extra storage.

索引是一种数据结构，用于加速数据库表的数据检索。AQA 要求你理解，CREATE INDEX 能显著提升对索引列进行过滤或排序的 SELECT 查询的性能，但代价是 INSERT/UPDATE/DELETE 操作会略为变慢，并占用额外存储空间。

Syntax:

语法：

CREATE INDEX idx_student_lastname
ON Student (LastName);

A unique index automatically created on the primary key also enforces uniqueness. Exam questions may ask you to suggest where to place an index to speed up a given query, so always think about the columns used in WHERE, JOIN and ORDER BY.

主键上自动创建的唯一索引还会强制唯一性。考题可能要求你建议在何处创建索引以加速特定查询，因此要始终考虑 WHERE、JOIN 和 ORDER BY 中用到的列。

11. Database Security and SQL Injection | 数据库安全与 SQL 注入

AQA expects you to be aware of how SQL can be used to control access and prevent malicious attacks. GRANT and REVOKE statements manage user privileges, for example allowing a user to SELECT on a table but not DELETE.

AQA 要求你了解如何使用 SQL 控制访问并防止恶意攻击。GRANT 和 REVOKE 语句管理用户权限，例如允许用户对表执行 SELECT 但不允许 DELETE。

GRANT SELECT ON Student TO ‘user1’@’localhost’;
REVOKE DELETE ON Student FROM ‘user1’@’localhost’;

A critical topic is SQL injection – a technique where an attacker inserts malicious SQL code into an input field, often through unvalidated strings, to manipulate the database. The best defence is to use parameterised queries (prepared statements) and input validation, which ensure that user input is treated as data, not executable code.

一个重要的主题是 SQL 注入 – 攻击者通过未经验证的字符串将恶意 SQL 代码插入输入字段，从而操纵数据库的技术。最佳防御方法是使用参数化查询（预处理语句）和输入验证，确保用户输入被视为数据而非可执行代码。

You should be able to recognise a piece of vulnerable code and explain how parameterisation prevents injection. This is a recurring exam question area.

你应该能够识别易受攻击的代码，并能解释参数化如何防止注入。这是考试中反复出现的问题区域。

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

Finally, here are some practical strategies for the SQL section of your AQA Computer Science exam. First, when you see a question asking you to ‘write an SQL statement’, make sure your answer follows the correct syntax exactly – commas, semicolons, parentheses and quote types matter. Second, read the table schema carefully; many marks are lost by misreading column names or data types.

最后，为你提供一些 AQA 计算机科学考试 SQL 部分的实用策略。首先，当遇到要求你“编写一条 SQL 语句”的题目时，请确保答案的语法完全正确 – 逗号、分号、括号和引号类型都要注意。其次，仔细阅读表结构；因看错列名或数据类型而丢分的情况很常见。

Common pitfalls include:

常见误区包括：

Forgetting to add a semicolon at the end of a statement.
Using double quotes for strings instead of single quotes.
Confusing GROUP BY with ORDER BY.
Applying WHERE after GROUP BY when HAVING is required.
Missing the ON condition in a JOIN.

忘记在语句末尾添加分号。
使用双引号而非单引号表示字符串。
混淆 GROUP BY 与 ORDER BY。
在需要 HAVING 的地方在 GROUP BY 后使用 WHERE。
JOIN 中遗漏 ON 条件。

Practice writing queries by hand without an IDE – that mirrors the exam experience. Check your work by simulating the query on small, sample tables. Confidence with SQL will not only earn you marks in the database section but also deepen your understanding of data handling across the whole specification.

练习时不用 IDE，手写查询语句 – 这样能模拟考试体验。通过在小的示例表上推演查询来检查自己的答案。对 SQL 充满信心不仅能为数据库部分赢得分数，还能加深你对整个大纲中数据处理的理解。

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

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

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