SQL Exam Essentials for IB & CCEA Computer Science | IB CCEA 计算机 SQL 考点精讲

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

Structured Query Language (SQL) is the cornerstone of modern database management and a key topic in both IB and CCEA Computer Science syllabi. This article distills the core SQL concepts, commands, and exam techniques you need to master, from basic queries to complex joins and subqueries, ensuring you can confidently handle any database-related question.

结构化查询语言（SQL）是现代数据库管理的基石，也是 IB 和 CCEA 计算机科学课程中的核心考点。本文提炼了你需要掌握的核心 SQL 概念、命令和考试技巧，从基础查询到复杂连接与子查询，确保你能自信地应对任何数据库相关问题。

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

A relational database stores data in tables (relations) composed of rows (records) and columns (attributes). Each table usually has a primary key that uniquely identifies each row, and relationships between tables are established via foreign keys. SQL is the standard language used to define, manipulate, and query this data, divided into Data Definition Language (DDL) for structure and Data Manipulation Language (DML) for data content.

关系数据库将数据存储在由行（记录）和列（属性）组成的表（关系）中。每个表通常有一个唯一标识每行数据的主键，表之间的关系通过外键建立。SQL 是用于定义、操作和查询这些数据的标准语言，分为用于结构的数据定义语言（DDL）和用于数据内容的数据操作语言（DML）。

2. Data Definition Language: CREATE, ALTER, DROP | 数据定义语言：CREATE、ALTER、DROP

DDL commands let you define and modify the database schema. You use CREATE TABLE to build new relations, specifying column names, data types, and constraints. ALTER TABLE adds, modifies, or deletes columns and constraints in an existing table, while DROP TABLE permanently removes the table and all its data. Always be cautious with DROP – there is no undo.

DDL 命令用于定义和修改数据库模式。你使用 CREATE TABLE 创建新关系，需指定列名、数据类型和约束。ALTER TABLE 可以对现有表添加、修改或删除列和约束，而 DROP TABLE 会永久删除表及其所有数据。使用 DROP 时务必谨慎——此操作无法撤销。

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

ALTER TABLE Student ADD Email VARCHAR(100);
DROP TABLE Student;

3. Data Manipulation Language: INSERT, UPDATE, DELETE | 数据操作语言：INSERT、UPDATE、DELETE

DML deals with the data inside tables. INSERT INTO adds new rows; you can specify values for all columns or only a subset. UPDATE modifies existing rows based on a condition – without a WHERE clause, all rows will be updated, which is a common exam pitfall. DELETE FROM removes rows, again requiring a carefully placed WHERE clause to avoid wiping out the entire table.

DML 处理表中的数据。INSERT INTO 添加新行；你可以为所有列或仅部分列指定值。UPDATE 根据条件修改现有行——如果没有 WHERE 子句，所有行都将被更新，这是考试中常见的陷阱。DELETE FROM 删除行，同样需要小心放置 WHERE 子句，以免清空整个表。

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

UPDATE Student SET Email = 'alice.b@school.edu'
WHERE StudentID = 101;

DELETE FROM Student WHERE StudentID = 101;

4. The SELECT Statement: Retrieving Data | SELECT 语句：检索数据

The SELECT statement is the heart of SQL querying. It retrieves columns from one or more tables. Using SELECT * returns all columns, while SELECT column1, column2 projects only those attributes. The keyword DISTINCT eliminates duplicate rows, which is frequently tested in scenarios where you need unique values, such as listing all distinct cities from a customer table.

SELECT 语句是 SQL 查询的核心。它从一个或多个表中检索列。使用 SELECT * 返回所有列，而 SELECT column1, column2 仅投影这些属性。关键字 DISTINCT 用于消除重复行，在需要唯一值的场景中经常被考查，例如列出客户表中所有不重复的城市。

SELECT FirstName, LastName FROM Student;
SELECT DISTINCT City FROM Customer;

5. Filtering with WHERE, Logical Operators, and Special Predicates | 使用 WHERE、逻辑运算符与特殊谓词进行筛选

The WHERE clause filters rows based on a condition. You can combine conditions using AND, OR, and NOT. Exams love to test BETWEEN for inclusive ranges, IN to match a list of values, LIKE for pattern matching (with % for any sequence and _ for a single character), and IS NULL to find missing values. Remember that NULL cannot be tested with =.

WHERE 子句根据条件筛选行。你可以使用 AND、OR 和 NOT 组合条件。考试喜欢考查 BETWEEN（包含范围）、IN（匹配值列表）、LIKE（模式匹配，% 表示任意序列，_ 表示单个字符）以及 IS NULL（查找缺失值）。请记住，NULL 不能用 = 来测试。

SELECT * FROM Product
WHERE Price BETWEEN 10.00 AND 50.00
  AND Category IN ('Electronics', 'Books')
  AND ProductName LIKE 'A%';

6. Sorting and Aggregation: ORDER BY, GROUP BY, HAVING | 排序与聚合：ORDER BY、GROUP BY、HAVING

