hive50题

表格数据

select * from course;
+————–+—————-+————–+–+
| course.c_id | course.c_name | course.t_id |
+————–+—————-+————–+–+
| 01 | 语文 | 02 |
| 02 | 数学 | 01 |
| 03 | 英语 | 03 |
+————–+—————-+————–+–+

select * from score;
+————-+————-+—————-+–+
| score.s_id | score.c_id | score.s_score |
+————-+————-+—————-+–+
| 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 |
+————-+————-+—————-+–+

select * from student;
+—————+—————–+——————+—————-+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+—————+—————–+——————+—————-+–+
| 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 | 女 |
+—————+—————–+——————+—————-+–+

select * from teacher;
+—————+—————–+–+
| teacher.t_id | teacher.t_name |
+—————+—————–+–+
| 01 | 张三 |
| 02 | 李四 |
| 03 | 王五 |
+—————+—————–+–+

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

with one as 
(select * from score where c_id = 01),
two as 
(select * from score where c_id = 02),
result as (select o.s_id, o.s_score 01_score,t.s_score 02_score from one o join two t on o.s_id = t.s_id where o.s_score > t.s_score )
select s.*,01_score,02_score from result r  join  student  s on  s.s_id = r.s_id;

+———+———–+————-+———-+———–+———–+–+
| s.s_id | s.s_name | s.s_birth | s.s_sex | 01_score | 02_score |
+———+———–+————-+———-+———–+———–+–+
| 02 | 钱电 | 1990-12-21 | 男 | 70 | 60 |
| 04 | 李云 | 1990-08-06 | 男 | 50 | 30 |
+———+———–+————-+———-+———–+———–+–+

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

with one as 
(select * from score where c_id = 01),
two as 
(select * from score where c_id = 02),
result as (select o.s_id, o.s_score 01_score,t.s_score 02_score from one o join two t on o.s_id = t.s_id where o.s_score < t.s_score )
select s.*,01_score,02_score from result r  join  student  s on  s.s_id = r.s_id;

+———+———–+————-+———-+———–+———–+–+
| s.s_id | s.s_name | s.s_birth | s.s_sex | 01_score | 02_score |
+———+———–+————-+———-+———–+———–+–+
| 01 | 赵雷 | 1990-01-01 | 男 | 80 | 90 |
| 05 | 周梅 | 1991-12-01 | 女 | 76 | 87 |
+———+———–+————-+———-+———–+———–+–+

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

with avg_student as
(select s_id ,  avg(s_score) avg_score  from score group by s_id  having avg_score >= 60)
select s.s_id id, s.s_name name, round(avg_score ,1) avg_score from avg_student a join student s on s.s_id = a.s_id;

+—–+——-+————+–+
| id | name | avg_score |
+—–+——-+————+–+
| 01 | 赵雷 | 89.7 |
| 02 | 钱电 | 70.0 |
| 03 | 孙风 | 80.0 |
| 05 | 周梅 | 81.5 |
| 07 | 郑竹 | 93.5 |
+—–+——-+————+–+

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:

– (包括有成绩的和无成绩的)

with avg_student as
(select s_id , avg(s_score) avg_score  from score  group by s_id ),
result as
(select s.s_id id, s.s_name name, nvl(round(avg_score,1),0) avg_score  from student s left join avg_student a  on s.s_id = a.s_id)
select * from result where  avg_score < 60;

+————+————–+——————-+–+
| result.id | result.name | result.avg_score |
+————+————–+——————-+–+
| 04 | 李云 | 33.3 |
| 06 | 吴兰 | 32.5 |
| 08 | 王菊 | 0.0 |
+————+————–+——————-+–+

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

with t1 as
(select s_id,count(*) course_sum, sum(s_score) sum_score from score group by s_id) 
select s.s_id id, s.s_name name, nvl(course_sum,0) course_sum,nvl(sum_score,0) sum_score from student s left join t1  on t1.s_id = s.s_id;

