Lab 6：银行数据库SQL查询与分析实战

课程：数据库技术与应用：AI时代的基石数据技能
授课教师：王健楠（计算机科学与技术系）
学期：2026 年（春季）

实验目的与学习目标

本实验以银行数据库为背景，引导你完成由浅入深的 20 道 SQL 实战任务。完成本实验后，你应能够：

1. 排序与过滤：使用 ORDER BY 按单列或多列对结果排序，使用 WHERE 筛选满足条件的记录。
2. 多表连接：使用 INNER JOIN 关联多张表获取复合信息，使用 LEFT OUTER JOIN 保留左表中不匹配的记录。
3. 聚合与分组：使用 COUNT、SUM、AVG、MAX、MIN 对数据进行统计，结合 GROUP BY 分组汇总，并用 HAVING 对分组结果进行过滤。
4. 子查询：在 WHERE 或 FROM 子句中嵌套子查询，将复杂问题拆解为多个步骤逐层求解。
5. 集合运算：使用 UNION、INTERSECT、EXCEPT 对多个查询的结果集进行合并、取交集和求差集。

实验背景

你刚刚收到了一家跨国银行数据分析部门的实习录取通知！该银行为全球数以万计的客户提供金融服务，在纽约、伦敦、柏林等多个城市设有分行。

入职第一天，主管把你带到工位前，指着屏幕上的数据库架构图说：

银行每天产生大量业务数据，我们的工作就是通过 SQL 查询，从这些数据中提炼出管理层所需的信息。今天，你就来体验一下真实的数据分析工作吧！

你打开电脑，准备迎接挑战 :)

---

数据库结构

数据库文件 bank.db 包含以下 6 张表，下划线标注主键，上标标注外键：

• Customer（客户）= {customerID, firstName, lastName, income, birthDate}
• Account（账户）= {accNumber, type, balance, branchNumber^FK→Branch}
• Owns（持有关系）= {customerID^{FK→Customer}, accNumber^FK→Account}
• Transactions（交易）= {transNumber, accNumber^FK→Account, amount}
• Employee（员工）= {sin, firstName, lastName, salary, branchNumber^FK→Branch}
• Branch（分行）= {branchNumber, branchName, managerSIN^{FK→Employee}, budget}

字段说明：

• customerID：客户唯一编号（不是社会保险号）
• type：账户类型，取值为 CHQ（支票账户）、SAV（储蓄账户）或 BUS（企业账户）
• Owns 表示多对多关系（同一账户可由多名客户共同持有）
• transNumber + accNumber 联合唯一标识一笔交易
• managerSIN：分行经理的员工编号（sin）

---

评分规则： 共 100 分，20 道题，每题 5 分。

任务	主题	题数	分值
任务 1	排序、过滤与基础聚合	4 题	20 分
任务 2	连接查询	5 题	25 分
任务 3	聚合与分组	6 题	30 分
任务 4	子查询与集合运算	5 题	25 分

作答要求：

• 每道题的答案必须是一条 SQL 语句（可以使用 CTE或子查询），但最终应形成一个完整的 SQL 查询语句。
• 除题目特别说明外，排序默认升序（ASC）。
• 查询结果的每一列都应有有意义的列名；计算列请用 AS 命名。
• 不要引入不必要的表（例如只需要 customerID，无需额外关联 Customer 表）。

第 0 步：环境准备

安装 ipython-sql

ipython-sql 是一个 Jupyter Notebook 扩展，安装后可以在 Notebook 中直接执行 SQL 语句，结果以表格形式呈现。运行下面的代码完成安装（若已安装则会跳过）：

!pip install ipython-sql

如何在 Notebook 中编写 SQL？

安装完成后，按以下三步使用 SQL 魔法命令：

第 1 步：加载扩展（每次启动 Notebook 执行一次）

%load_ext sql

第 2 步：连接数据库

%sql sqlite:///bank.db

