Mysql必练50题（第二天）

第六题
查询"李"姓老师的数量

select t_name,count(*) from teacher where t_name like "李%";

第七题
查询学过"张三"老师授课的同学的信息

select s.* from student s 
inner join score sc on s.s_id = sc.s_id 
inner join course c on sc.c_id = c.c_id
inner join teacher t on c.t_id = t.t_id
where t_name = "张三";

第八题
查询没学过"张三"老师授课的同学的信息

select * from student where s_id not in
(select sc.s_id from score sc inner join course c on sc.c_id = c.c_id
inner join teacher t on c.t_id = t.t_id where t_name = "张三"
);

第九题
查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select * from student where s_id in (select sc1.s_id from 
(select * from score sc where c_id = 1) sc1
inner join 
(select * from score sc where c_id =2) sc2
on sc1.s_id = sc2.s_id)

第十题
查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select * from student where s_id in 
(select s_id from score where c_id = 1 and s_id not in 
(select s_id from score where c_id = 2));