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;