sql经典50题使用hive

建表

学生

create table student(
s_id int,
s_name string,
dt string,
sex string
)
row format delimited
fields terminated by '\t'
;
01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-01	女
06	吴兰	1992-03-01	女
07	郑竹	1989-07-01	女
08	王菊	1990-01-20	女

load data local inpath './test/student' into table student;

课程

create table course(
c_id string,
c_name string,
t_id string
)
row format delimited
fields terminated by '\t'
;
01	语文	02
02	数学	01
03	英语	03

load data local inpath './test/course' into table course;

教师

create table teacher(
t_id string,
t_name string
)
row format delimited
fields terminated by '\t'
;

01	张三
02	李四
03	王五

load data local inpath './test/teacher' into table teacher;

成绩表

create table score(
s_id string,
c_id string,
score int
)
row format delimited
fields terminated by '\t'
;

01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

load data local inpath './test/score' into table score;

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:

select t1.s_id,t1.score,t2.s_id from
(select s_id,score from score where c_id='01') t1
left join
(select s_id,score from score where c_id='02') t2
on t1.s_id=t2.s_id
where t1.score>t2.score

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:

select t1.s_id,t1.score,t2.s_id from
(select s_id,score from score where c_id='01') t1
left join
(select s_id,score from score where c_id='02') t2
on t1.s_id=t2.s_id
where t1.score<t2.score

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:

select
t2.s_id,
t2.s_name,
t1.avgs
from
(select s_id,round(avg(nvl(score,0)),2) avgs from score group by s_id) t1
left join
student t2
on t2.s_id=t1.s_id
where t1.avgs>60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
(包括有成绩的和无成绩的)

select
t2.s_id,
t2.s_name,
t1.avgs
from
student t2
left join
(select s_id,round(avg(nvl(score,0)),2) avgs from score group by s_id) t1
on t2.s_id=t1.s_id
where t1.avgs<60;

---

select * from
(
select 
t1.s_id,round(avg(nvl(score,0)),2) avgs
from
student t1
left join
score s1
on t1.s_id=s1.s_id
group by t1.s_id
)t2
where t2.avgs<60
;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:

select
st.s_id,
st.s_name,
count(*),
sum(sc.score)
from
student st left join score sc
on st.s_id=sc.s_id
group by st.s_id,st.s_name
;

6、查询"李"姓老师的数量:

select t_name from teacher where t_name like '李%';

7、查询学过"张三"老师授课的同学的信息:

select
st.s_id,st.s_name
from
student st
left join
score sc
on st.s_id=sc.s_id
left join
course cs
on 
sc.c_id=cs.c_id
left join
(select t.t_id,t.t_name from teacher t where t_name='张三') ts
on
cs.t_id=ts.t_id
where ts.t_name='张三'

---

8、查询没学过"张三"老师授课的同学的信息:

select
*
from 
student
where
s_id not in
(
select
st.s_id
from
student st
left join
score sc
on st.s_id=sc.s_id
left join
course cs
on 
sc.c_id=cs.c_id
left join
teacher ts
on
cs.t_id=ts.t_id
where ts.t_name='张三'
);

---

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:

select
*
from
(
select
st.s_id,st.s_name
from
student st
left join
score sc
on st.s_id=sc.s_id
where sc.c_id='01')t1
where
t1.s_id in
(
select
st.s_id
from
student st
left join
score sc
on st.s_id=sc.s_id
where sc.c_id='02');

---

select * from 
student s 
join
(select t.s_id from
(select s_id from score where c_id="01"
union all
select s_id from score where c_id="02") t
group by s_id
having count(s_id)=2) t1
on s.s_id=t1.s_id;

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:

select
*
from
(
select
st.s_id,st.s_name
from
student st
left join
score sc
on st.s_id=sc.s_id
where sc.c_id='01')t1
where
t1.s_id not in
(
select
st.s_id
from
student st
left join
score sc
on st.s_id=sc.s_id
where sc.c_id='02');

---

select 
*
from 
(select s_id from score where c_id='01') stt
left join
student st
on
stt.s_id=st.s_id
left join
(select s_id from score where c_id='02') sct
on 
st.s_id=sct.s_id
where sct.s_id is null;

11、查询没有学全所有课程的同学的信息:
–先查询出课程的总数量–再查询所需结果

