【SQL学习历程】3.39道SQL经典例题

表情况：学生表：student(SID学号,SNAME学生姓名,SAGE出生年月,SSEX性别) ； 成绩表：sc(SID学号,CID课程号,SCO成绩) 课程表：course(CID课程号,CNAME课程名称,TID教师号) 教师表：teacher(TID教师号,TNAME教师姓名)

1.求每门课程的学生人数

SELECT COURSE.CNAME'课程名称',COUNT(*)'人数' FROM COURSE,SCORE WHERE COURSE.CID = SCORE.CID

2.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名

本题涉及到学生表、成绩表，两个简单的限定条件可以用and进行限定。

SELECT A.SID，A.SNAME FROM STUDENT A,SC B WHERE A.SID=B.SID AND B.SID='01' AND B.SCORE>=80

3.统计每门课程的学生选修人数（超过 5 人的课程才统计）

本题涉及到课程表中的课程名称，使用成绩表中的学生id进行计数，两个表用CID连接。最后的限定条件是在分组之后进行的，因此使用GROUP BY和Having

SELECT B.CNAME,COUNT(*) FROM SC A,COURSE B WHERE A.CID=B.CID GROUP BY A.CID HAVING COUNT(*)>5

4.检索至少选修两门课程的学生学号

本题只需要用到成绩表，限定条件使用having完成。

SELECT SID FROM SC GROUP BY SID HAVING COUNT(CID)>=2

5.选修了全部课程的学生信息

难点在于如何获取全部课程数量，需要从第三张表额外获取。

SELECT A.* FROM STUDENT A,SC B WHERE A.SID=B.SID GROUP BY A.SID HAVING COUNT(CID)=(SELECT COUNT(*) FROM COURSE)

6.查询存在不及格的课程

值得一提的是，同一门课程不及格的学生可能不止一个，因此要进行去重操作。

SELECT DISTINCT COURSE.CNAME FROM SC,COURSE WHERE SC.CID=COURSE.CID AND SC.SCORE<60

7.查询任何一门课程成绩在 70 分以上的学生姓名、课程名称和分数

三张表的联结

SELECT A.SNAME,B.CNAME,C.SCO FROM STUDENT A,COURSE B,SC C WHERE A.SID=C.SID AND B.CID=C.CID AND C.SCO>70

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

首先是三张表联结，但是因为存在有学生成绩、课程为空值，则需要以学生表为准使用左联结，使用sid字段联结到成绩表，在使用cid字段联结到课程表。

SELECT A.SNAME,B.CNAME,C.SCO FROM STUDENT A LEFT JOIN SC C ON A.SID=C.SID LEFT JOIN COURSE B ON B.CID=C.CID

9.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT A.SNAME,B.CNAME,C.SCO FROM STUDENT A,COURSE B,SC C WHERE A.SID=C.SID AND C.CID=B.CID AND B.CNAME='%数学%' AND S.SCO<60

10.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

平均成绩是表内没有的列，使用avg之后用单引号使其在查询结果中有列名。

SELECT A.SID,A.SNAME,AVG(B.SCO)'平均成绩' FROM STUDENT A,SC B WHERE A.SID=B.SID GROUP BY A.SID HAVING AVG(B.SCO)>=85

11.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

需要查询的字段都在成绩表中，先按照成绩降序，再按照课程号升序。

SELECT CID,AVG(SCO) FROM SC GROUP BY CID ORDER BY AVG(SCORE) DESC,CID ASC

12.查询各科成绩最高分、最低分和平均分

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率 优秀率及格为>=60，中等为：60-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

及格率、中等率、优良率算出在各个分段的人数后除以总人数得到。使用case when函数进行计数。别忘了逗号

