第1关 交换工资
#请在此添加实现代码
########## Begin ##########
update tb_Salary
set sex = case when sex = 'm' then 'f' else 'm' end;
########## End ##########
第2关 换座位
#请在此添加实现代码
########## Begin ##########
UPDATE tb_Seat AS s1
JOIN tb_Seat AS s2 ON s1.id = s2.id - 1
SET s1.name = s2.name,
s2.name = s1.name
WHERE s1.id % 2 = 1;
select *
from tb_Seat;
########## End ##########
第3关 分数排名
#请在此添加实现代码
########## Begin ##########
SELECT
s1.Score,
(
SELECT COUNT(distinct s2.Score) + 1
FROM score s2
WHERE s2.Score > s1.Score
) AS Rank
FROM
score s1
ORDER BY
s1.Score DESC;
SELECT
s1.Score,
(
SELECT COUNT(s2.Score) + 1
FROM score s2
WHERE s2.Score > s1.Score
) AS Rank
FROM
score s1
ORDER BY
s1.Score DESC;
########## End ##########
第4关 体育馆的人流量
#请在此添加实现代码
########## Begin ##########
#请在此添加实现代码
########## Begin ##########
select * from gymnasium where exists (
select * from (
select a.date a1,b.date b1,c.date c1 from
(select * from gymnasium where visitors_flow >=100) a
inner join
(select * from gymnasium where visitors_flow >=100) b
on b.id-a.id=1
inner join
(select * from gymnasium where visitors_flow >=100) c
on c.id-a.id=2 ) as tb_date
where gymnasium.date in (a1,b1,c1)
);
########## End ##########
########## End ##########
第5关 统计总成绩
#请在此添加实现代码
########## Begin ##########
SELECT tb_class.classname,
SUM(CASE WHEN tb_score.chinese >= 60 THEN tb_score.chinese ELSE 0 END) AS chinese,
SUM(CASE WHEN tb_score.maths >= 60 THEN tb_score.maths ELSE 0 END) AS maths
FROM tb_score
JOIN tb_class ON tb_score.name = tb_class.stuname
GROUP BY tb_class.classname;
########## End ##########
第6关 查询学生平均分
#请在此添加实现代码
########## Begin ##########
SELECT s.s_id,s.s_name,IFNULL(ROUND(AVG(sc.s_score), 2), 0.00) 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(sc.s_score) < 60 OR COUNT(sc.s_score) = 0;
########## End ##########
第7关 查询修课相同学生信息
#请在此添加实现代码
########## Begin ##########
SELECT s_id,s_name,s_sex
FROM student
WHERE student.s_id in(SELECT s_id FROM score
WHERE s_id!='01'AND c_id IN( SELECT c_id FROM score
WHERE s_id='01')
GROUP BY s_id HAVING COUNT(*) =(SELECT COUNT(*) FROM score WHERE s_id='01'))
########## End ##########
第8关 查询各科成绩并排序
#请在此添加实现代码
########## Begin ##########
select a.s_id,a.c_id,a.s_score,count(b.s_score)+1 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关 查询张老师课程成绩最高的学生信息
#请在此添加实现代码
########## Begin ##########
SELECT student.s_id,student.s_name,student.s_sex,score.s_score,course.c_id,course.c_name
FROM student,score,course,teacher
WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND course.t_id = teacher.t_id AND teacher.t_name = '张三' AND score.s_score = (SELECT MAX(s_score)
FROM score,course,teacher
WHERE teacher.t_name = '张三' AND score.c_id = course.c_id AND course.t_id = teacher.t_id
)
########## End ##########
第10关 查询两门课程不及格同学信息
#请在此添加实现代码
########## Begin ##########
SELECT student.s_id,student.s_name,ROUND(AVG(score.s_score)) AS avg_score
FROM student,score
WHERE student.s_id = score.s_id
GROUP BY student.s_id, student.s_name
HAVING SUM(score.s_score < 60) >= 2;
########## End ##########