CMU 15-445 Homework 1

./pics/image-20230627101614403.png

找到 title 里带有 Sci-Fi 的 tuples,按照 runtime desc、title asc 的顺序输出前 10 个 primary_title、premiered 和 runtime_minutes,并且 runtime_minutes 后面需要加上 (mins)。

输出格式:Cicak-Man 2: Planet Hitam|2008|999 (mins)

  • 使用 SELECT "String" || ... 来按照格式输出
  • 使用 LIMIT 来限制数量
  • 使用 ORDER BY 来排序
  • 使用 LIKE 来模糊匹配

找到最老的前 20 个,出生在 1900 之后(包括)的人名。born 为 NULL 的人到现在(2022)年还没死。按照 age desc,name asc 的顺序输出 name 和 age。

输出格式:NAME|AGE

  • 使用 died - born AS age 来进行计算
  • 判断 arg 为空的方法是IS [NOT] NULL
  • 用 UNION ALL 把多个选择好的结果合并在一起

找到在 crew table 里出现次数最多前 20 个人。按照次数 desc 的顺序输出 name 和 count。

输出格式:NAME|NUM_APPEARANCES

  • 使用 GRUOP BY 来把 person_id 分别进行处理
  • 使用 COUNT() 来计算每个 person_id 出现的次数

按照每个年代计算平均 rating(保留两位小数),最高 rating、最低 rating 以及每个年代的发布数量。排除未首映的标题(即 premiered 为 NULL )。按照 1990s 的格式输出年代。按照平均 rating desc,年代 asc ,数量 desc 的顺序输出。

输出格式:DECADE|AVG_RATING|TOP_RATING|MIN_RATING|NUM_RELEASES

  • decade 用 premiered / 10 * 10 来计算,因为 premiered 是 INT 类型
  • 控制精度用 ROUND(num, precision),precision 表示要保留的精度位数

找到前 10 个制作人里有名字带有 Cruise 的,出生于 1962 的,votes 最高的电影。按照 votes desc 输出。

输出格式:Top Gun|408389

本题主要是考察多个 table 之间的调用,其实只需要在 WHERE 里把 title_id 绑定完整就好了,没有太多需要注意的

只输出和“Army of Thieves”同一年首映的作品的数量。答案里需要包括“Army of Thieves”。对于本题,按照 title_id 来区分不同作品,而不是 name。

  • 用 Nested Queries 来先把 “Army of Thieves” 选出来
  • 对 title_id 使用 DISTINCT() 来去除重复。

列出与1967年出生的 Nicole Kidman 共同出演过作品的所有不同男演员和女演员。按照字母顺序打印出演员的姓名。答案应包括妮可·基德曼本人。每个姓名在输出中只能出现一次。在考虑时,请参考字段“category”。角色“actor”和“actress”是不同的,应分别计算。

  • 用 GROUP BY 把 actor 和 sctress 分组计算
  • 使用 OR 把 categroy='actor' 和 category='actress' 合起来

对于所有出生于 1955 年的人,获取他的名字和他所参与的所有 movie 的平均 rating(保留 2 位小数)。然后将其用 NTILE(10) 分成 10 组,输出第 9 组。

输出格式:Stanley Nelson|7.13

  • 使用 Nested Queries 先把符合条件的人和他的 rating 计算出来

NTILE() 格式:

1
2
3
4
NTILE(expression) OVER ( 
	PARTITION BY expression1, expression2,...
	ORDER BY expression1 [ASC | DESC]expression2,
)
  • PARTITION BY:这个子句用于在分配桶之前对结果集进行分区。可以根据一列或多列的值对结果集进行分区,每个分区都会独立地进行桶的分配。换句话说,每个分区内部的行将独立地分配到各个桶中。例如,如果使用PARTITION BY子句将结果集按照员工部门进行分区,那么每个部门的员工将独立地分配到各个桶中。
  • ORDER BY:这个子句用于指定在分配桶之前对结果集进行排序的顺序。可以根据一列或多列对结果集进行排序。排序的顺序将决定行在分配到桶之前的顺序。例如,如果使用ORDER BY子句按照员工的薪水对结果集进行排序,那么将按照薪水高到低按顺序分配到桶中。

使用这两个子句的组合可以实现更精细的桶分配逻辑。例如,可以使用PARTITION BY将结果集按照某个属性进行分区,然后使用ORDER BY在每个分区内按照另一个属性对行进行排序。这样可以确保在每个分区内,行按照指定的顺序进行桶分配。

在 akas 里找到 type 为 tvSeries、primary_title 为 House of the Dragon 的 title_id 的所有 title ,用逗号将其连接起来输出。

  • 用 Recursive CTEs 实现递归
  • 使用 GRUOP_CONCAT(<column>, '<separator>') 来把一列合并

在作业准备教程的第六步要求我们运行以下指令:

1
2
3
4
5
6
7
8
CREATE INDEX ix_people_name ON people (name);
CREATE INDEX ix_titles_type ON titles (type);
CREATE INDEX ix_titles_primary_title ON titles (primary_title);
CREATE INDEX ix_titles_original_title ON titles (original_title);
CREATE INDEX ix_akas_title_id ON akas (title_id);
CREATE INDEX ix_akas_title ON akas (title);
CREATE INDEX ix_crew_title_id ON crew (title_id);
CREATE INDEX ix_crew_person_id ON crew (person_id);

这些指令的作用是生成 sqlite 的 index 索引,这些 index 是改善数据检索操作速度的数据库结构。通过创建索引,可以大大加快涉及搜索、排序或基于索引列过滤数据的查询速度。如果没有索引,数据库需要执行全表扫描,检查每一行以找到所需的信息。

在数据库中,SELECT DISTINCT语句用于从数据库表中选择唯一的、不重复的记录。

例如,考虑以下示例表格:

ID Name City
1 John New York
2 Alice London
3 John Paris
4 Michael Berlin
5 Alice Sydney

如果你使用以下SQL查询:

1
SELECT DISTINCT(Name) FROM Customers;

它将返回以下结果:

Name
John
Alice
Michael

GROUP BY 用于在 aggregation function 中分组计算。

aggregation function 例如 AVG()、SUM() 等是对括号内指定的所有数据进行计算。现在如果我们需要统计每个 id 的出现个数,而不是所有 id 的出现个数,那么我们的需求就是现将 id 进行分组,而这里我们就将每个相同的 id 各自分为一组,然后对每个组进行计算。使用 GROUP BY id。