📚 SQL Exam Essentials for IB & Edexcel Computer Science | IB与Edexcel计算机：SQL考点精讲
Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. For both IB Computer Science and Edexcel Computer Science qualifications, a solid understanding of SQL is essential. Students are expected to write accurate SQL statements for data retrieval, definition, and manipulation, and to understand fundamental database concepts such as primary keys, foreign keys, and normalization. This guide focuses on the core SQL exam topics, covering syntax, techniques, and common pitfalls.
结构化查询语言（SQL）是管理和操作关系数据库的标准语言。对于IB计算机科学和Edexcel计算机科学课程，扎实掌握SQL至关重要。要求学生能够编写准确的SQL语句进行数据检索、定义和操作，并理解主键、外键和规范化等基本数据库概念。本指南聚焦于SQL核心考点，涵盖语法、技巧和常见易错点。
1. Introduction to Databases and SQL | 数据库与SQL简介
A relational database stores data in tables, which consist of rows (records) and columns (fields). SQL allows users to interact with the database by executing queries. It is divided into several sublanguages: DDL (Data Definition Language) for schema definition, DML (Data Manipulation Language) for data retrieval and updating, and DCL (Data Control Language) for security, though exam focus is on DDL and DML.
关系数据库将数据存储在由行（记录）和列（字段）组成的表中。SQL允许用户通过执行查询与数据库交互。SQL分为多个子语言：DDL（数据定义语言）用于定义模式，DML（数据操作语言）用于检索和更新数据，DCL（数据控制语言）用于安全性，但考试重点在DDL和DML。
Key terms include primary key (uniquely identifies each row), foreign key (link between tables), and composite key. Understanding these is vital for writing JOINs and designing normalized databases.
关键术语包括主键（唯一标识每一行）、外键（表之间的链接）和复合键。理解这些对于编写JOIN和设计规范化数据库至关重要。
2. DDL: Creating and Altering Tables | DDL：创建与修改表
DDL commands include CREATE, ALTER, and DROP. To create a table, you define column names, data types, and constraints. Example: CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE );
DDL命令包括CREATE、ALTER和DROP。创建表时，需要定义列名、数据类型和约束。例如：CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE );
You can alter a table to add or remove columns: ALTER TABLE Students ADD Email VARCHAR(100);
可以修改表以添加或删除列：ALTER TABLE Students ADD Email VARCHAR(100);
Constraints such as NOT NULL, UNIQUE, and DEFAULT ensure data integrity. In exams, you may be asked to write a complete CREATE TABLE statement with appropriate constraints.
NOT NULL、UNIQUE和DEFAULT等约束确保数据完整性。考试中可能要求编写带有适当约束的完整CREATE TABLE语句。
3. Data Types and Constraints | 数据类型与约束
Common SQL data types include INT, VARCHAR(n), DATE, BOOLEAN, FLOAT. Choosing correct data types is important for efficient storage and query performance. Edexcel often expects candidates to use VARCHAR for text, INT for integers, and DATE for dates.
常见的SQL数据类型包括INT、VARCHAR(n)、DATE、BOOLEAN、FLOAT。选择正确的数据类型对于高效存储和查询性能很重要。Edexcel通常希望考生对文本使用VARCHAR，整数使用INT，日期使用DATE。
Constraints enforce rules at the column level. A PRIMARY KEY constraint uniquely identifies each record and implies NOT NULL and UNIQUE. A FOREIGN KEY constraint maintains referential integrity by linking a column to a primary key in another table.
约束在列级别实施规则。PRIMARY KEY约束唯一标识每条记录，意味着NOT NULL和UNIQUE。FOREIGN KEY约束通过将列链接到另一表的主键来维护引用完整性。
Example: CREATE TABLE Enrolments ( EnrolmentID INT PRIMARY KEY, StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Students(StudentID) );
示例：CREATE TABLE Enrolments ( EnrolmentID INT PRIMARY KEY, StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Students(StudentID) );
4. Basic SELECT and Column Aliases | 基本SELECT与列别名
The SELECT statement retrieves data. The simplest form is SELECT * FROM TableName; which returns all columns. To select specific columns: SELECT Name, DOB FROM Students;
SELECT语句用于检索数据。最简单的形式是SELECT * FROM TableName;它返回所有列。若要选择特定列：SELECT Name, DOB FROM Students;
Aliases give a temporary name to columns or tables for readability: SELECT Name AS StudentName FROM Students; In exams, using AS is often optional but helps clarify output.
别名可以为列或表提供临时名称以提高可读性：SELECT Name AS StudentName FROM Students;考试中，使用AS通常是可选的，但有助于使输出更清晰。
You can use SELECT DISTINCT to eliminate duplicate rows: SELECT DISTINCT City FROM Students;
可以使用SELECT DISTINCT消除重复行：SELECT DISTINCT City FROM Students;
5. Filtering Data with WHERE | 使用WHERE过滤数据
The WHERE clause filters rows based on conditions. Operators include =, <>, <, >, <=, >=. Example: SELECT * FROM Students WHERE DOB > '2005-01-01';
WHERE子句根据条件过滤行。运算符包括=、<>、<、>、<=、>=。示例：SELECT * FROM Students WHERE DOB > '2005-01-01';
Logical operators AND, OR, NOT combine conditions. SELECT * FROM Students WHERE City = 'London' AND DOB > '2005-01-01';
逻辑运算符AND、OR、NOT可以组合条件。SELECT * FROM Students WHERE City = 'London' AND DOB > '2005-01-01';
Other useful operators: BETWEEN for ranges, IN for lists, LIKE with wildcards (% zero or more characters, _ single character). Example: SELECT * FROM Students WHERE Name LIKE 'J%'; finds names starting with J.
其他有用的运算符：BETWEEN用于范围，IN用于列表，LIKE与通配符（%匹配零个或多个字符，_匹配单个字符）。示例：SELECT * FROM Students WHERE Name LIKE 'J%';查找以J开头的名字。
6. Sorting with ORDER BY | 使用ORDER BY排序
ORDER BY sorts the result set. Default is ascending (ASC), but DESC can be used for descending. SELECT * FROM Students ORDER BY Name ASC;
ORDER BY对结果集进行排序。默认是升序（ASC），但可以使用DESC降序。SELECT * FROM Students ORDER BY Name ASC;
You can sort by multiple columns: ORDER BY City ASC, DOB DESC. In exams, ensure columns exist in the SELECT list if query requires sorting by derived fields.
可以按多列排序：ORDER BY City ASC, DOB DESC。考试中，如果需要按派生字段排序，确保这些列在SELECT列表中存在。
When combined with WHERE, ORDER BY must come after WHERE: SELECT Name, DOB FROM Students WHERE City = 'London' ORDER BY DOB;
与WHERE结合时，ORDER BY必须出现在WHERE之后：SELECT Name, DOB FROM Students WHERE City = 'London' ORDER BY DOB;
7. Aggregate Functions and GROUP BY | 聚合函数与GROUP BY
Aggregate functions perform calculations on a set of rows: COUNT, SUM, AVG, MIN, MAX. Example: SELECT COUNT(*) FROM Students; returns the number of students.
聚合函数对一组行执行计算：COUNT、SUM、AVG、MIN、MAX。示例：SELECT COUNT(*) FROM Students;返回学生人数。
GROUP BY groups rows with the same values in specified columns, allowing aggregates per group. SELECT City, COUNT(*) FROM Students GROUP BY City;
GROUP BY将指定列中具有相同值的行分组，允许对每组进行聚合。SELECT City, COUNT(*) FROM Students GROUP BY City;
HAVING filters groups after aggregation (similar to WHERE but for groups). SELECT City, COUNT(*) FROM Students GROUP BY City HAVING COUNT(*) > 5;
HAVING在聚合后过滤分组（类似于WHERE但用于组）。SELECT City, COUNT(*) FROM Students GROUP BY City HAVING COUNT(*) > 5;
Exam tip: In a query with GROUP BY, all non-aggregated columns in SELECT must appear in GROUP BY. Using WHERE before GROUP BY filters rows before grouping, which is more efficient.
考试提示：在带有GROUP BY的查询中，SELECT中所有非聚合列必须出现在GROUP BY中。在GROUP BY之前使用WHERE过滤行，在分组前进行，效率更高。
8. Joining Tables | 连接表
JOIN combines rows from two or more tables based on a related column. The most common is INNER JOIN, which returns rows with matching values in both tables. Syntax: SELECT * FROM Students INNER JOIN Enrolments ON Students.StudentID = Enrolments.StudentID;
JOIN基于相关列组合两个或多个表的行。最常见的是INNER JOIN，它返回两个表中匹配的行。语法：SELECT * FROM Students INNER JOIN Enrolments ON Students.StudentID = Enrolments.StudentID;
LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matched rows from the right; unmatched right columns are NULL. RIGHT JOIN works oppositely. Exams often test INNER JOIN and LEFT JOIN.
LEFT JOIN（或LEFT OUTER JOIN）返回左表的所有行以及右表中匹配的行；未匹配的右表列为NULL。RIGHT JOIN与之相反。考试常考INNER JOIN和LEFT JOIN。
You can join multiple tables: SELECT Students.Name, Courses.Title FROM Students JOIN Enrolments ON Students.StudentID = Enrolments.StudentID JOIN Courses ON Enrolments.CourseID = Courses.CourseID;
可以连接多个表：SELECT Students.Name, Courses.Title FROM Students JOIN Enrolments ON Students.StudentID = Enrolments.StudentID JOIN Courses ON Enrolments.CourseID = Courses.CourseID;
Always qualify column names with table names if ambiguous (e.g., StudentID appears in both tables). Using table aliases shortens syntax: SELECT S.Name, C.Title FROM Students S JOIN Enrolments E ON S.StudentID = E.StudentID JOIN Courses C ON E.CourseID = C.CourseID;
如果列名有歧义，始终用表名限定列名（如StudentID同时出现在两个表中）。使用表别名可简化语法：SELECT S.Name, C.Title FROM Students S JOIN Enrolments E ON S.StudentID = E.StudentID JOIN Courses C ON E.CourseID = C.CourseID;
9. Subqueries and Nested Queries | 子查询与嵌套查询
A subquery is a SELECT statement nested inside another query. It can appear in WHERE, FROM, or SELECT. In a WHERE clause, subqueries often use IN, EXISTS, or comparison operators with ALL/ANY. Example: SELECT Name FROM Students WHERE StudentID IN (SELECT StudentID FROM Enrolments WHERE CourseID = 101);
子查询是嵌套在另一个查询中的SELECT语句。它可以出现在WHERE、FROM或SELECT中。在WHERE子句中，子查询通常使用IN、EXISTS或带ALL/ANY的比较运算符。示例：SELECT Name FROM Students WHERE StudentID IN (SELECT StudentID FROM Enrolments WHERE CourseID = 101);
A correlated subquery references columns from the outer query and is evaluated for each row. Example: SELECT Name FROM Students S WHERE EXISTS (SELECT 1 FROM Enrolments E WHERE E.StudentID = S.StudentID AND CourseID = 101);
相关子查询引用外部查询中的列，并为每一行计算。示例：SELECT Name FROM Students S WHERE EXISTS (SELECT 1 FROM Enrolments E WHERE E.StudentID = S.StudentID AND CourseID = 101);
Subqueries in FROM must be aliased and treated as derived tables. SELECT AVG(Grade) FROM (SELECT Grade FROM Enrolments WHERE CourseID = 101) AS CourseGrades; Exams may ask you to
Published by TutorHao | IB Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导