50道SQL练习题及答案与分析（31-40）多种解法

题目以及建表语句sql等在本系列的第一篇文章，请自取 链接地址

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

SELECT student.sname,course.cid,course.Cname,sc.score FROM
student LEFT JOIN sc
ON student.sid = sc.sid
LEFT JOIN
course ON sc.cid = course.cid
WHERE course.cid = '01' and sc.score >= 80

32 求每门课程的学生人数

select sc.CId,count(*) as 学生人数
from sc
GROUP BY sc.CId;

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

SELECT s.*,c.cname,SC.score,t.tname,t.tid
FROM Student s
JOIN SC ON s.SId = SC.SId
JOIN Course c ON SC.CId = c.CId
JOIN Teacher t ON c.TId = t.TId
WHERE t.Tname = '张三'
AND SC.score = (
  SELECT MAX(score)
  FROM SC
  WHERE CId IN (
    SELECT CId
    FROM Course
    WHERE TId = t.TId
  )
);

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

select  a.cid, a.sid,  a.score from sc as a
inner join 
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by cid, sid;

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

SELECT a.sid, a.cid
FROM sc AS a
LEFT JOIN sc AS b
ON a.cid = b.cid AND b.score > a.score
GROUP BY a.cid, a.sid
HAVING COUNT(b.sid) < 2
ORDER BY a.cid, a.score DESC;

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

SELECT cid,COUNT(sid) as number from sc
GROUP BY cid
HAVING COUNT(sid) > 5

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

SELECT sid,COUNT(cid) as number from sc
where 1 =1
GROUP BY sid
HAVING number > 2

39 查询选修了全部课程的学生信息

SELECT sid,GROUP_CONCAT(CId ORDER BY CId) as clist from sc
GROUP BY sid
HAVING clist = (SELECT GROUP_CONCAT(cid) AS id_list
 from course)

select Sid from SC 
group by Sid 
having count(Cid)=(select distinct COUNT(0)cid from Course)

40 查询各学生的年龄，只按年份来算

SELECT Sname, YEAR(CURRENT_TIMESTAMP())-YEAR(Sage) AS Age
FROM Student;