hive50道习题练习

hive50道习题练习

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:

select s.*, a.score, b.score from student s 
join sc a on s.sid = a.sid and a.cid = '01'
join sc b on s.sid = b.sid and b.cid = '02'
where a.score > b.score;

select student.*,a.score as 01_score,b.score as 02_score
from student
join sc a on a.sid=student.sid
join sc b on b.sid=student.sid
where  a.cid='01' and b.cid='02' and a.score > b.score;

select s.*, chinese, math from student s 
join (
select sid, 
max(case when cid = '01' then score end) as chinese,
max(case when cid = '02' then score end) as math
from sc 
group by sid 
) a on s.sid = a.sid 
where chinese > math;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:

select s.*, a.score, b.score from student s 
join sc a on s.sid = a.sid and a.cid = '01'
join sc b on s.sid = b.sid and b.cid = '02'
where a.score < b.score;

select s.*, a.score, b.score from student s 
join sc a on s.sid = a.sid 
join sc b on s.sid = b.sid 
where a.cid = '01' and b.cid = '02' and a.score < b.score;

select s.*, chinese, math from student s 
join (
select sid, 
max(case when cid = '01' then score end) as chinese,
max(case when cid = '02' then score end) as math
from sc
group by sid
) a on s.sid = a.sid
where a.chinese < a.math;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:

select s.sid, s.sname, round(avg_score, 2) avg_score from student s 
join (
select sid, avg(score) as avg_score from sc 
group by sid
having avg(score) >= 60
) t 
on s.sid = t.sid;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
(包括有成绩的和无成绩的)

select s.sid, s.sname, nvl(avg_score, 0) as avg_score from student s 
left join (
select sid, round(avg(score), 2) avg_score from sc 
group by sid
) t 
on s.sid = t.sid
where t.sid is null or t.avg_score < 60;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:

select s.sid, s.sname, nvl(t.num, 0) as num, nvl(t.total_score, 0) as total_score 
from student s 
left join (
select sid, count(1) num, sum(score) total_score from sc 
group by sid
) t 
on s.sid = t.sid;

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

select count(1) from teacher where tname like '李%';