SQL经典45题+答案

一、数据准备：

1、创建相关列表

学生表 Student：

create table Student(SId varchar(10) ,Sname varchar(10),Sage datetime,Ssex varchar(10));

教师表 Teacher:

create table Teacher(TId varchar(10),Tname varchar(10));

科目表 Course:

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

成绩表 SC:

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1)); 
2、数据插入

学生表 Student：

insert into Student values('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' , '女'),('09' , '张三' , '2017-12-20' , '女'),('10' , '李四' , '2017-12-25' , '女'),('11' , '李四' , '2017-12-30' , '女'),('12' , '赵六' , '2017-01-01' , '女'),('13' , '孙七' , '2018-01-01' , '女');

科目表 Course

insert into Course values('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03');

教师表 Teacher

insert into Teacher values('01' , '张三'),('02' , '李四'),('03' , '王五');

成绩表 SC

insert into SC values('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);

二、习题练习

-----1、查询"01"课程成绩比"02"课程成绩高的学生的信息及分数
select s.,sc.score from student s join sc on s.SId=sc.SId join sc a on sc.SId=a.SId and sc.CId='01' and a.CId='02' where sc.score>a.score;
-----2、查询平均成绩>=60分同学的学生编号、姓名和平均成绩
select s.SId,s.Sname,avg(score) from student s join sc on s.SId=sc.SId group by s.SId having avg(score)>=60;
-----3、查询在SC表中有成绩的学生信息
select s. from student s join sc on s.SId=sc.SId group by sc.SId;
-----4、查询所有同学的学生编号、姓名、选课总数、所有课程的总成绩（没成绩显示null）
select s.SId,s.Sname,count(sc.CId),sum(sc.Score) from student s left join sc on s.SId=sc.SId group by s.SId;
-----5、查询姓李老师的数量
select t.Tname,count() from teacher t where t.Tname like "李%";
-----6、查询学过"张三"老师授课对的学生信息
select s. from student s join sc on s.SId=sc.SId join course c on sc.CId=c.CId where c.TId=(select t.TId from teacher t where t.Tname="张三") group by s.SId;
-----7、查询没有学全所有课程的同学信息
select s.* ,count() from student s left join sc on s.SId=sc.SId group by s.SId having count()<(select count(CId) from course);
-----8、查询至少有一门课与学号为"01"的同学所学课程相同的同学信息
select s.* from student s join sc on s.SId=sc.SId where sc.CId in(select sc.CId from sc where sc.SId='01')group by SId;
-----9、查询与学号为"01"的同学所学课程完全相同的同学信息
select s.* from student s join(select SId,group_concat(CId order by CId) courses from sc group by SId)a join (select SId,group_concat(CId order by CId) courses from sc group by SId having SId='01')b on a.SId!=b.SId and a.courses=b.courses and a.SId=s.SId;
-----10、查询没学过"张三"老师讲授的任一门课的学生姓名
select s.Sname from student s where s.SId not in (select sc.SId from sc join course c on sc.CId=c.CId join teacher t on c.TId=t.TId where Tname="张三") ;
-----11、查询两门及以上不合格课程的同学的学号，姓名和平均成绩
select sc.,s.Sname,avg(score) from student s join sc on s.SId=sc.SId where sc.score<60 group by SId having  count()>=2;
-----12、查询"01"课程分数小于60的学生信息，按分数降序排列
select s.* from student s join sc on s.SId=sc.SId where sc.CId=1 and score<60 group by SId order by score desc;
难点-----13、按平均成绩从高到低显示所有学生的所有课程成绩及平均成绩
select a.SId,a.CId,a.score,b.avg_score from 
(select s.SId,sc.CId,sc.score from student s left join sc on s.SId=sc.SId )a left join (select sc.SId,avg(score)avg_score from sc  group by sc.SId)b on a.SId=b.SId order by b.avg_score desc;
------14、查询各科成绩的最高分、平均分、最低分、选修人数、合格率、中等率、优良率、优秀率，按人数降序，课程升序
select sc.CId,max(score),avg(score),min(score),count(),
sum(case when score>=60 then 1 else 0 end)/count() as "合格率",
sum(case when score>=70 and score <=80 then 1 else 0 end)/count() as "中等率",
sum(case when score>=80 and score<= 90 then 1 else 0 end)/count() as "优良率",
sum(case when score>=90 then 1 else 0 end)/count() as "优秀率"
from sc group by sc.CId order by count() desc,sc.CId asc;
-----15、按各科成绩进行排序，显示排名，分数重复继续排名
select sc.,row_number()over(order by score desc)rank from sc order by rank;
-----16、查询学生总成绩，进行排名，总分重复并列
select SId,CId,sum(score),dense_rank()over(partition by SId order by sum(score) desc)rank from sc group by SId order by rank;
-----17、查询各科成绩前三名的记录
select * ,dense_rank()over(partition by CId order by score desc)rank from sc  order by rank where rank<=3;
-----18、统计各科成绩各分数段人数：课程编号、课程名称、[0-60](60-70](70-85](85-100]所占百分比
select sc.CId,c.Cname,sum(case when score<=60 and score>=0 then 1 else 0 end)/count()as '[0-60]',
sum(case when score<=70 and score>60 then 1 else 0 end)/count()as '(60-70]',
sum(case when score<=85 and score>70 then 1 else 0 end)/count()as '(70-85]',
sum(case when score<=100 and score>85 then 1 else 0 end)/count()as '(85-100]'
from sc join course c on sc.CId=c.CId group by sc.CId;
-----19、查询每门课程被选修的学生数
select CId,count()from sc group by CId;
-----20、查询只选修两门课程的学生学号和姓名
select s.SId,s.Sname from student s join sc on s.SId=sc.SId group by s.SId having count()=2;
-----21、查询男生、女生人数
select Ssex,count()from student group by Ssex;
-----22、查询名字中含有"风"字的学生信息
select * from student where Sname like "%风%";
-----23、查询同名同性学生名单，并统计同名同姓人数
select * ,count()from student a join student b on a.SId!=b.SId and a.Sname=b.Sname and a.Ssex=b.Ssex;
-----24、查询1990年出生的学生信息
select * from student where year(Sage)=1990;
-----25、查询每门课程平均成绩，并降序排列，平均成绩相同，则按课程编号升序排列
select CId,avg(score) from sc group by CId order by avg(score) desc,CId asc;
----26、查询平均成绩大于等于85分学生的学号、姓名、平均成绩
select s.SId,s.Sname,avg(score) from student s join sc on s.SId=sc.SId group by s.SId having avg(score)>=85;
----27、查询课程为数学，且分数低于60的学生姓名和分数
select s.Sname,score from student s join sc on s.SId=sc.SId where sc.CId=(select CId from course where Cname='数学') and score<60;
----28、查询所有学生的课程及分数情况（存在学生没成绩、没选课情况）
select s.SId,s.Sname,sc.CId,sc.score from student s left join sc on s.SId=sc.SId;
----29、查询任意一门课程成绩在70分以上的学生姓名、所选课程名称和分数
select s.Sname,c.Cname,score from sc left join student s on s.SId=sc.SId join course c on sc.CId=c.CId where score>=70;
----30、查询存在不及格的课程
select sc.CId from sc where sc.score<60 group by CId;
----31、查询课程编号为01 且成绩在80分以上的学生学号和姓名
select s.SId,s.Sname,sc.score from student s join sc on s.SId=sc.SId where sc.CId='01' and score>=80;
----32、求每门课程的学生人数
select CId,count()from sc group by CId;
----33、假设成绩不重复，查询选修"张三"老师课程的学生中，成绩最高学生信息及成绩
select s.,max(sc.score) from student s join sc on s.SId=sc.SId join course c on sc.CId=c.CId where c.TId=(select t.TId from teacher t where Tname="张三") ;
----34、假设成绩重复，查询选修"张三"老师课程的学生中，成绩最高学生信息及成绩
select s.,sc.score,dense_rank()over(partition by CId order by score desc)rank from student s join sc on s.SId=sc.SId join course c on sc.CId=c.CId where c.TId=(select t.TId from teacher t where Tname="张三") where rank=1;
---35、查询不同课程成绩相同学生的学生编号、课程编号、学生成绩
select a.* from sc a join sc b on a.score=b.score and a.CId!=b.CId and a.SId=b.SId group by a.CId; 
---36、查询每门课程成绩最好的前两名
select ,dense_rank()over(partition by CId order by score desc)rank from sc where rank<=2;
---37、统计每门课程的学生选修人数（超过5人的课程才统计）
select CId,count()from sc group by CId having count()>=5;
---38、检索至少选修两门课程的学生学号
select SId from sc group by SId having count()>=2;
---39、查询选修全部课程的学生信息
select SId from sc group by SId having count()>=(select count()from course);
---40、查询各学生的年龄，只按年份来算
select s.*,(year(now())-year(Sage))age from student s;
---41、按照出生日期来算当前月日<出生年月的月日则年龄减一
select s.SId,s.Sname,timestampdiff(year,Sage,curdate())age from student s;
---42、查询本周过生日的同学信息
select *,substr(yearweek(Sage,1),5,2)birth_week,substr(yearweek(now(),1),5,2)now_week from student having birth_week=now_week;
---43、查询下周过生日的学生信息
select *,substr(yearweek(Sage,1),5,2)birth_week,substr(yearweek(now(),1),5,2)now_week from student having birth_week=now_week+1;
---44、查询本月过生日的同学信息
select * ,month(Sage)birth_month,month(now())now_month from student having birth_month=now_month;
45、查询下月过生日的学生信息
select * ,month(Sage)birth_month,month(now())now_month from student having birth_month=now_month+1;

以上仅为个人解答方法，欢迎评论区交流讨论，谢谢~~~