select
*
from
(
select st.*,count(1) over(partition by st.s_id rows between unbounded preceding  and UNBOUNDED FOLLOWING) sum
from
score sc 
left join student st
on st.s_id=sc.s_id
) t2,(select count(*) c from course) s
where t2.sum<s.c;

---

select
st.*
from
(
select
t1.s_id s_id
from
(select s_id,count(c_id) c1 from score group by s_id) t1,
(select count(c_id) c2 from course) t2
where t1.c1<t2.c2
)t3 left join
student st
on st.s_id=t3.s_id

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息:

select
*
from
(select
sc.s_id
from
score sc
where 
sc.c_id in (select  c_id from score where s_id ='01') group by sc.s_id
)t1
left join
student st
on st.s_id=t1.s_id;

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息:

select
*
from
(select s_id,
count(b.c_id) over(partition by b.s_id rows between unbounded preceding  and UNBOUNDED FOLLOWING) c -- 开窗函数求出与01同学不同的学生的课程总数,a.sum from
(
select c_id,
count(1) over(partition by s_id rows between unbounded preceding  and UNBOUNDED FOLLOWING) sum from 
score where s_id='01'
) a -- 开窗函数求出01同学的所学课程总数，及所学课程的id
left join
(select * from score where s_id!='01') b -- 将01课程的学生与其他学生连接
on a.c_id=b.c_id
) t1
left join
student st	--与学生表相连接
on t1.s_id=st.s_id
where t1.c=t1.sum;

14、查询没学过"张三"老师讲授的任一门课程的学生姓名:

select * 
from teacher t
left join
course c
on t.t_id=c.c_id
left join
score sc
on sc.c_id=c.c_id
left join 
student st
on 
st.s_id=sc.s_id
where t.t_name='张三'
;

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

select
t1.s_id,st.s_name,t1.r
from
(
select
s_id,count(*) c,round(avg(score),2) r
from
score
where score<60
group by s_id
)t1
left join
student st
on st.s_id=t1.s_id
where r>=2
;

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

select * 
from score sc 
left join 
student st 
on sc.s_id=st.s_id 
where sc.c_id='01' and sc.score<60
order by sc.score desc;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:

select
*
from
(
select *,round(avg(sc.score) over(partition by sc.s_id rows between unbounded preceding  and UNBOUNDED FOLLOWING),2) avgs
from 
score sc
)t
order by t.avgs desc;

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率:

select
ce.c_name,t1.*
from
(
select
sc.c_id,max(score),min(score),
round(sum(case when score>=60 then 1 else 0 end)/count(score),2),
round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(score),2),
round(sum(case when score>=70 and score<80 then 1 else 0 end)/count(score),2),
round(sum(case when score>=70 and score<80 then 1 else 0 end)/count(score),2),
round(sum(case when score>=80 and score<90 then 1 else 0 end)/count(score),2),
round(sum(case when score>=90 then 1 else 0 end)/count(score),2)
from score sc 
group by sc.c_id
) t1
left join course ce
on t1.c_id=ce.c_id
;

19、按各科成绩进行排序，并显示排名:– row_number() over()分组排序功能

select *,row_number() over(partition by c_id order by score desc) from score

20、查询学生的总成绩并进行排名:

select
s_id,sum(score) sim
from score
group by s_id
order by sim
;

21、查询不同老师所教不同课程平均分从高到低显示:

select sc.c_id,t_id,avg(score)
from 
score sc left join course co
on sc.c_id=co.c_id
group by co.t_id,sc.c_id
;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:

select *
from
(
select *,rank() over(partition by c_id order by score desc) rank from 
score sc
left join
student st
on
sc.s_id=st.s_id
) t1
where t1.rank='02' or t1.rank='03'
;

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select
sc.c_id,
cs.c_name,
sum(case when sc.score<=60 then 1 else 0 end)/count(nvl(score,0)),
sum(case when sc.score>=60 and sc.score<=70 then 1 else 0 end)/count(nvl(score,0)),
sum(case when sc.score>=80 and sc.score<=90 then 1 else 0 end)/count(nvl(score,0)),
sum(case when sc.score>=90 and sc.score<=100 then 1 else 0 end)/count(nvl(score,0))
from score sc left join course cs on sc.c_id=cs.c_id
group by sc.c_id,cs.c_name
;