+—–+——-+————-+————+–+
| id | name | course_sum | sum_score |
+—–+——-+————-+————+–+
| 01 | 赵雷 | 3 | 269 |
| 02 | 钱电 | 3 | 210 |
| 03 | 孙风 | 3 | 240 |
| 04 | 李云 | 3 | 100 |
| 05 | 周梅 | 2 | 163 |
| 06 | 吴兰 | 2 | 65 |
| 07 | 郑竹 | 2 | 187 |
| 08 | 王菊 | 0 | 0 |
+—–+——-+————-+————+–+

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

select count(1) num from teacher where t_name like "李%";

+——+–+
| num |
+——+–+
| 1 |
+——+–+

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

with t as
(select st.s_id from score s join course c on s.c_id = c.c_id join teacher t on t.t_id=c.t_id join student st on st.s_id = s.s_id where t_name="张三" group by st.s_id)
select student.* from student join t on t.s_id = student.s_id;

+—————+—————–+——————+—————-+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+—————+—————–+——————+—————-+–+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+—————+—————–+——————+—————-+–+

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

with t as
(select s.s_id from score s join course c on s.c_id = c.c_id join teacher t on t.t_id=c.t_id  where t_name="张三" group by s.s_id)
select student.* from student left join t on t.s_id = student.s_id where t.s_id is null;

+—————+—————–+——————+—————-+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+—————+—————–+——————+—————-+–+
| 06 | 吴兰 | 1992-03-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+—————+—————–+——————+—————-+–+

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

with one as
(select s_id,c_id from score where c_id = 01),
two as
(select s_id,c_id from score where c_id = 02)
select s.* from student s join one o on s.s_id = o.s_id join two  t on t.s_id = o.s_id;

+———+———–+————-+———-+–+
| s.s_id | s.s_name | s.s_birth | s.s_sex |
+———+———–+————-+———-+–+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
+———+———–+————-+———-+–+

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

with one as
(select s_id,c_id from score where c_id = 01),
two as
(select s_id,c_id from score where c_id = 02)
select s.* from student s join one o on s.s_id = o.s_id left join two  t on t.s_id = o.s_id where t.s_id is null;

+———+———–+————-+———-+–+
| s.s_id | s.s_name | s.s_birth | s.s_sex |
+———+———–+————-+———-+–+
| 06 | 吴兰 | 1992-03-01 | 女 |
+———+———–+————-+———-+–+

– 11、查询没有学全所有课程的同学的信息:

with t1 as
(select count(c_id) course_num from course ),
t2 as
(select s_id, count(c_id) cour_num from score group by s_id)
select s.* from student s left join t2 on t2.s_id=s.s_id join t1 where t2.s_id is null or  t2.cour_num < t1.course_num;

+———+———–+————-+———-+–+
| s.s_id | s.s_name | s.s_birth | s.s_sex |
+———+———–+————-+———-+–+
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+———+———–+————-+———-+–+

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

with t1 as
(select c_id from score where s_id = 01),
t2 as
(select s_id from t1 join score  s on s.c_id = t1.c_id group by s_id)
select student.* from student  join t2 on t2.s_id = student.s_id where t2.s_id != 01;

+—————+—————–+——————+—————-+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+—————+—————–+——————+—————-+–+
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+—————+—————–+——————+—————-+–+

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

with num as
(select  count(c_id) cour_num  from score where s_id = 01),
one as
(select * from score where s_id = 01 ),
t1 as
(select s.s_id , count(s.c_id) c_num from one o join score s on o.c_id = s.c_id group by  s.s_id),
t2 as
(select s_id from t1 join num where cour_num=t1.c_num and s_id != 01)
select student.* from student join t2 on t2.s_id = student.s_id;

+—————+—————–+——————+—————-+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+—————+—————–+——————+—————-+–+
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
+—————+—————–+——————+—————-+–+

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

with t as
(select s.s_id from score s join course c on s.c_id = c.c_id join teacher t on t.t_id=c.t_id  where t_name="张三" group by s.s_id)
select student.* from student left join t on t.s_id = student.s_id where t.s_id is null;

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