连接成功后会显示 Connected: @bank.db。

第 3 步：编写 SQL 查询

写法	格式	适用场景
单行查询	%sql SELECT ...	简短语句
多行查询	单元格首行写 %%sql，SQL 从第二行开始	多行 SQL
存入变量	单元格首行写 %%sql 变量名 <<，SQL 从第二行开始	需在 Python 中处理结果

示例——将查询结果存入变量，再转为 pandas DataFrame：

%%sql my_result <<
SELECT firstName, lastName
FROM Customer LIMIT 5

df = my_result.DataFrame()
print(df)

本实验约定： 每道题的 SQL 单元格首行为 %%sql qX_Y <<（如 %%sql q1_1 <<）， 结果将被保存到对应变量，供下方检验单元格自动验证。

%load_ext sql

%sql sqlite:///bank.db

运行下面的代码，验证数据库连接是否正常，并查看数据库中的所有表：

%%sql
SELECT name AS 表名 FROM sqlite_master WHERE type='table' ORDER BY name;

运行以下代码，快速浏览 Customer 和 Branch 表的部分数据，熟悉数据内容：

%%sql
SELECT * FROM Customer LIMIT 3;

%%sql
SELECT * FROM Branch;

---

任务 1：排序、过滤与基础聚合（20 分）

任务背景

"好，我们先从最基础的查询开始，" 你的主管说，"在实际分析中，WHERE 帮你筛选感兴趣的行，ORDER BY 让结果整洁有序，聚合函数则把大量数据压缩成一个关键数字——这三件工具是所有分析的起点。"

他递给你四份数据需求单：

1.1 中等收入客户名单（5 分）

财务部门需要一份中等收入客户的排序名单，用于市场细分分析。

请查询收入（income）在 6 万至 7 万之间（含两端）的客户的 firstName（名）、lastName（姓）和 income（收入）。

排序： 按 income 降序；收入相同时按 lastName 升序，再按 firstName 升序。

%%sql q1_1 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 1.1 =====
try:
    df = q1_1.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 16, f"结果应有 16 行，实际 {len(df)} 行，请检查 WHERE 条件"
assert set(['firstName','lastName','income']).issubset(set(df.columns)), \
    f"缺少必要列，当前列名：{list(df.columns)}"
assert int(df['income'].iloc[0]) == 69842, \
    f"第一行 income 应为 69842，实际 {df['income'].iloc[0]}，请检查 ORDER BY"
print(f"✅ 检验 1.1 通过！共查询到 {len(df)} 位中等收入客户。")
q1_1

1.2 重点分行员工薪资差距（5 分）

人力资源部门想了解几个重点分行中员工与其经理的薪资差距。

请查询在纽约（New York）、伦敦（London）或柏林（Berlin）分行工作的全体员工的以下信息： sin（员工号）、branchName（分行名）、salary（员工薪资）、以及经理薪资 − 员工薪资（命名为 boss_diff）。

排序： 按 boss_diff 升序。

提示： 需同时关联 Employee（员工）、Branch（分行）、以及再次关联 Employee（经理）三张表，通过 managerSIN 找到经理薪资。

%%sql q1_2 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 1.2 =====
try:
    df = q1_2.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 58, f"结果应有 58 行，实际 {len(df)} 行，请检查分行名拼写和连接条件"
assert df.shape[1] == 4, f"结果应有 4 列，实际 {df.shape[1]} 列"
diffs = df.iloc[:, 3].astype(float).tolist()
assert diffs == sorted(diffs), "第 4 列（boss_diff）应按升序排列，请检查 ORDER BY"
print(f"✅ 检验 1.2 通过！共查询到 {len(df)} 条员工记录。")
q1_2

1.3 纽约分行人员构成统计（5 分）

纽约分行需要了解当前员工的规模与姓氏多样性。

请查询纽约（New York）分行的员工总人数（命名为 count_employees）和不同姓氏数量（命名为 count_distinct_lastnames），结果为一行两列。

