MYSQL经典面试题（50）

student（学生表）

学生表数据

 course（课程表）

课程表数据

 sc（成绩表）

成绩表数据

teacher（教师表）

 教师表数据

#1、查询“001”课程比“002”课程成绩高的所有学生的学号
SELECT s1.Sid 学号 FROM (SELECT Sid,Score FROM sc WHERE Cid=1) s1,
(SELECT Sid,Score FROM sc WHERE Cid=2) s2 WHERE s1.Sid=s2.Sid AND s1.Score>s2.Score

#2、查询平均成绩大于60分的同学的学号和平均成绩
SELECT Sid 学号,AVG(score) 平均成绩 FROM sc GROUP BY Sid HAVING 平均成绩>60

#3、查询所有同学的学号、姓名、选课数、总成绩
SELECT s.Sid 学号,s.Sname 姓名,COUNT(sc.Cid) 选课数,SUM(sc.Score) 总成绩 
FROM student s LEFT JOIN sc ON s.Sid=sc.Sid GROUP BY s.Sid,s.Sname

#4、查询姓‘李’的老师的个数：
SELECT COUNT(tid) FROM teacher WHERE tname LIKE '张%';

#5、查询没有学过“叶平”老师课的同学的学号、姓名：
SELECT Sid,Sname FROM student 
WHERE sid NOT IN
(SELECT DISTINCT sc.sid FROM sc,course,teacher 
WHERE sc.cid=course.cid AND course.tid=teacher.tid AND teacher.tname="张大仙")

#6、查询学过“叶平”老师所教的所有课的同学的学号、姓名：
SELECT Sid,Sname FROM student 
WHERE sid IN
(SELECT DISTINCT sc.sid FROM sc,course,teacher 
WHERE sc.cid=course.cid AND course.tid=teacher.tid AND teacher.tname="张大仙")

#7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名：
SELECT s.Sid 学号,s.Sname 姓名 FROM student s RIGHT JOIN 
(SELECT DISTINCT sc.Sid FROM sc LEFT JOIN course c ON sc.Cid=c.Cid WHERE sc.Cid=1 OR sc.Cid=2) c
ON s.Sid=c.Sid

#8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：
SELECT s1.Sid 学号 FROM (SELECT Sid,Score FROM sc WHERE Cid=1) s1,
(SELECT Sid,Score FROM sc WHERE Cid=2) s2 WHERE s1.Sid=s2.Sid AND s1.Score>s2.Score

#9、查询所有课程成绩小于60的同学的学号、姓名：
SELECT s.Sid 学号,s.Sname 姓名 FROM student s 
RIGHT JOIN (SELECT Sid FROM sc GROUP BY Sid HAVING SUM(Score)<60) sc
ON s.Sid=sc.Sid

#10、查询没有学全所有课的同学的学号、姓名：
SELECT Sid,Sname FROM student s WHERE (SELECT COUNT(*) 
FROM sc WHERE s.Sid=sc.Sid) < (SELECT COUNT(*) FROM course)

#11、查询至少有一门课与学号为“001”同学所学相同的同学的学号和姓名：
SELECT DISTINCT s.Sid,s.Sname FROM student s 
LEFT JOIN sc ON s.Sid =sc.Sid WHERE sc.Cid IN 
(SELECT DISTINCT sc.Cid FROM sc RIGHT JOIN course c ON sc.Cid=c.Cid)

#12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
SELECT DISTINCT s.Sid,s.Sname FROM student s 
LEFT JOIN sc ON s.Sid =sc.Sid WHERE sc.Cid IN 
(SELECT DISTINCT sc.Cid FROM sc RIGHT JOIN course c ON sc.Cid=c.Cid)

#13、把“SC”表中“吕德华”老师教的课的成绩都更改为此课程的平均成绩：
UPDATE sc INNER JOIN
(SELECT Cid,AVG(Score) c FROM sc WHERE Cid IN 
(SELECT Cid FROM course c  LEFT JOIN teacher t ON c.Tid=t.Tid WHERE t.Tname="吕德华") GROUP BY Cid) a
SET sc.Score=a.c WHERE sc.Cid=a.Cid