with t1 as
(select s_id,count(c_id) c_num from score where s_score <60 group by s_id having c_num >=2)
select  s.s_id,s.s_name,round(avg(s_score),1) avg_score from student s join t1 on t1.s_id = s.s_id join score sc on t1.s_id = sc.s_id group by s.s_id,s.s_name

+———+———–+————+–+
| s.s_id | s.s_name | avg_score |
+———+———–+————+–+
| 04 | 李云 | 33.3 |
| 06 | 吴兰 | 32.5 |
+———+———–+————+–+

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

with t1 as
(select * from score where c_id = 01 and s_score <60  order by s_score desc) 
select s.* from student s join t1 on s.s_id=t1.s_id ;

+———+———–+————-+———-+–+
| s.s_id | s.s_name | s.s_birth | s.s_sex |
+———+———–+————-+———-+–+
| 04 | 李云 | 1990-08-06 | 男 |
| 06 | 吴兰 | 1992-03-01 | 女 |
+———+———–+————-+———-+–+

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

select s_id , max(case  when c_id=01 then s_score else null end ) chinese,max(case  when c_id=02 then s_score else null end ) math,max(case  when c_id=03 then s_score else null end ) english , round(avg(s_score),2) avg_score from score group by s_id order by avg_score desc;

+——-+———-+——-+———-+————+–+
| s_id | chinese | math | english | avg_score |
+——-+———-+——-+———-+————+–+
| 07 | NULL | 89 | 98 | 93.5 |
| 01 | 80 | 90 | 99 | 89.67 |
| 05 | 76 | 87 | NULL | 81.5 |
| 03 | 80 | 80 | 80 | 80.0 |
| 02 | 70 | 60 | 80 | 70.0 |
| 04 | 50 | 30 | 20 | 33.33 |
| 06 | 31 | NULL | 34 | 32.5 |
+——-+———-+——-+———-+————+–+

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

–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

with t1 as
(select c_id course_ID ,max(s_score) max_score,min(s_score) min_score,round(avg(s_score),2) avg_score , count(s_id) num_sid, sum(case when s_score >= 60  then 1 else 0 end) pa***ate, sum(case when s_score >= 70 and s_score <80 then 1 else 0 end) mediumRate ,sum(case when s_score >= 80 and s_score <90 then 1 else 0 end) goodRate , sum(case when s_score >= 90 then 1 else 0 end)  excellenceRate from  score group by  c_id )
select course_ID,c_name course_name,max_score,min_score, avg_score, round(pa***ate/num_sid,3) pa***ate,round(mediumRate/num_sid,3) mediumRate,round(goodRate/num_sid,3) goodRate, round(excellenceRate/num_sid,3) excellenceRate  from t1 join course c on c.c_id = t1.course_ID;

+————+————–+————+————+————+———–+————-+———–+—————–+–+
| course_id | course_name | max_score | min_score | avg_score | pa***ate | mediumrate | goodrate | excellencerate |
+————+————–+————+————+————+———–+————-+———–+—————–+–+
| 01 | 语文 | 80 | 31 | 64.5 | 0.667 | 0.333 | 0.333 | 0.0 |
| 02 | 数学 | 90 | 30 | 72.67 | 0.833 | 0.0 | 0.5 | 0.167 |
| 03 | 英语 | 99 | 20 | 68.5 | 0.667 | 0.0 | 0.333 | 0.333 |
+————+————–+————+————+————+———–+————-+———–+—————–+–+

– 19、按各科成绩进行排序，并显示排名:

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

+————-+————-+—————-+——–+–+
| score.s_id | score.c_id | score.s_score | ranks |
+————-+————-+—————-+——–+–+
| 03 | 01 | 80 | 1 |
| 01 | 01 | 80 | 2 |
| 05 | 01 | 76 | 3 |
| 02 | 01 | 70 | 4 |
| 04 | 01 | 50 | 5 |
| 06 | 01 | 31 | 6 |
| 01 | 02 | 90 | 1 |
| 07 | 02 | 89 | 2 |
| 05 | 02 | 87 | 3 |
| 03 | 02 | 80 | 4 |
| 02 | 02 | 60 | 5 |
| 04 | 02 | 30 | 6 |
| 01 | 03 | 99 | 1 |
| 07 | 03 | 98 | 2 |
| 02 | 03 | 80 | 3 |
| 03 | 03 | 80 | 4 |
| 06 | 03 | 34 | 5 |
| 04 | 03 | 20 | 6 |
+————-+————-+—————-+——–+–+

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

