hive50題

發布時間：2020-07-16 23:24:53 來源：網絡閱讀：186 作者：_追夢_ 欄目：大數據

表格數據

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;

+---------------+-----------------+------------------+----------------+--+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+---------------+-----------------+------------------+----------------+--+
| 06 | 吳蘭 | 1992-03-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+---------------+-----------------+------------------+----------------+--+

– 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 '%風%';

– 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);

+---------------+-----------------+------------------+----------------+--+
| student.s_id | student.s_name | student.s_birth | student.s_sex |
+---------------+-----------------+------------------+----------------+--+
+---------------+-----------------+------------------+----------------+--+

– 48、查詢下周過生日的學生:

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

– 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;