#14、查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名：
SELECT Sid,Sname FROM student WHERE Sid IN
(SELECT sc.Sid FROM student s LEFT JOIN sc ON s.Sid=sc.Sid GROUP BY sc.Sid HAVING COUNT(*) =
(SELECT COUNT(*) FROM student s LEFT JOIN sc ON s.Sid=sc.Sid WHERE s.Sid=2 GROUP BY sc.Sid))

#15、删除学习“胖炸”老师课的SC表记录：
DELETE FROM sc WHERE sc.Cid=
(SELECT c.Cid FROM course c LEFT JOIN teacher t ON c.Tid=t.Tid WHERE Tname="胖炸")

#16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、002号课的平均成绩：
INSERT INTO sc (Sid,Cid,Score) 
SELECT DISTINCT st.Sid,sc.Cid,(SELECT AVG(Score)FROM sc WHERE Cid='2')
FROM student st,sc WHERE NOT EXISTS (SELECT * FROM sc WHERE Cid='2' AND sc.Sid=st.Sid) AND sc.Cid='2';

#17、按平均成绩从高到低显示所有学生的“英雄联盟”、“王者荣耀”、“和平精英”三门的课程成绩，按如下形式显示：学生ID，英雄联盟，王者荣耀，和平精英，有效课程数，有效平均分：
SELECT Sid AS 学生ID,
(SELECT Score FROM sc  WHERE sc.Sid=t.Sid AND sc.Cid =1) AS 英雄联盟,
(SELECT Score FROM sc WHERE sc.Sid=t.Sid AND sc.Cid =2) AS 王者荣耀,
(SELECT Score FROM sc WHERE sc.Sid=t.Sid AND sc.Cid =4) AS 和平精英,
COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM sc AS t
GROUP BY Sid
ORDER BY AVG(t.Score) DESC

#18、查询各科成绩最高和最低的分：以如下的形式显示：课程ID，最高分，最低分
SELECT L.Cid AS 课程ID,L.Score AS 最高分,R.Score AS 最低分
FROM sc L ,SC AS R 
WHERE L.Cid = R.Cid AND
L.Score = (SELECT MAX(IL.Score) 
FROM SC AS IL,Student AS IM 
WHERE L.Cid = IL.Cid AND IM.Sid=IL.Sid 
GROUP BY IL.Cid) 
AND
R.Score = (SELECT MIN(IR.Score) 
FROM SC AS IR 
WHERE R.Cid = IR.Cid 
GROUP BY IR.Cid 
)

#19、按各科平均成绩从低到高和及格率的百分数从高到低顺序：
SELECT sc.cid 课程编号,AVG(score) 平均成绩,d.f/(b.c*100) 及格率 
FROM sc,(SELECT cid,SUM(score) f FROM sc GROUP BY cid) d,(SELECT cid,COUNT(sid) c FROM sc GROUP BY cid) b
WHERE sc.cid=d.cid AND sc.cid=b.cid
GROUP BY sc.cid
ORDER BY 平均成绩 ASC,及格率 DESC

#20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 英雄联盟（001），王者荣耀（002），永劫无间（003），和平精英（004）：
SELECT b.p 英雄联盟平均成绩,b.q 英雄联盟及格率,c.o 王者荣耀平均成绩,c.w 王者荣耀及格率,
d.i 永劫无间平均成绩,d.r 永劫无间及格率,e.u 和平精英平均成绩,e.t 和平精英及格率 
FROM sc,
(SELECT cid,AVG(score) p,SUM(score)/(COUNT(sid)*100) q FROM sc GROUP BY cid HAVING cid=1) b,
(SELECT cid,AVG(score) o,SUM(score)/(COUNT(sid)*100) w FROM sc GROUP BY cid HAVING cid=2) c,
(SELECT cid,AVG(score) i,SUM(score)/(COUNT(sid)*100) r FROM sc GROUP BY cid HAVING cid=3) d,
(SELECT cid,AVG(score) u,SUM(score)/(COUNT(sid)*100) t FROM sc GROUP BY cid HAVING cid=4) e
WHERE sc.cid=b.cid OR sc.cid=c.cid OR sc.cid=d.cid OR sc.cid=e.cid 
GROUP BY b.q,c.w,d.r,e.t,b.p,c.o,d.i,e.u

