一、准备工作
1、学生表 Student(SId,Sname,Sage,Ssex)
SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
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' , '女');
2、课程表 Course(CId,Cname,TId)
CId 课程编号,Cname 课程名称,TId 教师编号
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
3、教师表 Teacher(TId,Tname)
TId 教师编号,Tname 教师姓名
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
4、成绩表 SC(SId,CId,score)
SId 学生编号,CId 课程编号,score 分数
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
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);
二、题目
#1、查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
#2、查询同时存在” 01 “课程和” 02 “课程的情况
#3、查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
#4、查询不存在” 01 “课程但存在” 02 "课程的情况
#5、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#6、查询在 SC 表存在成绩的学生信息
#7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
#8、查有成绩的学生信息
#9、查询「李」姓老师的数量
#10、查询学过「张三」老师授课的同学的信息
#11、查询没有学全所有课程的同学的信息
#12、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
#13、查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
#15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
#16、检索" 01 "课程分数小于 60,按分数降序排列的学生信息
#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
#18、查询各科成绩最高分、最低分和平均分
#19、以如下形式显示:课程 ID,课程 name,及格率,中等率,优良率,优秀率;及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
#20、按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
#21、按各科成绩进行排序,并显示排名, Score 重复时合并名次
#22、要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
#23、查询学生的总成绩,并进行排名
#24、查询每个学生的总成绩,平均分以及最高分
#25、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
#26、查询各科成绩前三名的记录
#27、查询每门课程被选修的学生数
#28、查询出只选修两门课程的学生学号和姓名
#29、查询男生、女生人数
#30、查询名字中含有「风」字的学生信息
#31、查询同名同性学生名单,并统计同名人数
#32、查询 1990 年出生的学生名单
#33、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
#34、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
#35、查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
#36、查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
#37、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
#38、查询不及格的课程
#39、查询课程编号为 01 且课程成绩在 80 分以上(包括80分)的学生的学号和姓名
#40、求每门课程的学生人数
#41、成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
#42、成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
#43、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
#44、统计每门课程的学生选修人数(超过 5 人的课程才统计)
#45、检索至少选修两门课程的学生学号
#46、查询选修了全部课程的学生信息
#47、查询本周过生日的学生
#48、查询下周过生日的学生
#49、查询本月过生日的学生
#50、查询下月过生日的学生
三、答案
#1、查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
SELECT t1.sid, t1.score AS class1,t2.score AS class2 FROM
(SELECT sid,cid,score FROM sc WHERE cid = '01') t1,
(SELECT sid,cid,score FROM sc WHERE cid = '02') t2
WHERE t1.sid = t2.sid AND t1.score > t2.score
#2、查询同时存在” 01 “课程和” 02 “课程的情况
SELECT sc.SId,st.Sname FROM sc
INNER JOIN student st
ON sc.SId = st.SId
WHERE sc.CId = '01'
AND sc.SId IN(SELECT SId FROM sc WHERE CId = '02')
#3、查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
SELECT t1.SId,t1.CId,t1.score,t2.SId,t2.CId,t2.score
FROM (SELECT SId,CId,score FROM sc WHERE sc.cid = '01') t1
LEFT JOIN (SELECT SId,CId,score FROM sc WHERE sc.cid = '02') t2
ON t1.sid = t2.sid
#4、查询不存在” 01 “课程但存在” 02 "课程的情况
SELECT sc.SId,st.Sname FROM sc
INNER JOIN student st
ON sc.SId = st.SId
WHERE sc.CId = '02'
AND sc.SId NOT IN(SELECT SId FROM sc WHERE CId = '01')
#5、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.SId AS student_SId,s.Sname,AVG(sc.score)
FROM student s
INNER JOIN sc
ON s.SId = sc.SId
WHERE sc.score >= 60
GROUP BY student_SId
#6、查询在 SC 表存在成绩的学生信息
SELECT SId,Sname,Sage,Ssex FROM student WHERE SId IN(
SELECT SId FROM sc GROUP BY(SId))
#7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT st.SId,st.Sname,SUM(sc.CId),SUM(sc.score) FROM student st
LEFT JOIN sc
ON st.SId = sc.SId
GROUP BY(st.SId)
#8、查有成绩的学生信息
SELECT SId,Sname,Sage,Ssex FROM student
WHERE SId IN(SELECT SId FROM sc GROUP BY(SId))
#9、查询「李」姓老师的数量
SELECT count(*) FROM teacher WHERE Tname LIKE '李%'
#10、查询学过「张三」老师授课的同学的信息
SELECT SId,Sname,Sage,Ssex FROM student WHERE SId IN(
SELECT SId FROM sc WHERE CId IN (
SELECT CId FROM course WHERE TId IN (
SELECT TId FROM teacher WHERE Tname = '张三')))
#11、查询没有学全所有课程的同学的信息
SELECT SId,Sname,Sage,Ssex FROM student WHERE SId NOT IN
(SELECT SId FROM sc
GROUP BY SId
HAVING COUNT(CId) = (SELECT COUNT(Cname) FROM course))
#12、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT SId,Sname,Sage,Ssex FROM student
WHERE SId IN (
SELECT SId FROM sc WHERE CId IN(
SELECT CId FROM sc WHERE SId = '01')
GROUP BY SId)
#13、查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT st.SId,st.Sname,st.Sage,st.Ssex
FROM sc
INNER JOIN student st
ON sc.SId = st.SId
WHERE sc.CId IN (SELECT cid FROM sc WHERE SId = '01')
AND sc.SId != '01'
GROUP BY sc.SId
HAVING COUNT(sc.SId) = (Select count(cid) from sc where sid = '01')
#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT st.SId,st.sname FROM student st
WHERE st.sname NOT IN
(SELECT st.sname FROM student st
INNER JOIN sc
ON st.sid = sc.sid
INNER JOIN course c
ON sc.cid = c.cid
INNER JOIN teacher t
ON c.tid = t.tid
WHERE t.tname = '张三')
#15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT st.SId,st.Sname,AVG(sc.score) FROM student st
INNER JOIN sc
ON st.SId = sc.SId
WHERE sc.score < 60
GROUP BY sc.SId
HAVING COUNT(sc.CId) >= 2
#16、检索" 01 "课程分数小于 60,按分数降序排列的学生信息
SELECT s.SId,s.Sname,s.Sage,s.Ssex
FROM student s
INNER JOIN sc
ON s.SId = sc.SId
AND sc.CId = '01'
AND sc.score < 60
ORDER BY sc.score
#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sc.sid,t1.avg_score,sc.cid,sc.score FROM sc
LEFT JOIN (SELECT sid,avg(score) AS avg_score,cid,score FROM sc GROUP BY sid) t1
ON sc.sid = t1.sid
GROUP BY t1.avg_score DESC
#18、查询各科成绩最高分、最低分和平均分
SELECT c.Cname,MAX(sc.score),MIN(sc.score),AVG(sc.score)
FROM course c
INNER JOIN sc
ON c.CId = sc.CId
GROUP BY(sc.CId)
#19、以如下形式显示:课程 ID,课程 name,及格率,中等率,优良率,优秀率;及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
SELECT sc.cid,c.cname,
sum(case when sc.score>=60 then 1 else 0 end)/count(sc.score) AS '及格',
sum(case when sc.score between 70 and 80 then 1 else 0 end)/count(sc.score) AS '中等',
sum(case when sc.score between 80 and 90 then 1 else 0 end)/count(sc.score) AS '优良',
sum(case when sc.score>=90 then 1 else 0 end)/count(sc.score) AS '优秀'
FROM sc
INNER JOIN course c
ON sc.cid = c.cid
GROUP BY sc.cid
#20、按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
SELECT a.cid, a.sid, a.score, count(b.score)+1 AS rank
FROM sc AS a
LEFT JOIN sc AS b
ON a.score<b.score AND a.cid = b.cid
GROUP BY a.cid, a.sid,a.score
ORDER BY a.cid, rank ASC;
#21、按各科成绩进行排序,并显示排名, Score 重复时合并名次
SELECT *,
CASE WHEN @sco=score THEN @rank ELSE @rank:=@rank+1 END AS rn,
@sco:=score
FROM sc,(SELECT @rank:=0,@sco:=NULL) t
ORDER BY score DESC;
#22、要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT cid, count(sid) FROM sc
GROUP BY cid
ORDER BY count(sid) DESC,cid ASC
SELECT cid, count(sid) FROM sc
GROUP BY cid
ORDER BY 2 DESC,1 ASC
#23、查询学生的总成绩,并进行排名
SELECT sc.SId,st.Sname,SUM(score)
FROM sc
INNER JOIN student st
ON sc.SId = st.SId
GROUP BY SId
ORDER BY SUM(score) DESC
#24、查询每个学生的总成绩,平均分以及最高分
SELECT sc.SId,st.Sname,SUM(score),AVG(score),MAX(score)
FROM sc
INNER JOIN student st
ON sc.SId = st.SId
GROUP BY SId
#25、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
SELECT CId,
ROUND((SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(SId)),2) AS '[100-85]',
ROUND((sum(case when score between 70 and 85 then 1 else 0 end)/count(sid)),2) AS '[85-70]',
ROUND((sum(case when score between 60 and 70 then 1 else 0 end)/count(sid)),2) AS '[70-60]',
ROUND((sum(case when score between 0 and 60 then 1 else 0 end)/count(sid)),2) AS '[60-0]'
FROM sc
GROUP BY CId
#26、查询各科成绩前三名的记录
SELECT CO.cname '课程',
(SELECT sc.SId
FROM sc
WHERE sc.CId = co.CId
ORDER BY sc.score Desc LIMIT 0, 1) '第一名',
(SELECT sc.SId
FROM sc
WHERE sc.CId = co.CId
ORDER BY sc.score Desc LIMIT 1, 1) '第二名',
(SELECT sc.SId
FROM sc
WHERE sc.CId = co.CId
ORDER BY sc.score Desc LIMIT 2, 1) '第三名'
FROM course co;
#27、查询每门课程被选修的学生数
SELECT sc.CId,COUNT(sc.SId) AS '被选修的学生数' FROM sc GROUP BY(sc.CId)
#28、查询出只选修两门课程的学生学号和姓名
SELECT sc.SId,st.Sname,COUNT(sc.CId) AS cidCount,sc.score
FROM sc
INNER JOIN student st
ON sc.SId = st.SId
GROUP BY SId
HAVING cidCount = 2
#29、查询男生、女生人数
SELECT COUNT(*) AS '男生、女生人数' FROM student GROUP BY(Ssex)
SELECT count(DISTINCT t1.sid) 男,count(DISTINCT t2.sid) 女 FROM
(SELECT sid FROM student WHERE ssex = '男') t1,
(SELECT sid FROM student WHERE ssex = '女') t2
#30、查询名字中含有「风」字的学生信息
SELECT SId,Sname,Sage,Ssex FROM student WHERE Sname LIKE CONCAT('%','风','%')
#31、查询同名同性学生名单,并统计同名人数
SELECT sname, count(*) FROM student
GROUP BY sname
HAVING count(*)>1;
SELECT SId,Sname,Sage,Ssex FROM
(SELECT t1.SId,t1.Sname,t1.Sage,t1.Ssex
FROM student t1
INNER JOIN student t2
ON t1.sname = t2.sname
WHERE t1.sid != t2.sid
GROUP BY t1.sid) a
#32、查询 1990 年出生的学生名单
SELECT SId,Sname,YEAR(Sage) AS SageYear,Ssex FROM student WHERE YEAR(Sage) = '1990'
#33、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT SId,CId,AVG(score) as score_avg FROM sc
GROUP BY CId
ORDER BY score_avg DESC,CId ASC
#34、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT sc.SId,s.Sname,AVG(sc.score) AS Avgscore FROM sc
INNER JOIN student s
ON sc.SId = s.SId
GROUP BY(sc.SId)
HAVING Avgscore >= 85
#35、查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT s.Sname,sc.score FROM sc
INNER JOIN course c
ON sc.CId = c.CId
INNER JOIN student s
ON s.SId = sc.SId
WHERE c.Cname = '数学'
AND sc.score < 60
#36、查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT st.sid,st.sname,st.Sage,st.Ssex,sc.CId,sc.score
FROM student st
LEFT JOIN sc
ON st.sid = sc.sid
#37、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT sc.sid,s.Sname,c.Cname,sc.score FROM sc
INNER JOIN student s
ON sc.SId = s.SId
INNER JOIN course c
ON sc.CId = c.CId
WHERE sc.score > 70
#38、查询不及格的课程
SELECT sc.sid,s.Sname,c.Cname,sc.score FROM sc
INNER JOIN course c
ON sc.CId = c.CId
INNER JOIN student s
ON s.SId = sc.SId
WHERE score < 60
#39、查询课程编号为 01 且课程成绩在 80 分以上(包括80分)的学生的学号和姓名
SELECT s.SId,s.Sname,sc.score FROM sc
INNER JOIN student s
ON sc.SId = s.SId
WHERE sc.CId = '01' AND sc.score >= 80
#40、求每门课程的学生人数
SELECT c.Cname,COUNT(sc.SId) AS '每门课程的学生人数' FROM sc
INNER JOIN course c
ON sc.CId = c.CId
GROUP BY(sc.CId)
SELECT sc.cid, COUNT(sc.sid) AS '每门课程的学生人数' FROM sc
GROUP BY sc.cid
#41、成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT st.SId,st.Sname,st.Sage,st.Ssex,max(sc.score) FROM sc
INNER JOIN course c
ON sc.CId = c.CId
INNER JOIN teacher t
ON c.TId = t.TId AND t.Tname = '张三'
INNER JOIN student st
ON sc.SId = st.SId
#42、成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT t1.SId,t1.Sname,t1.Sage,t1.Ssex, t1.score FROM
(SELECT st.SId,st.Sname,st.Sage,st.Ssex, sc.score FROM sc
INNER JOIN course c
ON sc.cid = c.cid
INNER JOIN teacher t
ON c.tid = t.tid AND t.tname = '张三'
INNER JOIN student st
ON sc.sid = st.sid) t1,
(SELECT st.SId,st.Sname,st.Sage,st.Ssex,max(sc.score) AS max_score FROM sc
INNER JOIN course c
ON sc.CId = c.CId
INNER JOIN teacher t
ON c.TId = t.TId AND t.Tname = '张三'
INNER JOIN student st
ON sc.SId = st.SId) t2
WHERE t1.score = t2.max_score
#43、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT sc.* FROM sc,
(SELECT sid,cid,score FROM sc
GROUP BY sid
HAVING count(DISTINCT cid) > count(DISTINCT score)) t1
WHERE sc.sid = t1.sid
#44、统计每门课程的学生选修人数(超过 5 人的课程才统计)
SELECT sc.CId,c.Cname,COUNT(sc.SId) AS Scount FROM sc
INNER JOIN course c
ON sc.CId = c.CId
GROUP BY(sc.CId)
HAVING Scount > 5
#45、检索至少选修两门课程的学生学号
SELECT SId,COUNT(CId) Cnum from sc GROUP BY(SId)
HAVING Cnum >= 2
#46、查询选修了全部课程的学生信息
SELECT SId,Sname,Sage,Ssex FROM student WHERE SId IN(
SELECT SId FROM sc
GROUP BY(SId)
HAVING COUNT(sc.CId) = (SELECT COUNT(CId) AS Ccount FROM course))
#47、查询本周过生日的学生
SELECT SId,Sname,Sage,Ssex
FROM student
WHERE WEEKOFYEAR(Sage)=WEEKOFYEAR(current_date())
#48、查询下周过生日的学生
SELECT SId,Sname,Sage,Ssex
FROM student
WHERE WEEKOFYEAR(Sage)=WEEKOFYEAR(current_date())+1
#49、查询本月过生日的学生
SELECT SId,Sname,Sage,Ssex FROM student
WHERE MONTH(Sage) = MONTH(current_date())
#50、查询下月过生日的学生
SELECT SId,Sname,Sage,Ssex FROM student
WHERE MONTH(Sage) = MONTH(current_date())+1