with t1 as
(select s_id,sum(s_score) sum_score  from score group by s_id order by sum_score desc )
select s.s_id id,s_name name, sum_score, row_number() over(order by  sum_score desc) rank from student s join t1 on s.s_id=t1.s_id;

+————-+———+———–+———-+–+
| score.s_id | s_name | sumscore | ranking |
+————-+———+———–+———-+–+
| 01 | 赵雷 | 269 | 1 |
| 03 | 孙风 | 240 | 2 |
| 02 | 钱电 | 210 | 3 |
| 07 | 郑竹 | 187 | 4 |
| 05 | 周梅 | 163 | 5 |
| 04 | 李云 | 100 | 6 |
| 06 | 吴兰 | 65 | 7 |
+————-+———+———–+———-+–+

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

select t.t_name teacher_name,c.c_name course_name,round(avg(s.s_score),2) avg_score from teacher t join course c on t.t_id = c.t_id join score s on s.c_id = c.c_id group by t.t_name,c.c_name order by avg_score desc;

+—————+————–+————+–+
| teacher_name | course_name | avg_score |
+—————+————–+————+–+
| 张三 | 数学 | 72.67 |
| 王五 | 英语 | 68.5 |
| 李四 | 语文 | 64.5 |
+—————+————–+————+–+

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

with t as
(select s_id, max(case when c_id = 01 then s_score else 0 end) chinese,max(case when c_id = 02 then s_score else 0 end) math,max(case when c_id = 03 then s_score else 0 end) english,sum(s_score) sum_score ,row_number() over( order by sum(s_score) desc) rank from score group by s_id)
select  s.* , chinese,math,english ,sum_score from student s join  t on t.s_id = s.s_id where rank between 2 and 3 ;

+———+———–+————-+———-+———-+——-+———-+————+–+
| s.s_id | s.s_name | s.s_birth | s.s_sex | chinese | math | english | sum_score |
+———+———–+————-+———-+———-+——-+———-+————+–+
| 03 | 孙风 | 1990-05-20 | 男 | 80 | 80 | 80 | 240 |
| 02 | 钱电 | 1990-12-21 | 男 | 70 | 60 | 80 | 210 |
+———+———–+————-+———-+———-+——-+———-+————+–+

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

with score_process as
(select  c_id, 
sum(case when s_score>=85 and s_score<=100 then 1 else 0 end) Num85_100 , round(sum(case when s_score>=85 and s_score<=100 then 1 else 0 end)/count(s_id),2) Rate85_100, 
sum(case when s_score>=70 and s_score<85 then 1 else 0 end) Num70_85 , round(sum(case when s_score>=70 and s_score<85 then 1 else 0 end)/count(s_id),2) Rate70_85,
sum(case when s_score>=60 and s_score<70 then 1 else 0 end) Num60_70 , round(sum(case when s_score>=60 and s_score<70 then 1 else 0 end)/count(s_id),2) Rate60_70,
sum(case when s_score>=0 and s_score<60 then 1 else 0 end) Num0_60 , round(sum(case when s_score>=0 and s_score<60 then 1 else 0 end)/count(s_id),2) Rate0_60
from score group by c_id)
select  c.c_name,s.* from course c join score_process s on s.c_id = c.c_id;

+———–+———+————–+—————+————-+————–+————-+————–+————+————-+–+
| c.c_name | s.c_id | s.num85_100 | s.rate85_100 | s.num70_85 | s.rate70_85 | s.num60_70 | s.rate60_70 | s.num0_60 | s.rate0_60 |
+———–+———+————–+—————+————-+————–+————-+————–+————+————-+–+
| 语文 | 01 | 0 | 0.0 | 4 | 0.67 | 0 | 0.0 | 2 | 0.33 |
| 数学 | 02 | 3 | 0.5 | 1 | 0.17 | 1 | 0.17 | 1 | 0.17 |
| 英语 | 03 | 2 | 0.33 | 2 | 0.33 | 0 | 0.0 | 2 | 0.33 |
+———–+———+————–+—————+————-+————–+————-+————–+————+————-+–+

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

