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

11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
select stu.sid
from
(
select other.s sid,other.c cid
from
(
select cid from sc where sid=01
) s01
join
(
select sid s,cid c from sc where sid!=01
) other
on other.c=s01.cid
) stu
group by stu.sid
having count(cid)>=1;

12.查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名

select stu.sid,count(cid)
from
(
select other.s sid,other.c cid
from
(
select cid from sc where sid=1
) s01
left join
(
select sid s,cid c from sc where sid!=01
) other
on other.c=s01.cid
) stu
group by stu.sid
having count(cid)=3;

13.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select sid,count(score) c,round(avg(score),1) from sc where score<60 group by sid having c>=2;

14.检索"01"课程分数小于60，按分数降序排列的学生信息

select sid,score s from sc where cid=01 and score<60 order by s desc;

15.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select nvl(c12.sid,c3.sid),c12.c1,c12.c2,nvl(c3.score,0),round((c12.c1+c12.c2+nvl(c3.score,0))/3,1) avg
from
(
select nvl(c1.sid,c2.sid) sid,nvl(c1.score,0) c1,nvl(c2.score,0) c2
from
(
select sid,score from sc where cid=1
) c1
full join
(
select sid,score from sc where cid=2
) c2
on c1.sid=c2.sid
) c12
full join
(
select sid,score from sc where cid=3
) c3
on c12.sid=c3.sid

order by avg desc;

16.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
select c.cid,course.cname,c.max,c.min,c.avg,c.ge60,c.ge60lt70,c.ge70lt80,c.ge80lt90,c.ge90
from course
join
(
select cid,max(score) max,min(score) min,round(avg(score),1) avg,
round(sum(case when score>=60 then 1 else 0 end)/count(cid),3) ge60,
round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(cid),3) ge60lt70, 
round(sum(case when score>=70 and score<80 then 1 else 0 end)/count(cid),3) ge70lt80,
round(sum(case when score>=80 and score<90 then 1 else 0 end)/count(cid),3) ge80lt90,
round(sum(case when score>=90 then 1 else 0 end)/count(cid),3) ge90
from sc group by cid
) c

on c.cid=course.cid;

17.查询学生的总成绩并进行排名
select sid,t.s,row_number()over(order by t.s desc)
from
(
select sid,sum(score) s from sc group by sid

) t;

18.查询不同老师所教不同课程平均分从高到低显示
select c.cid,course.tid,c.avg 
from course
join
(
select cid,round(avg(score),1) avg from sc group by cid
) c
on course.cid=c.cid

order by c.avg desc;

19.查询每门课程被选修的学生数
select cid,count(sid) from sc group by cid;

20.查询出只选修了一门课程的全部学生的学号和姓名

select sid,count(cid) from sc group by sid having count(cid)=1;