SQL Exam Essentials for IB and AQA | SQL 考点精讲

📚 SQL Exam Essentials for IB and AQA | SQL 考点精讲

Structured Query Language (SQL) is the standard language for managing and querying relational databases. Whether you are studying IB Computer Science or AQA A‑level Computer Science, a solid grasp of SQL is essential for both exam success and real‑world data handling. This revision guide covers every key SQL concept, from data definition to transaction control, with clear syntax examples and exam‑focused tips.

结构化查询语言（SQL）是管理和查询关系数据库的标准语言。无论你学的是IB计算机科学还是AQA A‑level计算机科学，扎实掌握SQL对考试成功和实际数据处理都至关重要。本复习指南涵盖从数据定义到事务控制的每个关键SQL概念，配有清晰的语法示例和考试技巧。

1. Introduction to SQL and Relational Databases | SQL与关系数据库简介

SQL is used to communicate with relational databases, where data is organised into tables (relations) made up of rows and columns. Each table has a primary key to uniquely identify each record. Relational databases minimise data redundancy and enforce integrity through relationships among tables using foreign keys.

SQL用于与关系数据库通信，关系数据库中的数据组织成由行和列组成的表（关系）。每个表都有一个主键来唯一标识每条记录。关系数据库通过使用外键在表之间建立关系，最大限度地减少数据冗余并确保完整性。

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

DDL commands define or modify the structure of database objects such as tables. The main DDL statements are CREATE, ALTER, and DROP. For example, CREATE TABLE Student (ID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE); creates a new table. ALTER TABLE Student ADD Email VARCHAR(100); adds a column, while DROP TABLE Student; deletes the entire table. In exams, you must be precise with data types and constraints like NOT NULL and UNIQUE.

DDL命令定义或修改数据库对象（例如表）的结构。主要的DDL语句是CREATE、ALTER和DROP。例如，CREATE TABLE Student (ID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE);创建新表。ALTER TABLE Student ADD Email VARCHAR(100);添加一列，而DROP TABLE Student;删除整个表。考试中必须精确使用数据类型和NOT NULL、UNIQUE等约束。

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

DML is used to manage data inside tables. The core commands are INSERT INTO, UPDATE, and DELETE. INSERT INTO Student (ID, Name) VALUES (1, 'Alex'); adds a new row. UPDATE Student SET Name = 'Alexander' WHERE ID = 1; modifies existing data. DELETE FROM Student WHERE ID = 1; removes a row. Always remember to include a WHERE clause when updating or deleting, unless you intend to affect all rows.

DML用于管理表中的数据。核心命令是INSERT INTO、UPDATE和DELETE。INSERT INTO Student (ID, Name) VALUES (1, 'Alex');添加新行。UPDATE Student SET Name = 'Alexander' WHERE ID = 1;修改现有数据。DELETE FROM Student WHERE ID = 1;删除一行。除非打算影响所有行，否则更新或删除时务必包含WHERE子句。

4. The SELECT Statement | SELECT 查询语句

The SELECT statement retrieves data from one or more tables. You can select specific columns (SELECT Name, DOB FROM Student;), all columns with * (SELECT * FROM Student;), or use DISTINCT to eliminate duplicates (SELECT DISTINCT Name FROM Student;). Understanding the order of execution is important: FROM → WHERE → SELECT → ORDER BY.

SELECT语句从一个或多个表中检索数据。可以选择特定列（SELECT Name, DOB FROM Student;），使用*选择所有列（SELECT * FROM Student;），或者使用DISTINCT消除重复（SELECT DISTINCT Name FROM Student;）。理解执行顺序很重要：FROM → WHERE → SELECT → ORDER BY。

5. Filtering with WHERE | 使用 WHERE 进行筛选

The WHERE clause applies conditions to filter rows. Comparison operators (=, <>, >, <, >=, <=) combine with logical operators AND, OR, NOT. Special operators include BETWEEN, IN, LIKE (with wildcards % and _), and IS NULL. For instance, SELECT * FROM Student WHERE DOB BETWEEN '2005-01-01' AND '2005-12-31' AND Name LIKE 'A%'; returns students born in 2005 whose names start with 'A'.

WHERE子句应用条件来筛选行。比较运算符（=、<>、>、<、>=、<=）与逻辑运算符AND、OR、NOT结合使用。特殊运算符包括BETWEEN、IN、LIKE（配合通配符%和_）以及IS NULL。例如，SELECT * FROM Student WHERE DOB BETWEEN '2005-01-01' AND '2005-12-31' AND Name LIKE 'A%';返回2005年出生且姓名以'A'开头的学生。

6. Sorting and Functions | 排序与函数

ORDER BY sorts result sets by one or more columns, in ascending (ASC, default) or descending (DESC) order. Example: SELECT Name, DOB FROM Student ORDER BY DOB DESC, Name ASC;. SQL also provides scalar functions like UPPER(), LOWER(), LENGTH(), and ROUND(). These can be used in SELECT or WHERE clauses, e.g., SELECT UPPER(Name) FROM Student;.

ORDER BY按一个或多个列对结果集排序，升序（ASC，默认）或降序（DESC）。示例：SELECT Name, DOB FROM Student ORDER BY DOB DESC, Name ASC;。SQL还提供标量函数，如UPPER()、LOWER()、LENGTH()和ROUND()。这些可以在SELECT或WHERE子句中使用，例如SELECT UPPER(Name) FROM Student;。