with t as
(select  s_id, round(avg(s_score),2) avg_score,  RANK() over(order by avg(s_score) desc) rank from score group by s_id)
select s.s_name name, avg_score ,rank from student s join t  on  t.s_id = s.s_id;

+——-+————+——-+–+
| name | avg_score | rank |
+——-+————+——-+–+
| 郑竹 | 93.5 | 1 |
| 赵雷 | 89.67 | 2 |
| 周梅 | 81.5 | 3 |
| 孙风 | 80.0 | 4 |
| 钱电 | 70.0 | 5 |
| 李云 | 33.33 | 6 |
| 吴兰 | 32.5 | 7 |
+——-+————+——-+–+

– 25、查询各科成绩前三名的记录

with t as
(select  *, row_number() over(partition by c_id order by s_score desc ) ranking from score )
select c.c_id c_id,c.c_name course_name,s.s_name stu_name, t.s_score score from t join course c on c.c_id = t.c_id join student s on s.s_id = t.s_id where ranking between 1 and 3;

+——-+————–+———–+——–+–+
| c_id | course_name | stu_name | score |
+——-+————–+———–+——–+–+
| 01 | 语文 | 孙风 | 80 |
| 01 | 语文 | 赵雷 | 80 |
| 01 | 语文 | 周梅 | 76 |
| 02 | 数学 | 赵雷 | 90 |
| 02 | 数学 | 郑竹 | 89 |
| 02 | 数学 | 周梅 | 87 |
| 03 | 英语 | 赵雷 | 99 |
| 03 | 英语 | 郑竹 | 98 |
| 03 | 英语 | 钱电 | 80 |
+——-+————–+———–+——–+–+

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

with t as
(select c_id,count(s_id) student_num from score where s_score < 60 group by c_id )
select c.c_name course_name, student_num from course c join t on c.c_id= t.c_id;

+————–+————–+–+
| course_name | student_num |
+————–+————–+–+
| 语文 | 2 |
| 数学 | 1 |
| 英语 | 2 |
+————–+————–+–+

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

with t as
(select s_id,count(c_id) course_num from score group by s_id having course_num =2)
select s.s_id id, s_name name from student s join t on t.s_id = s.s_id;

+—–+——-+–+
| id | name |
+—–+——-+–+
| 05 | 周梅 |
| 06 | 吴兰 |
| 07 | 郑竹 |
+—–+——-+–+

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

select sum(case when s_sex = '女' then 1 else 0 end) female_num,sum(case when s_sex = '男' then 1 else 0 end) male_num from student ;

+————-+———–+–+
| female_num | male_num |
+————-+———–+–+
| 4 | 4 |
+————-+———–+–+

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

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

+—————+—————–+——————+—————-+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+—————+—————–+——————+—————-+–+
| 03 | 孙风 | 1990-05-20 | 男 |
+—————+—————–+——————+—————-+–+

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

select  s_name,s_sex,count(s_id) num from student group by s_name,s_sex having  num >= 2;

+———+——–+——+–+
| s_name | s_sex | num |
+———+——–+——+–+
+———+——–+——+–+

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

with  t as
(select *,year(s_birth) birth_year from student)
select t.s_name name from t where birth_year = 1990;

+——-+–+
| name |
+——-+–+
| 赵雷 |
| 钱电 |
| 孙风 |
| 李云 |
| 王菊 |
+——-+–+

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

select score.c_id, c_name,round(avg(s_score),2) avg_score from score join course on score.c_id= course.c_id group by score.c_id,c_name  order by avg_score desc,c_id asc ;

+————-+———+————+–+
| score.c_id | c_name | avg_score |
+————-+———+————+–+
| 02 | 数学 | 72.67 |
| 03 | 英语 | 68.5 |
| 01 | 语文 | 64.5 |
+————-+———+————+–+

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

