Mastering SQL for IB & WJEC Computer Science | IB 与 WJEC 计算机：SQL 考点精讲

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

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. Whether you are preparing for IB Computer Science or WJEC Computer Science, a clear understanding of SQL is essential for both paper-based exams and practical assessments. This guide covers all key SQL concepts, from basic queries to joins, aggregation, and normalisation, with paired English and Chinese explanations to support bilingual learners.

结构化查询语言（SQL）是管理和操作关系数据库的标准语言。无论你正在准备 IB 计算机科学还是 WJEC 计算机科学考试，清晰掌握 SQL 对于笔试和实践评估都至关重要。本指南涵盖所有核心 SQL 概念，从基本查询到连接、聚合以及规范化，并配以中英双语解释，支持双语学习者。

1. Relational Databases and Keys | 关系数据库与键

A relational database organises data into tables (relations) consisting of rows (tuples) and columns (attributes). Each table stores data about a specific entity, and relationships are enforced through keys.

关系数据库将数据组织成由行（元组）和列（属性）组成的表（关系）。每个表存储特定实体的数据，并通过键来实施关系。

A primary key is a column or combination of columns that uniquely identifies each row. It cannot contain NULL values and must be unique. A foreign key is a column in one table that references the primary key of another table, creating a link between them.

主键是唯一标识每一行的一列或多列组合。它不能包含空值，并且必须唯一。外键是一个表中的列，它引用另一个表的主键，从而在它们之间创建链接。

For WJEC, you must also understand candidate keys, composite keys, and the role of foreign keys in enforcing referential integrity. IB often focuses on the conceptual use of primary and foreign keys in database design questions.

对于 WJEC，你还必须理解候选键、复合键以及外键在实施引用完整性中的作用。IB 则经常侧重于在数据库设计题中概念性地使用主键和外键。

Example: Students(StudentID, Name) and Enrolments(EnrolID, StudentID, CourseID)

示例：Students(StudentID, Name) 和 Enrolments(EnrolID, StudentID, CourseID)

2. Introduction to SQL and Data Types | SQL 与数据类型简介

SQL consists of several sublanguages: Data Definition Language (DDL) for defining schema, Data Manipulation Language (DML) for data operations, and Data Query Language (DQL) for retrieving data. Both IB and WJEC syllabi require knowledge of common data types.

SQL 由几个子语言组成：数据定义语言（DDL）用于定义模式，数据操作语言（DML）用于数据操作，数据查询语言（DQL）用于检索数据。IB 和 WJEC 教学大纲都要求掌握常见的数据类型知识。

Common data types include: INTEGER (whole numbers), VARCHAR(n) or TEXT (variable-length strings), CHAR(n) (fixed-length strings), DATE, FLOAT/DECIMAL (real numbers), and BOOLEAN. Choosing the correct type is important for data integrity and efficiency.

常见数据类型包括：INTEGER（整数），VARCHAR(n) 或 TEXT（可变长度字符串），CHAR(n)（固定长度字符串），DATE，FLOAT/DECIMAL（实数）以及 BOOLEAN。正确选择数据类型对于数据完整性和效率至关重要。

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

DDL statements are used to create, alter, and delete database structures. The core commands are CREATE TABLE, ALTER TABLE, and DROP TABLE.

DDL 语句用于创建、修改和删除数据库结构。核心命令是 CREATE TABLE、ALTER TABLE 和 DROP TABLE。

To create a table, you specify the column names, data types, and constraints such as PRIMARY KEY, NOT NULL, and UNIQUE. For example:

要创建表，你需要指定列名、数据类型以及约束，例如 PRIMARY KEY、NOT NULL 和 UNIQUE。示例如下：

CREATE TABLE Book ( ISBN VARCHAR(13) PRIMARY KEY, Title VARCHAR(100) NOT NULL, Author VARCHAR(80), Price DECIMAL(5,2) );

ALTER TABLE allows you to add, modify, or drop columns. ALTER TABLE Book ADD Genre VARCHAR(30); adds a new column. Dropping a table is done with DROP TABLE Book;, which removes the table and all its data permanently.

