cmu 15445 homework 1 sql

记录学习这门课的学习过程，lab目前写完project0 ，分享自己的思路和做法，仅供参考，欢迎一起友好探讨。

HomeWork 1

概论：这个作业简单的通过10个作业来让小白熟悉sql语法。个人认为按着顺序一个一个做，思路真的会比较清晰，是很好的sql学习的入门作业。

评判方式:官网lab只能评测project，不能评测homework，有大佬写了个脚本Learning/cmu15445/homework1/q5_german_type_ratings.sql at main · MondayCha/Learning (github.com)

参考资料：SQL学习地址

CMU15445 作业1

SQL学习

准备环境：作业 #1 - SQL |CMU 15-445/645 ：： 数据库系统简介（2023 年春季）

需要安装sqlite3，然后根据官网的指示，输入命令行创建环境（文件夹）。

之后就可以做了。

入门速成

当使用 SQL 查询语句时，以下是基本结构：

sqlCopy codeSELECT column1, column2, ... FROM table_name WHERE condition;

1. SELECT子句： 指定你要检索的列。你可以选择检索表中的所有列，也可以指定特定的列。用逗号分隔列名。

2. FROM子句： 指定你要从中检索数据的表的名称。表名跟在 FROM 关键字后面。

3. WHERE子句（可选）： 用于筛选行的条件。只有满足条件的行才会包含在结果中。条件可以包括比较运算符（例如 =, <, >, <> 等）和逻辑运算符（例如 AND, OR）。

这些子句协同工作以执行查询。查询的基本逻辑如下：

1. FROM子句： 先从指定的表中获取数据。

2. WHERE子句： 然后，根据 WHERE 子句中的条件过滤出满足条件的行。

3. SELECT子句： 最后，从剩余的行中选择指定的列。

以下是一个简单的示例，假设我们有一个名为 employees 的表：

sqlCopy codeSELECT employee_id, first_name, last_name FROM employees WHERE department = 'IT' AND salary > 50000;

这个查询的逻辑是从 employees 表中选择 employee_id、first_name 和 last_name 列，但只选择部门为 'IT' 且薪水超过 50000 的员工。这就是 SELECT、FROM 和 WHERE 子句如何协同工作的基本概念。

如果想要将其拼接起来，使用“||”

作业解答

Q1 [0 分] （q1_sample）：

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

详：按字母顺序列出演职人员的前十个角色。

答案：我们输出字母顺序的前十个的category，然后category在crew表，所以这个作业是让我们去试着写一个程序。语句结构和上面速成一致，不难理解LIMIT就是截止的意思。

SELECT DISTINCT(category) FROM crew ORDER BY category LIMIT 10;

Q2 [5 分] （q2_not_the_same_title）：

查找 10 部具有最新首映日期的动作电影，其原始标题与其主要标题不同。

详：打印首映年份，然后以特殊格式打印两个标题。列出两个标题的列应采用以下格式：即使作品被归类为多种流派，只要是其中一种流派即可。另请注意，首映年份有可能在未来。如果多部电影在同一年首映，请按字母顺序排序。

答案：在TITLES下面有这样这句话

For this assignment, we will focus on the fields title_id (e.g. "tt0088763"), type (e.g. "movie"), primary_title (e.g. "Back to the Future"), premiered (e.g. "1985"), ended (in this case NULL) and genres (e.g. "Adventure,Comedy,Sci-Fi").

再根据schema2022.png (879×541) (cmu.edu) 我们知道titles里的premiered就是电影的日期

于是我们就知道和上一个差不多，通过premiered从大到小排序再按照字典序排序就是本题的答案

SELECT titles.premiered, titles.primary_title || '(' || titles.original_title || ')' AS formatted_titles FROM titles WHERE titles.type = 'movie' AND titles.genres LIKE '%Action%' AND titles.original_title != titles.primary_title ORDER BY titles.premiered DESC, formatted_titles ASC LIMIT 10;