SELECT COURSE.CNAME,SC.CID,AVG(SC.SCORE),MAX(SC.SCORE),MIN(SC.SCORE),COUNT(SC.SID), SUM(CASE WHEN SCORE<60 THEN 1 ELSE 0 END)/COUNT(SC.CID)'及格率', SUM(CASE WHEN SCORE<80 AND SCORE>=60 THEN 1 ELSE 0 END)/COUNT(SC.CID)'中等率'， SUM(CASE WHEN SCORE<90 AND SCORE>=80 THEN 1 ELSE 0 END)/COUNT(SC.CID)'优良率'， SUM(CASE WHEN SCORE>=90 THEN 1 ELSE 0 END)/COUNT(SC.CID)'优秀率' FROM SC,COURSE WHERE SC.CID=COURSE.CID GROUP BY SC.CID ORDER BY COUNT(SC.CID) DESC,CID ASC

13.查询男生、女生人数

SELECT SSEX,COUNT(*) FROM STUDENT GROUP BY SSEX

14.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT A.* FROM STUDENT A,SC B WHERE A.SID=B.SID AND B.CID='01' AND B.SCO<60 ORDER BY B.SCO DESC

15.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT A.SID,A.SCO,B.'平均成绩' FROM SC A RIGHT JOIN (SELECT SID,AVG(SCORE)'平均成绩' FROM SC GROUP BY SID)B ON A.SID=B.SID ORDER BY B.'平均成绩' DESC

16.查询没学过"张三"老师讲授的任一门课程的学生姓名

拿到题目的第一反应是四表联结，但如果使用where teacher.tname ！=张三的话，选了两门课（包含一门张三的课）的同学也会被查询到，因此用not in

SELECT STUDENT.SNAME FROM STUDENT WHERE STUDENT.SID NOT IN (SELECT SC.CID FROM TEACHER,COURSE,SC WHERE SC.CID=COURSE.CID AND TEACHER.TID=COURSE.TID AND TEACHER.TNAME='张三')

17.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

只需要最高的学生成绩或者最高的几位学生成绩，就不需要用max，对查询结果限定出前几名就可以了。

SELECT A.*,B.SCO FROM STUDENT A,SC B,TEACHER C,COURSE D WHERE A.SID=B.SID AND B.CID=D.CID AND C.TID=D.TID AND C.TNAME='张三' ORDER BY B.SCORE DESC LIMIT 1

18.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩。

找出最高的成绩，找出成绩等于这个成绩的学生即可

SELECT A.*,B.SCO， FROM STUDENT A,SC B,TEACHER C,COURSE D WHERE A.SID=B.SID AND B.CID=D.CID AND C.TID=D.TID AND C.TNAME='张三' AND B.SCO=（SELECT MAX(B.SCORE) FROM STUDENT A,SC B,TEACHER C,COURSE D WHERE A.SID=B.SID AND B.CID=D.CID AND C.TID=D.TID AND C.TNAME='张三'

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

要查询不同课程成绩相同的学生，需要用到两次成绩表，因此使用自联结，相当于将一张表复制成两份，提取出需要的元素。

SELECT ANY_VALUE(A.CID),ANY_VALUE(A.SID),ANY_VALUE(A.SCO) FROM SC AS A INNER JOIN SC AS B ON A.SID=B.SID AND A.CID!=B.CID AND A.SCO=B.SCO GROUP BY A.CID,B.CID

20.查询每门功成绩最好的前两名

同样，也是需要同一个表内比较，使用自联结。

SELECT ANY_VALUE(A.SID),ANY_VALUE(A.CID),ANY_VALUE(A.SCORE) FROM SC AS A LEFT JOIN SC AS B ON A.CID=B.CID AND A.SCO<B.SCO GROUP BY A.CID,A.SID HAVING COUNT(B.SCO)<2 ORDER BY A.CID

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

SELECT COUNT(*),CID FROM SC GROUP BY CID

22.查询出只选修两门课程的学生学号和姓名

SELECT A.SID,A.SNAME FROM STUDENT A,SC B WHERE A.SID=B.SID GROUP BY A.SID HAVING COUNT(*)=2

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