ALTER TABLE 允许你添加、修改或删除列。ALTER TABLE Book ADD Genre VARCHAR(30); 添加新列。删除表使用 DROP TABLE Book;，这会永久移除表及其所有数据。

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

DML deals with inserting, updating, and deleting data within tables. The three key statements are INSERT INTO, UPDATE, and DELETE FROM.

DML 处理表中数据的插入、更新和删除。三个关键语句是 INSERT INTO、UPDATE 和 DELETE FROM。

Insert data by specifying column names and values: INSERT INTO Book (ISBN, Title, Author, Price) VALUES ('978-3-16-148410-0', 'Database Systems', 'Smith', 49.99);

通过指定列名和值来插入数据：INSERT INTO Book (ISBN, Title, Author, Price) VALUES ('978-3-16-148410-0', 'Database Systems', 'Smith', 49.99);

Update existing records using a WHERE clause to target specific rows: UPDATE Book SET Price = 44.99 WHERE ISBN = '978-3-16-148410-0'; Without WHERE, all rows would be updated.

使用 WHERE 子句定位特定行来更新现有记录：UPDATE Book SET Price = 44.99 WHERE ISBN = '978-3-16-148410-0'; 如果没有 WHERE，所有行都会被更新。

Delete records with DELETE FROM Book WHERE Author = 'Smith'; Again, the WHERE clause is crucial to avoid wiping out the entire table.

使用 DELETE FROM Book WHERE Author = 'Smith'; 删除记录。同样，WHERE 子句至关重要，以避免清空整个表。

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

The SELECT statement retrieves data from one or more tables. To select all columns, use SELECT * FROM TableName;. To select specific columns, list them: SELECT Title, Price FROM Book;

SELECT 语句从一个或多个表中检索数据。选择所有列使用 SELECT * FROM TableName;。选择特定列则列出它们：SELECT Title, Price FROM Book;

The WHERE clause filters rows based on conditions. Comparison operators include =, <, >, <=, >=, <> (or !=). Logical operators AND, OR, NOT combine conditions.

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

Example: SELECT Title, Price FROM Book WHERE Price > 30 AND Author = 'Smith'; This returns titles and prices of books by Smith priced above 30.

示例：SELECT Title, Price FROM Book WHERE Price > 30 AND Author = 'Smith'; 这会返回 Smith 所著且价格高于 30 的书籍标题和价格。

Pattern matching uses LIKE with wildcards: % matches any sequence of characters, _ matches a single character. WHERE Title LIKE 'Data%' finds titles starting with “Data”. IB and WJEC both test these patterns extensively.

模式匹配使用 LIKE 和通配符：% 匹配任意字符序列，_ 匹配单个字符。WHERE Title LIKE 'Data%' 查找以 “Data” 开头的标题。IB 和 WJEC 都广泛考查这些模式。

6. Sorting and Eliminating Duplicates | 排序与去重

Results can be sorted using ORDER BY column name, with ASC (ascending, default) or DESC (descending). Multiple columns can be sorted: ORDER BY Author ASC, Price DESC.

可以使用 ORDER BY 列名对结果进行排序，ASC（升序，默认）或 DESC（降序）。可以按多列排序：ORDER BY Author ASC, Price DESC。

The DISTINCT keyword removes duplicate rows from the result set. SELECT DISTINCT Author FROM Book; returns each author name only once. This is often needed when you want unique values from a column.

DISTINCT 关键字从结果集中移除重复行。SELECT DISTINCT Author FROM Book; 只返回每位作者的名字一次。当需要列的唯一值时，通常会用到它。

In WJEC, you may be asked to combine DISTINCT with ORDER BY to list unique categories alphabetically. IB papers also feature such command of basic retrieval.

在 WJEC 中，你可能会被要求结合 DISTINCT 和 ORDER BY 以按字母顺序列出唯一类别。IB 试卷也考查这种基本检索能力。

