复杂查询（一）

熟练的掌握对函数、表的内联查询、多表联合查询的使用

交换工资

任务描述

本关任务：给定一张 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 函数，使用方法如下实例：

1
2
3
4
5
6
7
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'，则返回值'女' 否则返回'其他’
1
select if(sex='1','男','女') as sex from sys_user;     ###如果sex='1'则返回值'男' 否则返回值为'女'

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
7
8
9
10
+----+--------+-----+--------+
| 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 |
+----+--------+-----+--------+

我的代码

1
2
3
4
5
#请在此添加实现代码
########## Begin ##########
update tb_Salary set sex = if(sex='f','m','f');

########## End ##########

换座位

任务描述

本关任务：改变相邻俩学生的座位。

小美是一所中学的信息科技老师，她有一张 tb_Seat座位表，平时用来储存学生名字和与他们相对应的座位 id。

tb_Seat表结构数据如下：

id name
1 Elon
2 Donny
3 Carey
4 Karin
5 Larisa

现在小美想改变相邻俩学生的座位（若学生人数为奇数，则无需改变最后一位同学的座位），现在需要你编写SQL输出小美想要的的结果。

相关知识

略

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
id    name
1    Donny
2    Elon
3    Karin
4    Carey
5    Larisa

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

#请在此添加实现代码
########## Begin ##########
select
case 
when id=1 then 2
when id=2 then 1
when id=3 then 4
when id=4 then 3
when id=5 then 5
end id , name from tb_Seat order by id;


########## End ##########

分数排名

任务描述

本关任务：编写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区域内进行代码补充。

测试说明
补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

我的代码

1
2
3
4
5
6
#请在此添加实现代码
########## Begin ##########
select Score ,(select count(distinct score) from score where score>=s.score) as Rank from score s order by score desc;
select Score ,(select count(score) from score where score>s.score )+1 as Rank from score s order by score desc;

########## End ##########

体育馆的人流量

任务描述

本关任务：某市建了一个新的体育馆，每日人流量信息被记录在gymnasium表中：序号 (id)、日期 (date)、 人流量 (visitors_flow)。

请编写一个查询语句，找出人流量处于高峰的记录 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区域内进行代码补充。

测试说明
补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
7
8
+----+------------+---------------+
| id | date       | visitors_flow |
+----+------------+---------------+
|  5 | 2019-01-05 |           168 |
|  6 | 2019-01-06 |          1352 |
|  7 | 2019-01-07 |           382 |
|  8 | 2019-01-08 |           326 |
+----+------------+---------------+

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
#请在此添加实现代码
########## Begin ##########
select distinct a.* from gymnasium a , gymnasium b , gymnasium c  where
a.visitors_flow>=100 and c.visitors_flow>=100 and b.visitors_flow>=100 and
(
    (a.id+1=b.id and a.id+2=c.id) or
    (a.id-1=b.id and a.id+1=c.id) or
    (a.id-1=b.id and a.id-2=c.id)
) 
order by a.id;

########## End ##########

统计总成绩

任务描述

本关任务：计算每个班的语文总成绩和数学总成绩，其中低于 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区域内进行代码补充。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
7
+-----------+---------+-------+
| classname | chinese | maths |
+-----------+---------+-------+
| C1        |      89 |   164 |
| C2        |     187 |   155 |
| C3        |      88 |   165 |
+-----------+---------+-------+

我的代码

1
2
3
4
5
#请在此添加实现代码
########## Begin ##########
select classname ,sum(case when chinese>=60 then chinese else 0 end) as chinese, sum(case when maths>=60 then maths else 0 end) as maths from tb_score,tb_class where name=stuname group by classname;

########## End ##########