提示： 使用 COUNT(DISTINCT 列名) 统计不重复值。

%%sql q1_3 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 1.3 =====
try:
    df = q1_3.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 1, f"结果应为 1 行，实际 {len(df)} 行"
assert df.shape[1] == 2, f"结果应为 2 列，实际 {df.shape[1]} 列"
assert int(df.iloc[0, 0]) == 21, f"员工总数应为 21，实际 {df.iloc[0, 0]}"
assert int(df.iloc[0, 1]) == 20, f"不同姓氏数量应为 20，实际 {df.iloc[0, 1]}"
print(f"✅ 检验 1.3 通过！纽约分行员工总数：{int(df.iloc[0,0])}，不同姓氏数：{int(df.iloc[0,1])}。")
q1_3

1.4 纽约分行薪资总额（5 分）

财务部门需要汇总纽约分行的人力成本。

请查询纽约（New York）分行所有员工薪资之和（命名为 sum_salaries），结果为一行一列。

%%sql q1_4 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 1.4 =====
try:
    df = q1_4.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 1, f"结果应为 1 行，实际 {len(df)} 行"
assert df.shape[1] == 1, f"结果应为 1 列，实际 {df.shape[1]} 列"
assert int(df.iloc[0, 0]) == 1021648, f"薪资总和应为 1021648，实际 {df.iloc[0, 0]}"
print(f"✅ 检验 1.4 通过！纽约分行薪资总额：{int(df.iloc[0, 0])}。")
q1_4

---

任务 2：连接查询（25 分）

任务背景

"一张表的信息总是有限的，" 主管接着跟你说，"比如 Account 表只有分行号，没有分行名；Employee 表只有员工号，没有分行详情。要把散落在多张表的信息整合到一起，就需要用到 JOIN——这是 SQL 中最强大的工具之一。"

他打开了五个新的需求工单，每个工单都需要查询不止一张表……

2.1 特定经理分行的大额账户（5 分）

财务审计部门需要核查由 Phillip Edwards 担任经理的分行中，余额超过 11 万元的账户。

请查询这些账户的 branchName（分行名）、accNumber（账号）和 balance（余额）。

排序： 按 accNumber 升序。

提示： 关联 Account、Branch、Employee 三张表，通过 managerSIN 定位经理。

%%sql q2_1 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 2.1 =====
try:
    df = q2_1.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 8, f"结果应有 8 行，实际 {len(df)} 行，请检查经理姓名和余额条件"
assert df.shape[1] == 3, f"结果应有 3 列，实际 {df.shape[1]} 列"
assert df.iloc[:, 1].tolist() == sorted(df.iloc[:, 1].tolist()), "账号列应升序排列"
print(f"✅ 检验 2.1 通过！共查询到 {len(df)} 个大额账户。")
q2_1

2.2 跨国 VIP 客户识别（5 分）

营销部门希望识别同时在伦敦（London）和拉脱维亚（Latveria）两地都持有账户、且收入超过 9 万的客户，并列出他们的所有账户信息。

请返回 customerID（客户编号）、income（收入）、accNumber（账号）、branchNumber（分行号）。

排序： 先按 customerID 升序，再按 accNumber 升序。

注意： 结果应包含满足条件客户的所有账户，即使某账户并不在伦敦或拉脱维亚分行。

提示： 先用 INTERSECT 取"在伦敦持有账户的客户编号"与"在拉脱维亚持有账户的客户编号"的交集，再作为子查询筛选。

%%sql q2_2 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 2.2 =====
try:
    df = q2_2.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) > 0, "结果为空，请检查收入条件和分行名拼写（'London', 'Latveria'）"
assert df.shape[1] == 4, f"结果应有 4 列，实际 {df.shape[1]} 列"
cids = df.iloc[:, 0].tolist()
assert all(cids[i] <= cids[i+1] for i in range(len(cids)-1)), "结果应先按 customerID 升序"
print(f"✅ 检验 2.2 通过！共 {df.iloc[:,0].nunique()} 位 VIP 客户，{len(df)} 条账户记录。")
q2_2