Q3 [5 分] （q3_longest_running_tv）：

查找播放时间最长的 20 部电视剧。

详：打印标题和该系列的运行年份。 该系列必须有一个非Null首映年份。如果结束日期为 ，则假定它是当前年份 （2023）。如果多部电视剧（tvSeries）的播出年数相同，请按字母顺序排列。打印前 20 个结果。 输出应采用以下格式：

答案：这个和上面那个其实没有大改动，如果结束时间是NULL的就默认为2023年

SELECT primary_title, CASE WHEN ended IS NULL THEN 2023 - premiered ELSE ended - premiered END AS years_running FROM titles WHERE type = 'tvSeries' AND premiered is NOT NULL ORDER BY years_running DESC, primary_title ASC LIMIT 20;

第四季度 [10 分] （q4_directors_in_each_decade）：

列出自 1900 年以来每个十年出生的director人数。

详：通过构造一个字符串，以更高级的格式打印十年 看起来像这样： .按十年对结果进行排序。 您的输出应如下所示： 第一行应如下所示： 1990s``DECADE|NUM_DIRECTORS``1900s|376

答案：由于出生这个信息来自people这个表，职责来则crew这个表，所以我们需要一个内联函数将两个表链接起来，注意可能会有重复的数据，所以我们用DISTNCT来除掉这个重复的，之后就是算一个人数进行排序，也就是说在这个作业里学习了新的语句 ——JOIN

SELECT CAST((born / 10) *10 AS INTEGER) || 's' AS decade , COUNT(DISTINCT people.person_id) FROM people JOIN crew ON people.person_id = crew.person_id WHERE born >= 1900 AND crew.category = 'director' GROUP BY decade order BY decade;

Q5 [10 分] （q5_german_type_ratings）：

计算具有德语标题的不同类型的作品的统计数据。

详：计算平均值（四舍五入到小数点后 2 位）、最小值和最大值 对于具有德语标题和akas的每种类型的作品 types 的数据类型为 imdbDisplay 或 original。按每种标题类型的平均评分对输出进行排序。 输出应采用以下格式： 第一行应如下所示： imdbDisplay``original``TITLE_TYPE|AVG_RATING|MIN_RATING|MAX_RATING``movie|6.65|3.4|8.2

答案：这个题和上面题没有区别，学习新的函数是 AVG，MAX，MIN，具体思路还是通过相同的属性将多个表链接（JOIN）然后查询

SELECT titles.type, ROUND(AVG(ratings.rating), 2) AS average, MIN(ratings.rating), MAX(ratings.rating) FROM titles JOIN akas ON titles.title_id = akas.title_id, ratings ON ratings.title_id = titles.title_id WHERE akas.language = 'de' AND akas.types IN ('imdbDisplay', 'original') GROUP BY titles.type ORDER BY average;

Q6 [10 分] （q6_cruiseing_altitude）：

确定与带有“Cruise”的人一起最受欢迎的作品 姓名，出生于1962年。

详：获得剧组中有人得票最多的作品 他们的名字中有“克鲁斯”，出生于 1962 年。返回 工作和票数，并仅列出前 10 个结果，顺序从 从多到少的选票。请确保输出的格式如下：Top Gun|408389

答案：返回的是标题和票数，还是将其连接起来然后按照votes进行从大到小排序

SELECT titles.primary_title, ratings.votes FROM titles JOIN crew ON titles.title_id = crew.title_id JOIN people ON crew.person_id = people.person_id JOIN ratings ON titles.title_id = ratings.title_id WHERE people.name LIKE '%Cruise%' AND people.born = 1962 ORDER BY ratings.votes DESC LIMIT 10;

Q7 [15 分] （q7_year_of_thieves）：

列出同年首播的作品数量，《军队的军队》 盗贼》首映。