ORDER BY sorts the result set by one or more columns, ascending (ASC) by default. Aggregate functions like COUNT, SUM, AVG, MAX, and MIN are used with GROUP BY to summarise data. The HAVING clause filters groups after aggregation, similar to how WHERE filters rows before grouping. A classic exam question asks for the number of orders per customer but only showing those with more than two orders.

ORDER BY 按一列或多列对结果集进行排序，默认为升序（ASC）。聚合函数如 COUNT、SUM、AVG、MAX 和 MIN 与 GROUP BY 结合用于汇总数据。HAVING 子句在聚合后筛选分组，犹如 WHERE 在分组前筛选行。一个经典的考题是查询每个客户的订单数量，但只显示订购超过两次的客户。

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Order
GROUP BY CustomerID
HAVING COUNT(OrderID) > 2
ORDER BY OrderCount DESC;

7. SQL Joins: Combining Tables | SQL 连接：合并表

Joins are used to retrieve data from two or more related tables based on a common column. An INNER JOIN returns only rows with matching values in both tables. LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matched rows from the right, filling unmatched right columns with NULL. RIGHT JOIN and FULL JOIN behave similarly. The ON clause specifies the join condition.

连接用于基于公共列从两个或多个相关表中检索数据。INNER JOIN 只返回两个表中匹配的行。LEFT JOIN（或 LEFT OUTER JOIN）返回左表的所有行以及右表中匹配的行，未匹配的右表列补以 NULL。RIGHT JOIN 和 FULL JOIN 行为类似。ON 子句指定连接条件。

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

-- Left join to include students with no class
SELECT Student.FirstName, Class.ClassName
FROM Student
LEFT JOIN Class ON Student.ClassID = Class.ClassID;

8. Subqueries: Queries Inside Queries | 子查询：查询中的查询

A subquery is a SELECT statement nested inside another query. It can appear in WHERE, FROM, or SELECT. Single-row subqueries use operators like =, >, <, while multi-row subqueries require IN, ANY, ALL, or EXISTS. For example, to find students older than the average, you can use a subquery that computes the average date of birth.

子查询是嵌套在另一个查询中的 SELECT 语句。它可以出现在 WHERE、FROM 或 SELECT 中。单行子查询使用 =、>、< 等运算符，而多行子查询需要 IN、ANY、ALL 或 EXISTS。例如，要找出年龄大于平均年龄的学生，可以使用计算平均出生日期的子查询。

SELECT FirstName, LastName
FROM Student
WHERE DateOfBirth < (SELECT AVG(DateOfBirth) FROM Student);

-- Using EXISTS to find classes with at least one student
SELECT ClassName
FROM Class c
WHERE EXISTS (SELECT 1 FROM Student s WHERE s.ClassID = c.ClassID);

9. Primary Keys, Foreign Keys, and Referential Integrity | 主键、外键与引用完整性

A primary key is a column or set of columns that uniquely identifies each record in a table; it must be unique and not null. A foreign key is a column that references the primary key of another table, enforcing referential integrity – you cannot insert a value in the foreign key column unless it exists in the referenced primary key. Options like ON DELETE CASCADE automatically delete child rows when a parent row is deleted.

主键是一列或一组列，唯一标识表中的每条记录；它必须唯一且非空。外键是引用另一表主键的列，用于强制引用完整性——除非外键列中的值在引用主键中存在，否则无法插入。像 ON DELETE CASCADE 这样的选项可在删除父行时自动删除子行。

CREATE TABLE Order (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
    ON DELETE CASCADE
);

10. Useful SQL Functions and Exam Tips | 常用 SQL 函数与考试技巧

SQL provides built-in functions for strings (UPPER, LOWER, CONCAT, SUBSTRING), dates (CURRENT_DATE, DATEDIFF, YEAR), and numbers (ROUND, ABS, MOD). In IB and CCEA exams, you may need to format output or calculate age from a birth date. Always watch for common mistakes: forgetting the WHERE clause in UPDATE/DELETE, confusing WHERE with HAVING, and incorrect join conditions leading to Cartesian products.

SQL 提供了针对字符串（UPPER、LOWER、CONCAT、SUBSTRING）、日期（CURRENT_DATE、DATEDIFF、YEAR）和数字（ROUND、ABS、MOD）的内置函数。在 IB 和 CCEA 考试中，你可能需要格式化输出或从出生日期计算年龄。始终提防常见错误：在 UPDATE/DELETE 中忘记 WHERE 子句，混淆 WHERE 与 HAVING，以及错误的连接条件导致笛卡尔积。

SELECT CONCAT(FirstName, ' ', LastName) AS FullName,
       ROUND(Height, 2) AS HeightRounded,
       DATEDIFF(CURRENT_DATE, DateOfBirth) / 365 AS Age
FROM Student;

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

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

SQL Exam Essentials for IB & CCEA Computer Science | IB CCEA 计算机 SQL 考点精讲