Hive sql语句必练50题

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);