重名=有两个及以上相同的值

SELECT SNAME,COUNT(*) FROM STUDENT GROUP BY SNAME HAVING COUNT(*)>1

24.查询 1990 年出生的学生名单

需要用到日期函数YEAR

SELECT * FROM STUDENT WHERE YEAR(SAGE)=1990

25.查询各学生的年龄

考察日期比较函数的使用，TIMESTAMPDIFF（时间单位，之前的时间，现在的时间）；CURDATE（）返回现在的时间

SELECT SID,SNAME,TIMESTAMPDIFF(YEAR,SAGE,CURDATE()) FROM STUDENT

26.查询本周过生日的学生

SELECT * FROM STUDENT WHERE WEEK(CURDATE())=WEEK(SAGE)

27.查询本月过生日的学生

SELECT * FROM STUDENT WHERE MONTH(CURDATE())=MONTH(SAGE)

28.查询「李」姓老师的数量

SELECT COUNT(*) FROM TEACHER WHERE TNAME LIKE '李%'

29.查有成绩的学生信息

SELECT * FROM STUDENT WHERE SID IN(SELECT SC.SID FROM SC)

30.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

有的学生没有成绩、没有选课，因此要把学生表当成主表进行左连接。

SELECT A.SID,A.SNAME,COUNT(B.CID),COUNT(B.SCO) FROM STUDENT A LEFT JOIN SC B ON A.SID=B.SID GROUP BY A.SID

31.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT A.SID,A.SNAME,AVG(B.SCO) FROM STUDENT A,SC B WHERE A.SID=B.SID GROUP BY A.SID HAVING AVG(B.SCO)>=60

32.查询不存在" 01 “课程但存在” 02 "课程的情况

SELECT * FROM SC WHERE CID='02' AND SID NOT IN(SELECT SID FROM SC WHERE CID = '01')

33.查询存在" 01 “课程但可能不存在” 02 "课程的情况

与上一题的不同在于，01和02可以同时存在，其实只需要01就可以了

SELECT * FROM SC WHERE CID='01'

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

保留名次空缺，因此选择rank

SELECT *,RANK() OVER(ORDER BY SCO DESC)AS RANK_NUM FROM SC

35.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT A.*,A.SCO,C.SCO FROM STUDENT A (SELECT SCO,SID FROM SC WHERE CID='01')B (SELECT SCO,SID FROM SC WHERE CID='02')C WHERE B.SID=C.SID AND A.SID=B.SID AND B.SCO>C.SCO

36.查询学过「张三」老师授课的同学的信息

SELECT A.* FROM STUDENT A,TEACHER B,COURSE C,SC D WHERE A.SID=D.SID AND D.CID=C.CID AND B.TID=C.TID AND B.TNAME='张三'

37.查询没有学全所有课程的同学的信息

还是没选课的同学的问题，需要左联结

SELECT A.* FROM STUDENT A LEFT JOIN SC B ON A.SID=B.SID GROUP BY A.SID HAVING COUNT(B.CID)<(SELECT COUNT(CID) FROM COURSE)

38.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

首先找出01同学学的课程，再找出cid在01同学课程里的同学的sid就可以了。

SELECT A.* FROM STUDENT A,SC B WHERE A.SID=B.SID AND B.CID IN(SELECT CID FROM SC WHERE SID='01') GROUP BY A.SID

39.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

完全相同，则学的课程没有01同学没有学的课程，并且学的课程的数量一致。

SELECT * FROM STUDENT WHERE SID IN (SELECT SID FROM SC WHERE SID NOT IN (SELECT SID FROM SC WHERE CID NOT IN (SELECT CID FROMSC WHERE SID='01')) GROUP BY SID HAVING COUNT(*)=(SELECT COUNT(CID) FROM SC WHERE SID='01')AND SID<>'01')