2.3 伦敦分行薪资最低员工（5 分）

伦敦分行计划对薪资最低的员工进行福利补贴，首先需要确定这些员工的信息（薪资并列最低时全部列出）。

请查询伦敦（London）分行中薪资最低的员工的 sin、firstName、lastName 和 salary。

排序： 按 sin 升序。

提示： 子查询先求 MIN(salary)，外层再筛选等于该值的员工。

%%sql q2_3 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 2.3 =====
try:
    df = q2_3.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) >= 1, "结果为空，请检查分行名拼写（'London'）"
assert df.shape[1] == 4, f"结果应有 4 列，实际 {df.shape[1]} 列"
salaries = df.iloc[:, 3].astype(float).tolist()
assert len(set(salaries)) == 1, "所有返回员工的薪资应相同（均为最低薪资）"
assert int(salaries[0]) == 13950, f"伦敦分行最低薪资应为 13950，实际 {salaries[0]}"
print(f"✅ 检验 2.3 通过！{len(df)} 位员工并列最低薪资，薪资为 {int(salaries[0])}。")
q2_3

2.4 高薪员工与经理标注（外连接）（5 分）

人力资源部门想要一份薪资超过 7 万元的员工名单，并标注其中担任分行经理的人员。

请查询薪资超过 7 万的所有员工的 sin、firstName、lastName、salary，以及 branchName（若该员工担任分行经理，则显示分行名；否则为 NULL）。

排序： 按 branchName 升序（NULL 排最前面）。

强制要求：必须使用 LEFT OUTER JOIN 实现。

提示： Employee LEFT OUTER JOIN Branch，连接条件为 e.sin = b.managerSIN。

%%sql q2_4 <<
-- 在此处编写你的 SQL 查询（必须使用 LEFT OUTER JOIN）

# ===== 检验 2.4 =====
try:
    df = q2_4.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 21, f"结果应有 21 行，实际 {len(df)} 行，请检查薪资条件"
assert df.shape[1] == 5, f"结果应有 5 列，实际 {df.shape[1]} 列"
assert df.iloc[:, 4].isna().any(), "第 5 列（branchName）应包含 NULL（非经理员工），请检查是否用了外连接"
print(f"✅ 检验 2.4 通过！经理 {df.iloc[:,4].notna().sum()} 人，非经理 {df.iloc[:,4].isna().sum()} 人。")
q2_4

2.5 同题重做：不用 JOIN（5 分）

上一题要求用外连接实现，本题要求达到完全相同的结果，但不允许使用任何 JOIN 操作。

提示：可以用 UNION 将两个子查询合并：

• 第一个子查询：查询担任经理的高薪员工（可关联 Branch 但不用 JOIN）
• 第二个子查询：查询不是经理的高薪员工（branchName 用 NULL 填充）

通过对比两种实现，你会发现外连接和 UNION 在某些场景下可以相互替代。

%%sql q2_5 <<
-- 在此处编写你的 SQL 查询（不允许使用任何 JOIN）

# ===== 检验 2.5 =====
try:
    df = q2_5.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 21, f"结果应有 21 行（与 2.4 相同），实际 {len(df)} 行"
assert df.shape[1] == 5, f"结果应有 5 列，实际 {df.shape[1]} 列"
assert df.iloc[:, 4].isna().any(), "第 5 列应含 NULL（非经理员工）"
print(f"✅ 检验 2.5 通过！不使用 JOIN 也得到了与 2.4 相同的 {len(df)} 行结果。")
q2_5

---

任务 3：聚合与分组（30 分）

任务背景

季度末，各部门纷纷向数据团队提交汇总报表需求。你的主管把几份需求单推到你面前：