24、查询学生平均成绩及其名次:

select
s.s_id,s.avgs,rank() over(order by s.avgs desc)
from(
select
st.s_id,
avg(nvl(score,0)) avgs
from student st left join score sc on st.s_id=sc.s_id group by st.s_id
)s
;

25、查询各科成绩前三名的记录三个语句

select
*
from
(
select c_id,rank() over(partition by c_id order by score) rank from score
)s
where s.rank<4;

26、查询每门课程被选修的学生数:

select c_id,count(*) from score group by c_id;

27、查询出只有两门课程的全部学生的学号和姓名:

select 
*
from
(
select sc.s_id,count(*) c from score sc
group by sc.s_id
)t 
left join
student st
on
st.s_id = t.s_id
where t.c='2'
;

28、查询男生、女生人数:

select 
sum(case when sex='男' then 1 else 0 end) as `男`,
sum(case when sex='女' then 1 else 0 end) as `女`
 from student;

29、查询名字中含有"风"字的学生信息:

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

30、查询同名同性学生名单，并统计同名人数:

select st.s_name,count(*) from student st left join student stt on st.s_name=stt.s_name
where st.s_id!=stt.s_id
group by st.s_name
;

31、查询1990年出生的学生名单:

select * from student where year(dt)='1990';

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:

select c_id,avg(score) avgs from score group by c_id order by avgs desc,c_id asc;

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:

select st.s_id,st.s_name,avg(score) av from student st left join score sc on st.s_id=sc.s_id
group by st.s_id,st.s_name
having av>=80;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数:

select 
st.s_name,sc.score
from
course cs 
left join
score sc
on cs.c_id=sc.c_id
left join
student st
on st.s_id=sc.s_id
where sc.score<60 and cs.c_name='数学'
;

35、查询所有学生的课程及分数情况:

select * from course cs left join score sc on cs.c_id=sc.c_id left join student st on sc.s_id=st.s_id;  

36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:

select * from course cs left join score sc on cs.c_id=sc.c_id left join student st on sc.s_id=st.s_id where sc.score>70;

37、查询课程不及格的学生:

select * from course cs left join score sc on cs.c_id=sc.c_id left join student st on sc.s_id=st.s_id where sc.score<60;  

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:

select * from  student st 
left  join 
score sc 
on st.s_id=sc.s_id
 where sc.score>80  and sc.c_id='01';

39、求每门课程的学生人数:

select c_id,count(*) from score group by c_id;

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩:

select
*
from
(
select 
st.*,sc.score,
rank() over(order by sc.score desc rows between unbounded preceding and unbounded following) rc
from
student st
left join
score sc
on st.s_id=sc.s_id
left join
course cs
on sc.c_id=sc.s_id
left join
teacher t on t.t_name='张三'
) t
where rc=1;

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:

select * from score scc left join score sc on sc.score=scc.score
where sc.c_id!=scc.c_id and sc.s_id!=scc.s_id;

42、查询每门课程成绩最好的前三名:

select
*
from
(
select *,row_number() over(partition by c_id order by score rows between unbounded preceding and unbounded following) rc from score 
) t
where t.rc<4
;

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

select c_id,count(*) c from score group by c_id having c>5 order by c desc,c_id;

44、检索至少选修两门课程的学生学号:

select s_id,count(*) c from score group by s_id having c>=2;

45、查询选修了全部课程的学生信息:

select
*
from
(select s_id,count(c_idllc from score group by s_id) t
left semi join
(select count(*) cc from course) t2
on t.c=t2.cc

46、查询各学生的年龄(周岁):
– 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select cast((year(current_timestamp)-year(dt)) as int),month(dt),day(dt) from student 
;

SELECT dt,(year(current_date())-year(dt)-(CASE WHEN month(current_date())>month(dt) THEN 0 when month(current_date())= month(dt) and day(current_date())>=day(dt) then 0 ELSE 1 END)) AS age
FROM student;
from student s;

47、查询本周过生日的学生:

select * from student where weekofyear(current_date())+1=weekofyear(dt)

48、查询下周过生日的学生:

select * from student where month(current_date())+1=month(dt)

49、查询本月过生日的学生:

select * from student where month(current_date())=month(dt)

50、查询12月份过生日的学生:

select * from student where month(dt)='12';