📚 SQL Exam Essentials for IB & OCR | IB OCR 计算机：SQL 考点精讲
Structured Query Language (SQL) is the universal tool for defining, manipulating, and querying relational databases. Whether you are preparing for IB Computer Science (Topic 3: Databases) or OCR A-Level Computer Science (Component 02: Algorithms and programming, including SQL), a solid grasp of SQL syntax and concepts is non-negotiable. This article distils the core SQL topics that frequently appear in exam papers, from simple data retrieval to multi-table joins and aggregate functions, while aligning with the syllabus requirements of both examination boards. By working through the examples and explanations, you will build the confidence to handle any SQL-related question under timed conditions.
结构化查询语言（SQL）是定义、操作和查询关系型数据库的通用工具。无论你正在备考IB计算机科学（主题3：数据库）还是OCR A-Level计算机科学（组件02：算法与编程，包含SQL），扎实掌握SQL语法和概念都是必不可少的。本文浓缩了考试中频繁出现的核心SQL主题，从简单的数据检索到多表连接和聚合函数，同时符合两大考试局的考纲要求。通过跟随示例和解释，你将建立信心，在限时条件下从容应对任何与SQL相关的问题。
1. Introduction to SQL and Relational Databases | SQL与关系型数据库简介
Relational databases store data in tables (relations) made up of rows (tuples) and columns (attributes). Each table usually has a primary key to uniquely identify records, and tables can be linked via foreign keys. SQL is divided into two main categories: Data Definition Language (DDL) for creating and modifying the database structure, and Data Manipulation Language (DML) for retrieving and updating data. IB papers often test understanding of relational concepts together with SQL statements, while OCR requires you to write and interpret SQL in pseudo-code or exact syntax contexts.
关系型数据库将数据存储在由行（元组）和列（属性）组成的表（关系）中。每个表通常有一个主键来唯一标识记录，表可以通过外键相连接。SQL分为两大类：用于创建和修改数据库结构的数据定义语言（DDL），以及用于检索和更新数据的数据操作语言（DML）。IB试卷经常将关系概念与SQL语句一同考查，而OCR要求能在伪代码或精确语法环境中编写和解读SQL。
| Term | Description |
|---|---|
| Primary Key | Unique identifier for each row (e.g. CustomerID). |
| Foreign Key | Field that links to the primary key of another table (e.g. CustomerID in Orders table). |
| Referential Integrity | Foreign key values must match existing primary key values or be NULL. |
术语表：主键是每行的唯一标识（如 CustomerID）；外键是引用了另一张表主键的字段（如 Orders 表中的 CustomerID）；参照完整性要求外键值必须匹配现存主键值或为空。
2. Basic SELECT Statements | 基本SELECT语句
The most fundamental SQL query retrieves columns from a table: SELECT column1, column2 FROM TableName;. To select all columns, use the asterisk wildcard: SELECT * FROM Customers;. In exams, you might be asked to write a query that extracts only specific attributes from a given table—always read the question carefully to avoid selecting unnecessary fields. OCR often presents a table structure and asks for a query to return exact columns; IB may embed this in a broader database design question.
最基本的SQL查询是从表中检索列：SELECT 列1, 列2 FROM 表名;。要选择所有列，使用星号通配符：SELECT * FROM Customers;。考试中可能会要求写出仅从给定表中提取特定属性的查询——务必仔细读题，避免选择不必要的字段。OCR经常展示一个表结构并要求返回精确列的查询；IB可能将其嵌入更广泛的数据库设计问题中。
3. Filtering Data with WHERE | 使用WHERE过滤数据
The WHERE clause allows you to retrieve only rows that satisfy a specified condition. Comparison operators such as =, <> (not equal), >, <, >=, <= can be used, along with logical operators AND, OR, NOT. For text data, use single quotes around string literals: SELECT * FROM Products WHERE Price > 100 AND Category = ‘Electronics’;. The LIKE operator is convenient for pattern matching with % (any sequence of characters) and _ (a single character). Exam tips: when filtering by a foreign key value, ensure correct handling of data types; if the field is numeric, do not quote the value.
WHERE子句让你能仅检索满足指定条件的行。可以使用比较运算符如 =、<>（不等于）、>、<、>=、<=，以及逻辑运算符 AND, OR, NOT。对于文本数据，在字符串字面量两侧使用单引号：SELECT * FROM Products WHERE Price > 100 AND Category = ‘Electronics’;。LIKE运算符便于模式匹配，其中 % 表示任意字符序列，_ 表示单个字符。考试提示：在按外键值过滤时，确保正确处理数据类型；如果字段是数值型，不要用引号括起值。
4. Sorting Results with ORDER BY | 使用ORDER BY排序结果
To control the order of output rows, add an ORDER BY clause: SELECT Name, Score FROM Students ORDER BY Score DESC;. The default sort order is ascending (ASC), and DESC gives descending order. You can sort by multiple columns: ORDER BY Department ASC, Salary DESC;. OCR specification explicitly mentions the use of ORDER BY in query writing. An exam question might ask you to sort customer names alphabetically by last name then first name, testing both column priority and syntax precision.
要控制输出行的顺序，添加ORDER BY子句：SELECT Name, Score FROM Students ORDER BY Score DESC;。默认排序顺序为升序（ASC），DESC 为降序。可以按多列排序：ORDER BY Department ASC, Salary DESC;。OCR考纲明确提到在查询编写中使用ORDER BY。考试题目可能要求按姓氏然后名字的字母顺序排序客户姓名，这既考察列优先级也考察语法准确性。
5. Joining Tables: INNER JOIN | 表连接：INNER JOIN
When data is normalized across multiple tables, you need a JOIN to combine rows based on a related column. The most common type is INNER JOIN, which returns only rows where the join condition matches in both tables. Syntax: SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;. Both IB and OCR expect you to match primary and foreign keys correctly. Use table aliases to simplify: FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID. Always specify the table prefix when column names are ambiguous.
当数据规范化到多个表中时，你需要JOIN来基于相关列组合行。最常见的类型是INNER JOIN，它只返回两个表中连接条件匹配的行。语法：SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;。IB和OCR都期望你能正确匹配主键和外键。使用表别名可简化：FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID。当列名不明确时，务必指定表前缀。
6. Joining Tables: LEFT/RIGHT JOIN | 表连接：LEFT/RIGHT JOIN
Sometimes you need to keep all records from one table even if there is no match in the other. A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, with matched rows from the right table; unmatched columns are filled with NULL. RIGHT JOIN does the reverse. For example, to list all customers and their orders, including those who have never placed an order: SELECT c.CustomerName, o.OrderID FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;. IB HL papers may ask you to interpret the result of a LEFT JOIN in terms of referential integrity. OCR exam style often gives a scenario where you must decide which join type to use.
有时即使另一表中没有匹配项，你仍需要保留一张表的所有记录。LEFT JOIN（或LEFT OUTER JOIN）返回左表的所有行，以及右表中匹配的行；未匹配的列用NULL填充。RIGHT JOIN则相反。例如，要列出所有客户及其订单，包括从未下过订单的客户：SELECT c.CustomerName, o.OrderID FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;。IB HL试卷可能要求从参照完整性角度解释LEFT JOIN的结果。OCR考试风格常常给出一个场景，让你决定使用哪种连接类型。
7. Aggregating Data with GROUP BY | 使用GROUP BY聚合数据
Aggregate functions – COUNT, SUM, AVG, MAX, MIN – are used together with GROUP BY to summarise data across groups. For instance, SELECT Department, AVG(Salary) FROM Employees GROUP BY Department; returns the average salary per department. When writing such queries in exams, remember that every non-aggregated column in the SELECT list must appear in the GROUP BY clause. IB and OCR both test this rule heavily, often by providing an invalid query and requiring you to identify the error.
聚合函数——COUNT、SUM、AVG、MAX、MIN——与GROUP BY一起使用以汇总各组数据。例如，SELECT Department, AVG(Salary) FROM Employees GROUP BY Department; 返回每个部门的平均工资。在考试中编写此类查询时，请记住SELECT列表中每个未聚合的列都必须出现在GROUP BY子句中。IB和OCR都大量考查此规则，通常通过提供无效查询并要求你找出错误来出题。
8. Filtering Groups with HAVING | 使用HAVING过滤分组
While WHERE filters rows before aggregation, HAVING filters groups after aggregation. Threshold conditions on aggregate values must use HAVING: SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 5; returns customers with more than 5 orders. A typical exam pitfall is using WHERE instead of HAVING on an aggregate – OCR markschemes penalise such mistakes. A query may combine WHERE and HAVING: SELECT Category, SUM(Revenue) FROM Sales WHERE Year = 2024 GROUP BY Category HAVING SUM(Revenue) > 10000;.
WHERE在聚合前过滤行，而HAVING在聚合后过滤组。对聚合值的阈值条件必须使用HAVING：SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 5; 返回订单数超过5的客户。一个典型的考试陷阱是在聚合上使用WHERE而不是HAVING——OCR评分方案对此类错误会扣分。查询可以结合WHERE和HAVING：SELECT Category, SUM(Revenue) FROM Sales WHERE Year = 2024 GROUP BY Category HAVING SUM(Revenue) > 10000;。
9. Subqueries and Nested Queries | 子查询与嵌套查询
A subquery is a SELECT statement embedded inside another SQL statement. It often appears in the WHERE clause with operators like IN, EXISTS, or comparisons. Example: SELECT Name FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = ‘London’);. Subqueries can be correlated (referring to columns from the outer query) or uncorrelated. IB HL often explores correlated subqueries for scenario-based analysis. OCR may present a nested query and ask for the output or purpose.
子查询是嵌入在另一个SQL语句中的SELECT语句。它常与IN、EXISTS等运算符或比较运算符一起出现在WHERE子句中。示例：SELECT Name FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = ‘London’);。子查询可以是相关的（引用外部查询的列）或非相关的。IB HL常为情景分析探讨相关子查询。OCR可能呈现一个嵌套查询并询问其输出或用途。
10. Data Definition Language (DDL) | 数据定义语言（DDL）
DDL is used to create, alter, and delete database objects. Key statements include:
CREATE TABLE Students (StudentID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE);
ALTER TABLE Students ADD Email VARCHAR(100);
DROP TABLE OldRecords;
When defining primary and foreign keys, you can specify them inside the CREATE TABLE statement or with an ALTER TABLE ADD CONSTRAINT. For example: CREATE TABLE Enrolments (StudentID INT, CourseID INT, PRIMARY KEY(StudentID, CourseID), FOREIGN KEY(StudentID) REFERENCES Students(StudentID));. Both syllabuses require you to write correct DDL with appropriate data types and key definitions.
DDL用于创建、修改和删除数据库对象。关键语句包括：
CREATE TABLE Students (StudentID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE);
ALTER TABLE Students ADD Email VARCHAR(100);
DROP TABLE OldRecords;
定义主键和外键时，可以在CREATE TABLE语句内部指定，也可以用ALTER TABLE ADD CONSTRAINT。例如：CREATE TABLE Enrolments (StudentID INT, CourseID INT, PRIMARY KEY(StudentID, CourseID), FOREIGN KEY(StudentID) REFERENCES Students(StudentID));。两个考纲都要求能用适当的数据类型和键定义写出正确的DDL。
11. Data Manipulation Language (DML) | 数据操作语言（DML）
DML deals with the actual data stored in tables. The main statements are INSERT INTO, UPDATE, and DELETE FROM. INSERT syntax: INSERT INTO Customers (Name, Country) VALUES (‘Acme Ltd’, ‘UK’); UPDATE syntax: UPDATE Products SET Price = Price * 1.05 WHERE Category = ‘Books’; DELETE syntax: DELETE FROM Orders WHERE OrderDate < ‘2023-01-01’; Exams often combine DML with validation or referential integrity — for instance, explaining why an INSERT might fail if a foreign key value doesn’t exist in the referenced table. Always use a WHERE clause with UPDATE and DELETE unless you intend to modify the entire table.
DML处理表中存储的实际数据。主要语句是INSERT INTO、UPDATE和DELETE FROM。INSERT语法：INSERT INTO Customers (Name, Country) VALUES (‘Acme Ltd’, ‘UK’); UPDATE语法：UPDATE Products SET Price = Price * 1.05 WHERE Category = ‘Books’; DELETE语法：DELETE FROM Orders WHERE OrderDate < ‘2023-01-01’; 考试常将DML与验证或参照完整性相结合——例如，解释如果外键值在引用表中不存在，INSERT可能失败的原因。除非打算修改整张表，否则务必在UPDATE和DELETE中使用WHERE子句。
12. Indexes and Performance Considerations | 索引与性能考量
An index is a data structure that speeds up data retrieval on a column at the cost of additional storage and slower write operations. SQL command: CREATE INDEX idx_lastname ON Employees(LastName); In exam context, you may be asked to suggest suitable columns for indexing based on query patterns — for example, foreign key columns used in JOINs or columns frequently appearing in WHERE clauses. IB touches on indexing in the context of database efficiency, while OCR relates it to computational thinking and algorithm performance. Remember that primary keys are automatically indexed.
索引是一种数据结构，能加速对某一列的数据检索，但代价是额外存储和较慢的写操作。SQL命令：CREATE INDEX idx_lastname ON Employees(LastName); 在考试情境中，可能要求根据查询模式建议适合建索引的列——例如，JOIN中使用的外键列或WHERE子句中频繁出现的列。IB在数据库效率的背景下涉及索引，而OCR将其与计算思维和算法性能相关联。请记住主键会自动创建索引。
Published by TutorHao | Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导