sql_01答案

#参考博客 https://blog.csdn.net/paul0127/article/details/82529216

#学生表 Student
SELECT * from student;
#课程表 Course
SELECT * from Course;
#教师表 Teacher
SELECT * from Teacher;
#成绩表 SC
SELECT * from SC;

#1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT s.*,a.score 01成绩,b.score 02成绩 from student s,
(SELECT Sid,score from SC where Cid = '01') a,
(SELECT Sid,score from SC where Cid = '02') b
WHERE a.Sid = b.Sid and a.score > b.score and s.Sid = a.Sid;

#1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT s.*,a.score 01成绩,b.score 02成绩 from student s,
(SELECT Sid,score from SC where Cid = '01') a,
(SELECT Sid,score from SC where Cid = '02') b
WHERE a.Sid = b.Sid and s.Sid = a.Sid;

#1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT s.*,a.score 01成绩,b.score 02成绩 from student s
LEFT JOIN (SELECT Sid,score from SC where Cid = '01') a
ON s.Sid = a.Sid
LEFT JOIN (SELECT Sid,score from SC where Cid = '02') b
ON a.Sid = b.Sid 
WHERE a.score != '';

#1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT s.*,a.score 01成绩,b.score 02成绩 from student s
LEFT JOIN (SELECT Sid,score from SC where Cid = '02') b
ON s.Sid = b.Sid
LEFT JOIN (SELECT Sid,score from SC where Cid = '01') a
ON a.Sid = b.Sid 
WHERE a.score is null and b.score != '';

#2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.Sid,s.Sname,AVG(c.score) avg_score from student s,SC c
WHERE s.Sid = c.Sid
GROUP BY Sid HAVING avg_score > 60;

#3. 查询在 SC 表存在成绩的学生信息
SELECT * from student WHERE Sid in (SELECT Sid FROM sc );

#4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT s.Sid,s.Sname,count(Cid) count,SUM(score) score from student s
LEFT JOIN SC c on s.Sid = c.Sid GROUP BY Sid;

#4.1 查有成绩的学生信息
select s.sid, s.sname, count(*) as 选课总数, sum(score) as 总成绩,
    sum(case when cid = 01 then score else null end) as score_01,
    sum(case when cid = 02 then score else null end) as score_02,
    sum(case when cid = 03 then score else null end) as score_03
from student as s, sc c
where s.sid = c.sid
group by s.sid;

#5. 查询「李」姓老师的数量 
SELECT COUNT(*) count from Teacher where Tname LIKE '李%';

#6. 查询学过「张三」老师授课的同学的信息 
 SELECT * from student where Sid in
 (SELECT sc.Sid from Teacher t,course c,sc WHERE t.Tname = '张三'
  and t.Tid = C.Tid and c.Cid = sc.Cid);

#7. 查询没有学全所有课程的同学的信息
select s.* from student s,
(select sid,count(cid) count from sc group by sid) sc,
(SELECT count(*) count from Course) c
where s.Sid = sc.sid and sc.count < c.count;

#8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from Student 
where Sid in(select distinct Sid from SC where Cid in(select Cid from SC where Sid='01'));

#9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 
select * from Student 
where Sid in(select distinct Sid from SC where Cid in(select Cid from SC where Sid='01') and Sid <> '01'
group by Sid  having COUNT(Sid)>=3
);

#10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 
SELECT s.Sname from student s WHERE Sid NOT in (
SELECT Sid from SC WHERE Cid in ( SELECT c.Cid from Teacher t,Course c WHERE t.Tname = '张三' and t.Tid = c.Tid));

#11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
SELECT s.Sid,s.Sname,AVG(c.score) avg_score from student s,SC c
WHERE s.Sid = c.Sid and score<60
GROUP BY s.Sid HAVING COUNT(score) >= 2;

#12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT s.*,c.score from student s,SC c where s.Sid = c.Sid and c.Cid = '01' and c.score <60 ORDER BY c.score desc

#13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s.Sid,s.Sname,
sum(case when cid=01 then score else null end) as score_01,
sum(case when cid=02 then score else null end) as score_02,
sum(case when cid=03 then score else null end) as score_03,
AVG(c.score) avg_score from student s,SC c
WHERE s.Sid = c.Sid 
GROUP BY s.Sid  ORDER BY avg_score desc;

