2-sql练习

1

考虑并成为2D平面上的两个点，其中分别是北纬（LAT_N）的最小值和最大值，分别是车站中西部经度（LONG_W）的最小值和最大值。

查询点之间的欧几里德距离，并格式化您的答案以显示十进制数字。

输入格式

STATION表描述如下：

LAT_N是北纬，LONG_W是西经。

p1(x1,y1) p2(x2,y2)

create index idx_lat_n on station (lat_n);

create index idx_long_w on station (long_w);

select cast(sqrt(power(min(lat_n)-max(lat_n), 2)+power(min(long_w)-max(long_w), 2)) as decimal(18, 4))
from station;

这个查询操作的作用是计算"station"表中"lat_n"列的最小值和最大值之差，以及"long_w"列的最小值和最大值之差，然后将两个差值的平方和开方，并将结果转换为精度为18位和4位小数的十进制数。最终的结果是一个代表"station"表中所有行的位置距离的估计值。

要点：

CAST(expression AS type)
SELECT CAST('123' AS INT);
decimal(18, 4)
“Decimal”表示“十进制”，所以“decimal(18, 4)”是指一个具有18位精度，其中小数点后有4位的十进制数。

2

一个*中位数被定义为数据集的上半部分和下半部分之间的一个数字。从STATION查询北部纬度*（LAT_N）的中位数，并将答案四舍五入到小数点后几位。

输入格式

STATION表描述如下：

LAT_N是北纬，LONG_W是西经。

SELECT ROUND(s.lat_n, 4) FROM station s WHERE ( SELECT COUNT(lat_n) FROM station WHERE s.lat_n > lat_n ) = ( SELECT COUNT(lat_n) FROM station WHERE s.lat_n < lat_n );
这个SQL查询语句的作用是选择满足以下条件的“s”表中的“lat_n”列，并将其四舍五入到小数点后第四位：

在所有“s”表中的行中，该行的“lat_n”值等于该行“lat_n”值左右两侧行“lat_n”值的数量之和。

具体而言，查询将使用嵌套的子查询来计算每行“lat_n”值左右两侧行“lat_n”值的数量。如果左侧行数量等于右侧行数量，则该行将被选择。然后，使用“ROUND”函数将“lat_n”值四舍五入到小数点后第四位。

要点：

ROUND(number, precision)
“ROUND”是SQL函数之一，用于将数字四舍五入到指定的小数位数。

3

给你两张表：学生和年级。学生包含三列ID、姓名和标记。

成绩包含以下数据：

Ketty给Eve一个任务来生成包含三列的报告：姓名、成绩和标记。Ketty不想要那些成绩低于8分的学生的姓名。报告必须按年级降序排列——即先输入高年级。如果有不止一个学生分配了相同的成绩（8-10分），请按字母顺序排列这些特定的学生。最后，如果成绩低于8分，请使用“NULL”作为他们的名字，并按成绩降序排列。如果有多个学生被分配了相同的分数（1-7），请按分数升序排列这些学生。

Write a query to help Eve.

样本输入

样本输出

Maria 10 99
Jane 9 81
Julia 9 88 
Scarlet 8 78
NULL 7 63
NULL 7 68

注

如果等级小于8，则打印“NULL”作为名称。

说明

考虑下表中分配给学生的分数：

所以，以下学生得到了8、9或10分：

• 玛丽亚（10年级）
• 简（9年级）
• 朱莉娅（9年级）
• 猩红（8年级）

Select IF(Grade > 7, Students.Name,'NULL') as StuName, Grades.Grade, Students.Marks from Students LEFT JOIN GRADES ON Min_Mark <= Marks and Marks <= Max_Mark ORDER BY Grades.Grade DESC, StuName

要点： IF(Grade > 7, Students.Name,‘NULL’)

IF` 是 SQL 中的条件表达式，其语法为 `IF(condition, value_if_true, value_if_false)

4

朱莉娅刚刚完成了一场编码竞赛，她需要你的帮助来组装排行榜！写一个查询来打印在多个挑战中获得满分的黑客的各自hacker_id和姓名。按黑客获得满分的挑战总数降序排列您的输出。如果多个黑客在相同数量的挑战中获得满分，然后按升序hacker_id排序。

---

输入格式

下表包含竞赛数据：

• 黑客：的hacker_id是黑客的ID，并且姓名是黑客的名字。
• 难度：的difficult_level是挑战的难度，以及得分是难度级别的挑战分数。
• 挑战：的challenge_id是挑战的ID，是hacker_id是创建挑战的黑客的ID，并且difficulty_level是挑战的难度级别。
• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge that the submission belongs to, and score is the score of the submission.

---

Sample Input

黑客表： 难度表： Challenges Table: Submissions Table:

