目录
第1关:交换工资
任务描述
本关任务:给定一张
tb_Salary
表,如下所示,有m = 男性
和f = 女性
的值。交换所有的f
和m
值(例如,将所有f
值更改为m
,反之亦然)。
id name sex salary 1 Elon f 7000 2 Donny f 8000 3 Carey m 6000 4 Karin f 9000 5 Larisa m 5500 6 Sora m 500 要求只使用一句更新
update
语句,且不允许含有任何select
语句完成任务。 ####相关知识 略 ####编程要求根据提示并仔细阅读右侧代码,在
Begin - End
区域内进行代码补充。提示
可能需要使用到 CASE 函数或 IF 函数,使用方法如下实例:
SELECT case ###如果 when sex='1' then '男' ###sex='1',则返回值'男' when sex='2' then '女' ###sex='2',则返回值'女' else '其他' ###其他的返回'其他’ end ###结束 from sys_user ###整体理解: 在sys_user表中如果sex='1',则返回值'男'如果sex='2',则返回值'女' 否则返回'其他’
select if(sex='1','男','女') as sex from sys_user; ###如果sex='1'则返回值'男' 否则返回值为'女'
测试说明
补充完代码后,点击测评,平台会对你编写的代码进行测试,当你的结果与预期输出一致时,即为通过。
预期输出:
+----+--------+-----+--------+ | id | name | sex | salary | +----+--------+-----+--------+ | 1 | Elon | m | 7000 | | 2 | Donny | m | 8000 | | 3 | Carey | f | 6000 | | 4 | karin | m | 9000 | | 5 | Larisa | f | 5500 | | 6 | Sora | f | 500 | +----+--------+-----+--------+
答案:
#请在此添加实现代码 ########## Begin ########## UPDATE tb_Salary SET sex = CASE WHEN sex = 'm' THEN 'f' WHEN sex = 'f' THEN 'm' ELSE sex END; ########## End ##########
第2关:换座位
任务描述
本关任务:改变相邻俩学生的座位。
小美是一所中学的信息科技老师,她有一张
tb_Seat
座位表,平时用来储存学生名字和与他们相对应的座位id
。
tb_Seat
表结构数据如下:
id name 1 Elon 2 Donny 3 Carey 4 Karin 5 Larisa 现在小美想改变相邻俩学生的座位(若学生人数为奇数,则无需改变最后一位同学的座位),现在需要你编写
SQL
输出小美想要的的结果。####相关知识 略 ####编程要求
请仔细阅读右侧代码,根据方法内的提示,在
Begin - End
区域内进行代码补充。
测试说明
补充完代码后,点击测评,平台会对你编写的代码进行测试,当你的结果与预期输出一致时,即为通过。
预期输出:
id name 1 Donny 2 Elon 3 Karin 4 Carey 5 Larisa
答案:
#请在此添加实现代码 ########## Begin ########## SELECT CASE WHEN MOD(id, 2) = 1 AND id <> (SELECT MAX(id) FROM tb_Seat) THEN id + 1 WHEN MOD(id, 2) = 0 THEN id - 1 ELSE id END AS id,name FROM tb_Seat ORDER BY id; ########## End ##########
第3关:分数排名
任务描述
本关任务:编写
SQL
查询来实现二种排名方式的分数排名。
score
表结构信息如下:
Id Score 1 3.52 2 3.65 3 4.23 4 3.85 5 4.23 6 3.65 如果两个分数相同,则两个分数排名(
Rank
)相同。情况一:平分后的下一个名次是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。例:
1、1、2、3、4、4。
情况二:排名是非连续的。例:
1、1、1、4、4、6。
相关知识
略
####编程要求
请仔细阅读右侧代码,根据方法内的提示,在
Begin - End
区域内进行代码补充。
答案:
#请在此添加实现代码 ########## Begin ########## SELECT Score, (SELECT COUNT(DISTINCT Score) FROM score WHERE Score >= s.Score) AS Rank FROM score s ORDER BY Rank; SELECT Score,(SELECT COUNT(Score) FROM score AS s2 WHERE s2.score >s1.score)+1 AS Rank FROM score s1 ORDER BY Rank; ########## End ##########
第4关:体育馆的人流量
任务描述
本关任务:某市建了一个新的体育馆,每日人流量信息被记录在
gymnasium
表中:序号 (id
)、日期 (date
)、 人流量 (visitors_flow
)。请编写一个查询语句,找出人流量的高峰期。高峰期时,至少连续三行记录中的人流量不少于
100
。
gymnasium
表结构数据如下:
id date visitors_flow 1 2019-01-01 58 2 2019-01-02 110 3 2019-01-03 123 4 2019-01-04 67 5 2019-01-05 168 6 2019-01-06 1352 7 2019-01-07 382 8 2019-01-08 326 9 2019-01-09 99 提示:每天只有一行记录,日期随着
id
的增加而增加。相关知识
略
####编程要求
请仔细阅读右侧代码,根据方法内的提示,在
Begin - End
区域内进行代码补充。
测试说明
补充完代码后,点击测评,平台会对你编写的代码进行测试,当你的结果与预期输出一致时,即为通过。
预期输出:
+----+------------+---------------+ | id | date | visitors_flow | +----+------------+---------------+ | 5 | 2019-01-05 | 168 | | 6 | 2019-01-06 | 1352 | | 7 | 2019-01-07 | 382 | | 8 | 2019-01-08 | 326 | +----+------------+---------------+
答案:
#请在此添加实现代码 ########## Begin ########## SELECT DISTINCT t1.* FROM gymnasium t1,gymnasium t2,gymnasium t3 WHERE t1.visitors_flow >= 100 AND t2.visitors_flow >= 100 AND t3.visitors_flow >= 100 AND ( (t1.id=t2.id-1 AND t2.id=t3.id-1) OR (t1.id=t2.id+1 AND t1.id=t3.id-1) OR (t1.id=t2.id+1 AND t2.id=t3.id+1) ) ORDER BY id; ########## End ##########
第5关:统计总成绩
任务描述
本关任务:计算每个班的语文总成绩和数学总成绩,要求科目中低于
60
分的成绩不记录总成绩。
tb_score
结构数据:
name chinese maths A 89 98 B 99 89 C 55 66 D 88 66 E 55 66 F 88 99
tb_class
表结构数据:
stuname classname A C1 B C2 C C3 D C2 E C1 F C3 相关知识
略
####编程要求
请仔细阅读右侧代码,根据方法内的提示,在
Begin - End
区域内进行代码补充。
测试说明
补充完代码后,点击测评,平台会对你编写的代码进行测试,当你的结果与预期输出一致时,即为通过。
预期输出:
+-----------+---------+-------+ | classname | chinese | maths | +-----------+---------+-------+ | C1 | 89 | 164 | | C2 | 187 | 155 | | C3 | 88 | 165 | +-----------+---------+-------+
答案:
#请在此添加实现代码 ########## Begin ########## SELECT c.classname, SUM(CASE WHEN s.chinese >= 60 THEN s.chinese ELSE 0 END) AS chinese, SUM(CASE WHEN s.maths >= 60 THEN s.maths ELSE 0 END) AS maths FROM tb_class c JOIN tb_score s ON c.stuname = s.name GROUP BY c.classname; ########## End ##########
第6关:查询学生平均分
任务描述
本关任务:根据提供的表和数据,查询平均成绩小于
60
分的同学的学生编号(s_id
)、学生姓名(s_name
)和平均成绩(avg_score
),要求平均成绩保留2
位小数点。(注意:包括有成绩的和无成绩的)
student
表数据:
s_id s_name s_sex 01 Mia 女 02 Riley 男 03 Aria 女 04 Lucas 女 05 Oliver 男 06 Caden 男 07 Lily 女 08 Jacob 男
course
表数据:
c_id c_name t_id 01 Chinese 02 02 Math 01 03 English 03
teacher
表数据:
t_id t_name 01 张三 02 李四 03 王五
score
表部分数据:
s_id c_id s_score 01 01 80 01 02 90 01 03 99 02 01 70 ... ... ... 相关知识
略
####编程要求
请仔细阅读右侧代码,根据方法内的提示,在
Begin - End
区域内进行代码补充。
测试说明
补充完代码后,点击测评,平台会对你编写的代码进行测试,当你的结果与预期输出一致时,即为通过。
预期输出:
+------+--------+-----------+ | s_id | s_name | avg_score | +------+--------+-----------+ | 04 | Lucas | 33.33 | | 06 | Caden | 32.50 | | 08 | Jacob | 0.00 | +------+--------+-----------+
答案:
#请在此添加实现代码 ########## Begin ########## SELECT s.s_id, s.s_name, ROUND(AVG(COALESCE(sc.s_score, 0)), 2) AS avg_score FROM student s LEFT JOIN score sc ON s.s_id = sc.s_id GROUP BY s.s_id, s.s_name HAVING avg_score < 60; ########## End ##########
第7关:查询修课相同学生信息
任务描述
本关任务:根据提供的表和数据,查询与
s_id=01
号同学学习的课程完全相同的其他同学的信息(学号s_id
,姓名s_name
,性别s_sex
)。
student
表数据:
s_id s_name s_sex 01 Mia 女 02 Riley 男 03 Aria 女 04 Lucas 女 05 Oliver 男 06 Caden 男 07 Lily 女 08 Jacob 男
course
表数据:
c_id c_name t_id 01 Chinese 02 02 Math 01 03 English 03
teacher
表数据:
t_id t_name 01 张三 02 李四 03 王五
score
表部分数据:
s_id c_id s_score 01 01 80 01 02 90 01 03 99 02 01 70 ... ... ... 相关知识
略
####编程要求
请仔细阅读右侧代码,根据方法内的提示,在
Begin - End
区域内进行代码补充。
测试说明
补充完代码后,点击测评,平台会对你编写的代码进行测试,当你的结果与预期输出一致时,即为通过。
预期输出:
+------+--------+-------+ | s_id | s_name | s_sex | +------+--------+-------+ | 02 | Riley | 男 | | 03 | Aria | 女 | | 04 | Lucas | 女 | +------+--------+-------+
答案:
#请在此添加实现代码 ########## Begin ########## SELECT s2.s_id, s2.s_name, s2.s_sex FROM student s1 JOIN student s2 ON s1.s_id <> s2.s_id LEFT JOIN score sc1 ON s1.s_id = sc1.s_id LEFT JOIN score sc2 ON s2.s_id = sc2.s_id AND sc1.c_id = sc2.c_id WHERE s1.s_id = '01' AND sc1.s_id IS NOT NULL GROUP BY s2.s_id, s2.s_name, s2.s_sex HAVING COUNT(DISTINCT sc1.c_id) = COUNT(DISTINCT sc2.c_id); ########## End ##########
第8关:查询各科成绩并排序
任务描述
本关任务:根据提供的表和数据,查询各科成绩,进行排序并显示排名,按学生编号(
s_id
)、课程编号(c_id
)、学生成绩(s_score
)和排名(rank
)进行输出,具体效果请查看测试集。
student
表数据:
s_id s_name s_sex 01 Mia 女 02 Riley 男 03 Aria 女 04 Lucas 女 05 Oliver 男 06 Caden 男 07 Lily 女 08 Jacob 男
course
表数据:
c_id c_name t_id 01 Chinese 02 02 Math 01 03 English 03
teacher
表数据:
t_id t_name 01 张三 02 李四 03 王五
score
表部分数据:
s_id c_id s_score 01 01 80 01 02 90 01 03 99 02 01 70 ... ... ... 相关知识
略
答案:
#请在此添加实现代码 ########## Begin ########## SELECT a.s_id, a.c_id, a.s_score, COUNT(b.s_score) + 1 AS rank FROM score a LEFT JOIN score b ON a.c_id = b.c_id AND a.s_score < b.s_score GROUP BY a.s_id, a.c_id, a.s_score ORDER BY a.c_id, a.s_score DESC, COUNT(b.s_score), a.s_id DESC; ########## End ##########
第9关:查询张老师课程成绩最高的学生信息
任务描述
本关任务:根据提供的表和数据,查询选修“张三”老师所授课程的学生中,成绩最高的学生信息(具体输出信息请查看测试说明)及其成绩。
student
表数据:
s_id s_name s_sex 01 Mia 女 02 Riley 男 03 Aria 女 04 Lucas 女 05 Oliver 男 06 Caden 男 07 Lily 女 08 Jacob 男
course
表数据:
c_id c_name t_id 01 Chinese 02 02 Math 01 03 English 03
teacher
表数据:
t_id t_name 01 张三 02 李四 03 王五
score
表部分数据:
s_id c_id s_score 01 01 80 01 02 90 01 03 99 02 01 70 ... ... ... 相关知识
略
####编程要求
请仔细阅读右侧代码,根据方法内的提示,在
Begin - End
区域内进行代码补充。
测试说明
补充完代码后,点击测评,平台会对你编写的代码进行测试,当你的结果与预期输出一致时,即为通过。
预期输出:
+------+--------+-------+---------+------+--------+ | s_id | s_name | s_sex | s_score | c_id | c_name | +------+--------+-------+---------+------+--------+ | 01 | Mia | 女 | 90 | 02 | Math | +------+--------+-------+---------+------+--------+
答案:
#请在此添加实现代码 ########## Begin ########## SELECT s.s_id, s.s_name, s.s_sex, sc.s_score, sc.c_id, c.c_name FROM student s JOIN score sc ON s.s_id = sc.s_id JOIN course c ON sc.c_id = c.c_id JOIN teacher t ON c.t_id = t.t_id WHERE t.t_name = '张三' AND sc.s_score = ( SELECT MAX(s_score) FROM score WHERE c_id IN ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) ); ########## End ##########
第10关:查询两门课程不及格同学信息
任务描述
本关任务:根据提供的表和数据,查询两门及其以上不及格课程的同学的学号(
s_id
)、姓名(s_name
)及其平均成绩(avg_score
),要求计算平均成绩后为整数。
student
表数据:
s_id s_name s_sex 01 Mia 女 02 Riley 男 03 Aria 女 04 Lucas 女 05 Oliver 男 06 Caden 男 07 Lily 女 08 Jacob 男
course
表数据:
c_id c_name t_id 01 Chinese 02 02 Math 01 03 English 03
teacher
表数据:
t_id t_name 01 张三 02 李四 03 王五
score
表部分数据:
s_id c_id s_score 01 01 80 01 02 90 01 03 99 02 01 70 ... ... ... 相关知识
略
####编程要求
请仔细阅读右侧代码,根据方法内的提示,在
Begin - End
区域内进行代码补充。
测试说明
补充完代码后,点击测评,平台会对你编写的代码进行测试,当你的结果与预期输出一致时,即为通过。
预期输出:
+------+--------+-----------+ | s_id | s_name | avg_score | +------+--------+-----------+ | 04 | Lucas | 33 | | 06 | Caden | 33 | +------+--------+-----------+
答案:
#请在此添加实现代码 ########## Begin ########## SELECT s.s_id, s.s_name, ROUND(AVG(sc.s_score)) AS avg_score FROM student s JOIN score sc ON s.s_id = sc.s_id WHERE sc.s_score < 60 GROUP BY s.s_id, s.s_name HAVING COUNT(DISTINCT sc.c_id) >= 2; ########## End ##########
这题似乎有一点问题提交会出现
ERROR at line 50: Failed to open file 'sr1c/step5/query5.sql', error: 2
的错误
但是在前一个题目的测试里运行答案是对的