Hive sql语句必练50题之31-40

31、查询1990年出生的学生名单:
select *
from student
where
year(s_birth)=‘1990’;

s_id s_name s_birth s_sex
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:
select c_id,round(avg(s_score),2) avgscore
from score
group by c_id
order by avgscore desc,c_id asc;

c_id avgscore
02 72.67
03 68.5
01 64.5

?33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
select stu.s_id,stu.s_name,
avg(sc.s_score) avgScore
from student stu
join score sc on stu.s_id=sc.s_id
group by stu.s_id
having avgScore>=85;

select stu.s_id,stu.s_name,round(avg(sc.s_score),2) avgscore
from student stu
join score sc on stu.s_id=sc.s_id
group by stu.s_id
having round(avg(sc.s_score),2) >= 85
;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数:
select stu.s_name,sc.s_score
from student stu
join course cs on cs.c_name=‘数学’
join score sc on sc.s_id=stu.s_id and cs.c_id=sc.c_id
where sc.s_score<60;

s_name s_score
李云 30

?35、查询所有学生的课程及分数情况:
select *
from student stu
join course cs
left join score sc on sc.s_id=stu.s_id and sc.c_id=cs.c_id ;
-------------------------部分截取--------------------------------
s_id s_name s_birth s_sex c_id c_name t_id s_id c_id s_score
01 赵雷 1990-01-01 男 01 语文 02 01 01 80
01 赵雷 1990-01-01 男 02 数学 01 01 02 90

36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
select stu.s_name,cs.c_name,sc.s_score
from student stu
join score sc on sc.s_id=stu.s_id
join course cs on cs.c_id=sc.c_id
where
sc.s_score>=70;

s_name c_name s_score
赵雷 语文 80
赵雷 数学 90
赵雷 英语 99
钱电 语文 70
钱电 英语 80
孙风 语文 80
孙风 数学 80
孙风 英语 80
周梅 语文 76
周梅 数学 87
郑竹 数学 89
郑竹 英语 98

37、查询课程不及格的学生:
select stu.*,sc.s_score
from student stu
join score sc
where
sc.s_score <60;
---------------------截取部分---------
s_id s_name s_birth s_sex s_score
01 赵雷 1990-01-01 男 50
01 赵雷 1990-01-01 男 30
01 赵雷 1990-01-01 男 20
01 赵雷 1990-01-01 男 31

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
select sc.s_id,stu.s_name
from student stu
join score sc on stu.s_id=sc.s_id
where sc.c_id=‘01’ and s_score=‘80’;

s_id s_name
01 赵雷
03 孙风

39、求每门课程的学生人数:
select c_id,
count(*) count
from score
group by c_id;

c_id count
01 6
02 6
03 6

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩:
select *
from (
select stu.*,
row_number() over(distribute by sc.c_id sort by sc.s_score desc) rn
from student stu
join teacher ter on ter.t_name=‘张三’
join course cs on cs.t_id = ter.t_id
join score sc on sc.c_id = cs.c_id
)a
where a.rn==1;

s_id s_name s_birth s_sex rn
06 吴兰 1992-03-01 女 1