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;