"光有原始数据还不够，管理层更需要统计汇总——每个分行的薪资水平如何？哪些客户账户分布集中？哪些账户交易频繁？这些问题都要靠 GROUP BY、HAVING 和聚合函数来回答。"

你翻开第一份需求单……

3.1 各分行薪资统计（5 分）

总部人力资源部需要一张汇总各分行薪资水平的报表。

请查询每个分行的 branchName（分行名）、salary_gap（薪资极差 = 最高 − 最低）和 avg_salary（平均薪资）。

排序： 按 branchName 升序。

%%sql q3_1 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 3.1 =====
try:
    df = q3_1.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 5, f"结果应有 5 行（共 5 个分行），实际 {len(df)} 行"
assert df.shape[1] == 3, f"结果应有 3 列，实际 {df.shape[1]} 列"
assert df.iloc[:,0].tolist() == sorted(df.iloc[:,0].tolist()), "分行名应按升序排列"
assert (df.iloc[:,1].astype(float) >= 0).all(), "薪资极差不应为负"
print(f"✅ 检验 3.1 通过！共统计了 {len(df)} 个分行的薪资数据。")
q3_1

3.2 账户分布集中的客户（5 分）

客户关系部门希望识别账户分布集中的客户：他们的账户涉及最多 2 个不同分行，便于针对性推广本地化服务。

请查询满足条件的客户的 customerID（客户编号）、firstName（名）和 lastName（姓）。

排序： 先按 lastName 升序，再按 firstName 升序。

提示： 子查询中关联 Owns、Account，按 customerID 分组，用 COUNT(DISTINCT branchNumber) <= 2 筛选，外层再取姓名。

%%sql q3_2 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 3.2 =====
try:
    df = q3_2.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 91, f"结果应有 91 行，实际 {len(df)} 行，请检查 HAVING 条件和去重逻辑"
assert df.shape[1] == 3, f"结果应有 3 列，实际 {df.shape[1]} 列"
assert df.iloc[:,2].tolist() == sorted(df.iloc[:,2].tolist()), "结果应先按 lastName 升序"
print(f"✅ 检验 3.2 通过！共找到 {len(df)} 位账户分布集中的客户。")
q3_2

3.3 不同年龄段的平均收入（5 分）

市场研究部门需要对比年长与年轻客户群体的平均收入水平。

请查询年龄超过 60 岁的客户平均收入（命名为 avg_income_over60）和年龄不足 20 岁的客户平均收入（命名为 avg_income_under20），结果为一行两列。

提示： 为避免不同运行日期导致结果变化，本题统一以 2026-05-10 作为参考日期。使用 SQLite 的 date('2026-05-10', '-N years') 计算精确年龄分界日期：

• 年龄超过 60 岁 等价于出生日期满足 birthDate <= date('2026-05-10', '-60 years')
• 年龄不足 20 岁 等价于出生日期满足 birthDate > date('2026-05-10', '-20 years')

结合 CASE WHEN ... THEN income END 在同一行计算两组平均值。详见 SQLite 日期时间函数文档。

%%sql q3_3 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 3.3 =====
try:
    df = q3_3.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 1, f"结果应为 1 行，实际 {len(df)} 行"
assert df.shape[1] == 2, f"结果应为 2 列，实际 {df.shape[1]} 列"
# 参考日期固定为 2026-05-10
val_over60 = df.iloc[0, 0]
val_under20 = df.iloc[0, 1]
assert abs(float(val_over60) - 52926.96296296296) < 1e-6, \
    f"年龄 >60 的平均收入应为 52926.96296296296，实际 {val_over60}"
assert val_under20 is not None and abs(float(val_under20) - 69037.0) < 1e-6, \
    f"年龄 <20 的平均收入应为 69037.0，实际 {val_under20}"
print(f"✅ 检验 3.3 通过！年龄 >60 平均收入：{float(val_over60):.1f}，"
      f"年龄 <20 平均收入：{float(val_under20):.1f}（参考日期固定为 2026-05-10）。")
