hive 50道经典练习

数据

Student(Sid,Sname,Sage,Ssex)学生表
Sid：学号
Sname：学生姓名
Sbirth：学生生日
Ssex：学生性别

Course(Cid,Cname,T#)课程表
Cid：课程编号
Cname：课程名称
Tid：教师编号

SC(Sid,Cid,score)成绩表
Sid：学号
Cid：课程编号
score：成绩

Teacher(Tid,Tname)教师表
Tid：教师编号：
Tname：教师名字

01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-01	女
06	吴兰	1992-03-01	女
07	郑竹	1989-07-01	女
08	王菊	1990-01-20	女

01	语文	02
02	数学	01
03	英语	03

01	张三
02	李四
03	王五

01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

题目和答案

--1、查询01课程比02课程成绩高的所有学生的学号
select sc1.sid,sc1.score score1,sc2.score score2
from sc sc1
join sc sc2 on sc1.sid=sc2.sid
where sc1.cid = 1 and sc2.cid = 2 and sc1.score>sc2.score

--2、查询平均成绩大于60分的同学的学号和平均成绩
select sid,avg(score) avgscore
from sc
group by sid
having  avgscore>60; 

--3、查询所有同学的学号、姓名、选课数、总成绩
select stu.sid,stu.sname,
count(sc.cid) countcourse,
case when sum(sc.score) is null then 0 else sum(sc.score) end sumscore
from student stu
left join sc
on sc.sid=stu.sid
group by stu.sid,stu.sname;


--4、查询姓‘李’的老师的个数：
select count(*) from teacher where tname like '李%';

--5、查询没有学过“张三”老师课程的同学的学号、姓名：
select stu.sid,sname
from student stu
join course cs
join teacher t
left join sc on  stu.sid=sc.sid and cs.cid=sc.cid and t.tid=cs.tid
where tname='张三'
group by stu.sid,sname
having sum(case when sc.sid is null then 0 else 1 end)=0;

--6、查询学过“张三”老师所教的所有课的同学的学号、姓名：
select stu.sid,sname
from student stu
join course cs
join teacher t
left join sc on  stu.sid=sc.sid and cs.cid=sc.cid and t.tid=cs.tid
where tname='张三'
group by stu.sid,sname
having sum(case when sc.sid is null then 1 else 0 end)=0;

--7、查询学过01并且也学过编号02课程的同学的学号、姓名：
select stu.sid,stu.sname
from student stu
join sc sc1 on sc1.sid=stu.sid
join sc sc2 on sc2.sid=sc1.sid
where sc1.cid=01 and sc2.cid=02;

--8、查询课程编号02的成绩比课程编号01课程成绩低的所有同学的学号、姓名：
select stu.sid,stu.sname
from student stu
join sc sc1 on sc1.sid=stu.sid and sc1.cid=01
left join sc sc2 on sc2.sid=sc1.sid and sc2.cid=02