1016 HQL36-50题

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select
st.s_name,c.c_name,s_score
from student st join score s on st.s_id = s.s_id
join course c on s.c_id = c.c_id
where s.s_score>70
group by st.s_name, c.c_name, s_score;
+------+------+-------+
|s_name|c_name|s_score|
+------+------+-------+
|周梅
|语文
|76
|
|周梅
|数学
|87
|
|孙风
|数学
|80
|
|孙风
|英语
|80
|
|孙风
|语文
|80
|
|赵雷
|语文
|80
|
|赵雷
|数学
|90
|
|赵雷
|英语
|99
|
|郑竹
|数学
|89
|
|郑竹
|英语
|98
|
|钱电
|英语
|80
|
+------+------+-------+

37、查询不及格的课程

select s_name,c_name,s_score
from student
join score s on student.s_id = s.s_id
join course c on s.c_id = c.c_id
where s_score<60;
+------+------+-------+
|s_name|c_name|s_score|
+------+------+-------+
|李云
|语文
|50
|
|李云
|数学
|30
|
|李云
|英语
|20
|
|吴兰
|语文
|31
|
|吴兰
|英语
|34
|
+------+------+-------+

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select student.s_id,student.s_name,s_score
from student
join score s on student.s_id = s.s_id
where c_id='01' and s_score>=80;
+----+------+-------+
|s_id|s_name|s_score|
+----+------+-------+
|01
|赵雷
|80
|
|03
|孙风
|80
|
+----+------+-------+

39、求每门课程的学生人数

select c_name,count(1)
from score
join course c on score.c_id = c.c_id
group by c.c_name
;
+------+--+
|c_name|c1|
+------+--+
|数学
|6 |
|英语
|6 |
|语文
|6 |
+------+--+

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select st.s_id, s_name, s_birth, s_sex,max(s.s_score)
`max`
from student st
join score s on st.s_id = s.s_id
join course c on s.c_id = c.c_id
join teacher t on c.t_id = t.t_id
where
t_name='张三'
group by st.s_id, s_name, s_birth, s_sex
order by max desc
limit 1;
+----+------+----------+-----+---+
|s_id|s_name|s_birth
|s_sex|max|
+----+------+----------+-----+---+
|01
|赵雷
|1990-01-01|男
|90 |
+----+------+----------+-----+---+

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct s1.s_id,s1.c_id,s1.s_score
from score s1,score s2
where s1.s_score=s2.s_score and s1.c_id<>s2.c_id;
+----+----+-------+
|s_id|c_id|s_score|
+----+----+-------+
|01
|01
|80
|
|02
|03
|80
|
|03
|01
|80
|
|03
|02
|80
|
|03
|03
|80
|
+----+----+-------+

42、查询每门功成绩最好的前两名

 select t.* from
(select s_id,c_id, row_number() over (distribute by c_id sort by s_score desc) `rn`
from score) t where t.rn<3
order by t.c_id;
+----+----+--+
|s_id|c_id|rn|
+----+----+--+
|04
|01
|2 |
|06
|01
|1 |
|02
|02
|2 |
|04
|02
|1 |
|06
|03
|2 |
|04
|03
|1 |
+----+----+--+

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c.c_id,count(1) `num`
from score
join course c on score.c_id = c.c_id
group by c.c_id
having num>=5
order by num desc,c_id;
;
+----+---+
|c_id|num|
+----+---+
|01
|6
|
|02
|6
|
|03
|6
|
+----+---+

44、检索至少选修两门课程的学生学号

select s_id,count(c_id)
from score
group by s_id
having count(c_id)>=2;
+----+--+
|s_id|c1|
+----+--+
|01
|3 |
|02
|3 |
|03
|3 |
|04
|3 |
|05
|2 |
|06
|2 |
|07
|2 |
+----+--+

45、查询选修了全部课程的学生信息

 select st.s_id, s_name, s_birth, s_sex
from student st join score sc on st.s_id=sc.s_id
group by st.s_id, s_name, s_birth, s_sex
having count(c_id)=3;
+----+------+----------+-----+
|s_id|s_name|s_birth
|s_sex|
+----+------+----------+-----+
|01
|赵雷
|1990-01-01|男
|
|02
|钱电
|1990-12-21|男
|
|03
|孙风
|1990-05-20|男
|
|04
|李云
|1990-08-06|男
|
+----+------+----------+-----+

46、查询各学生的年龄

按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select st.*,year(current_date())-year(s_birth)-(if(s_birth<current_date,1,0)) `age` from student st;
+----+------+----------+-----+---+
|s_id|s_name|s_birth
|s_sex|age|
+----+------+----------+-----+---+
|01
|赵雷
|1990-01-01|男
|29 |
|02
|钱电
|1990-12-21|男
|29 |
|03
|孙风
|1990-05-20|男
|29 |
|04
|李云
|1990-08-06|男
|29 |
|05
|周梅
|1991-12-01|女
|28 |
|06
|吴兰
|1992-03-01|女
|27 |
|07
|郑竹
|1989-07-01|女
|30 |
|08
|王菊
|1990-01-20|女
|29 |
|09
|郑竹
|1990-08-08|女
|29 |
+----+------+----------+-----+---+

47、查询本周过生日的学生

select * from student where weekofyear(current_date)=weekofyear(s_birth);

48、查询下周过生日的学生

select * from student where weekofyear(current_date)+1=weekofyear(s_birth);

49、查询本月过生日的学生

select * from student where month(current_date)=month (s_birth);

50、查询下月过生日的学生

select * from student where month(current_date)+1=month (s_birth);

最后

以上就是动人火车为你收集整理的Hive sql语句必练50题1016 HQL36-50题的全部内容，希望文章能够帮你解决Hive sql语句必练50题1016 HQL36-50题所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。