#21、查询不同老师所教不同课程平均分从高到低显示：
SELECT MAX(t.tid) 老师编号,t.tname 老师名称,c.cid 课程编号,AVG(score) 平均成绩
FROM sc,course c,teacher t
WHERE sc.cid=c.cid AND c.tid=t.tid
GROUP BY c.Cid
ORDER BY AVG(score) DESC

#22、查询如下课程成绩第3名到第6名的学生成绩单：英雄联盟(001)，王者荣耀(002)，永劫无间(003)：
SELECT s.sid,sname,sc.score,cname
FROM student s LEFT JOIN sc ON s.sid=sc.sid LEFT JOIN course c ON sc.cid=c.cid
LEFT JOIN (SELECT sc.sid f,score,cid g FROM sc WHERE cid=(SELECT cid FROM course WHERE cname="英雄联盟") LIMIT 0,3) d
ON sc.sid=d.f 
LEFT JOIN (SELECT sc.sid f,score,cid g FROM sc WHERE cid=(SELECT cid FROM course WHERE cname="王者荣耀") LIMIT 0,3) k
ON sc.sid=k.f 
LEFT JOIN (SELECT sc.sid f,score,cid g FROM sc WHERE cid=(SELECT cid FROM course WHERE cname="永劫无间") LIMIT 0,3) l
ON sc.sid=l.f 
WHERE (s.sid=d.f AND sc.cid=d.g) OR (s.sid=k.f AND sc.cid=k.g) OR (s.sid=l.f AND sc.cid=l.g)
ORDER BY cname 

#23、统计下列各科成绩，各分数段人数：课程ID，课程名称，[100-85],[85-70],[70-60],[ 小于60] 
SELECT sc.cid AS 课程ID, Cname AS 课程名称,
    SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS "[100 - 85]",
    SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS "[85 - 70]",
    SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS "[70 - 60]",
    SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS "[60分以下]" 
FROM sc,course 
WHERE sc.cid=course.cid
GROUP BY sc.cid,cname

#24、查询学生平均成绩及其名次：
 SELECT s.sid,sname,b.c FROM student s 
 RIGHT JOIN (SELECT sid,AVG(score)  c 
 FROM sc GROUP BY sid ORDER BY AVG(score) DESC) b
 ON s.sid=b.sid

#25、查询各科成绩前三名的记录（不考虑成绩并列情况）：
SELECT sid,cid,score FROM sc s 
WHERE sid IN (SELECT sid FROM sc GROUP BY sid HAVING COUNT(sid)<=3) ORDER BY sid

#26、查询每门课程被选修的学生数：
SELECT cname,COUNT(sid) FROM sc LEFT JOIN course c ON sc.Cid=c.cid GROUP BY c.cid

#27、查询出只选修一门课程的全部学生的学号和姓名：
SELECT s.sid,sname FROM student s 
LEFT JOIN sc ON s.sid=sc.sid 
WHERE s.sid IN (SELECT sid FROM sc GROUP BY sid HAVING COUNT(sid)=1)

#28、查询男生、女生人数：
SELECT ssex,COUNT(ssex) FROM student GROUP BY ssex

#29、查询姓“小”的学生名单：
SELECT * FROM student WHERE sname LIKE'小%'

#30、查询同名同姓的学生名单，并统计同名人数：
SELECT sname,COUNT(*) FROM student GROUP BY sname HAVING COUNT(*)>1

#31、1981年出生的学生名单（注：student表中sage列的类型是datetime）:
SELECT * FROM student WHERE DATE_FORMAT(birthday,'%y')=81

#32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩：
SELECT s.sid,sname,AVG(score) FROM student s 
LEFT JOIN sc ON s.sid=sc.sid GROUP BY s.sid HAVING AVG(score)>85