7. Joining Tables | 表连接

Joins combine rows from two or more tables based on a related column. INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table and matched rows from the right; unmatched right columns become NULL. RIGHT JOIN and FULL OUTER JOIN behave similarly. Syntax: SELECT Student.Name, Exam.Score FROM Student INNER JOIN Exam ON Student.ID = Exam.StudentID;. Always specify the join condition with ON.

连接根据相关列组合两个或多个表中的行。INNER JOIN仅返回匹配的行。LEFT JOIN返回左表的所有行以及右表中的匹配行；未匹配的右表列变为NULL。RIGHT JOIN和FULL OUTER JOIN类似。语法：SELECT Student.Name, Exam.Score FROM Student INNER JOIN Exam ON Student.ID = Exam.StudentID;。始终用ON指定连接条件。

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

Aggregate functions perform calculations on a set of rows: COUNT(), SUM(), AVG(), MAX(), MIN(). They are often used with GROUP BY to group rows that have the same values. For example, SELECT StudentID, COUNT(*) AS NumExams, AVG(Score) AS AvgScore FROM Exam GROUP BY StudentID;. The HAVING clause filters groups, similar to WHERE but applied after aggregation, e.g., HAVING AVG(Score) > 75;.

聚合函数对一组行执行计算：COUNT()、SUM()、AVG()、MAX()、MIN()。它们常与GROUP BY一起使用，将具有相同值的行分组。例如，SELECT StudentID, COUNT(*) AS NumExams, AVG(Score) AS AvgScore FROM Exam GROUP BY StudentID;。HAVING子句过滤分组，类似于WHERE但在聚合之后应用，例如HAVING AVG(Score) > 75;。

9. Subqueries | 子查询

A subquery is a query nested inside another query. It can appear in SELECT, FROM, or WHERE. In WHERE, subqueries often use IN, EXISTS, or comparison operators with ANY/ALL. For example, SELECT Name FROM Student WHERE ID IN (SELECT StudentID FROM Exam WHERE Score > 90);. Subqueries must be enclosed in parentheses and can return single or multiple values depending on the context.

子查询是嵌套在另一个查询中的查询。它可以出现在SELECT、FROM或WHERE中。在WHERE中，子查询常使用IN、EXISTS，或与ANY/ALL配合的比较运算符。例如，SELECT Name FROM Student WHERE ID IN (SELECT StudentID FROM Exam WHERE Score > 90);。子查询必须括在括号中，根据上下文返回单个或多个值。

10. Views and Indexes | 视图与索引

A VIEW is a virtual table based on the result of a stored query. It simplifies complex queries and adds a security layer. Syntax: CREATE VIEW HighScorers AS SELECT Name, Score FROM Student JOIN Exam ON Student.ID = Exam.StudentID WHERE Score > 80;. An INDEX speeds up data retrieval on specified columns. CREATE INDEX idx_name ON Student(Name); creates an index, but excessive indexes can slow down write operations.

VIEW是基于存储查询结果的虚拟表。它简化复杂查询并增加安全层。语法：CREATE VIEW HighScorers AS SELECT Name, Score FROM Student JOIN Exam ON Student.ID = Exam.StudentID WHERE Score > 80;。INDEX加速指定列上的数据检索。CREATE INDEX idx_name ON Student(Name);创建索引，但过多的索引会减慢写入操作。

11. Transactions and ACID | 事务与ACID特性

A TRANSACTION is a sequence of database operations treated as a single unit of work. It ensures ACID properties: Atomicity (all or nothing), Consistency (valid state before and after), Isolation (concurrent transactions do not interfere), and Durability (committed changes are permanent). In SQL, BEGIN TRANSACTION, COMMIT, and ROLLBACK control transactions. For example, transferring money between accounts must be wrapped in a transaction to avoid partial updates.

TRANSACTION（事务）是被视为单一工作单元的一系列数据库操作。它确保ACID特性：原子性（全有或全无）、一致性（前后状态有效）、隔离性（并发事务不干扰）和持久性（已提交的更改是永久的）。在SQL中，BEGIN TRANSACTION、COMMIT和ROLLBACK控制事务。例如，账户间转账必须包装在事务中，以避免部分更新。

12. Exam Tips and Common Pitfalls | 考试技巧与常见错误

In written exams, always use correct syntax: semicolons at the end of statements, proper quoting for strings (single quotes), and careful indentation for readability. Common mistakes include forgetting the WHERE clause in UPDATE/DELETE, missing join conditions, confusing WHERE with HAVING, and placing aggregate functions in WHERE. Practice writing queries by hand and double‑check the order of keywords. For IB Paper 2 or AQA practical tasks, always test edge cases such as NULL values and empty result sets.

在笔试中，始终使用正确的语法：语句末尾加分号，字符串使用正确的引号（单引号），并适当缩进以提高可读性。常见错误包括在UPDATE/DELETE中忘记WHERE子句、缺少连接条件、混淆WHERE与HAVING，以及在WHERE中放置聚合函数。练习手写查询，并仔细检查关键字的顺序。对于IB Paper 2或AQA实操任务，务必测试边界情况，如NULL值和空结果集。

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

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

SQL Exam Essentials for IB and AQA | SQL 考点精讲