详：仅打印作品总数。答案应该包括 “盗贼军团”本身。对于这个问题，通过以下方式确定不同的作品 他们的名字，而不是他们的名字。title_id

答案：直接数有多少个电影的首映时间和这个电影是一样的就行，因为在同一个表查询就比较方便

SELECT COUNT(*) FROM titles WHERE titles.premiered = (SELECT premiered FROM titles WHERE titles.primary_title = 'Army of Thieves');

Q8 [15分] （q8_kidman_colleagues）：

列出出演过一部作品的所有不同演员 妮可·基德曼（Nicole Kidman，1967年出生）。

详：仅按字母顺序打印演员姓名 次序。答案应该包括妮可·基德曼本人。每个名称都应 在输出中只出现一次。 注意：如架构中所述，在考虑 船员中的个人，请参阅现场。角色 “演员”和“女演员”是不同的，应该这样解释

SELECT DISTINCT(p.name) AS name FROM crew c JOIN people p ON c.person_id = p.person_id WHERE c.title_id IN ( SELECT title_id FROM crew WHERE person_id = ( SELECT person_id FROM people WHERE name = 'Nicole Kidman' AND born = 1967 ) ) AND (c.category = 'actor' OR c.category = 'actress') ORDER BY name; ​

Q9 [15分] （q9_9th_decile_ratings）：

对于所有出生于 1955 年的人，请获得他们的名字和所有的平均评分 他们在整个职业生涯中都参与了电影。输出 9 的十分位数 个人，以他们的平均职业电影评级来衡量。

详：计算 1955 年出生的每个人的平均评分 只有他们参与过的电影。计算每个分位数 使用 NTILE（10） 的个人平均评分。 确保您的输出格式如下（将平均评分四舍五入为 最接近的百分之一，结果应按复合值排序 他们的评级降序，其次是他们的名字，按字母顺序排列）： 注意：您应该在处理平均职业生涯后取分位数 个人的电影评分。换句话说，找到具有 在所有个人中，平均职业电影评分在第 9 位。Stanley Nelson|7.13

答案：对于计算每个人电影的平均分数应该先将三个表连接起来然后进行计算，然后再根据NTILE进行排序，最终输出ranking为9的即可

WITH AvgRatings AS ( SELECT people.name, AVG(ratings.rating) AS AVG_RATING FROM people JOIN crew ON people.person_id = crew.person_id JOIN titles ON crew.title_id = titles.title_id JOIN ratings ON titles.title_id = ratings.title_id WHERE people.born = 1955 AND titles.type = 'movie' GROUP BY people.person_id ), RankedRatings AS ( SELECT name, ROUND(AVG_RATING, 2) AS AVG_RATING, NTILE(10) OVER (ORDER BY AVG_RATING ASC) AS ranking FROM AvgRatings ) SELECT name, AVG_RATING FROM RankedRatings WHERE ranking = 9 ORDER BY AVG_RATING DESC, name ASC; ​

Q10 [15分] （q10_house_of_the_dragon）：

串联电视剧《House of the Dragon“作为逗号分隔值的字符串，按字母顺序排列 标题。

详：查找新电视节目“House of the 龙“并按字母顺序排列。打印包含所有 这些标题用逗号分隔。 提示：您可能会发现递归 CTE 很有用。 注意：两个标题是不同的，即使它们仅在 资本化。逗号分隔值结果中的元素应为 用逗号和空格分隔，例如“foo， bar”。

答案：

NumberedTitles AS ( SELECT ROW_NUMBER() OVER () AS num, title FROM DragonTitles ), ​ ConcatenatedTitles AS ( SELECT num, title FROM NumberedTitles WHERE num = 1 ​ UNION ​ SELECT NumberedTitles.num, ConcatenatedTitles.title || ', ' || NumberedTitles.title FROM NumberedTitles JOIN ConcatenatedTitles ON NumberedTitles.num = ConcatenatedTitles.num + 1 )