hive学习之经典sql50题 hive版(四)

21.查询男生、女生人数 
select f.c,m.c
from
(
select count(sid) c from student where ssex='男'
) f
join
(
select count(sid) c from student where ssex='女'

) m;

22.查询名字中含有"风"字的学生信息

select * from student where sname like '%风%';

23.查询同名同性学生名单，并统计同名人数 
select sname,ssex,count(1)
from student
group by sname,ssex

having count(1)>1;

24.查询1990年出生的学生名单

select sname from student where substring(sage,0,4)='1990';

25.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cid c,round(avg(score),1) a from sc group by cid order by a,c desc;


26.查询不及格的课程，并按课程号从大到小排列 

select cid c,score from sc where score<60 order by c desc; 

27.查询每门功课成绩最好的前两名 
select s.sid,s.cid,s.score
from
(
select  sid,cid,score,row_number()over(partition by cid order by score desc) rank from sc
) s

where s.rank<=2;

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

select cid,count(sid) c from sc group by cid having count(sid)>5 order by c desc,cid;

29.检索至少选修两门课程的学生学号

select sid,count(cid) c from sc group by sid having c>=2;

30.查询选修了全部课程的学生信息 
select student.sid,student.sname
from
student
join
(
select s.sid
from
(
select count(cid) c from course
) cou
join
(
select sid,count(cid) c from sc group by sid
) s
on cou.c=s.c
) o
on student.sid=o.sid;