with t as
(select s_id,avg(s_score) avg_score from score group by s_id having avg_score>=85)
select s.s_id `学号`, s_name `姓名`, round(avg_score,2 ) `平均分` from student  s join t on t.s_id = s.s_id;

+—–+—–+——–+–+
| 学号 | 姓名 | 平均分 |
+—–+—–+——–+–+
| 01 | 赵雷 | 89.67 |
| 07 | 郑竹 | 93.5 |
+—–+—–+——–+–+

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

select s_name  `学生姓名` ,s_score `分数` from course c join score s on c.c_id = s.c_id join student st on st.s_id = s.s_id  where c.c_name = '数学' and s_score < 60 ;

+——-+—–+–+
| 学生姓名 | 分数 |
+——-+—–+–+
| 李云 | 30 |
+——-+—–+–+

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

with t as
(select  s_id, max(if(c_id=01 ,s_score, 0 )) chinese,max(if(c_id=02 ,s_score, 0 )) math,max(if(c_id=03 ,s_score, 0 )) english,sum(s_score) sum_score from score group by s_id )
select s_name name,chinese,math,english,sum_score from student s join t on t.s_id = s.s_id;

+——-+———-+——-+———-+————+–+
| name | chinese | math | english | sum_score |
+——-+———-+——-+———-+————+–+
| 赵雷 | 80 | 90 | 99 | 269 |
| 钱电 | 70 | 60 | 80 | 210 |
| 孙风 | 80 | 80 | 80 | 240 |
| 李云 | 50 | 30 | 20 | 100 |
| 周梅 | 76 | 87 | 0 | 163 |
| 吴兰 | 31 | 0 | 34 | 65 |
| 郑竹 | 0 | 89 | 98 | 187 |
+——-+———-+——-+———-+————+–+

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

select s_name,c_name,s_score  from  course c join score s on c.c_id = s.c_id join student st on st.s_id = s.s_id where s_score > 70;

+———+———+———-+–+
| s_name | c_name | s_score |
+———+———+———-+–+
| 赵雷 | 语文 | 80 |
| 赵雷 | 数学 | 90 |
| 赵雷 | 英语 | 99 |
| 钱电 | 英语 | 80 |
| 孙风 | 语文 | 80 |
| 孙风 | 数学 | 80 |
| 孙风 | 英语 | 80 |
| 周梅 | 语文 | 76 |
| 周梅 | 数学 | 87 |
| 郑竹 | 数学 | 89 |
| 郑竹 | 英语 | 98 |
+———+———+———-+–+

– 37、查询课程不及格学生的名字，课程，分数:

select s_name,c_name,s_score from course c join score s on c.c_id = s.c_id join student st on st.s_id = s.s_id  where s_score < 60  ;

+———+———+———-+–+
| s_name | c_name | s_score |
+———+———+———-+–+
| 李云 | 语文 | 50 |
| 李云 | 数学 | 30 |
| 李云 | 英语 | 20 |
| 吴兰 | 语文 | 31 |
| 吴兰 | 英语 | 34 |
+———+———+———-+–+

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

select  s.s_id `学号`,s_name `姓名` from  student st join score s on s.s_id = st.s_id  where c_id=01 and s_score >=80;

+—–+—–+–+
| 学号 | 姓名 |
+—–+—–+–+
| 01 | 赵雷 |
| 03 | 孙风 |
+—–+—–+–+

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

select c_name,count(s.s_id) student_num from course c join  score s on s.c_id = c.c_id group by c_name;

+———+————–+–+
| c_name | student_num |
+———+————–+–+
| 数学 | 6 |
| 英语 | 6 |
| 语文 | 6 |
+———+————–+–+

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

select st.*,c_name,s_score  from course c join score s on c.c_id = s.c_id join teacher t on t.t_id = c.t_id join student  st on st.s_id=s.s_id where t_name='张三' order by s_score desc limit 1;

