最后更新：Aug 28, 2025

概述

第一个作业是构建一组 SQL 查询来分析将提供给您的数据集。为此，您将研究 Lahman Database。本次作业是一次很好的机会来：(1) 学习基本和某些高级 SQL 特性，(2) 熟悉使用功能完备的数据库管理系统 DuckDB，它在未来可能对您可能有所帮助。

这是一个个人项目，需要独立完成（即不分组）。

发布日期： Aug 27, 2025
截止日期： Sep 07, 2025 @ 11:59pm

规格说明

本次作业共包含 6 道题目，满分 100 分。每道题都需要构建一个 SQL 查询来获取所需数据。每道题的答案应只包含一条语句。完成这些题目大约需要 6 小时。

占位文件夹

创建提交用的占位文件夹，并为每道题创建空的 SQL 文件：

$ mkdir placeholder
$ cd placeholder
$ touch q1_sample.duckdb.sql \
        q2_hr.duckdb.sql \
        q3_mvp.duckdb.sql \
        q4_award.duckdb.sql \
        q5_hof.duckdb.sql \
        q6_upsert.duckdb.sql

填写完查询后，您可以运行以下命令来压缩文件夹：

$ zip -j submission.zip placeholder/*.sql

-j 参数可以让您将所有 SQL 查询压缩到 zip 文件中而不包含路径信息。如果不这样做，评分脚本将无法正常工作。

说明

下载数据库转储文件：

$ wget https://15445.courses.cs.cmu.edu/fall2025/files/lahman-cmudb2025.db.gz

检查其 MD5 校验和以确保您已正确下载文件：

$ md5sum lahman-cmudb2025.db.gz
a4fb302c11f80030f26bb6bb59d68cac  lahman-cmudb2025.db.gz

在 shell 中运行以下命令解压提供的数据库转储文件。请注意，解压后数据库文件应为 40MB。
```
$ tar zxf lahman-cmudb2025.db.gz
```

然后按照以下说明安装 DuckDB。

DuckDB

请按照说明安装 DuckDB 的命令行环境。

$ duckdb lahman-cmudb2025.db
DuckDB v1.3.2 (Ossivalis) 0b83e5d2f6
Enter ".help" for usage hints.
⚪◗

您可以在 DuckDB 终端运行 .tables 命令检查数据库内容。您应该看到 11 张表。输出应如下所示：

⚪◗ .tables
appearances     collegeplaying  leagues         schools
awardsmanagers  divisions       managers        teams
awardsplayers   halloffame      people

检查模式

熟悉表的模式（结构）（它们包含哪些属性，主键和外键是什么）。在 duckdb 终端对每张表运行 DESCRIBE $TABLE_NAME; 命令。输出应如下面的示例所示。

appearances

⚪◗ DESCRIBE appearances
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ yearID      │ SMALLINT    │ NO      │ PRI     │ NULL    │ NULL    │
│ teamID      │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ lgID        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ playerID    │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ G_all       │ SMALLINT    │ YES     │ NULL    │ NULL    │ NULL    │
│ G_batting   │ SMALLINT    │ YES     │ NULL    │ NULL    │ NULL    │
│ HR          │ SMALLINT    │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含球员出场记录的详细信息。例如，这是表中的一行数据：

2004|SFN|NL|aardsda01|11|11|0

重要字段包括 G_all (11)，描述出场比赛数， G_batting (11)，描述打击出场数，以及 HR (0)，描述本垒打数。

awardsmanagers

⚪◗ DESCRIBE awardsmanagers
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ playerID    │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ awardID     │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ yearID      │ SMALLINT    │ NO      │ PRI     │ NULL    │ NULL    │
│ lgID        │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含授予经理的奖项详情。例如，这是表中的一行数据：

mccarjo99│TSN Manager of the Year│1936│ML

awardsplayers

⚪◗ DESCRIBE awardsplayers
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ playerID    │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ awardID     │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ yearID      │ SMALLINT    │ NO      │ PRI     │ NULL    │ NULL    │
│ lgID        │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含授予球员的奖项详情。例如，这是表中的一行数据：

bondto01│Pitching Triple Crown│1877│NL

collegeplaying

⚪◗ DESCRIBE collegeplaying
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ playerID    │ VARCHAR     │ NO      │ NULL    │ NULL    │ NULL    │
│ schoolID    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ yearID      │ SMALLINT    │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含球员在特定年份为哪所学校效力的详情。例如，这是表中的一行数据：

aardsda01│pennst│2001

divisions

⚪◗ DESCRIBE divisions
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ divID       │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ lgID        │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ division    │ VARCHAR     │ NO      │ NULL    │ NULL    │ NULL    │
│ active      │ VARCHAR     │ NO      │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含分区信息。例如，这是表中的一行数据：

E|AL|AL East|Y

halloffame

⚪◗ DESCRIBE halloffame
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ playerID    │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ yearid      │ SMALLINT    │ NO      │ PRI     │ NULL    │ NULL    │
│ inducted    │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含名人堂球员的详情。例如，这是表中的一行数据：

abbotji01|2005|N

leagues

⚪◗ DESCRIBE leagues
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ lgID        │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ league      │ VARCHAR     │ NO      │ NULL    │ NULL    │ NULL    │
│ active      │ VARCHAR     │ NO      │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含联盟信息。例如，这是表中的一行数据：

ML|Major League|Y

managers

⚪◗ DESCRIBE managers
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ playerID    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ yearID      │ SMALLINT    │ NO      │ PRI     │ NULL    │ NULL    │
│ teamID      │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ lgID        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ inseason    │ SMALLINT    │ NO      │ PRI     │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含经理的详细信息。例如，这是表中的一行数据：

actama99│2007│WAS│NL│1

people

⚪◗ DESCRIBE people
┌──────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name  │ column_type │  null   │   key   │ default │  extra  │
│   varchar    │   varchar   │ varchar │ varchar │ varchar │ varchar │
├──────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ ID           │ INTEGER     │ NO      │ NULL    │ NULL    │ NULL    │
│ playerID     │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ birthYear    │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │
│ birthMonth   │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │
│ birthDay     │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │
│ birthCountry │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ birthState   │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ birthCity    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ deathYear    │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │
│ deathMonth   │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │
│ deathDay     │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │
│ deathCountry │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ deathState   │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ deathCity    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ nameFirst    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ nameLast     │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ nameGiven    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
├──────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤

包含球员的详细信息。例如，这是表中的一行数据：

1│aardsda01│ … │David│Aardsma│David Allan

对我们来说，重要字段包括 playerID（例如 aardsda01）、 nameFirst（例如 David）、 nameLast（例如 Aardsma），以及 nameGiven（例如 David Allan）。

schools

⚪◗ DESCRIBE schools
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ schoolID    │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ name_full   │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ city        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ state       │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ country     │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

包含学校的详细信息。例如，这是表中的一行数据：

abilchrist│Abilene Christian University│Abilene│TX│USA

teams

⚪◗ DESCRIBE teams
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ yearID      │ SMALLINT    │ NO      │ PRI     │ NULL    │ NULL    │
│ lgID        │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ teamID      │ VARCHAR     │ NO      │ PRI     │ NULL    │ NULL    │
│ franchID    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ divID       │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ teamRank    │ SMALLINT    │ YES     │ NULL    │ NULL    │ NULL    │
│ name        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ attendance  │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

该表包含团队的详细信息。例如，这是表中的一行数据：

1884│UA│ALT│ALT│NULL│10│Altoona Mountain City│NULL

完整性检查

统计表中的行数

D select count(*) from appearances;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│  115355  │
└──────────┘

下图展示了这些表的模式：

构建 SQL 查询

现在开始构建 SQL 查询并将它们放入占位文件中。您可以先使用 SQLite。

Q1 [0 分]

此查询的目的是确保您的输出格式与我们的自动评分脚本的输出格式完全匹配。

文件： q1_sample.duckdb.sql

详情： 按字母顺序列出所有活跃的分区。

答案：以下是正确的 SQL 查询和预期输出：

D SELECT DISTINCT division FROM divisions WHERE active = 'Y' ORDER BY division;
AL Central
AL East
AL West
NL Central
NL East
NL West

您应该将此 SQL 查询放入提交目录（placeholder）中的相应文件（q1_sample.duckdb.sql）中。

Q2 [20 分]

找出在任何单次出场中本垒打（HR）最高的十个人，不考虑年份或球队。只考虑曾经在 PA 的学校打过球的人。按本垒打数从多到少排序，然后按名字字母顺序排列。

文件： q2_hr.duckdb.sql

提示： || 可能对构造名称字段很有用。名称应按 {FIRST} ({GIVEN}) {LAST} 的格式组装。球员在任何单次出场中的最高本垒打数可以通过对给定球员的 appearances 中所有年份和球队进行聚合来找到。

您的输出应如下所示：

name|max_hr_appearance

您的第一行应如下所示：

Bobby (Robert Leigh) Higginson|30

Q3 [20 分]

找出十对（球员，球队），其中球员在 1999 年之后在活跃联盟中赢得了 Gold Glove 奖项，并且打击出场的比赛场次多于该球队自 1999 年以来的平均打击出场数。按不同获奖年份从多到少排序，然后按 given name 字母顺序排列。

文件： q3_mvp.duckdb.sql

提示： 只考虑 awardID 匹配 Gold Glove 的记录。球队平均值可以通过对自 1999 年以来每个球队球员的 appearances 中的打击出场数取平均来计算。您可能会发现 DuckDB 中的相关子查询很有用。

您的输出应如下所示：

nameGiven|teamID|distinct_years

您的第一行应如下所示：

Ichiro|SEA|10

Q4 [30 分]

考虑事件 E：对于给定的（球队，年份），球队有超过 5 名不同球员获得奖项，且某位经理在同一年获得了奖项。对于所有活跃联盟，找出事件 E 发生过超过一次的球队。按事件 E 发生次数从多到少排序，然后按球队名称字母顺序排列。

文件： q4_award.duckdb.sql

提示： 您可能会发现 DuckDB 中的相关子查询和 CTE 很有用。考虑将问题分解为子问题并组合它们的结果。

您的输出应如下所示：

league|team_name|distinct_years

输出中的一行如下所示：

National League|Atlanta Braves|3

Q5 [20 分]

找出 10 位入选名人堂的球员。对于每位入选的球员，找出他们曾一起出场的最早队友。如果有多位队友，选择按队友构造名称字母顺序排列的第一位队友。按名人堂球员的构造名称字母顺序排列。

文件： q5_hof.duckdb.sql

详情： 要找到球员的队友，找出两人都作为 appearances 在同一年的同一支球队效力的记录。

提示： 您可能会发现 DuckDB 中的Lateral Joins很有用。所有名称应按 {FIRST} ({GIVEN}) {LAST} 的格式组装。

您的输出应如下所示：

hof_player_name|earliest_teammate_name|earliest_teammate_year

您的第一行应如下所示：

Addie (Adrian) Joss|Bill (William Henry) Bernhard|1902

Q6 [10 分]

联盟总监要求您向 teams 表中插入新记录。对于 2024 年的所有球队，使用 franchID 作为 teamID 插入一条新记录。如果插入因冲突而失败，将现有记录的 attendance 设置为 -1。

文件： q6_upsert.duckdb.sql

详情： 我们将通过选择表中 2024 年的所有球队并按每列依次排序来进行评估。

SELECT yearID, lgID, teamID, franchID, teamRank, attendance
FROM teams
WHERE yearID = 2024
ORDER BY lgID, teamID, franchID, teamRank, attendance;

提示考虑如何在一条语句中同时插入和更新现有行。

运行选择语句后，应存在以下两行：

2024|AL|HOU|HOU|1|-1
2024|AL|KCA|KCR|2|1658347

评分标准

每份提交将根据 SQL 查询是否从数据库中获取了预期的元组集合来评分。每个 SQL 查询只允许一条语句。请注意，您的 SQL 查询将通过将其输出（即元组集合）与正确输出进行比较来进行自动评分。对于您的查询，输出列的顺序很重要；列名则不重要。

迟交政策

请参见课程大纲中的迟交政策。

提交

我们使用 Gradescope 的自动评分器进行评分，以便为您提供即时反馈。完成作业后，您可以将压缩文件夹 submission.zip（仅一个文件）提交到 Gradescope：

https://www.gradescope.com/courses/1074751

重要提示：使用在 Piazza 上公布的 Gradescope 课程代码。

我们将使用类似于 diff 的函数来比较输出文件。您可以随时多次提交答案。

协作政策

每位学生必须独立完成本次作业。
学生可以与他人讨论项目的高层次细节。
学生不允许在与其他学生的小组会议后复制白板上的内容。
学生不允许复制其他同学的答案。

警告：本项目的所有代码必须是您自己的。您不得从其他学生或网上找到的其他来源复制源代码。抄袭行为绝不被容忍。更多信息请参见 CMU 的学术诚信政策。

作业 #1 - SQL 最后更新：Aug 28, 2025

概述

规格说明

占位文件夹

说明

DuckDB

检查模式

appearances

awardsmanagers

awardsplayers

collegeplaying

divisions

halloffame

leagues

managers

people

schools

teams

完整性检查

构建 SQL 查询

Q1 [0 分]

Q2 [20 分]

Q3 [20 分]

Q4 [30 分]

Q5 [20 分]

Q6 [10 分]

评分标准

迟交政策

提交

协作政策