#14. 查询各科成绩最高分、最低分和平均分
select c.cid as 课程号, c.cname as 课程名称, count(*) as 选修人数,
    max(score) as 最高分, min(score) as 最低分, avg(score) as 平均分,
    sum(case when score >= 60 then 1 else 0 end)/count(*) as 及格率,
    sum(case when score >= 70 and score < 80 then 1 else 0 end)/count(*) as 中等率,
    sum(case when score >= 80 and score < 90 then 1 else 0 end)/count(*) as 优良率,
    sum(case when score >= 90 then 1 else 0 end)/count(*) as 优秀率
from sc, course c
where c.cid = sc.cid
group by c.cid
order by count(*) desc, c.cid asc;

#15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

#15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

#16.  查询学生的总成绩，并进行排名，总分重复时保留名次空缺

#16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

#17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

#18. 查询各科成绩前三名的记录

#19. 查询每门课程被选修的学生数 

#20. 查询出只选修两门课程的学生学号和姓名 
select Sid,Sname from Student 
where Sid in(
SELECT sc.Sid count from SC sc  GROUP BY sc.Sid  HAVING COUNT(*) = 2
);

#21. 查询男生、女生人数
Select
count(case when Ssex = '男' then 1 else null end) as count1,
count(case when Ssex = '女' then 1 else null end) as count2
from
Student;

#22. 查询名字中含有「风」字的学生信息
Select * from Student where Sname like '%风%';

#23. 查询同名同姓学生名单，并统计同名人数

#24. 查询 1990 年出生的学生名单
Select * from Student where year(Sage) = 1990;

#25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT Cid,AVG(score) avg from sc GROUP BY Cid ORDER BY avg desc,Cid desc;

#26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 
select s.Sid,s.Sname,c.avg from Student s,(SELECT Sid,AVG(score) avg from sc GROUP BY Sid HAVING avg >= 85) c
where s.Sid = c.Sid;

#27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT s.Sname,c.score from student s, (SELECT Sid,score from sc where Cid in (SELECT Cid from Course where Cname = '数学') and score <60) c
where s.Sid = c.Sid;

#28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

#29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT s.Sname,c.Cname,sc.score from  student s,Course c,SC sc where c.Cid = sc.Cid and s.Sid = sc.Sid and sc.score >70;

#30. 查询不及格的课程
SELECT * FROM SC where score <60;

#31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT s.Sname,c.Cname,sc.score from  student s,Course c,SC sc where c.Cid = sc.Cid and s.Sid = sc.Sid and sc.score >80 and c.Cid = '01';

#32. 求每门课程的学生人数 
select Cid,COUNT(Sid) from SC GROUP BY Cid;
#33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*, max(score)
from student s, teacher t, course c, sc
where s.sid = sc.sid
    and sc.cid = c.cid
    and c.tid = t.tid
    and t.tname = '张三';
#34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

#35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

#36. 查询每门功成绩最好的前两名
SELECT * FROM (SELECT Cid,Sid,score from SC where Cid = 01 ORDER BY score desc LIMIT 2) t1
UNION
SELECT * FROM (SELECT Cid,Sid,score from SC where Cid = 02 ORDER BY score desc LIMIT 2) t2
UNION
SELECT * FROM (SELECT Cid,Sid,score from SC where Cid = 03 ORDER BY score desc LIMIT 2) t3;

#37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT Cid,COUNT(*) count from Sc GROUP BY Cid HAVING count >5;

#38. 检索至少选修两门课程的学生学号 
SELECT s.Sid from student s,(SELECT Sid,COUNT(*) count from Sc GROUP BY Sid HAVING count >=2) c where s.Sid = c.Sid;

#39. 查询选修了全部课程的学生信息
SELECT s.* from student s,(SELECT Sid,count(*) count from SC GROUP BY Sid HAVING count = (SELECT count(Cid) count from Course)) c 
where s.Sid = c.Sid;

#40. 查询各学生的年龄，只按年份来算 
SELECT Sname,(year(NOW()) - year(Sage)) from student;

#41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select sname, timestampdiff(year, sage, now()) as age from student;

#42. 查询本周过生日的学生
select * from student where week(now()) = week(sage);

#43. 查询下周过生日的学生
select * from student where (week(now())+1) = week(sage);

#44. 查询本月过生日的学生
select * from student where month(now()) = month(sage);

#45. 查询下月过生日的学生
select * from student where (month(now())+1) = month(sage);