【SQL】进阶57题

1. 题号1142 查询姓“李”的教师数量

select count(t_id) from teacher where t_name like '李%'; 
/* 
  1. 大水题，无脑写

2. 题号1143 查询课程编号为”01“的课程比”02“的课程成绩高的所有学生的学号（尝试了两种写法，未能解决，很蓝瘦~~~）

select sc1.s_id 
	from 
		(select s_id , s_score from score where score.c_id = '01') sc1 
	join
		(select s_id , s_score from score where score.c_id = '02') sc2
	on sc1.s_id = sc2.s_id
	  where sc1.s_score > sc2.s_score
/* 
  1. 使用子查询从成绩表中获取课程编号为'01'和'02'的学生编号和分数，然后将这两个子查询结果进行联结，
  并筛选出成绩比课程'02'高的学生的学号。

3. 题号1144 查询平均成绩大于60分的学生的学号和平均成绩

select s_id , avg(s_score) from score
	group by s_id
	having avg(s_score) > 60;
/*
  1. 从score表中选择学生编号（s_id）和成绩的平均值（AVG(s_score)）作为结果列。
  2. 使用GROUP BY子句将数据按照学生编号（s_id）进行分组。
  3. 对每个学生分组计算其成绩的平均值。
  4. 使用HAVING子句筛选出平均成绩大于60的学生。

4. 题号1145 查询所有学生的学号、姓名、选课数、总成绩

select student.s_id , s_name, ifnull(count(score.c_id) , 0) , ifnull(sum(score.s_score) , 0) from student
	left join score on student.s_id = score.s_id
	group by student.s_id
/*
  1. 通过左连接（LEFT JOIN），将student表与score表连接在一起，以便获取每个学生的成绩信息。
  2. 使用COUNT函数计算每个学生所拥有的成绩数量，并使用SUM函数计算每个学生的总成绩。
  3. 如果没有匹配的成绩记录，这两个聚合函数的结果将为NULL。
  4. 使用IFNULL函数将NULL结果替换为0，以确保结果中显示为0而不是NULL。

5. 题号1146 查询没学过“张三”老师课的学生的学号、姓名

select s_id , s_name from student
	where s_id not in (
		select student.s_id from student
				left join score on student.s_id = score.s_id
						where c_id = (
													select c_id from course where t_id = (select t_id from teacher where t_name = '张三')
													)
										)
/*
  1. 从student表中选择学生编号（s_id）和学生姓名（s_name）作为结果列。
  2. 使用子查询来获取所有学过“张三”老师课程的学生的学生编号。
      子查询中的第一个子查询是从teacher表中选择教师编号（t_id），以获得“张三”老师的教师编号。
      第二个子查询是从course表中选择课程编号（c_id），以获取与“张三”老师相关的课程编号。
      最后一个子查询是从score表中选择学生编号（s_id），以获取学过与“张三”老师课程相关的学生编号。
  3. 主查询中使用NOT IN子句来选择在学生表（student）中学号不在子查询结果中的学生的学号和姓名。

6. 题号1147 查询学过“张三”老师所教的所有课的同学的学号、姓名

select student.s_id , student.s_name from student
		left join score on student.s_id = score.s_id
				where c_id = (
											select c_id from course where t_id = (select t_id from teacher where t_name = '张三')
											)
/*
  1. 这题就是上一题的核心部分，详见上一题思路。

7. 题号1148 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

select student.s_id , s_name from student
	left join (select * from score where c_id = '01') sc1 on student.s_id = sc1.s_id
	left join (select * from score where c_id = '02') sc2 on sc1.s_id = sc2.s_id
	where sc1.s_id = sc2.s_id;
/*
  1. 从student表中选择学生编号（s_id）和学生姓名（s_name）作为结果列。
  2. 使用左连接（LEFT JOIN）将student表与两个子查询连接起来。
      第一个子查询选择了score表中课程编号为'01'的所有记录（即学生参加的'01'课程成绩）。
      第二个子查询选择了score表中课程编号为'02'的所有记录（即学生参加的'02'课程成绩）。
  3. 在连接过程中，通过student.s_id = sc1.s_id将学生表与第一个子查询的结果连接起来，
      再通过sc1.s_id = sc2.s_id将第一个子查询的结果与第二个子查询的结果连接起来。
      这样，将具有相同学生编号的记录连接在一起。
  4. 最后，通过WHERE子句过滤出满足条件sc1.s_id = sc2.s_id的记录，即同时参加了'01'课程和'02'课程的学生。
  5. 返回满足条件的学生的学生编号（s_id）和学生姓名（s_name）作为查询结果。

8. 题号1149 查询课程编号为“02”的总成绩

select  c_id , sum(s_score) from score where c_id = '02';
/*
  大水题

9. 题号1150 查询所有课程成绩小于60分的学生的学号、姓名

select distinct student.s_id , s_name from student
	left join score on student.s_id = score.s_id
	where s_score < 60 or s_score is null
/*
  1. 注意，我写了半天发现漏看题目细节了，需要考虑学生缺考没有成绩的情况，还要去重。
  2. 从student表中选择学生编号（s_id）和学生姓名（s_name）作为结果列。
      使用左连接（LEFT JOIN）将student表与score表连接起来。通过student.s_id = score.s_id将两个表关联起来。
  3. 在连接后的结果上应用过滤条件。过滤条件是s_score < 60或s_score为空值的记录。
  4. 使用DISTINCT关键字确保返回的结果中没有重复的学生记录。
  5. 返回满足条件的学生的学生编号（s_id）和学生姓名（s_name）作为查询结果。

10. 查询没有学全所有课的学生的学号、姓名

select student.s_id , s_name from student
	where student.s_id not in (
		select s_id from score
			group by s_id
				having count(c_id) >= (select count(c_id) from course)
)
/*
select student.s_id , s_name from student
	where student.s_id in (
		select s_id from score
			group by s_id
				having ifnull(count(c_id) , 0) < (select count(c_id) from course) 
)
  1. 见鬼了，我只是把我的条件反向描述，就过了。
  2. 使用NOT IN运算符来排除满足子查询条件的学生。
  3. 子查询通过分组计数（COUNT）来统计每个学生学习的课程数量，然后与总课程数量进行比较。
  4. 如果学生学习的课程数量大于或等于总课程数量，那么该学生被视为学习了所有课程。
  5. 主查询中的NOT IN运算符将排除满足该条件的学生，返回剩余的学生。

更多题解持续更新