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_