数据集
https://blog.csdn.net/Hudas/article/details/122869529
------------创建数据库---------------
create database data charset=utf8;
------------ 建表语句-----------------
# 学生表 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));
------------ 插入数据语句-----------------
# 学生表 Student:
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');
# 科目表 Course
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
# 教师表 Teacher
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
# 成绩表 SC
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);
45题
1-15
1.查询“01”课程比“02”课程成绩高的学生的信息及课程分数
SELECT
t2.*,t1.Cid,t1.Score
from sc t1
join student t2
on t1.Sid=t2.Sid
join sc t3
on t1.Sid=t3.Sid and t1.Cid="01" and t3.Cid="02"
where t1.Score>t3.Score
1.1查询同时存在“01”课程和“02”课程的学生情况
SELECT
s1.*
from sc s1
join sc s2
on s1.Sid=s2.Sid
where s1.Cid="01" and s2.Cid="02"
1.2查询存在“01”课程但可能不存在“02”课程的学生情况(不存在时显示为null)
SELECT
*
from sc s1
left join sc s2
on s1.Sid=s2.Sid and s2.Cid="02"
where s1.Cid="01"
1.3查询不存在“01”课程但存在“02”课程的学生情况
select
*
from sc
where Sid not in (
select Sid
from sc
where Cid="01")and Cid="02"
2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select
t1.Sid,t1.Sname,t2.avg_score
from student t1
join (
SELECT
Sid,avg(score) avg_score
from sc
group by Sid
having avg(Score)>=60
)t2
on t1.Sid=t2.Sid
3.查询在SC表存在成绩的学生信息
SELECT
s1.*
from student s1
join (
SELECT DISTINCT Sid
from sc
where score is not null)s2
on s1.Sid=s2.Sid
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
SELECT
s1.Sid,s1.Sname,s2.count_Cid,s2.sum_score
from student s1
left join (
SELECT Sid ,count(Cid) count_Cid,sum(score) sum_score
from sc
group by Sid )s2
on s1.Sid=s2.Sid
4.1查有成绩的学生信息
SELECT
s1.*
from student s1
join (
SELECT DISTINCT Sid
from sc
where score is not null)s2
on s1.Sid=s2.Sid
5.查询[李]姓老师的数量
select
count(*)
from teacher
where Tname like "李%"
6.查询学过[张三]老师授课的同学的信息
select
student.*,t4.Tname
from (
SELECT
sc.Sid,t3.Tname
from(select
t1.Cid,t2.Tname
from Course t1
join teacher t2
on t1.Tid=t2.Tid
where t2.Tname="张三")t3
join sc on sc.CId=t3.Cid)t4
join student on student.SId=t4.Sid
select
student.*,teacher.Tname
from course
join teacher
on course.TId=teacher.TId
join sc
on sc.CId=course.Cid
join student
on student.Sid=sc.SId
where teacher.Tname="张三"
7.查询没有学全所有课程的同学的信息
select
t2.*
from
(select
Sid
from sc
group by Sid
having count(Cid)<(select count(*) from course))t1
join student t2
on t1.Sid=t2.Sid
8.查询至少有一门课与学号为“01”的同学所学相同的同学的信息
select
t4.*
from (
select
distinct Sid
from sc
where Cid in (select Cid from sc where Sid="01"))t3
left join student t4
on t3.Sid=t4.Sid
9.查询和“01”号的同学学习的课程完全相同的其他同学的信息
select
student.*
from(
select
t1.Sid
from (
select
Sid,Cid
from sc
where Cid in ( SELECT Cid from sc where Sid="01" )
)t1
where t1.Sid !="01"
group by t1.Sid
having count(t1.Cid)=(SELECT count(*) from sc where Sid="01")
)t3
join student
on student.Sid=t3.Sid
10.查询没学过“张三”老师讲授的任一门课程的学生姓名
select
Sid,Sname
from student
where Sid not in (
select
distinct Sid
from sc
where Cid in (
select
t2.Cid
from teacher t1
left join course t2
on t1.Tid=t2.Tid
where Tname="张三") )
11.查询两门及以上不及格课程的同学的学号,姓名及其平均成绩
-- 方法一
SELECT
student.*,t4.avg_score
from student
join (
select
sc.Sid,avg(sc.score) avg_score
from sc
join (
select
Sid
from sc
where score<60
group by Sid
having count(*)>=2
)t2
on sc.Sid=t2.Sid
group by sc.SId
)t4
on t4.Sid=student.Sid
12.检索“01”课程分数小于60分,按分数降序排列的学生信息
select
student.*,sc.score
from sc
join student
on sc.SId=student.SId
where Cid="01" and score <60
order by score desc
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
student.Sid,Cid,score,avg(score) over(partition by student.Sid )avg_score
from student
LEFT JOIN sc
on student.SId=sc.SId
ORDER BY avg_score desc
14.查询各科成绩最高分、最低分和平均分,以如下形式显示:
课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,
及格为:>=60,中等为:[70,80),优良为:[80,90),优秀为:>=90;
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select
Cid,count(Cid) count_Cid,max(score) max_score,min(score) min_score,avg(score) avg_score ,
sum(if(score<70 and score>=60,1,0))/count(*) c1,
sum(if(score>=70 and score <80,1,0))/count(*) c2,
sum(if(score >=80 and score <90,1,0))/count(*) c3,
sum(if(score >=80 and score >=90,1,0))/count(*) c4
from sc
group by Cid
order by count_Cid desc,Cid
15.按各科成绩进行排序,并显示排名,Score重复时也继续排名*
select
Cid,Sid,score,rank()over(PARTITION by Cid order by score desc)
from sc
15.1按各科成绩进行排序,并显示排名,Score重复时合并名次*
select
Cid,Sid,score,DENSE_RANK()over(PARTITION by Cid order by score desc)
from sc
15-30
16.查询学生的总成绩,并进行排名,总分重复时保留名次空缺*
select
Sid,t1.sum_score,rank() over(order by t1.sum_score desc)
from (
select
Sid,sum(score) sum_score
from sc
group by Sid
)t1
16.1查询学生的总成绩,并进行排名,总分重复时不保留名次空缺*******
select
Sid,t1.sum_score,dense_rank() over(order by t1.sum_score desc)
from (
select
Sid,sum(score) sum_score
from sc
group by Sid
)t1
17.统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0]及所占百分比*
select
t1.Cid,course.Cname,t1.sum_1,t1.sum_2,t1.sum_3,t1.sum_4
from (select
Cid,
CONCAT(sum(case when score>=85 and score<=100 then 1 else 0 end )/count(*)*100,"%") sum_1,
sum(case when score>=70 and score<85 then 1 else 0 end )/count(*)*100 sum_2,
sum(case when score>=60 and score<70 then 1 else 0 end )/count(*)*100 sum_3,
sum(case when score<60 then 1 else 0 end )/count(*)*100 sum_4
from sc
group by Cid
)t1
left join course
on t1.Cid=course.Cid
18.查询各科成绩前三名的记录*
select
Cid,score,t1.n
from (select
Cid,score,ROW_NUMBER()over(partition by Cid order by score desc ) n
from sc
)t1
where t1.n<=3
19.查询每门课程被选修的学生数
select
course.Cname,t1.count_Cid
from(select
Cid,count(*) count_Cid
from sc
group by Cid
)t1
left join course
on t1.Cid=course.Cid
20.查询出只选修两门课程的学生学号和姓名
select
t1.Sid,student.Sname
from (select
Sid
from sc
group by Sid
having count(Cid)=2
)t1
left join student
on student.SId=t1.Sid
21.查询男生、女生人数
select
Ssex,count(*) sex_count
from student
group by Ssex
22.查询名字中含有[风]字的学生信息
select
*
from student
where Sname like "%风%"
23.查询同名同性别学生名单,并统计同名同性别人数*
select
t1.Sname,t1.Ssex,count(*)
from(select
DISTINCT Sname,Ssex, Sid
from student)t1
group by t1.Sname,t1.Ssex
having count(*)>1
select
t1.Sname,t1.Ssex,count(t1.Sid)
from (select
s1.*
from student s1
join student s2
on s1.Sname=s2.Sname and s1.Ssex=s2.Ssex and s1.Sid!=s2.Sid )t1
group by t1.Sname,t1.Ssex
24.查询1990年出生的学生名单
select *
from student
where year(Sage)=1990
25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,
按课程编号升序排列
select
Cid,avg(score) avg_score
from sc
group by Cid
order by avg_score desc, Cid
26.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select
t1.Sid,student.Sname,t1.avg_score
from (select
Sid,avg(score) avg_score
from sc
group by Sid
having avg_score>=85)t1
left join student
on t1.Sid=student.SId
27.查询课程名称为[数学],且分数低于60的学生姓名和分数
SELECT
student.Sname,t1.score
from (select
sc.SId,sc.score
from course
join sc
on sc.CId=course.CId and course.Cname="数学"
where sc.score<60)t1
left join student
on student.Sid=t1.Sid
28.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT
student.*,sc.CId,sc.score
from student
left join sc
on student.Sid=sc.Sid
29.查询任何一门课程成绩都在70分以上的姓名、课程名称和分数
select
t1.Sid,student.Sname,course.Cname,t1.score
from (
select
Sid,Cid,score
from sc
where Sid not in (SELECT
distinct Sid
from sc
where score <=70)
)t1
left join course
on course.Cid=t1.Cid
left join student
on student.Sid=t1.Sid
30.查询不及格的课程
select distinct sc.Cid ,course.Cname
from sc
join course
on sc.CId=course.CId
where sc.score <60
30-45