2021-08-18 MySQL查询练习

11. 查询没有学全所有课程的同学的信息

SELECT student.* 
FROM score 
JOIN student
ON student.`s_id` = score.`s_id`
GROUP BY score.s_id
HAVING COUNT(score.s_id) != (
	SELECT COUNT(1) 
	FROM Course
)

12. 查询⾄少有⼀⻔课与学号为"01"的同学所学相同的同学的信息

SELECT * 
FROM score 
WHERE s_id != '01'
GROUP BY s_id
HAVING c_id IN 
	(SELECT c_id 
	FROM score 
	WHERE s_id = '01')

13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT stu.* 
FROM Score s1
JOIN Score s2
JOIN Student stu
WHERE s1.s_id = '01' AND s2.s_id!='01' AND s1.`c_id` = s2.`c_id` AND stu.`s_id` = s2.`s_id`
GROUP BY s2.`s_id`
HAVING COUNT(1)=(
	SELECT COUNT(1) FROM score WHERE s_id = '01'
)

14. 查询没学过"张三"⽼师讲授的任⼀⻔课程的学⽣姓名

SELECT * 
FROM Score
JOIN (SELECT Course.`c_id` 
	FROM teacher
	JOIN Course
	ON Course.`t_id` = Teacher.`t_id`
	WHERE t_name = '张三' ) temp
ON temp.c_id = Score.`c_id`