hive50道习题练习

hive50道习题练习,第1张

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 '李%';
					
										


					

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/5679452.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-12-17
下一篇 2022-12-17

发表评论

登录后才能评论

评论列表(0条)

保存