样本输出

90411 Joe

说明

黑客86870挑战71055获得30分，难度等级为2，因此86870获得了这次挑战的满分。

黑客90411挑战71055获得30分，难度等级为2，所以90411这次挑战获得了满分。

黑客90411挑战66730获得了100分，难度等级为6，所以90411这次挑战获得了满分。

只有黑客90411设法在多个挑战中获得满分，因此我们将他们的hacker_id和名称打印为空格分隔的值。

select s.hacker_id, h.name
from Hackers as h
join Submissions as s  on s.hacker_id = h.hacker_id
join Challenges as c on s.challenge_id = c.challenge_id
join Difficulty as d on d.difficulty_level = c.difficulty_level
where d.score = s.score
group by hacker_id, h.name
having count(s.challenge_id) > 1
order by count(s.challenge_id) desc, s.hacker_id;


在这个查询中，使用 GROUP BY 子句是为了对查询结果按照 hacker_id 和 name 进行分组，以便计算每个黑客提交的挑战数量。

由于这个查询使用了多个表进行连接，查询结果会返回每个连接组合的结果。但是，我们只关心每个黑客提交的挑战数量，因此需要将结果按照 hacker_id 和 name 进行分组。

使用 GROUP BY 子句后，查询结果将按照 hacker_id 和 name 进行分组，并且使用 count 聚合函数计算每个组中 challenge_id 的数量。然后，使用 having 子句过滤掉只有一个小于或等于 1 的计数结果的组，只保留提交挑战数量大于 1 的组。

最后，使用 order by 子句按照提交挑战数量从大到小排序，以便得到按照提交挑战数量从高到低的顺序的结果集。

要点：group by hacker_id, h.name
having count(s.challenge_id) > 1
order by count(s.challenge_id) desc, s.hacker_id;

5

哈利·波特和他的朋友们和罗恩在奥利凡德家，终于更换了查理的旧魔杖。

赫敏决定最好的选择方式是确定购买每根高功率和高年龄的非邪恶魔杖所需的最低金帆船数量。写一个查询来打印罗恩感兴趣的魔杖的ID、年龄、coins_needed和功率，按功率降序排序。如果不止一根魔杖具有相同的功率，请按年龄降序排序结果。

输入格式

下表包含奥利凡德库存中魔杖的数据：

• 魔杖：的id是魔杖的id，代码是魔杖的代码，coins_needed是购买魔杖所需的金帆船总数，以及权力表示魔杖的质量（功率越高，魔杖越好）。
• Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs, and , then and .

---

Sample Input

魔杖表： Wands_Property表：

样本输出

9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1

说明

魔杖的数据45岁（代码1）：

• 需要的最小帆船数量
• 需要的最小帆船数量

魔杖的数据40岁（代码2）：

• 需要的最小帆船数量
• 需要的最小帆船数量
• 需要的最小帆船数量
• 需要的最小帆船数量

魔杖的数据20岁（代码4）：

• 需要的最小帆船数量
• 需要的最小帆船数量
• 需要的最小帆船数量

魔杖的数据17岁（代码5）：

• 需要的最小帆船数量
• 需要的最小帆船数量

select id, age, coins_needed, power 
from wands as w

join Wands_Property as wp
on w.code = wp.code

where (age, power, coins_needed) in 
            (select age, power, min(coins_needed)
            from wands as w
            join Wands_Property as wp
            on w.code = wp.code

            where wp.is_evil = 0
            group by age, power)

order by power desc, age desc;

魔杖的年龄（age）和力量（power）与子查询中返回的年龄和力量相等；
魔杖的硬币需求（coins_needed）等于子查询中返回的年龄和力量组合的最小硬币需求。
子查询的作用是找到满足以下条件的年龄和力量的组合：

在Wands_Property表中，对于每个年龄和力量的组合，找到一个非邪恶（is_evil = 0）的魔杖；
返回这些非邪恶魔杖的年龄、力量和最小的硬币需求（使用聚合函数MIN）。
最终结果按照力量（power）降序排列，如果力量相同则按照年龄（age）降序排列。

power desc, age desc;

魔杖的年龄（age）和力量（power）与子查询中返回的年龄和力量相等；
魔杖的硬币需求（coins_needed）等于子查询中返回的年龄和力量组合的最小硬币需求。
子查询的作用是找到满足以下条件的年龄和力量的组合：

在Wands_Property表中，对于每个年龄和力量的组合，找到一个非邪恶（is_evil = 0）的魔杖；
返回这些非邪恶魔杖的年龄、力量和最小的硬币需求（使用聚合函数MIN）。
最终结果按照力量（power）降序排列，如果力量相同则按照年龄（age）降序排列。

要点： in