with sc1 as(select*from score where c_id =1),
sc2 as(select*from score where c_id =2)select s.*, sc1.s_score as score_01
, sc2.s_score as score_02
from student s
join sc1 on s.s_id = sc1.s_id
join sc2 on s.s_id = sc2.s_id
where sc1.s_score > sc2.s_score;
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
with sc1 as(select*from score where c_id =1),
sc2 as(select*from score where c_id =2)select s.*, sc1.s_score as score_01
, sc2.s_score as score_02
from student s
join sc1 on s.s_id = sc1.s_id
join sc2 on s.s_id = sc2.s_id
where sc1.s_score < sc2.s_score;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
with t1 as(select s_id
,avg(s_score)as avg_score
from score
groupby s_id
having avg_score >=60)select s.s_id
, s.s_name
,round(avg_score,2)as avg_score
from student s
join t1 on s.s_id = t1.s_id;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
with t1 as(select s_id
,avg(s_score)as avg_score
from score
groupby s_id)select s.s_id
, s.s_name
,if(avg_score isnull,0,round(avg_score,2))as avg_score
from student s
leftjoin t1 on s.s_id = t1.s_id
where avg_score isnullor avg_score <60;
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
with t1 as(select s_id
,count(*)as cnt_course
,sum(s_score)as sum_score
from score
groupby s_id)select s.s_id
, s.s_name
,if(cnt_course isnull,0, cnt_course)as cnt_course
,if(sum_score isnull,0, sum_score)as sum_score
from student s
leftjoin t1 on s.s_id = t1.s_id;
6、查询"李"姓老师的数量
selectcount(*)as cnt_name_li
from teacher
where t_name like'李%';
7、查询学过"张三"老师授课的同学的信息
with t1 as(select c_id
from course c
join teacher t on c.t_id = t.t_id
where t_name ='张三'),
t2 as(selectdistinct s_id
from score sc
join t1 on sc.c_id = t1.c_id)select*from student s
where s_id in(select t2.s_id
from t2);-- exists (-- select *-- from t2-- where s.s_id = t2.s_id);
8、查询没学过"张三"老师授课的同学的信息
with t1 as(select c_id
from course c
join teacher t on c.t_id = t.t_id
where t_name ='张三'),
t2 as(selectdistinct s_id
from score sc
join t1 on sc.c_id = t1.c_id)select*from student s
where s.s_id notin(select t2.s_id
from t2);-- not exists (-- select *-- from t2-- where s.s_id = t2.s_id);
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
-- 解法一with t1 as(select s_id
from score
where c_id in('01','02')groupby s_id
havingcount(*)=2)select*from student s
whereexists(select*from t1
where s.s_id = t1.s_id);-- 解法二with sc1 as(select s_id from score where c_id =1),
sc2 as(select s_id from score where c_id =2)select*from student s
where s.s_id in(select sc2.s_id from sc2)and s.s_id in(select sc1.s_id from sc1);--解法三with sc1 as(select s_id from score where c_id =1),
sc2 as(select s_id from score where c_id =2),
sc3 as(select sc1.s_id sid
from sc1
leftjoin sc2 on sc1.s_id = sc2.s_id
where sc2.s_id isnotnull)select*from student s
where s.s_id in(select sc3.sid from sc3);
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
-- 解法一with t1 as(select s_id from score where c_id =1),
t2 as(select s_id from score where c_id =2),
t3 as(select t1.s_id as s_id
from t1
leftjoin t2 on t1.s_id = t2.s_id
where t2.s_id isnull)select*from student s
whereexists(select*from t3 where s.s_id = t3.s_id);-- 解法二with sc1 as(select s_id from score where c_id =1),
sc2 as(select s_id from score where c_id =2)select*from student s
where s.s_id in(select sc1.s_id from sc1)and s.s_id notin(select sc2.s_id from sc2);
-- 解法一with t1 as(select s_id
from score
where c_id in('01','02','03')groupby s_id
havingcount(*)=(selectcount(*)as cnt_course from course))select*from student s
wherenotexists(select*from t1
where s.s_id = t1.s_id);-- 解法二with t1 as(selectcount(*)as cnt_course
from course),
t2 as(select s_id
,count(*)as cnt_course
from score
groupby s_id),
t3 as(select s_id
from t1
crossjoin t2
where t1.cnt_course = t2.cnt_course)select*from student s
wherenotexists(select*from t3
where s.s_id = t3.s_id);-- 解法三with t1 as(selectcount(*)as cnt_course
from course),
t2 as(select s_id
,count(*)as cnt_course
from score
groupby s_id),
t3 as(select s_id
from t1
leftjoin t2 on t1.cnt_course = t2.cnt_course
where t2.cnt_course isnotnull)select*from student s
wherenotexists(select*from t3
where s.s_id = t3.s_id);
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
with t1 as(select sc1.c_id cid from score sc1 where sc1.s_id ='01'),
t2 as(selectdistinct s_id
from score sc
join t1 on sc.c_id = t1.cid)select*from student s
whereexists(select*from t2 where s.s_id = t2.s_id);
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
with t1 as(select c_id from score where s_id =1),
t2 as(selectcount(*)as cnt_course from t1),
t3 as(select s_id
,count(*)as cnt_course
from score sc
join t1 on sc.c_id = t1.c_id
where s_id !=1groupby s_id),
t4 as(select s_id
from t2
crossjoin t3
where t2.cnt_course = t3.cnt_course)select*from student s
whereexists(select*from t4 where s.s_id = t4.s_id);
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 解法一with t1 as(select t_id from teacher where t_name ='张三'), t2 as(select c_id
from course c
join t1 on c.t_id = t1.t_id), t3 as(select s_id
from score sc
join t2 on sc.c_id = t2.c_id)select s_name
from student s
wherenotexists(select s_id
from t3
where s.s_id = t3.s_id);-- 解法二with t1 as(select c_id
from course c
innerjoin teacher t on c.t_id = t.t_id
where t_name ='张三'),
t2 as(select s_id
from score sc
innerjoin t1 on sc.c_id = t1.c_id)select s_name
from student s
wherenotexists(select*from t2 where s.s_id = t2.s_id);
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
with t1 as(select s_id
from score
where s_score <60groupby s_id
havingcount(*)>=2)select s.s_id
, s.s_name
,round(avg(s_score),2)as avg_score
from student s
innerjoin t1 on s.s_id = t1.s_id
innerjoin score sc on s.s_id = sc.s_id
groupby s.s_id, s.s_name;
16、检索"01"课程分数小于60,按分数降序排列的学生信息
with t1 as(select s_id
, s_score
from score
where c_id =1and s_score <60)select s.*, s_score as score_01
from student s
innerjoin t1 on s.s_id = t1.s_id
orderby score_01 desc;
17、按平均成绩从高到低 显示所有学生的所有课程的成绩以及平均成绩
-- 解法一with t1 as(select s.s_id,
s.s_name,sum(case c_id when1then s_score else0end)as chinese,sum(case c_id when2then s_score else0end)as math,sum(case c_id when3then s_score else0end)as english,round(avg(s_score),2)as avg_score
from student s
leftjoin score sc
on s.s_id = sc.s_id
groupby s.s_id, s.s_name
orderby avg_score desc)select s_id
, s_name
, chinese
, math
, english
,if(avg_score isnull,0, avg_score)as avg_score
from t1;-- 解法二select s.s_id,
s.s_name,sum(case c_id when1then s_score else0end)as chinese,sum(case c_id when2then s_score else0end)as math,sum(case c_id when3then s_score else0end)as english,round(if(avg(s_score)isnull,0,avg(s_score)),2)as avg_score
from student s
leftjoin score sc
on s.s_id = sc.s_id
groupby s.s_id, s.s_name
orderby avg_score desc;
18、查询各科成绩最高分、最低分和平均分,以如下形式显示:
-- 课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90select c.c_id as course_id,
c.c_name as course_name,max(s_score)as max_score,min(s_score)as ming_score,round(avg(s_score),2)as avg_score,
concat(round(sum(casewhen s_score >=60then1else0end)/count(*)*100,2),'%')as pass_rate,
concat(round(sum(casewhen s_score between70and80then1else0end)/count(*)*100,2),'%')as medium_rate,
concat(round(sum(casewhen s_score between80and90then1else0end)/count(*)*100,2),'%')as good_rate,
concat(round(sum(casewhen s_score >=90then1else0end)/count(*)*100,2),'%')as excellent_rate
from course c
innerjoin score s on c.c_id = s.c_id
groupby c.c_id, c.c_name;
19、按各科成绩进行排序,并显示排名
select c.c_id,
c.c_name,
s.s_id,
s.s_name,
s_score,
row_number()over(partitionby c.c_id orderby s_score desc)as rank
from score sc
innerjoin student s on sc.s_id = s.s_id
innerjoin course c on sc.c_id = c.c_id;
20、查询学生的总成绩并进行排名
with t1 as(select s_id
,sum(s_score)as sum_score
from score
groupby s_id)select s.s_id
, s.s_name
, sum_score
, row_number()over(orderby sum_score desc)as rank
from student s
innerjoin t1 on s.s_id = t1.s_id;
select c.c_id as course_id,
c.c_name as course_name,sum(casewhen s_score between85and100then1else0end)as number_100_85,
concat(round(sum(casewhen s_score between85and100then1else0end)/count(*)*100,2),'%')as percentage,sum(casewhen s_score between70and85then1else0end)as number_85_70,
concat(round(sum(casewhen s_score between70and85then1else0end)/count(*)*100,2),'%')as percentage,sum(casewhen s_score between60and70then1else0end)as number_70_60,
concat(round(sum(casewhen s_score between60and70then1else0end)/count(*)*100,2),'%')as percentage,sum(casewhen s_score between0and60then1else0end)as number_0_60,
concat(round(sum(casewhen s_score between0and60then1else0end)/count(*)*100,2),'%')as percentage
from course c
innerjoin score sc
on c.c_id = sc.c_id
groupby c.c_id, c.c_name;
24、查询学生平均成绩及其名次
with t1 as(select s_id
,round(avg(s_score),2)as avg_score
from score
groupby s_id)select s.s_id
, s.s_name
, avg_score
, row_number()over(orderby avg_score desc)as rank
from student s
innerjoin t1 on s.s_id = t1.s_id;
25、查询各科成绩前三名的记录
with t1 as(select c.c_id
, c.c_name
, s.s_id
, s.s_name
, s_score
, row_number()over(partitionby c.c_id orderby s_score desc)as rank
from score sc
innerjoin student s on sc.s_id = s.s_id
innerjoin course c on sc.c_id = c.c_id)select*from t1
where rank <=3;-- select c.c_id-- , c.c_name-- , s.s_id-- , s.s_name-- , s2.s_score-- ,row_number() over (partition by c.c_id order by s2.s_score desc) as rank-- from student s-- inner join score s2 on s.s_id = s2.s_id-- inner join course c on s2.c_id = c.c_id-- where rank < 4;
26、查询每门课程被选修的学生数
with t1 as(select c_id
,count(*)as cnt_student
from score
groupby c_id)select c.c_id
, c.c_name
, cnt_student
from course c
innerjoin t1 on c.c_id = t1.c_id;
27、查询出只有两门课程的全部学生的学号和姓名.
with t1 as(select s_id
from score
groupby s_id
havingcount(1)=2)select s.s_id, s.s_name
from student s
whereexists(select*from t1 where s.s_id = t1.s_id);
28、查询男生、女生人数
select s_sex
,count(*)as cnt_sex
from student
groupby s_sex;
29、查询名字中含有"风"字的学生信息
select*from student
where s_name like'%风%';
30、查询同名同性学生名单,并统计同名人数
with t1 as(select s_name
, s_sex
,count(*)as cnt_student
from student
groupby s_name, s_sex
having cnt_student >1)select*from student s
innerjoin t1 on s.s_name = t1.s_name and s.s_sex = t1.s_sex;
with t1 as(select c_id
,round(avg(s_score),2)as avg_score
from score
groupby c_id)select c.c_id
, c.c_name
, avg_score
from course c
innerjoin t1 on c.c_id = t1.c_id
orderby avg_score desc, c.c_id;
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
with t1 as(select s_id
,avg(s_score)as avg_score
from score
groupby s_id
having avg_score >=85)select s.s_id
, s.s_name
,round(avg_score,2)from student s
innerjoin t1 on s.s_id = t1.s_id;
34、查询课程名称为"数学",且分数低于60的学生姓名和分数
-- 解法一with t1 as(select c_id from course where c_name ='数学')select s.s_name
, s2.s_score
from student s
innerjoin score s2 on s.s_id = s2.s_id
innerjoin t1 on s2.c_id = t1.c_id and s2.s_score <60;-- 解法二with t1 as(select s_id, s_score
from score sc
innerjoin course c on sc.c_id = c.c_id
where c_name ='数学'and s_score <60)select s_name, s_score
from student s
innerjoin t1 on s.s_id = t1.s_id;
35、查询所有学生的课程及分数情况
select s.s_id
, s.s_name
,sum(case c_id when1then s_score else0end)as chinese
,sum(case c_id when2then s_score else0end)as math
,sum(case c_id when3then s_score else0end)as english
from student s
leftjoin score sc
on s.s_id = sc.s_id
groupby s.s_id, s.s_name;
36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
-- 解法一select s.s_name
, c.c_id
, s2.s_score
from student s
innerjoin score s2 on s.s_id = s2.s_id
innerjoin course c on s2.c_id = c.c_id
where s2.s_score >70;-- 解法二with t1 as(select s_id
, c_id
, s_score
from score
where s_score >70groupby s_id, c_id, s_score)select s_name
, c_name
, s_score
from t1
innerjoin student s on t1.s_id = s.s_id
innerjoin course c on t1.c_id = c.c_id;
37、查询课程不及格的学生
-- 解法一select s.s_name
, c.c_id
, s2.s_score
from student s
innerjoin score s2 on s.s_id = s2.s_id
innerjoin course c on s2.c_id = c.c_id
where s2.s_score <60;-- 解法二select s.s_id
, s.s_name
, c.c_id
, c.c_name
, s_score
from score sc
innerjoin student s on sc.s_id = s.s_id
innerjoin course c on sc.c_id = c.c_id
where s_score <60;
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
-- 解法一with t1 as(select s_id
from score
where c_id =1and s_score >=80)select s_id, s_name
from student s
whereexists(select*from t1 where s.s_id = t1.s_id);-- 解法二with t1 as(select s_id
from score
where c_id =1and s_score >=80)select s_id, s_name
from student s
where s.s_id in(select s_id from t1);
39、求每门课程的学生人数
with t1 as(select c_id
,count(*)as cnt_student
from score
groupby c_id)select c.c_id
, c.c_name
, cnt_student
from course c
innerjoin t1 on c.c_id = t1.c_id;
40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
-- 解法一with t1 as(select t_id from teacher where t_name ='张三'), t2 as(select c_id
from course c
join t1 on c.t_id = t1.t_id)select s.*, s2.s_score as max_score
from student s
innerjoin score s2 on s.s_id = s2.s_id
innerjoin t2 on s2.c_id = t2.c_id
orderby s2.s_score desclimit1;-- 解法二with t1 as(select c_id
from course c
innerjoin teacher t on c.t_id = t.t_id
where t_name ='张三')select s.*, s2.s_score as max_score
from student s
innerjoin score s2 on s.s_id = s2.s_id
innerjoin t1 on s2.c_id = t1.c_id
orderby s2.s_score desclimit1;-- 解法三with t1 as(select c_id
from course c
innerjoin teacher t on c.t_id = t.t_id
where t_name ='张三'),
t2 as(select s_id, s_score
from score sc
innerjoin t1 on sc.c_id = t1.c_id
orderby s_score desclimit1)select s.*, s_score as max_score
from student s
innerjoin t2 on s.s_id = t2.s_id;
with t1 as(select*,count(1)over(partitionby s_score)as cnt_student
from score)select s_id, c_id, s_score
from t1
where cnt_student >1;
42、查询每门课程成绩最好的前三名
-- 解法一with t1 as(select c_id
, s_id
, s_score
, row_number()over(partitionby c_id orderby s_score desc)as rank
from score)select t1.c_id
, s.s_id
, s.s_name
, t1.s_score
, t1.rank
from student s
join t1 on s.s_id = t1.s_id
where rank <4;-- 解法二with t1 as(select c.c_id
, c.c_name
, s.s_id
, s.s_name
, s_score
, row_number()over(partitionby c.c_id orderby s_score desc)as rank
from score sc
innerjoin student s on sc.s_id = s.s_id
innerjoin course c on sc.c_id = c.c_id
)select*from t1
where rank <=3;
43、统计每门课程的学生选修人数(超过5人的课程才统计)
-- 解法一with t1 as(select c_id
,count(1)as cnt_student
from score
groupby c_id
having cnt_student >5)select c.c_id, cnt_student
from course c
innerjoin t1
on c.c_id = t1.c_id
orderby cnt_student desc, c.c_id;-- 解法二with t1 as(select c_id
,count(1)as cnt_student
from score
groupby c_id
having cnt_student >5)select c_id, cnt_student
from t1
orderby cnt_student desc, c_id;
44、检索至少选修两门课程的学生学号
select s_id
from score
groupby s_id
havingcount(1)>1;
45、查询选修了全部课程的学生信息
-- 解法一with t1 as(selectcount(1) count from course), t2 as(select s_id,count(1) c_count from score groupby s_id), t3 as(select s_id
from t1
join t2 on t1.count = t2.c_count)select s.*from student s
where s.s_id in(select s_id from t3);-- 解法二with t1 as(selectcount(*)as cnt_course from course),
t2 as(select s_id
,count(*)as cnt_course
from score
groupby s_id),
t3 as(select s_id
from t1
crossjoin t2
where t1.cnt_course = t2.cnt_course)select*from student s
whereexists(select*from t3 where s.s_id = t3.s_id);
46、查询各学生的年龄(周岁)
-- 解法一select s_id
, s_name
, s_birth
,casewhenmonth(s_birth)>month(`current_date`())thenyear(current_date())-year(s_birth)-1whenmonth(s_birth)=month(`current_date`())andday(s_birth)>day(`current_date`())thenyear(current_date())-year(s_birth)-1elseyear(current_date())-year(s_birth)end age
from student;-- 解法二select s_id,
s_name,
s_birth,if(month(current_date())<month(s_birth)or(month(current_date())=month(s_birth)andday(current_date())<day(s_birth)),year(current_date())-year(s_birth)-1,year(current_date())-year(s_birth))as s_age
from student;
47、查询本周过生日的学生
-- 解法一select*from student
where datediff(concat(year(current_date()), date_format(s_birth,'-MM-dd')), to_date(current_date()))between0and7or datediff(concat(year(current_date())+1, date_format(s_birth,'-MM-dd')), to_date(current_date()))between0and7;-- 解法二SELECT s_id
, s_name
, s_birth
, s_sex
FROM student
WHERE WEEKOFYEAR(CURRENT_DATE)= WEEKOFYEAR(s_birth);
48、查询下周过生日的学生
-- 解法一select*from student
where datediff(concat(year(current_date()), date_format(s_birth,'-MM-dd')), to_date(current_date()))between7and14or datediff(concat(year(current_date())+1, date_format(s_birth,'-MM-dd')), to_date(current_date()))between7and14;-- 解法二SELECT s_id
, s_name
, s_birth
, s_sex
FROM student
WHERE WEEKOFYEAR(CURRENT_DATE)+1= WEEKOFYEAR(s_birth);