【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')