CS 61A 春季2024 作业10
家庭作业 10: SQL
截止时间：4月25日（周四）晚11:59
说明事项
下载 hw10.zip。
提交： 完成作业后，请将所有编辑过的代码文件上传至 Gradescope。截止日期前可多次提交，最终提交版本将被评分。请务必检查确认已成功提交。更多提交说明请参考实验 0。
使用 Ok： 若您在使用 Ok 时遇到任何问题，请参考本指南。
评分标准： 本作业根据正确率评分，每错一题扣一分。总分2分。
要检查进度，可以直接运行 sqlite3：
python3 sqlite_shell.py --init hw10.sql
建议使用 ok 检查代码：
python3 ok
必做题
视频教程
这些视频将帮助你更好地完成本次作业的编程题目。
观看视频前，请先登录您的 berkeley.edu 邮箱。
SQL
狗狗信息
在接下来的每个问题中，你需要基于以下数据表创建新的数据表。
CREATE TABLE parents AS
SELECT "abraham" AS parent, "barack" AS child UNION
SELECT "abraham" , "clinton" UNION
SELECT "delano" , "herbert" UNION
SELECT "fillmore" , "abraham" UNION
SELECT "fillmore" , "delano" UNION
SELECT "fillmore" , "grover" UNION
SELECT "eisenhower" , "fillmore";
CREATE TABLE dogs AS
SELECT "abraham" AS name, "long" AS fur, 26 AS height UNION
SELECT "barack" , "short" , 52 UNION
SELECT "clinton" , "long" , 47 UNION
SELECT "delano" , "long" , 46 UNION
SELECT "eisenhower" , "short" , 35 UNION
SELECT "fillmore" , "curly" , 32 UNION
SELECT "grover" , "short" , 28 UNION
SELECT "herbert" , "curly" , 31;
CREATE TABLE sizes AS
SELECT "toy" AS size, 24 AS min, 28 AS max UNION
SELECT "mini" , 28 , 35 UNION
SELECT "medium" , 35 , 45 UNION
SELECT "standard" , 45 , 60;
即使数据表中的数值发生变化，你创建的数据表也应能正常工作。例如，如果题目要求列出所有名字以 'h' 开头的狗狗，你应该使用以下 SQL 语句：
SELECT name FROM dogs WHERE "h" <= name AND name < "i";
不要直接假设 dogs 表的内容如上所示，然后直接写
SELECT "herbert";
如果将 grover 的名字改成 hoover，或者添加一行名字是 harry 的数据，之前的查询仍然有效。
Q1：按父母身高排序的狗
创建一个名为 by_parent_height 的表，该表包含所有有 parent 的狗的名字列，并按照父母狗的身高从高到低排序。
-- 所有有父母的狗，按父母身高从高到低排列
CREATE TABLE by_parent_height AS
SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
例如，fillmore 的父母是 eisenhower，身高为 35，因此应该出现在父母是 fillmore，身高为 32 的 grover 之前。父母身高相同的狗的名字应该以任何顺序一起出现。例如，barack 和 clinton 都应该出现在最后，但任何一个都可以排在另一个之前。
sqlite> select * from by_parent_height;
herbert
fillmore
abraham
delano
grover
barack
clinton
使用 Ok 来测试你的代码：
python3 ok -q by_parent_height
Q2：狗狗的尺寸
国际犬业联合会 (Fédération Cynologique Internationale) 将标准贵宾犬的身高定义为 45 厘米以上，60 厘米以下。sizes 表描述了这种和其他类似的分类，其中狗的身高必须超过 min 且小于或等于 max 才能被归类为 size。
创建一个名为 size_of_dogs 的表，其中包含两列，分别为每只狗的 name 和 size。
-- 每只狗的尺寸
CREATE TABLE size_of_dogs AS
SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
输出应如下所示：
sqlite> select * from size_of_dogs;
abraham|toy
barack|standard
clinton|standard
delano|standard
eisenhower|mini
fillmore|mini
grover|toy
herbert|mini
使用 Ok 来测试你的代码：
python3 ok -q size_of_dogs
Q3：句子
有两对兄弟姐妹的尺寸相同。创建一个表，其中包含一行字符串，对应于每对兄弟姐妹。每个字符串都应该是一个描述这些尺寸相同的兄弟姐妹的句子。
-- 填写这个辅助表是可选的
CREATE TABLE siblings AS
SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
-- 关于尺寸相同的兄弟姐妹的句子
CREATE TABLE sentences AS
SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
每对兄弟姐妹应该只在输出中出现一次，并且兄弟姐妹应该按字母顺序排列（例如，"barack and clinton..." 而不是 "clinton and barack..."），如下所示：
sqlite> select * from sentences;
The two siblings, barack and clinton, have the same size: standard
The two siblings, abraham and grover, have the same size: toy
提示：首先，创建一个辅助表，其中包含每对兄弟姐妹。这将使构建主表时，比较兄弟姐妹的尺寸更容易。
提示：如果表需要和自身连接，请在
FROM子句中使用AS给每个表指定别名。提示：为了将两个字符串连接成一个字符串，请使用
||运算符。
使用 Ok 来测试你的代码：
python3 ok -q sentences
Q4：低方差
我们希望创建一个表，记录相同毛发类型的所有狗狗的身高范围（最高身高与最低身高之差）。但是，我们只考虑这样的毛发类型：该类型下每只狗狗的身高，都在该类型狗狗平均身高的30%以内。
例如，如果短毛狗的平均身高是10，那么为了能出现在结果中，所有短毛狗的身高必须在7到13之间。
为此，我们可以使用 MIN、MAX 和 AVG。我们需要找到平均身高，并确保：
- 没有低于平均值0.7倍的身高。
- 没有高于平均值1.3倍的身高。
您的输出应首先包含毛发类型，然后包含满足此标准的毛发类型的身高范围。
-- 记录所有身高与平均身高差异不超过30%的毛发类型，并计算其身高范围
CREATE TABLE low_variance AS
SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
-- 示例：
SELECT * FROM low_variance;
-- 预期输出：
-- curly|1
说明：长毛狗的平均身高为 39.7，因此低方差标准要求每只长毛狗的身高在 27.8 到 51.6 之间。但是，abraham 是一只长毛狗，身高为 26，超出了这个范围。对于短毛狗，barack 也超出了有效范围（请自行验证）。因此，短毛狗和长毛狗都不会包含在输出中。有两只卷毛狗：fillmore 身高 32，herbert 身高 31。这给出的身高范围为 1。
使用 Ok 来测试你的代码：
python3 ok -q low_variance
提交
通过将你编辑过的所有文件上传到相应的 Gradescope 作业来提交此作业。Lab 00 包含详细说明。
请务必将 hw10.sql 提交给自动评分器！
考试练习
以下是之前学期的一些 SQL 考试题，你可能会发现它们作为额外的考试练习很有用。