sql练习题（5）

-- 31、查询1990年出生的学生名单
SELECT s.s_name FROM student s WHERE s_birth LIKE "1990%"

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
SELECT c_id, ROUND(AVG(s_score),2) 平均成绩 FROM score 
GROUP BY c_id
ORDER BY 平均成绩 DESC, c_id  

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
SELECT s.`s_id`, s.`s_name`, ROUND(AVG(s1.s_score),2) 平均成绩 FROM student s
LEFT JOIN score s1 ON s.`s_id` = s1.s_id
GROUP BY s.`s_id`
HAVING 平均成绩 >= 85

-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
-- 解法一
SELECT s.`s_name`, s1.s_score FROM student s
LEFT JOIN score s1 ON s.`s_id` = s1.s_id
LEFT JOIN course c ON s1.c_id = c.c_id 
WHERE c.c_name = '数学' AND s1.s_score < 60
-- 解法二
SELECT a.s_name,b.s_score FROM score b JOIN student a ON a.s_id=b.s_id WHERE b.c_id=(
SELECT c_id FROM course WHERE c_name ='数学') AND b.s_score<60

-- 35、查询所有学生的课程及分数情况；
-- 解法一：
SELECT s.`s_name`, c.c_name, s1.s_score FROM student s
LEFT JOIN score s1 ON s.`s_id` = s1.s_id
LEFT JOIN course c ON s1.c_id = c.c_id 

-- mark一下，上面的查询结果显示很不友好，解法二如下：
SELECT s.`s_id`, s.`s_name` 姓名, 
SUM(CASE c.c_name WHEN '语文' THEN s1.s_score ELSE 0 END) 语文,
SUM(CASE c.c_name WHEN '数学' THEN s1.s_score ELSE 0 END) 数学,
SUM(CASE c.c_name WHEN '英语' THEN s1.s_score ELSE 0 END) 英语,
SUM(s1.s_score) 总分
FROM student s LEFT JOIN score s1 ON s.`s_id` = s1.s_id
LEFT JOIN course c ON s1.c_id = c.c_id
GROUP BY s.`s_id`,s.`s_name`

-- 36、查询任何一门课程成绩在70分以上(含70)的姓名、课程名称和分数；
SELECT s.`s_name`, c.c_name, s1.s_score
FROM student s 
LEFT JOIN score s1 ON s.`s_id` = s1.s_id
LEFT JOIN course c ON s1.c_id = c.c_id
WHERE s1.s_score > 70

-- 37、查询不及格的课程
SELECT a.s_id,a.c_id,b.c_name,a.s_score FROM score a 
LEFT JOIN course b ON a.c_id = b.c_id
WHERE a.s_score<60

-- 38、查询课程编号为01且课程成绩在80分以上(含80)的学生的学号和姓名； 
SELECT s.`s_id`, s.`s_name` FROM student s WHERE s.`s_id` IN
(SELECT s_id FROM score WHERE c_id = '01' AND s_score >= 80 )

-- 39、求每门课程的学生人数
SELECT c_id, COUNT(s_id) FROM score GROUP BY c_id

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT s.*, MAX(s1.s_score),c.c_name FROM teacher t
LEFT JOIN course c ON t.t_id = c.t_id
LEFT JOIN score s1 ON c.c_id = s1.c_id
LEFT JOIN student s ON s1.s_id = s.`s_id`
WHERE t_name = '张三'