sql-50练习题11-15

前言

sql真的非常灵活，一个题目可能有很多很多种解法，我记录的只是我自己的一个解题思路，如果大家有更好的不同解法欢迎在评论区一起探讨
ps：有些题可能解法会重复，比如求大于，我们下一个题可能是求小于，大家如果第一遍没有做出来，看了我写的之后有了思路，可以试一下反面的解法。

数据库表结构介绍

学生表

课程表

成绩表

教师表

1-1 查询没有学全所有课程的同学的信息

需要的表结构：course，score，student
利用score中的成绩去裁剪表，score中有些同学只有两科成绩或者一科成绩这种，而学全所有课程的同学成绩有三科

SELECT
	b.*, count(a.s_id)
FROM
	score a
RIGHT JOIN student b ON a.s_id = b.s_id
GROUP BY
	a.s_id
HAVING
	count(a.s_id) < (
		SELECT
			count(c_id)
		FROM
			course
	)
ORDER BY
	b.s_id ASC;

结果如下：

1-2 查询至少有一门课与学号为’01’的同学所学相同的同学的信息

需要用到的表为：score和student

SELECT DISTINCT
	b.*
FROM
	score a,
	student b
WHERE
	a.s_id = b.s_id
AND a.c_id IN (
	SELECT
		c.c_id
	FROM
		score c
	WHERE
		c.s_id = '1'
)

结果如下：

1-3 查询和’1’号的同学学习的课程完全相同的其他同学的信息

select s2.s_id,student.s_name
from score as s1 
join score as s2 
on s1.c_id = s2.c_id
join student on student.s_id = s2.s_id
and s1.s_id = '01'
and s2.s_id != '01'
group by s2.s_id,student.s_id
having count(s2.c_id) = (select count(*) from score where s_id = '01')

结果如下：

1-4 查询没学过’张三’老师讲授的任一门课程的学生姓名

select a.s_name from student a where a.s_id not in (
    select s_id from score where c_id = 
                (select c_id from course where t_id =(
                    select t_id from teacher where t_name = '张三')) 
                group by s_id);

1-5 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
	b.*, avg(a.s_score) agescore
FROM
	score a,
	student b
WHERE
	a.s_id = b.s_id
GROUP BY
	a.s_id
HAVING
	sum(
		CASE
		WHEN a.s_score >= 60 THEN
			0
		ELSE
			1
		END
	) >= 2

结果如下：