7. Aggregate Functions and Grouping | 聚合函数与分组

SQL provides built-in aggregate functions: COUNT, SUM, AVG, MIN, and MAX. They operate on a set of values and return a single value. SELECT COUNT(*) FROM Book; counts all rows.

SQL 提供内置聚合函数：COUNT、SUM、AVG、MIN 和 MAX。它们对一组值进行操作并返回单个值。SELECT COUNT(*) FROM Book; 计算所有行数。

GROUP BY divides the rows into groups based on one or more columns, so aggregate functions can be applied to each group. For instance, SELECT Author, COUNT(*) FROM Book GROUP BY Author; gives the number of books per author.

GROUP BY 按一列或多列将行分成组，以便聚合函数可以应用于每个组。例如，SELECT Author, COUNT(*) FROM Book GROUP BY Author; 给出每位作者的书籍数量。

The HAVING clause filters groups after aggregation, similar to WHERE but for groups. SELECT Author, AVG(Price) FROM Book GROUP BY Author HAVING AVG(Price) > 40; returns authors with average book price above 40. Remember: WHERE filters before grouping, HAVING filters after.

HAVING 子句在聚合后过滤分组，类似于 WHERE 但用于组。SELECT Author, AVG(Price) FROM Book GROUP BY Author HAVING AVG(Price) > 40; 返回平均书价高于 40 的作者。请记住：WHERE 在分组前过滤，HAVING 在分组后过滤。

8. Joining Tables | 连接表

Relational data often spans multiple tables. Joins combine rows from two or more tables based on a related column. The most common is INNER JOIN, which returns rows when there is a match in both tables.

关系数据通常跨多个表。连接基于相关列组合两个或更多表中的行。最常见的是 INNER JOIN，当两个表中都存在匹配时返回行。

Syntax: SELECT columns FROM TableA INNER JOIN TableB ON TableA.key = TableB.key; Example: SELECT Student.Name, Enrolments.CourseID FROM Student INNER JOIN Enrolments ON Student.StudentID = Enrolments.StudentID;

语法：SELECT columns FROM TableA INNER JOIN TableB ON TableA.key = TableB.key; 示例：SELECT Student.Name, Enrolments.CourseID FROM Student INNER JOIN Enrolments ON Student.StudentID = Enrolments.StudentID;

LEFT JOIN returns all rows from the left table and matching rows from the right; if no match, NULLs appear. RIGHT JOIN is the opposite. WJEC expects knowledge of different join types, while IB often focuses on INNER JOIN and Left Join in context-based scenarios.

LEFT JOIN 返回左表的所有行以及右表的匹配行；如果没有匹配，则显示 NULL。RIGHT JOIN 则相反。WJEC 要求了解不同的连接类型，而 IB 通常在基于上下文的场景中侧重 INNER JOIN 和左连接。

9. Subqueries (Nested Queries) | 子查询（嵌套查询）

A subquery is a query embedded inside another query. It can be used in the WHERE clause with operators like IN, EXISTS, ANY, or ALL, or as a derived table in the FROM clause.

子查询是嵌入在另一个查询中的查询。它可以与 IN、EXISTS、ANY 或 ALL 等运算符一起用于 WHERE 子句，或作为派生表用于 FROM 子句。

Example using IN: SELECT Title FROM Book WHERE Author IN (SELECT Author FROM Bestsellers); This finds titles written by authors also listed in the Bestsellers table.

使用 IN 的示例：SELECT Title FROM Book WHERE Author IN (SELECT Author FROM Bestsellers); 这会找到由也被列入畅销书表的作者所写的书籍标题。

Subqueries can also be used with EXISTS to test for the existence of any row. Both IB and WJEC will test your ability to interpret nested queries and write them to retrieve specific information.

子查询还可以与 EXISTS 一起使用，以测试是否存在任何行。IB 和 WJEC 都会考查你解释嵌套查询以及编写它们检索特定信息的能力。

10. Database Normalisation | 数据库规范化

