MySQL HIVE经典50题 6~10题

– 6、查询"李"姓老师的数量

select count(1) from teacher where tname like ‘李%’;
#还需要去重
select count(distinct(tname)) from teacher where tname like ‘李%’;

– 7、查询学过"张三"老师授课的同学的信息
1
select s.* from student s join
(
select sc.* from sc join
(
select cid from course c where c.tid in
(select tid from teacher where tname=‘张三’)
) b1 on sc.cid=b1.cid
) b2 on s.sid=b2.sid;
2
select s.* from
(select tid from teacher where tname=‘张三’) b1 join course c on b1.tid=c.tid
join sc on c.cid=sc.cid join student s on s.sid=sc.sid;

– 8、查询没学过"张三"老师授课的同学的信息
1

select * from student dd where dd.sid not in(
select s.sid from
(select tid from teacher where tname=‘张三’) b1 join course c on b1.tid=c.tid
join sc on c.cid=sc.cid join student s on s.sid=sc.sid);

2
select * from student dd where dd.sid not in
(
select s.* from student s join
(
select sc.* from sc join
(
select cid from course c where c.tid in
(select tid from teacher where tname=‘张三’)
) b1 on sc.cid=b1.cid
) b2 on s.sid=b2.sid)

– 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
1
select s.* from student s join(
select sid from(
select * from sc where cid=‘01’ or cid=‘02’) b1 group by sid) b2 on s.sid=b2.sid;

– 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
1
select s.* from student s join(
select * from
(select sid from sc where cid=‘01’) b1 where b1.sid
not in(
select sid from sc where cid=‘02’)
) b2 on s.sid=b2.sid;

看后续