q3_3

3.4 特定姓名规律的多账户客户（5 分）

VIP 服务部门需要一批拥有多个账户、且名字有特定规律的客户名单。

请查询同时满足以下两个条件的客户的 customerID、lastName、firstName、income 和平均账户余额（命名为 avg_balance）：

1. 持有至少 2 个账户；
2. 满足以下姓名条件之一（或同时满足）：
   • 姓（lastName）以 S 开头且包含字母 e（如 Steve）
   • 名（firstName）以 A 开头，且倒数第 3 位为字母 n（如 Amanda：A-m-a-[n]-d-a，方括号标注倒数第3位）

排序： 按 customerID 升序。

提示： LIKE 模式：lastName LIKE 'S%e%' 和 firstName LIKE 'A%n__'（_ 匹配任意单个字符，A%n__ 表示以 A 开头，且倒数第 3 位为 n，即末尾还有任意两个字符）。 使用 HAVING COUNT(*) >= 2 筛选持有至少 2 个账户的客户。

%%sql q3_4 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 3.4 =====
try:
    df = q3_4.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 7, f"结果应有 7 行，实际 {len(df)} 行，请检查 LIKE 模式和 HAVING 条件"
assert df.shape[1] == 5, f"结果应有 5 列，实际 {df.shape[1]} 列"
assert df.iloc[:,0].tolist() == sorted(df.iloc[:,0].tolist()), "结果应按 customerID 升序"
print(f"✅ 检验 3.4 通过！共找到 {len(df)} 位满足条件的客户。")
q3_4

3.5 伦敦分行高频交易账户分析（5 分）

风控部门需要了解伦敦分行中交易次数较多的账户情况。

请查询伦敦（London）分行中交易次数不少于 15 次的账户的 accNumber（账号）、balance（余额）、sum_amount（交易金额之和）和 balance_minus_sum（余额 − 交易总额）。

排序： 按 sum_amount 升序。

%%sql q3_5 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 3.5 =====
try:
    df = q3_5.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 2, f"结果应有 2 行，实际 {len(df)} 行，请检查分行名和 HAVING 条件"
assert df.shape[1] == 4, f"结果应有 4 列，实际 {df.shape[1]} 列"
sums = df.iloc[:,2].astype(float).tolist()
assert sums == sorted(sums), "结果应按 sum_amount 升序排列"
print(f"✅ 检验 3.5 通过！伦敦分行共有 {len(df)} 个账户交易次数 ≥ 15。")
q3_5

3.6 大型分行各类账户平均交易金额（5 分）

业务分析部门需要了解规模较大的分行中，各类账户的平均交易情况。

请查询账户总数不少于 50 个的分行中，各账户类型的 branchName（分行名）、type（账户类型）和 avg_amount（平均交易金额）。

排序： 先按 branchName 升序，再按 type 升序。

%%sql q3_6 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 3.6 =====
try:
    df = q3_6.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 9, f"结果应有 9 行，实际 {len(df)} 行，请检查 HAVING >=50 的子查询和 JOIN 条件"
assert df.shape[1] == 3, f"结果应有 3 列，实际 {df.shape[1]} 列"
assert df.iloc[:,0].tolist() == sorted(df.iloc[:,0].tolist()), "结果应先按 branchName 升序"
print(f"✅ 检验 3.6 通过！共查询到 {len(df)} 个分行-账户类型组合。")
q3_6

---

任务 4：子查询与集合运算（25 分）

任务背景

"最后一组任务来了！" 你的主管推来一叠需求单，"这些问题比较复杂，一层查询解决不了，你需要把问题分解，借助子查询或集合运算把各步骤串联起来。"

他补充道："遇到困难时，先在纸上把逻辑理清楚，再动手写 SQL——这永远是最有效的方法。"

4.1 超高收入客户筛选（5 分）