Normalisation is the process of organising data to reduce redundancy and improve data integrity. It involves decomposing tables into smaller, well-structured relations. WJEC explicitly tests 1NF, 2NF, and 3NF. IB may include normalisation as part of database design.

规范化是组织数据以减少冗余并提高数据完整性的过程。它涉及将表分解为更小、结构良好的关系。WJEC 明确考查 1NF、2NF 和 3NF。IB 可能将规范化作为数据库设计的一部分包括在内。

First Normal Form (1NF): All attributes contain atomic (indivisible) values; no repeating groups. 第一范式（1NF）：所有属性包含原子（不可分割）值；没有重复组。
Second Normal Form (2NF): It is in 1NF and all non-key attributes are fully functionally dependent on the entire primary key (no partial dependencies). 第二范式（2NF）：满足 1NF，并且所有非键属性完全函数依赖于整个主键（无部分依赖）。
Third Normal Form (3NF): It is in 2NF and there are no transitive dependencies (non-key attributes depend only on the primary key). 第三范式（3NF）：满足 2NF，并且没有传递依赖（非键属性仅依赖于主键）。

For exams, you may be given a denormalised table and asked to normalise it step by step, showing new tables and identifying primary and foreign keys.

在考试中，你可能会得到一个未规范化的表，并被要求逐步对其进行规范化，展示新表并标识主键和外键。

11. Indexes and Performance | 索引与性能

An index is a data structure that improves the speed of data retrieval operations on a database table. It is created on one or more columns. CREATE INDEX idx_author ON Book(Author); speeds up queries filtering by Author.

索引是一种数据结构，可提高数据库表上数据检索操作的速度。它创建在一列或多列上。CREATE INDEX idx_author ON Book(Author); 可加速按 Author 筛选的查询。

While indexes speed up SELECT queries, they slow down INSERT, UPDATE, and DELETE because the index must also be updated. Balancing this trade-off is part of database design, a concept that appears in both IB and WJEC resources.

虽然索引会加速 SELECT 查询，但它们会减慢 INSERT、UPDATE 和 DELETE 的速度，因为索引也必须更新。平衡这种权衡是数据库设计的一部分，这一概念在 IB 和 WJEC 的资料中都有出现。

12. Common Exam Pitfalls and Tips | 常见考试陷阱与技巧

Many marks are lost due to simple syntax errors or misunderstanding of the question. Always read the scenario carefully: identify the tables, columns, and required output. Use table aliases in joins to shorten code.

许多考生因简单的语法错误或对题目的误解而丢分。始终仔细阅读场景：识别表、列和所需的输出。在连接中使用表别名以缩短代码。

Common mistakes include forgetting the semicolon ;, using WHERE instead of HAVING with aggregate functions, omitting GROUP BY when mixing aggregate and non-aggregate columns, and ignoring precision of data types like DECIMAL.

常见错误包括忘记分号 ;、在聚合函数处使用 WHERE 而非 HAVING、在混合聚合列和非聚合列时遗漏 GROUP BY，以及忽略 DECIMAL 等数据类型的精度。

In WJEC papers, there are often questions asking you to write SQL statements based on given table structures. Practise writing correct DDL and DML with appropriate constraints. For IB, focus on interpreting query results and writing queries that answer a specific information need. Always test your logic by mentally running through a small dataset.

在 WJEC 试卷中，常有题目要求根据给定的表结构编写 SQL 语句。练习使用适当的约束编写正确的 DDL 和 DML。对于 IB，重点在于解释查询结果并编写能满足特定信息需求的查询。始终通过在心里运行一个小型数据集来测试你的逻辑。

Additionally, remember that SQL keywords are case-insensitive, but table and column names may be case-sensitive depending on the system. In exams, using uppercase for keywords and lower case for identifiers is a clean style.

此外，请记住 SQL 关键字不区分大小写，但表名和列名可能因系统而异。在考试中，关键字使用大写而标识符使用小写是一种清晰的风格。

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

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

Mastering SQL for IB & WJEC Computer Science | IB 与 WJEC 计算机：SQL 考点精讲