+———-+————+————-+———–+———+———-+–+
| st.s_id | st.s_name | st.s_birth | st.s_sex | c_name | s_score |
+———-+————+————-+———–+———+———-+–+
| 01 | 赵雷 | 1990-01-01 | 男 | 数学 | 90 |
+———-+————+————-+———–+———+———-+–+

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

select distinct s1.* from score s1,score s2 where s1.c_id <>s2.c_id and s1.s_score=s2.s_score ;

+———-+———-+————-+–+
| s1.s_id | s1.c_id | s1.s_score |
+———-+———-+————-+–+
| 01 | 01 | 80 |
| 02 | 03 | 80 |
| 03 | 01 | 80 |
| 03 | 02 | 80 |
| 03 | 03 | 80 |
+———-+———-+————-+–+

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

with t as
(select c_name,s_name,s_score, row_number() over(partition by c_name order by s_score desc) rank from student s join score sc on sc.s_id=s.s_id join course c on c.c_id=sc.c_id)
select * from t where rank between 1 and 3;

+———–+———–+————+———+–+
| t.c_name | t.s_name | t.s_score | t.rank |
+———–+———–+————+———+–+
| 数学 | 赵雷 | 90 | 1 |
| 数学 | 郑竹 | 89 | 2 |
| 数学 | 周梅 | 87 | 3 |
| 英语 | 赵雷 | 99 | 1 |
| 英语 | 郑竹 | 98 | 2 |
| 英语 | 孙风 | 80 | 3 |
| 语文 | 孙风 | 80 | 1 |
| 语文 | 赵雷 | 80 | 2 |
| 语文 | 周梅 | 76 | 3 |
+———–+———–+————+———+–+

– 43、统计每门课程的学生选修人数（超过5人的课程才统计）:

– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id,count(s_id) num from score group by c_id having num > 5 order by num desc ,c_id asc;

+——-+——+–+
| c_id | num |
+——-+——+–+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+——-+——+–+

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

select s_id,count(c_id) course_num from score group by s_id having course_num>=2;

+——-+————-+–+
| s_id | course_num |
+——-+————-+–+
| 01 | 3 |
| 02 | 3 |
| 03 | 3 |
| 04 | 3 |
| 05 | 2 |
| 06 | 2 |
| 07 | 2 |
+——-+————-+–+

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

with t as
(select count(c_id) sum_course from course),
t1 as
(select s_id,count(c_id) course_num from  score group by s_id)
select  s.*  from t1 join t join student s on s.s_id=t1.s_id where sum_course=course_num;

+———+———–+————-+———-+–+
| s.s_id | s.s_name | s.s_birth | s.s_sex |
+———+———–+————-+———-+–+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
+———+———–+————-+———-+–+

–46、查询各学生的年龄(周岁):

select *, floor((datediff(current_date,s_birth))/365) age from student;

+—————+—————–+——————+—————-+——+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex | age |
+—————+—————–+——————+—————-+——+–+
| 01 | 赵雷 | 1990-01-01 | 男 | 29 |
| 02 | 钱电 | 1990-12-21 | 男 | 28 |
| 03 | 孙风 | 1990-05-20 | 男 | 29 |
| 04 | 李云 | 1990-08-06 | 男 | 29 |
| 05 | 周梅 | 1991-12-01 | 女 | 28 |
| 06 | 吴兰 | 1992-03-01 | 女 | 27 |
| 07 | 郑竹 | 1989-07-01 | 女 | 30 |
| 08 | 王菊 | 1990-01-20 | 女 | 29 |
+—————+—————–+——————+—————-+——+–+

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

select * from student where weekofyear(s_birth)=weekofyear(current_date);

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

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

+—————+—————–+——————+—————-+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+—————+—————–+——————+—————-+–+
| 02 | 钱电 | 1990-12-21 | 男 |
+—————+—————–+——————+—————-+–+

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

select * from student where month(current_date) = month(s_birth) ;

+—————+—————–+——————+—————-+–+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+—————+—————–+——————+—————-+–+
| 02 | 钱电 | 1990-12-21 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
+—————+—————–+——————+—————-+–+

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

select * from student where month(s_birth) = 12;

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/194342.html