HQL练习题（2）

有三份源数据文件，如下所示,请根据需求，编写对应的HQL语句。
文件1： 学生表student <学号sid,姓名sname,性别ssex,年龄sage,系department>
95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,赵丽,女,20,CS

文件2： 成绩表score <学生编号sid,课程编号cid，成绩sscore>
95001,1,81
95001,2,85
95001,3,88
95001,4,70
95002,1,90
95002,2,80
95002,3,90
95002,4,80
95003,1,70
95003,2,78
95003,3,65
95003,4,65
95004,1,70
95004,2,90
95004,3,85
95004,4,90
95005,1,70
95005,2,90
95005,3,70
95005,4,90
95006,1,70
95006,2,90
95006,3,70
95006,4,90

文件3： 课程表course<课程编号cid,课程名cname>
1,数据库
2,数学
3,信息系统
4,操作系统

需求1：创建三个外部表，并分别给外部表加载数据.

create external table student(sid int,sname string,ssex string,sage int,department string)row format delimited fields terminated by ',';
load data local inpath '/root/aaa/student.txt' overwrite into table student;

create external table score(sid int,cid int,sscore int)row format delimited fields terminated by ',';
load data local inpath '/root/aaa/score.txt' overwrite into table score;

create external table course(cid int,cname string)row format delimited fields terminated by ',';
load data local inpath '/root/aaa/course.txt' overwrite into table course;

需求2：查询各课的平均成绩，要求对平均成绩降序排序，并且小数保留2位，考虑四舍五入。

--方法一：
select b.cname course,avgscore from 
(select cid, round(avg(sscore),2) avgscore from score group by cid) a inner join 
course b on a.cid = b.cid order by avgscore desc;


--方法二：
select cid,round(avg(sscore),2) as avg_score from score 
group by cid
order by avg_score desc;--未显示课程名称

需求3: 查询CS系中数学成绩最高的学生的信息，包括学生的学号和名字（考虑成绩相同情况）

思路：
--3.1.三联
select a.sid id,a.sname name,a.department dep,b.sscore score,c.cname cou from student a, score b, course c where a.sid = b.sid and b.cid = c.cid;三表关联并留下有效列

--3.2.CS系学生数学分数
--方法一：
select id,name,score from 
(select a.sid id,a.sname name,a.department dep,b.sscore score,c.cname cou from student a, score b, course c where a.sid = b.sid and b.cid = c.cid) y 
where dep ='CS' and cou ='数学';
--方法二：
select a.sid id,a.sname name,a.department dep,b.sscore score,c.cname cou from student a, score b, course c where a.sid = b.sid and b.cid = c.cid and cname ='数学' and a.department='CS';

--3.3.CS系学生数学最高分
select max(score) max_score from 
(select id,name,score from(select a.sid id,a.sname name,a.department dep,b.sscore score,c.cname cou from student a, score b, course c where a.sid = b.sid and b.cid = c.cid) y where dep ='CS' and cou ='数学') z 
;--(求得CS系学生数学分数最高分)


最终SQL语句
--3.4.自关联查询CS系中数学成绩最高的学生的信息
--方法一：
select id,name,score from (select id,name,score from 
(select a.sid id,a.sname name,a.department dep,b.sscore score,c.cname cou from student a, score b, course c where a.sid = b.sid and b.cid = c.cid) y 
where dep ='CS' and cou ='数学') m,
(select max(score) max_score from 
(select id,name,score from(select a.sid id,a.sname name,a.department dep,b.sscore score,c.cname cou from student a, score b, course c where a.sid = b.sid and b.cid = c.cid) y where dep ='CS' and cou ='数学') z ) n 
where m.score = n.max_score;

--方法二：
select t3.sid,t3.sname 
from student t3 
inner join (
select * from (
select t2.sid,sscore,rank() over(order by sscore desc ) as row1
from 
course t1 inner join score t2 on t1.cid=t2.cid
where t1.cname='数学'
)t1 
where t1.row1<=1 
) t4 on t3.sid=t4.sid where t3.department="CS";
--别人写得，逻辑好像存在bug,可自行斟酌

需求4：查询数学成绩比数据库成绩高的学生信息和分数

--方法一：
select * from
(select * from
(select * from score s1 left join course c1 on s1.cid =c1.cid where c1.cname='数学') a left join 
(select * from score s2 left join course c2 on s2.cid =c2.cid where c2.cname='数据库') b 
on a.sid =b.sid where a.sscore>b.sscore) c
left join student d on c.sid = d.sid;

--方法二：
select t6.*,t5.math,t5.datas from (
	select * from 
		(select t1.sid as sssid,t1.sscore as math
			from score t1 inner join course t2 on t1.cid=t2.cid  where cname='数学') t3 
	inner join 
		(select t1.sid,t1.sscore as datas
			from score t1 inner join course t2 on t1.cid=t2.cid  where cname='数据库') t4 
	on t3.sssid=t4.sid where t3.math>t4.datas
	) t5 inner join student t6 on t5.sssid=t6.sid ;

需求5：求每个课程分数最高的前三名学生（考虑分数相同的情况）

--方法一：
select * from 
(select cou,name,score,
dense_rank() over(partition by cou order by score desc
) as rk 
from 
(select 
a.sid id,a.sname name,b.sscore score,c.cname cou 
from student a, score b, course c 
where a.sid = b.sid and b.cid = c.cid)k
)t
where t.rk <= 3;

--方法二：
select * from (
select  t1.*,t3.cname,rank() over(partition by t3.cid order by t2.sscore desc ) as row1
from student t1 inner join score t2 on t1.sid=t2.sid inner join course t3 on t2.cid=t3.cid
) t4 where t4.row1<=3；