sql必做50题自留答案
一、创建表
use schoolsql;#创建表前先引用数据库
#创建Student表
CREATE TABLE Student(Sid varchar(10) not null,
Sname nvarchar(10) not null,
Sage datetime not null,
Ssex nvarchar(10));
INSERT INTO Student values('01',N'赵雷','1990-01-01',N'男');
INSERT INTO Student values('02',N'钱电','1990-12-21',N'男');
INSERT INTO Student values('03',N'孙风','1990-05-20',N'男');
INSERT INTO Student values('04',N'李云','1990-08-06',N'男');
INSERT INTO Student values('05',N'周梅','1991-12-01',N'女');
INSERT INTO Student values('06',N'吴兰','1992-03-01',N'女');
INSERT INTO Student values('07',N'郑竹','1989-07-01',N'女');
INSERT INTO Student values('08',N'王菊','1990-02-10',N'女');
#创建Course表
CREATE TABLE Course(Cid varchar(10) not null,
Cname nvarchar(10) not null,
Tid varchar(10) not null);
INSERT INTO Course values('01',N'语文','02');
INSERT INTO Course values('02',N'数学','01');
INSERT INTO Course values('03',N'英语','03');
#创建Teacher表
CREATE TABLE Teacher(Tid varchar(10) not null,
Tname varchar(10) not null);
INSERT INTO Teacher values('01',N'张三');
INSERT INTO Teacher values('02',N'李四');
INSERT INTO Teacher values('01',N'王五');
#创建SC表
CREATE TABLE SC(Sid varchar(10) not null,
Cid varchar(10) not null,
score decimal(18,1) not null);
INSERT INTO SC values('01','01',80);
INSERT INTO SC values('01','02',90);
INSERT INTO SC values('01','03',99);
INSERT INTO SC values('02','01',70);
INSERT INTO SC values('02','02',60);
INSERT INTO SC values('02','03',80);
INSERT INTO SC values('03','01',80);
INSERT INTO SC values('03','02',80);
INSERT INTO SC values('03','03',80);
INSERT INTO SC values('04','01',50);
INSERT INTO SC values('04','02',30);
INSERT INTO SC values('04','03',20);
INSERT INTO SC values('05','01',76);
INSERT INTO SC values('05','02',87);
INSERT INTO SC values('06','01',31);
INSERT INTO SC values('06','03',34);
INSERT INTO SC values('07','02',89);
INSERT INTO SC values('07','03',98);
二、练习题
- 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select s.*,a.score as score_01,b.score as score_02
from Student s,(select Sid,score from sc where cid=01) a,
(select Sid,score from SC where Cid=02) as b
where a.Sid=b.Sid and a.score > b.score and s.Sid=a.Sid;
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.Sid,s.Sname,avg(a.score) as avg_sc
from Student s left join SC a on s.Sid=a.Sid
group by s.Sid
having avg_sc>60;
- 查询在 SC 表存在成绩的学生信息
select DISTINCT s.*
from Student s right join SC a on s.Sid=a.Sid;
select *
from Student
where Student.Sid in
(select SC.Sid from SC where SC.score is not null);
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select Student.Sid,Student.Sname,count(SC.Cid),sum(score)
from Student left join SC on Student.Sid=SC.Sid
group by Student.Sid;
- 4-1查有成绩的学生信息
select Student.*, count(SC.Cid),sum(score),
sum(case when Cid='01' then score else null end) as score_01,
sum(case when Cid='02' then score else null end) as score_02,
sum(case when Cid='03' then score else null end) as score_03
from Student,SC
where Student.Sid=SC.Sid
group by Student.Sid;
- 查询「李」姓老师的数量
select count(*)
from Teacher
where Tname like '李%';
- 查询学过「张三」老师授课的同学的信息
select Student.*
from Student
where Sid in(
select Sid
from Teacher,Course,SC
where Teacher.Tid=Course.Tid and Course.Cid=SC.Cid and Tname='张三');
- 查询没有学全所有课程的同学的信息
select Student.*
from Student
where Sid in (
select Sid
from SC
group by Sid
having count(Cid)<3);
- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select Student.*
from Student
where Sid in(
select distinct Sid
from SC
where Cid in(select Cid from SC where Sid='01') and Sid <>'01');
- 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
Select Student.*
from Student
where Student.Sid in
(select SC.Sid
from SC
where SC.Sid not in #选出所选课程是01所选课程子集的学生（逆向，选出包含01未选课程的同学再取反），再选出和01选课数相等的学生
(select SC.Sid from SC where SC.Cid not in(select SC.Cid from SC where SC.Sid='01')) and Sid <> '01'
group by Sid
having count(Cid)=(select count(Cid) from SC where Sid ='01'));
- 查询没学过"张三"老师讲授的任一门课程的学生姓名
select Sname
from Student
where Sid not in (
select SC.Sid from SC
where SC.Cid in(select Course.Cid
from Teacher join Course on Teacher.Tid=Course.Tid
where Teacher.Tname='张三'));
- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select Student.Sid,Student.Sname,avg(score)
from Student join SC on Student.Sid=SC.Sid
where SC.Sid in(
select SC.Sid from SC
where SC.score < 60
group by SC.Sid
having count(*)>=2)
group by Student.Sid;
- 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select Student.*,a.score as score_01
from Student,(select Sid,score from SC where SC.Cid='01') as a
where Student.Sid in(
select SC.Sid
from SC where SC.Cid='01' and SC.score < 60
order by SC.score DESC) and Student.Sid=a.Sid;
select Student.*,SC.score
from Student join SC on Student.Sid=SC.Sid
where Student.Sid in(
select SC.Sid
from SC where SC.Cid='01' and SC.score < 60
order by SC.score DESC) and SC.Cid='01';
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select DISTINCT Student.Sid,Student.Sname,a.score as score_01,b.score as score_02,c.score as score_03,
d.avgscore as score_avg
from Student,SC,
(select Student.Sid as id,sum(case when Cid='01' then SC.score else null end) as score
from Student left join SC on Student.Sid=SC.Sid group by Student.Sid) as a,
(select Student.Sid as id,sum(case when Cid='02' then SC.score else null end) as score
from Student left join SC on Student.Sid=SC.Sid group by Student.Sid) as b,
(select Student.Sid as id,sum(case when Cid='03' then SC.score else null end) as score
from Student left join SC on Student.Sid=SC.Sid group by Student.Sid) as c,#将这里改成case when... then...else... end
(select Student.Sid as id,avg(score) as avgscore from Student left join SC on Student.Sid=SC.Sid group by Student.Sid order by avg(score) DESC) as d
where Student.Sid=a.id and a.id=b.id and c.id=b.id and Student.Sid=d.id
order by score_avg DESC;
#simple
select sid,
sum(case when cid=01 then score else null end) as score_01,
sum(case when cid=02 then score else null end) as score_02,
sum(case when cid=03 then score else null end) as score_03,
avg(score)
from sc
group by sid
order by avg(score) desc
- 查询各科成绩最高分、最低分和平均分，
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select SC.Cid,Cname,count(distinct Sid),max(score),min(score),avg(score),
sum(case when score>=60 then 1 else 0 end)/count(*) as pass_ratio,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(*) as medium_ratio,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) as good_ratio,
sum(case when score>=90 then 1 else 0 end)/count(*) as excellence_ratio
from SC join Course on SC.Cid=Course.Cid
group by SC.Cid
order by count(distinct Sid) DESC,SC.Cid;
- 按平均成绩进行排序，显示总排名和各科排名，Score 重复时保留名次空缺
select Student.Sid,Student.Sname,rank_01,rank_02,rank_03,rank_avg
from Student
left join (select Sid,rank() over(partition by Cid order by score desc) as rank_01
from SC where Cid='01') a on Student.Sid=a.Sid
left join (select Sid,rank() over(partition by Cid order by score desc) as rank_02
from SC where Cid='02') b on Student.Sid=b.Sid
left join (select Sid,rank() over(partition by Cid order by score desc) as rank_03
from SC where Cid='03') c on Student.Sid=c.Sid
left join (select Sid,rank() over(order by avg(score) desc) as rank_avg
from SC group by Sid) d on Student.Sid=d.Sid
order by rank_avg asc;
- 15-1 按平均成绩进行排序，显示总排名和各科排名，Score 重复时合并名次
#rank()函数和dense_rank()函数
select Student.Sid,Student.Sname,rank_01,rank_02,rank_03,rank_avg
from Student
left join (select Sid,dense_rank() over(partition by Cid order by score desc) as rank_01
from SC where Cid='01') a on Student.Sid=a.Sid
left join (select Sid,dense_rank() over(partition by Cid order by score desc) as rank_02
from SC where Cid='02') b on Student.Sid=b.Sid
left join (select Sid,dense_rank() over(partition by Cid order by score desc) as rank_03
from SC where Cid='03') c on Student.Sid=c.Sid
left join (select Sid,dense_rank() over(order by avg(score) desc) as rank_avg
from SC group by Sid) d on Student.Sid=d.Sid
order by rank_avg asc;
#16 查询学生的总成绩，并进行排名，总分重复时保留名次空缺(类似#15)
select Student.Sid,Student.Sname,score_total,rank_total
from Student
left join (select Sid,sum(score) as score_total,rank() over(order by sum(score) desc) as rank_total
from SC group by Sid) a on Student.Sid=a.Sid
where score_total IS NOT NULL
order by rank_total asc;
- 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select SC.Cid,Cname,
sum(case when score<60 then 1 else 0 end)/count(*) as ratio1,
sum(case when score>=60 and score<70 then 1 else 0 end)/count(*) as ratio2,
sum(case when score>=70 and score<85 then 1 else 0 end)/count(*) as ratio3,
sum(case when score>=85 then 1 else 0 end)/count(*) as ratio4
from SC join Course on SC.Cid=Course.Cid
group by SC.Cid
order by count(distinct Sid) DESC,SC.Cid;
- 查询各科成绩前三名的记录
select *
from (select *,(rank() over(partition by Cid order by score)) as class_rank from SC) a
where a.class_rank <=3;
- 查询每门课程被选修的学生数
select distinct SC.Cid,Course.Cname,count(distinct Sid)
from SC join Course on SC.Cid=Course.Cid
group by SC.Cid;
- 查询出只选修两门课程的学生学号和姓名
select Student.Sid,Student.Sname
from Student left join SC on Student.Sid=SC.Sid
group by SC.Sid
having count(distinct SC.Cid)=2;
#21 查询男生、女生人数
– select Ssex,count(*)
– from Student
– group by Ssex
#22 查询名字中含有「风」字的学生信息
– select *
– from Student
– where Sname like ‘%风%’
#23 查询同名同姓学生名单，并统计同名人数
– select Sname,count()
– from Student
– group by Sname
– having count()>1
#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 left join SC on s.Sid=SC.Sid
– group by s.Sid
– having avg(score) >=85
#27 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
– select Cname,Student.Sname,score
– from Student,SC,Course
– where Student.Sid=SC.Sid and Course.Cid=SC.Cid and score< 60 and Cname=‘数学’
#28 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
#同04
#29 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
#30 查询不及格的课程
#31 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
#32 求每门课程的学生人数
#33 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
#34 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
#35 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select *
from Student s1 left join SC sc1 on s1.Sid=sc1.Sid
where
(select count(*) from Student s2 left join SC sc2 on s2.Sid=sc2.Sid
where sc2.score=sc1.score and s1.Sid<>s2.Sid) >1
#36 查询每门功课成绩最好的前两名
– select Student.Sid,Student.Sname,Cid,Cname,rank_all
– from Student,Course,(select SC.Sid as id_s,SC.Cid as id_c,rank() over(partition by Cid order by score) as rank_all from SC) a
– where Student.Sid=a.id_s and Course.Cid=a.id_c and rank_all <=2
– order by Cid,rank_all
#37 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
– select SC.Cid,Course.Cname,count(Sid)
– from SC left join Course on SC.Cid=Course.Cid
– group by SC.Cid
– having count(Sid)>5
#38 检索至少选修两门课程的学生学号
– select Sid
– from SC
– group by Sid
– having count(Cid)>=2
#39 查询选修了全部课程的学生信息
select Student.*
from SC left join Student on Student.Sid=SC.Sid
left join Course on Course.Cid=SC.Cid
group by Student.Sid
having count(SC.Cid)=(select count(Cid) as num_c from Course)
- 查询各学生的年龄，只按年份来算
select Sid,Sname,year(current_date())-year(Sage) as age
from Student
- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select Sid,Sname,timestampdiff(year,Sage,current_date()) as age
from Student
- 查询本周过生日的学生
select *
from Student
where week(current_date())=
week(concat(year(current_date()),'-',mid(Sage,6,5)))
- 查询下周过生日的学生
select *
from Student
where week(current_date())+1
=week(concat(year(current_date()),'-',mid(Sage,6,5)))
- 查询本月过生日的学生
select *
from Student
where month(current_date())
=month(concat(year(current_date()),'-',mid(Sage,6,5)))
- 查询下月过生日的学生
select *
from Student
where month(current_date())+1
=month(concat(year(current_date()),'-',mid(Sage,6,5)))
(有几个显而易见重复的就没写，代码一段一段复制有点麻烦待补充）
6455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