市场部门需要识别超高收入客户，定义为：收入不低于所有姓 Butler 的客户中最高收入的两倍。

请查询满足条件的客户的 firstName（名）、lastName（姓）和 income（收入）。

排序： 先按 lastName 升序，再按 firstName 升序。

提示： 子查询求 Butler 客户中 MAX(income)，外层筛选收入 >= MAX(income) * 2 的客户。

%%sql q4_1 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 4.1 =====
try:
    df = q4_1.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) > 0, "结果为空，请检查子查询中 lastName = 'Butler' 条件"
assert df.shape[1] == 3, f"结果应有 3 列，实际 {df.shape[1]} 列"
last_names = df.iloc[:,1].tolist()
assert last_names == sorted(last_names), "结果应先按 lastName 升序"
assert 'Butler' not in last_names, "结果中不应出现 Butler，请检查子查询逻辑"
print(f"✅ 检验 4.1 通过！共找到 {len(df)} 位超高收入客户。")
q4_1

4.2 储蓄与企业账户持有人分析（5 分）

产品部门希望了解持有储蓄（SAV）或企业（BUS）账户的客户，以及他们名下所有此类账户。

请查询至少持有一个 BUS 账户或至少一个 SAV 账户的客户的所有 BUS 和 SAV 账户信息，返回 customerID、type、accNumber、balance。

排序： 先按 customerID，再按 type，最后按 accNumber，全部升序。

提示： 用 UNION 将"持有 SAV 账户的客户编号"与"持有 BUS 账户的客户编号"合并，作为 IN 子查询条件。

%%sql q4_2 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 4.2 =====
try:
    df = q4_2.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) > 0, "结果为空，请检查账户类型条件（'BUS', 'SAV'）"
assert df.shape[1] == 4, f"结果应有 4 列，实际 {df.shape[1]} 列"
types = set(df.iloc[:,1].tolist())
assert types.issubset({'BUS','SAV'}), f"结果中不应出现 CHQ 账户，当前：{types}"
print(f"✅ 检验 4.2 通过！共 {df.iloc[:,0].nunique()} 位客户的 {len(df)} 个 BUS/SAV 账户。")
q4_2

4.3 "纯纽约"客户识别（5 分）

安全合规部门需要识别与伦敦分行完全无关联的纽约客户，用于一次专项排查。

请查询同时满足以下全部条件的客户的 customerID（不含重复）：

1. 在纽约（New York）分行持有账户；
2. 未在伦敦（London）分行持有任何账户；
3. 未与任何在伦敦分行持有账户的客户共同署名持有同一账户。

排序： 按 customerID 升序。

三步法：

1. 找出"伦敦客户"集合（在伦敦分行持有账户的客户编号）；
2. 找出"与伦敦客户共同持有账户的所有客户"：先在 Owns 中取第1步客户持有的全部账号（含非伦敦分行账户），再找 Owns 中持有这些账号的所有客户编号（双层子查询）；
3. 从纽约客户中用 NOT IN 排除第1步（对应条件2）和第2步（对应条件3）中的客户。

注意： 条件3排查的是"账户共享"，而非"在伦敦持有账户"——两者的差别在于，某个非伦敦账户也可能由纽约客户与伦敦客户共同持有。

%%sql q4_3 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 4.3 =====
try:
    df = q4_3.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 17, f"结果应有 17 行，实际 {len(df)} 行，请检查三个排除条件"
assert df.shape[1] == 1, f"结果应只有 1 列（customerID），实际 {df.shape[1]} 列"
cids = df.iloc[:,0].tolist()
assert len(set(cids)) == len(cids), "结果含重复 customerID，请加 DISTINCT"
assert cids == sorted(cids), "customerID 应按升序排列"
print(f"✅ 检验 4.3 通过！共找到 {len(df)} 位纯纽约客户。")
q4_3

4.4 覆盖 Helen Morgan 所有分行的客户（5 分）

