MySQL数据库 - 复杂查询

第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 ##########