mysql 经典数据库每日练习题之二

11、查询和”01”号的同学学习的课程完全相同的其他同学的信息
select a.* from student a
where s_id in
(SELECT s_id from score where s_id!=‘01’ and c_id in (SELECT c_id from score where s_id = ‘01’));

12、查询没学过”张三”老师讲授的任一门课程的学生姓名
SELECT a.s_name from student a
where a.s_id not in (SELECT s_id from score where c_id in
(SELECT c_id from course inner join teacher on course.t_id =teacher.t_id and teacher.t_name=‘张三’));

13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
join过滤条件放在on和where后区别：
内连接时无区别，外连接时不同
join过程：on是对笛卡尔积进行过滤，where是对以上过滤结果再次进行过滤

SELECT a.s_id,a.s_name,round(avg(b.s_score),1) avg_score FROM student a,score b
where a.s_