-- 20、查询学生的总成绩并进行排名
SELECT s_id,SUM(s_score) FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC

-- 21、查询不同老师所教不同课程平均分从高到低显示 
 SELECT t.t_id,t.t_name,c.c_name,AVG(sc.s_score) FROM teacher t 
LEFT JOIN course c ON c.t_id=t.t_id 
LEFT JOIN score sc ON sc.c_id =c.c_id
GROUP BY t.t_id ORDER BY AVG(sc.s_score) DESC

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
 SELECT t1.* FROM
(SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="01"
ORDER BY sc.s_score DESC LIMIT 1,2) t1

-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT c.c_id,c.c_name 
,((SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=100 AND sc.s_score>80)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id )) "100-85"
,((SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=85 AND sc.s_score>70)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id )) "85-70"
,((SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=70 AND sc.s_score>60)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id )) "70-60"
,((SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=60 AND sc.s_score>=0)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id )) "60-0"
FROM course c ORDER BY c.c_id

-- 24、查询学生平均成绩及其名次 
SELECT st.s_id,st.s_name,(CASE WHEN AVG(sc.s_score) IS NULL THEN 0 ELSE AVG(sc.s_score) END) FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id ORDER BY AVG(sc.s_score) DESC

 
-- 25、查询各科成绩前三名的记录
            -- 1.选出b表比a表成绩大的所有组
            -- 2.选出比当前id成绩大的 小于三个的
 select sc.c_id,sc.s_score
from score as sc 
where (select count(*) from score as s where s.c_id = sc.c_id and s.s_score > sc.s_score ) < 3
order by sc.c_id, sc.s_score;

-- 26、查询每门课程被选修的学生数 
 select c.c_id, c.c_name, count(*) as num
from course as c left join score as s on c.c_id = s.c_id
group by c.c_id;

-- 27、查询出只有两门课程的全部学生的学号和姓名 
select a.s_id, st.s_name from student as st join 
(select s.s_id, count(*) from score as s group by s.s_id  having count(*) = 2) as a on st.s_id = a.s_id; 

-- 28、查询男生、女生人数 
select s.s_sex,count(*) from student s group by s.s_sex; 

-- 29、查询名字中含有"风"字的学生信息
select * from student where s_name like "%风%"; 

-- 30、查询同名同性学生名单，并统计同名人数 
select s.s_name, s.s_sex, count(*) from student as st, student s where st.s_name = s.s_name and st.s_sex = s.s_sex and st.s_id != s.s_id

-- 31、查询1990年出生的学生名单
select s.* from student s where s.s_birth like '1990%';

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
select t.c_id, t.c_name, round(avg(t.s_score),1) as avg_score
from (select c.*, s.s_id, s.s_score
from course c left join score s on c.c_id = s.c_id) t
group by t.c_id order by avg_score desc, t.c_id asc;

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
select s.s_id, s.s_name, avg(sc.s_score) from student s, score sc where s.s_id = sc.s_id group by s.s_id having avg(sc.s_score) >= 85;
 

-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
 select c.c_id, st.s_name, sc.s_score
from course as c 
join score as sc on c.c_id = sc.c_id 
join student as st on st.s_id = sc.s_id
where c.c_name = "数学" and sc.s_score < 60;

-- 35、查询所有学生的课程及分数情况； 
 select st.s_id, st.s_name,st.s_sex, c.c_id,c.c_name,sc.s_score
from student as st 
left join score as sc on st.s_id = sc.s_id
left join course as c on c.c_id = sc.c_id;

 -- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； 
select st.s_name, c.c_name, sc.s_score
from (select sc.s_id, min(sc.s_score) as min_score
from score as sc 
group by sc.s_id
having min_score > 70) as t
inner join student as st on t.s_id = st.s_id  -- 这里是内连接
inner join score as sc on sc.s_id = st.s_id
inner join course as c on c.c_id = sc.c_id;

-- 37、查询不及格的课程
SELECT distinct c.c_id, c.c_name from course c left join score sc on sc.c_id = c.c_id where sc.s_score < 60;

-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名； 
select st.s_id, st.s_name
from student st,score sc where st.s_id = sc.s_id
and sc.c_id ='01' and sc.s_score >= 80;

-- 39、求每门课程的学生人数 
select c.c_name,count(*) from course c ,score sc WHERE c.c_id=sc.c_id GROUP BY c.c_id

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
        -- 查询老师id   
        -- 查询最高分（可能有相同分数）
        -- 查询信息
 SELECT st.s_name,sc.s_score FROM student as st
INNER JOIN score as sc  on st.s_id=sc.s_id
INNER JOIN course as co on sc.c_id=co.c_id
INNER JOIN teacher as te ON te.t_id=co.t_id
WHERE te.t_name='张三' ORDER BY s_score DESC LIMIT 0,1

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
 select  distinct sc.* from score as sc inner join score as ss 
on sc.c_id != ss.c_id and sc.s_score = ss.s_score and sc.s_id = ss.s_id;

-- 42、查询每门功成绩最好的前两名 
select  sc.c_id, sc.s_score from score as sc 
where (select count(*) from score as ss where ss.c_id = sc.c_id and ss.s_score > sc.s_score) < 2
order by sc.c_id asc, sc.s_score desc;

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
select c.c_name,count(*) from course c ,score sc WHERE c.c_id=sc.c_id GROUP BY c.c_id ORDER BY count(*)>5

-- 44、查询至少选修两门课程的学生学号 
select st.*, count(distinct sc.c_id) as st_num -- 担心有人重修，这里去重
from student as st 
left join score as sc on st.s_id = sc.s_id
group by st.s_id
having st_num >=2;

-- 45、查询选修了全部课程的学生信息 
select st.* from student as st inner join 
(select sc.s_id, count(sc.c_id) as st_num from score as sc group by sc.s_id
having st_num = (select count(c.c_id) from course as c)) as mt
on st.s_id = mt.s_id;

-- 46、查询各学生的年龄
    -- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT st.*,YEAR(CURDATE())-YEAR(st.s_birth) FROM student as st

-- 47、查询本周过生日的学生
 select st.* from student as st 
where week(now(),1) = week(st.s_birth,1);

-- 48、查询下周过生日的学生
 select st.* from student as st 
where week(now(),1) + 1 = week(st.s_birth,1);

-- 49、查询本月过生日的学生
select st.* from student as st 
where month(now())+1 = month(st.s_birth);
 

-- 50、查询下月过生日的学生
select st.* from student as st 
where month(now())+1 = month(st.s_birth);