– 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
SELECT s.s_id,s.s_name,c.c_name,tmp2.s_score
FROM student s
JOIN
(SELECT sc.*
FROM score sc
LEFT JOIN (SELECT s_id FROM score WHERE s_score<70 GROUP BY s_id) tmp
ON sc.s_id=tmp.s_id
WHERE tmp.s_id is NULL
)tmp2
ON s.s_id=tmp2.s_id
JOIN course c ON tmp2.c_id=c.c_id
怎么查每个学生的每个课程成绩高于70?先查出有在70分以下的学生,之后连接score表,就能找出那些都在70分以上的
– 37、查询课程不及格的学生:
SELECT s.s_name,tmp.*
FROM student s
JOIN
(SELECT sc.s_id,c_name,s_score
FROM score sc JOIN course c ON sc.c_id=c.c_id
WHERE s_score<60
)tmp
ON tmp.s_id=s.s_id
–38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
SELECT s.s_id,s.s_name,sc.s_score
FROM score sc
JOIN student s ON sc.s_id=s.s_id
WHERE c_id='01' AND s_score>=80
– 39、求每门课程的学生人数:
SELECT c.c_id,c.c_name,COUNT(1)
FROM score sc
JOIN course c ON sc.c_id=c.c_id
GROUP BY c.c_id,c.c_name
– 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩:
SELECT tmp.*
FROM
(SELECT s.*,s_score,RANK() over(ORDER BY s_score DESC) ranking
FROM teacher t
JOIN course c ON t.t_name='张三' AND t.t_id=c.t_id
JOIN score sc ON c.c_id=sc.c_id
JOIN student s ON sc.s_id=s.s_id) tmp
WHERE tmp.ranking=1
后续部分参见: