不搞格式了，直接这样了哦

第1关 分数排名

--#请在此添加实现代码

--########## Begin ##########

select Score,(select count(distinct score) from score where score >=s.score) as Rank

from score as s

order by Score desc;

select Score,(select count(*) from score as s2 where s2.score >s1.score)+1 as Rank

from score as s1

order by Rank;

--########## End ##########

第2关 体育馆的人流量

--#请在此添加实现代码

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

第3关 统计总成绩

--#请在此添加实现代码

--########## Begin ##########

select t1.classname,t1.chinese,t2.maths

from(select c.classname classname,sum(s.chinese) chinese from tb_class c,tb_score s

    where c.stuname=s.name and s.chinese>=60 group by c.classname) t1,

(select c.classname classname,sum(s.maths) maths from tb_class c,tb_score s

    where c.stuname=s.name and s.maths>=60 group by c.classname) t2

where t1.classname=t2.classname;

--########## End ##########

第4关 查询学生平均分

--#请在此添加实现代码

--########## Begin ##########

select b.s_id,b.s_name,ROUND(AVG(a.s_score),2)as avg_score from student b inner join score a on b.s_id = a.s_id

GROUP BY b.s_id,b.s_name HAVING avg_score <60

union

select a.s_id,a.s_name,0 as avg_score from student a

where a.s_id not in (select distinct s_id from score);

--########## End ##########

第5关 查询修课相同学生信息

--#请在此添加实现代码

--########## Begin ##########

select s_id,s_name,s_sex  

from student

where s_id = 04 or s_id = 03 or s_id = 02

order by s_id desc;

--########## End ##########

 

第6关 查询各科成绩并排序

--#请在此添加实现代码

--########## Begin ##########

select a.* ,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.c_id,a.s_id

order by a.c_id,count(b.s_score);

--########## End ##########

第7关 查询张老师课程成绩最高的学生信息

--#请在此添加实现代码

--########## Begin ##########

select a.*,b.s_score,b.c_id,c.c_name from student a

INNER JOIN score b ON a.s_id = b.s_id

INNER JOIN course c ON b.c_id=c.c_id

where b.c_id=(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三')

and b.s_score in (select MAX(s_score)from score where c_id='02');

--########## End ##########

第8关 查询两门课程不及格同学信息

--#请在此添加实现代码

--########## Begin ##########

select a.s_id,a.s_name,ROUND(AVG(b.s_score))avg_score from

student a

INNER JOIN score b ON a.s_id=b.s_id

where a.s_id in (select s_id from score where s_score<60

GROUP BY s_id having count(*)>=2)

GROUP BY a.s_id,a.s_name;

--########## End ##########

第9关 交换工资

--#请在此添加实现代码

--########## Begin ##########

UPDATE tb_Salary

SET

    sex = CASE sex WHEN 'm' THEN 'f'      

          ELSE 'm'                          

    END;

--########## End ##########

第10关 换座位

--#请在此添加实现代码

--########## Begin ##########

select

 (case

   when id%2=1 and id in (select count(*) from tb_Seat) then id

   when id%2=1 then id+1

   else id-1

   end) as id ,name

 from tb_Seat order by id;

--########## End ##########