VIP 服务部门想找出与某位重要客户"涉猎面"相当的客户（含本人）。

请查询收入超过 5000 且在 Helen Morgan 所持有账户涉及的所有分行都持有账户的客户的 customerID、firstName、lastName 和 income。

排序： 按 income 降序。

这是经典的"关系除法"查询——相当于"找出满足全称量词条件的客户"。 提示： 使用 NOT EXISTS (... EXCEPT ...) 结构：先找出 Helen Morgan 持有账户的分行集合，再检查当前客户是否缺少其中任何一个分行。 说明： Helen Morgan 本人也满足条件（她自身覆盖了自己的所有分行），因此结果中会包含她。

%%sql q4_4 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 4.4 =====
try:
    df = q4_4.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 3, f"结果应有 3 行，实际 {len(df)} 行，请检查 NOT EXISTS 和 EXCEPT 逻辑"
assert df.shape[1] == 4, f"结果应有 4 列，实际 {df.shape[1]} 列"
incomes = df.iloc[:,3].astype(float).tolist()
assert incomes == sorted(incomes, reverse=True), "income 应按降序排列"
print(f"✅ 检验 4.4 通过！共找到 {len(df)} 位满足条件的客户。")
q4_4

4.5 异常高额交易账户的全部交易记录（5 分）

风控部门需要审查交易金额异常偏高的账户。

筛选标准：某账户的平均交易金额超过同类型账户整体平均交易金额的 3 倍时，该账户被认为异常。

这里的“同类型账户整体平均交易金额”是指：将该类型账户对应的所有交易记录合并在一起后，对 amount 直接求一次 AVG；不是先分别求每个账户的平均交易金额，再对这些账户均值求平均。

例如：若所有企业账户（BUS）的整体平均交易金额为 2000 元，则某 BUS 账户的平均交易金额超过 6000 元时，该账户的所有交易记录均需返回（即使其中某笔交易金额低于 6000）。

请返回这些异常账户的 branchName（分行名）、type（账户类型）、accNumber（账号）、transNumber（交易号）和 amount（金额）。

排序： 先按 branchName，再按 type，再按 accNumber，最后按 transNumber，全部升序。

提示： 先用嵌套子查询找出平均交易金额超过 3 倍类型整体均值的账户编号；其中“类型整体均值”应按该类型的全部交易记录直接计算 AVG(amount)。再关联 Branch、Account、Transactions 三张表获取完整记录。

%%sql q4_5 <<
-- 在此处编写你的 SQL 查询

# ===== 检验 4.5 =====
try:
    df = q4_5.DataFrame()
except NameError:
    raise AssertionError("请先运行上面的 %%sql 单元格，再执行此检验")
assert len(df) == 22, f"结果应有 22 行，实际 {len(df)} 行，请检查三倍均值的子查询逻辑"
assert df.shape[1] == 5, f"结果应有 5 列，实际 {df.shape[1]} 列"
print(f"✅ 检验 4.5 通过！共找到 {len(df)} 条异常账户的交易记录。")
q4_5

---

实验圆满结束！

恭喜你完成了全部任务！你从一份真实的关系型数据库出发，系统地运用了 SQL 进阶查询的核心技能：

• 任务 1：ORDER BY 多列排序、范围过滤与基础聚合（COUNT、SUM）；
• 任务 2：内连接、外连接（LEFT OUTER JOIN）、以及用 UNION 替代外连接；
• 任务 3：GROUP BY、HAVING、日期函数、LIKE 模式匹配与复合聚合；
• 任务 4：子查询、集合运算（UNION、INTERSECT、EXCEPT）与关系除法。

---

提交前最终检查：

• 建议点击顶部菜单 Kernel → Restart & Run All，确保所有代码从头到尾无报错地运行完毕。
• 确认全部 20 道题的检验单元格均已通过。
• 确认无误后，将 Lab6.ipynb 与 bank.db 一并压缩打包，上传至网络学堂。