SQL经典50题 这里只贴个人解答,思路详细分析日后再说
在这里插入-- 50道经典SQL练习题 数据表介绍 --1.学生表 studentinfo(StuIdId,StuName,StuAge,StuSex) --StuIdId 学生编号,StuName 学生姓名,StuAge 出生年月,StuSex 学生性别 --2.教师表 teacherinfo(TeacherId,TeacherName) --TeacherId 教师编号,TeacherName 教师姓名 --3.课程表 courseinfo(CourseId,CourseName,fkTeacherId) --CourseId 课程编号,CourseName 课程名称,fkTeacherId 教师编号 --4.成绩表 scoreinfo(ScoreId,fkStuId,fkCourseId,score) --ScoreId 成绩编号,fkStuId 学生编号,fkCourseId 课程编号,score 分数 -- 请分两次执行代码 1、先创建表结构 2、insert数据 drop database if exists school; create database school; use school; create table studentinfo( StuId int auto_increment primary key, StuName varchar(10) not null, StuAge datetime not null, StuSex char(1) not null ); create table teacherinfo( TeacherId int auto_increment primary key, TeacherName varchar(10) not null ); create table courseinfo( CourseId int auto_increment primary key, CourseName varchar(10) not null, fkTeacherId int not null ); create table scoreinfo( ScoreId int auto_increment primary key, fkStuId int not null, fkCourseId int not null, score decimal(4,1) not null ); alter table courseinfo add constraint fk_course_teacher foreign key (fkTeacherId) references teacherinfo(TeacherId); alter table scoreinfo add constraint fk_score_stu foreign key(fkStuId) references studentinfo(StuId); alter table scoreinfo add constraint fk_score_course foreign key(fkCourseId) references courseinfo(CourseId); --------------------- 模拟数据 insert into studentinfo values (1,'赵雷','1990-01-01','男'), (2,'钱电','1990-12-21','男'), (3,'孙风','1990-05-20','男'), (4,'李云','1990-08-06','男'), (5,'周梅','1991-12-01','女'), (6,'吴兰','1992-03-01','女'), (7,'郑竹','1989-07-01','女'), (8,'王菊','1990-01-20','女'); insert into teacherinfo values (1,'张三'), (2,'李四'), (3,'王五'); insert into courseinfo values (1,'语文',2), (2,'数学',1), (3,'英语',3); insert into scoreinfo(fkStuId,fkCourseId,score) values (1,1,80), (1,2,90), (1,3,99), (2,1,70), (2,2,60), (2,3,80), (3,1,80), (3,2,80), (3,3,80), (4,1,50), (4,2,30), (4,3,20), (5,1,76), (5,2,87), (6,1,31), (6,3,34), (7,2,89), (7,3,98); 代码片
练习题目 1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 select * from scoreinfo S inner join scoreinfo T on S.fkStuId = T.fkStuId where S.score>T.score and T.fkCourseId = 2 and S.fkCourseId = 1 ; 1.1 查询同时存在" 01 "课程和" 02 "课程的情况 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) 略 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况 略 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 select U.stuId,U.stuName,avg(score) from scoreinfo S inner join studentinfo U on S.fkStuId = U.StuId group by S.fkStuId having avg(score) >= 60; 3.查询在 scoreinfo 表存在成绩的学生信息 select S.* from studentinfo S inner join scoreinfo F on S.StuId = F.fkStuId group by S.StuId ; 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) select U.StuId,U.StuName,count(S.score) cn,sum(S.score) from studentinfo U left join scoreinfo S on U.StuId = S.fkStuId left join courseinfo C on S.fkCourseId = C.CourseId group by U.StuId; 4.1 查有成绩的学生信息 select U.* from studentinfo U inner join scoreinfo S on U.StuId = S.fkStuId group by U.StuId; 5.查询「李」姓老师的数量 select count(*) from teacherinfo where TeacherName like '李%'; 6.查询学过「张三」老师授课的同学的信息 select U.* from studentinfo U inner join scoreinfo S on U.StuId = S.fkStuId inner join courseinfo C on S.fkCourseId = C.CourseId inner join teacherinfo T on C.fkTeacherId = T.TeacherId and T.TeacherName = '张三' 7.查询没有学全所有课程的同学的信息 select * from ( select U.StuId,U.StuName,count(S.score) cn from studentinfo U left join scoreinfo S on U.StuId = S.fkStuId group by U.StuId ) T where T.cn < (select count(CourseId) from courseinfo ); 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 select distinct studentinfo.* from studentinfo inner join scoreinfo on studentinfo.StuId = scoreinfo.fkStuId where fkCourseId in (select fkCourseId from scoreinfo where fkStuId = 1) and fkStuId != 1 ; 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息 SELECT * FROM ( SELECT fkStuId, group_concat( fkCourseId ORDER BY fkCourseId ) rst FROM scoreinfo WHERE fkStuId != 1 GROUP BY fkStuId ) A WHERE rst = ( SELECT group_concat( fkCourseId ORDER BY fkCourseId ) rst2 FROM scoreinfo WHERE fkStuId = 1 GROUP BY fkStuId ) 10.查询没学过"张三"老师讲授的任一门课程的学生姓名 select if (find_in_set(C.CourseId,A),null,StuName) from( select StuName,group_concat(fkCourseId order by fkCourseId) A from studentinfo U inner join scoreinfo S on U.StuId = S.fkStuId inner join courseinfo C on S.fkCourseId = C.CourseId inner join teacherinfo T on C.fkTeacherId = T.TeacherId group by StuName )B inner join ( select CourseId from courseinfo inner join teacherinfo on courseinfo.fkTeacherId = teacherinfo.TeacherId where TeacherName = '张三' )C -- on 1=1 11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 select StuId,StuName, avg(score) from studentinfo U inner join scoreinfo S on U.StuId = S.fkStuId inner join ( select fkStuId,count(1) cnt from scoreinfo where score<60 group by fkStuId )A on S.fkStuId = A.fkStuId where A.cnt>=2 12.检索" 01 "课程分数小于 60,按分数降序排列的学生信息 select U.* from studentinfo U inner join ( select fkStuId,score from scoreinfo where fkCourseId=1 and score<60 ) A on U.StuId = A.fkStuId order by score desc 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 select fkStuId,StuName,avg(score) avg,group_concat(concat(CourseName,'-',score)) allscore from scoreinfo S inner join studentinfo U on S.fkStuId = U.StuId inner join courseinfo C on S.fkCourseId = C.CourseId group by fkStuId order by avg desc 14.查询各科成绩最高分、最低分和平均分: 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 select fkCourseId, count(ScoreId) 选修人数, max(score), min(score), avg(score), sum(D)/count(ScoreId) as 及格率, sum(C)/count(ScoreId) as 中等率, sum(B)/count(ScoreId) as 优良率, sum(A)/count(ScoreId) as 优秀率 from (select *, case when score>=60 then 1 else 0 end as D, case when score>=70 and score<80 then 1 else 0 end as C , case when score>=80 and score<90 then 1 else 0 end as B , case when score>=90 then 1 else 0 end as A from scoreinfo)a group by fkCourseId order by count(ScoreId) desc,fkCourseId -- 这里还可以简化,from后的条件也并到select后的子查询里 15.按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺 select S1.fkCourseId, S1.fkStuId,S1.score, count(S2.score) +1 as rank from scoreinfo S1 left join scoreinfo S2 on S1.fkCourseId=S2.fkCourseId and S1.score<s2.score group by S1.fkCourseId, S1.fkStuId, S1.score order by S1.fkCourseId,rank asc 15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次 select S1.fkCourseId, S1.fkStuId,S1.score, count(distinct(S2.score)) +1 as rank from scoreinfo S1 left join scoreinfo S2 on S1.fkCourseId=S2.fkCourseId and S1.score<s2.score group by S1.fkCourseId, S1.fkStuId, S1.score order by S1.fkCourseId,rank asc 16.查询学生的总成绩,并进行排名,总分重复时保留名次空缺 SELECT A.fkStuId, 总分1, count(总分2) as 名次 FROM ( select fkStuId,sum(score) 总分1 from scoreinfo GROUP BY fkStuId ORDER BY 总分1 DESC )A inner join ( select fkStuId,sum(score) 总分2 from scoreinfo GROUP BY fkStuId ORDER BY 总分2 DESC )B on 总分1<=总分2 GROUP BY fkStuId ORDER BY 名次 16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺 SELECT A.fkStuId, 总分1, count(DISTINCT 总分2) as 名次 FROM ( select fkStuId,sum(score) 总分1 from scoreinfo GROUP BY fkStuId ORDER BY 总分1 DESC )A inner join ( select fkStuId,sum(score) 总分2 from scoreinfo GROUP BY fkStuId ORDER BY 总分2 DESC )B on 总分1<=总分2 GROUP BY fkStuId ORDER BY 名次 17.统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比 select S.fkCourseId, C.CourseName, (IFNULL(A,0)) as '[100-85] 人数', CONCAT_WS('',ROUND(((IFNULL(A,0))/COUNT(fkStuId)*100),2),'%') AS '[100-85]', (IFNULL(B,0)) as '[85-70] 人数', CONCAT_WS('',ROUND(((IFNULL(B,0))/COUNT(fkStuId)*100),2),'%') AS '[85-70]', (IFNULL(U,0)) as '[70-60] 人数', CONCAT_WS('',ROUND(((IFNULL(U,0))/COUNT(fkStuId)*100),2),'%') AS '[70-60]', (IFNULL(D,0)) as '[60-0] 人数', CONCAT_WS('',ROUND(((IFNULL(D,0))/COUNT(fkStuId)*100),2),'%') AS '[60-0]' from scoreinfo S LEFT JOIN ( select fkCourseId,count(1) A from scoreinfo where score>=85 group by fkCourseId )Z on S.fkCourseId = Z.fkCourseId LEFT JOIN ( select fkCourseId,count(1) B from scoreinfo where score<85 and score>=70 group by fkCourseId )X on S.fkCourseId = X.fkCourseId LEFT JOIN ( select fkCourseId,count(1) U from scoreinfo where score<70 and score>=60 group by fkCourseId )M on S.fkCourseId = M.fkCourseId LEFT JOIN ( select fkCourseId,count(1) D from scoreinfo where score<60 group by fkCourseId )V on S.fkCourseId = V.fkCourseId inner JOIN courseinfo C ON S.fkCourseId = C.CourseId GROUP BY S.fkCourseId 18.查询各科成绩前三名的记录 select fkCourseId,substring_index(group_concat(score order by score desc),',',3 )grade from scoreinfo group by fkCourseId 19.查询每门课程被选修的学生数 select fkCourseId,count(0) from scoreinfo group by fkCourseId 20.查询出只选修两门课程的学生学号和姓名 select fkStuId,StuName from scoreinfo C inner JOIN studentinfo S ON C.fkStuId=S.StuId group by fkStuId having count(1) =2 21.查询男生、女生人数 select count(if (StuSex='男',StuSex,null) )as 男,count(if(StuSex='女',StuSex,null)) as 女 from studentinfo ========================= select count(1) from studentinfo group by StuSex 22.查询名字中含有「风」 select * from studentinfo where StuName like '%风%' 23.查询同姓同性学生名单,并统计同名人数 select concat(A.StuName,'和',B.StuName) as 同姓同性的是,count(A.StuId) as 人数 from studentinfo A inner join studentinfo B where left(A.StuName,1)=left(B.StuName,1) and A.StuId != B.StuId and A.StuSex = B.StuSex 24.查询 1990 年出生的学生名单 select s.stuName from studentinfo s where year(StuAge)=1990 25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列 select fkCourseId,avg(score) grade from scoreinfo group by fkCourseId order by grade desc ,fkCourseId 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 select stuId,stuName,grade from studentinfo s inner join ( select fkStuId,avg(score) grade from scoreinfo c group by fkStuId )c on s.stuId = c.fkStuId where grade>=85 27.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数 select stuName,CourseName,score from studentinfo s inner join scoreinfo c on s.stuId = c.fkStuId inner join courseinfo i on c.fkCourseId = i.CourseId where CourseName ='数学' and score<60 28.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况) select stuId,stuName,fkCourseId,score from studentinfo s left join scoreinfo c on s.stuId = c.fkStuId 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 select stuName , CourseName,score from studentinfo s inner join scoreinfo c on s.stuId = c.fkStuId inner join courseinfo i on s.fkCourseId = i.CourseId where score >70 30.查询不及格的课程 select fkCourseId,CourseName,fkStuId,score from scoreinfo inner join courseinfo on scoreinfo.fkCourseId = courseinfo.CourseId where score<60 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名 select stuId,stuName,fkCourseId,score from studentinfo inner join scoreinfo c on studentinfo.stuId = c.fkStuId where fkCourseId=1 and score>80 32.求每门课程的学生人数 select fkCourseId,CourseName,count(1) from scoreinfo S inner join courseinfo C on S.fkCourseId = C.CourseId group by fkCourseId 33.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩 select fkStuId,stuName,max(score) from studentinfo u inner join scoreinfo s on u.stuId = s.fkStuId inner join courseinfo c on s.fkCourseId = c.CourseId inner join teacherinfo t on c.fkTeacherId = t.TeacherId where TeacherName = '张三' 34.成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩 select stuId,stuName,s.score,t.TeacherName from studentinfo u inner join scoreinfo s on u.stuId = s.fkStuId inner join courseinfo c on s.fkCourseId = c.CourseId inner join teacherinfo t on c.fkTeacherId = t.TeacherId where TeacherName='张三' and (select count(1) from scoreinfo c1 inner join courseinfo info on c1.fkCourseId = info.CourseId where c1.score>s.score and info.CourseId =2 )=0 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select s.fkStuId, s.fkCourseId,s.score,s1.fkStuId, s1.fkCourseId,s1.score from scoreinfo s inner join scoreinfo s1 where s.fkCourseId < s1.fkCourseId and s.score = s1.score 36.查询每门功成绩最好的前两名 select fkCourseId,SUBSTRING_INDEX(group_concat(info order by score desc ), ',' ,2) from ( select fkCourseId,CONCAT_WS('-',fkStuId,stuName,score) as info,score from scoreinfo s inner JOIN studentinfo u ON s.fkStuId = u.stuId )c group by fkCourseId 37.统计每门课程的学生选修人数(超过 5 人的课程才统计)。 select fkCourseId,count(1) cnt from scoreinfo group by fkCourseId having cnt >5 38.检索至少选修两门课程的学生学号 select fkStuId,count(fkCourseId) cnt from scoreinfo group by fkStuId having cnt >=2 39.查询选修了全部课程的学生信息 select fkStuId,stuName from scoreinfo c inner join studentinfo s on c.fkStuId = s.stuId group by fkStuId having count(fkCourseId) = (SELECT COUNT(DISTINCT fkCourseId) from scoreinfo) 40.查询各学生的年龄,只按年份来算 select StuId,StuName,(year(now())-year(StuAge)) as age FROM studentinfo 41.按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一 select StuId,StuName,ceil(datediff(now(),StuAge)/365) FROM studentinfo 42.查询本周过生日的学生 select StuId,StuName from studentinfo WHERE week(concat(year(now()),'-',DATE_FORMAT(StuAge,'%m-%d'))) = week(now()) 43.查询下周过生日的学生 select StuId,StuName from studentinfo WHERE week(concat(year(now()),'-',DATE_FORMAT(StuAge,'%m-%d'))) = week(now())+1 44.查询本月过生日的学生 select StuId,StuName from studentinfo where month(StuAge)=month(now()) 45.查询下月过生日的学生 select StuId,StuName from studentinfo where month(StuAge)=month(now())+1