#33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列：
SELECT cid,AVG(score) FROM sc GROUP BY cid ORDER BY AVG(score),cid DESC

#34、查询课程名称为“王者荣耀”，且分数低于60的学生名字和分数：
SELECT sname,score FROM student s 
LEFT JOIN sc ON s.sid=sc.sid 
LEFT JOIN course c ON sc.cid=c.cid WHERE score<60 AND cname="王者荣耀"

#35、查询所有学生的选课情况：
SELECT s.sid,sname,c.cname FROM student s 
LEFT JOIN sc ON s.sid=sc.sid LEFT JOIN course c ON sc.cid=c.cid 

#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数：
SELECT sname,cname,score FROM sc 
LEFT JOIN course c ON sc.cid=c.cid 
LEFT JOIN student s ON sc.sid=s.sid WHERE Score>70

#37、查询不及格的课程，并按课程号从大到小的排列：
SELECT DISTINCT sc.Cid,c.Cname FROM sc 
RIGHT JOIN course c ON sc.Cid=c.cid 
WHERE score<60 ORDER BY sc.Cid DESC

#38、查询课程编号为“003”且课程成绩在80分以上的学生的学号和姓名：
SELECT sc.Sid,s.Sname FROM sc 
LEFT JOIN student s ON sc.sid=s.sid 
WHERE cid=3 AND score>80

#39、求选了课程的学生人数：
SELECT COUNT(*) FROM (SELECT DISTINCT Sid FROM sc WHERE score IS NOT NULL) b

#40、查询选修“张大仙”老师所授课程的学生中，成绩最高的学生姓名及其成绩：
SELECT MAX(score) FROM course c 
LEFT JOIN sc ON c.cid=sc.cid 
WHERE c.tid=(SELECT tid FROM teacher WHERE tname="张大仙")

#41、查询各个课程及相应的选修人数：
SELECT cid,COUNT(*) FROM sc GROUP BY Cid

#42、查询不同课程成绩相同的学生和学号、课程号、学生成绩：
SELECT a.Sid,a.cid,a.score FROM sc a,sc b 
WHERE a.score=b.score AND a.cid != b.cid

#43、查询每门课程成绩最好的前两名：
SELECT CO.cname 课程,
       (SELECT sc.sid
          FROM sc
         WHERE sc.Cid = co.cid
         ORDER BY sc.sid LIMIT 0, 1) 第一名学生编号,
       (SELECT sc.sid
          FROM sc
         WHERE sc.cid = co.cid
         ORDER BY sc.sid LIMIT 1, 1) 第二名学生编号
FROM course co 

#44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序：
SELECT Cid,COUNT(Cid) FROM sc GROUP BY Cid ORDER BY COUNT(Cid) DESC,Cid ASC

#45、检索至少选修两门课程的学生学号：
SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(*)>=2

#46、查询全部学生选修的课程和课程号和课程名：
SELECT sc.Sid,sc.Cid,c.Cname FROM sc 
LEFT JOIN course c ON sc.Cid=c.Cid 
WHERE Score IS NOT NULL

#47、查询没学过”树叶”老师讲授的任一门课程的学生姓名：
SELECT Sid FROM student WHERE Sid NOT IN(
SELECT  Sid FROM sc RIGHT JOIN course c ON sc.Cid=c.Cid
WHERE Tid =(SELECT Tid FROM teacher WHERE Tname="树叶")
)

#48、查询两门以上不及格课程的同学的学号以及其平均成绩：
SELECT sc.Sid,AVG(Score) FROM sc 
WHERE sid IN (SELECT Sid FROM SC WHERE Score <60 GROUP BY Sid HAVING COUNT(*)>2
) GROUP BY Sid

#49、检索“004”课程分数小于60，按分数降序排列的同学学号：
SELECT sc.Sid FROM course c 
LEFT JOIN sc ON c.Cid=sc.Cid 
WHERE Score<60 ORDER BY Score DESC

#50、删除“002”同学的“001”课程的成绩：
DELETE FROM sc WHERE sc.Sid=2 AND sc.Cid=1