**
50道经典SQL练习题
**
PS:这50道题答案仅供参考，若是有更好的解题思路和更简便的解答方法，欢迎留言。
数据表介绍
1.学⽣表
Student(SId,Sname,Sage,Ssex) SId 学⽣编号,Sname 学⽣姓名,Sage 出⽣年⽉,Ssex 学⽣性别
–2.课程表
Course(CId,Cname,TId) CId 课程编号,Cname 课程名称,TId 教师编号
–3.教师表
Teacher(TId,Tname) TId 教师编号,Tname 教师姓名
–4.成绩表
SC(SId,CId,score) SId 学⽣编号,CId 课程编号,score 分数
数据SQL
```sql
-- 学⽣表
--Student
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-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');
insert into Student values('06' , '吴兰' , '1992-01-01' , '⼥');
insert into Student values('07' , '郑⽵' , '1989-01-01' , '⼥');
insert into Student values('09' , '张三' , '2017-12-20' , '⼥');
insert into Student values('10' , '李四' , '2017-12-25' , '⼥');
insert into Student values('11' , '李四' , '2012-06-06' , '⼥');
insert into Student values('12' , '赵六' , '2013-06-13' , '⼥');
insert into Student values('13' , '孙七' , '2014-06-01' , '⼥');
-- 科⽬表 Course
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');
-- 教师表 Teacher
create table Teacher(
TId varchar(10),
Tname varchar(10)
);
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表 SC
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 "课程成绩⾼的学⽣的信息及课程分数
–
思路：
-- 1、先分别查询01和02课程的学生编号和成绩
SELECT sid,score FROM sc WHERE cid='01';
SELECT sid,score FROM sc WHERE cid='02';
-- 2、发现并不是每个学生都同时选择了01和02课程，这时需要将两个查询的结果做join联结，条件是sid要相等，并且01课程成绩大于02课程成绩
SELECT s1.sid,s1.score FROM
(SELECT sid,score FROM sc WHERE cid='01') AS s1
JOIN
(SELECT sid,score FROM sc WHERE cid='02') AS s2
ON s1.sid = s2.sid
WHERE s1.score>s2.score;
-- 3、 题目要求查询学生信息，需要将查询结果与学生表在进行一个join联结
SELECT st.sid,st.sname,s.score FROM
(SELECT s1.sid,s1.score FROM
(SELECT sid,score FROM sc WHERE cid='01') AS s1
JOIN
(SELECT sid,score FROM sc WHERE cid='02') AS s2
ON s1.sid = s2.sid
WHERE s1.score>s2.score) AS s JOIN student st
ON s.sid = st.sid;
2.查询同时存在" 01 “课程和” 02 "课程的情况
思路：
-- 1、先分别查询选择了01课程和02课程的学生编号。
SELECT sid FROM sc WHERE cid='01';
SELECT sid FROM sc WHERE cid='02';
-- 2、将这两个查询的结果做一个join联结，条件是sid相等。
SELECT c1.sid FROM
(SELECT sid FROM sc WHERE cid='01') AS c1 JOIN
(SELECT sid FROM sc WHERE cid='02') AS c2
ON c1.sid = c2.sid;
3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
SELECT c1.*,c2.cid,c2.score FROM
(SELECT sid,cid,score FROM sc WHERE cid='01') AS c1 LEFT JOIN
(SELECT sid,cid,score FROM sc WHERE cid='02') AS c2
ON c1.sid =c2.sid;
4.查询不存在" 01 “课程但存在” 02 "课程的情况
SELECT c2.*,c1.cid,c1.score FROM
(SELECT sid,cid,score FROM sc WHERE cid='01') AS c1 RIGHT JOIN
(SELECT sid,cid,score FROM sc WHERE cid='02') AS c2
ON c1.sid = c2.sid;
5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
思路：
-- 1、先按照sid分组，计算学生的平均成绩,并且平均成绩要大于60分
SELECT sid,AVG(score) AS grade
FROM sc
GROUP BY sid
HAVING AVG(score) >= 60;
-- 2、将1中查询的结果作为虚拟表来与student表进行联结查询
SELECT st.sid,st.sname,ROUND(c.grade,2) AS 平均成绩 FROM
(SELECT sid,AVG(score) AS grade
FROM sc
GROUP BY sid
HAVING AVG(score) >= 60) AS c
JOIN student st
ON st.sid = c.sid;
PS：round(m,n)函数将字段m的查询结果保留小数点n位
6.查询在 SC 表存在成绩的学⽣信息
SELECT
DISTINCT S.`SId`,S.`Sname`
FROM
sc,student s
WHERE
sc.`SId`=s.`SId`
AND sc.`score` >= 0;
PS：DISTINCT 将后面的字段的查询结果进行去重。
7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
思路：
-- 1、按照sid分组，计算选课总数和所有课程的总成绩
SELECT sc.`SId`,COUNT(sc.`CId`) AS 选课总数,SUM(sc.`score`) AS 总成绩
FROM sc
GROUP BY sc.`SId`;
-- 2、将查询的结果作为一个虚拟表与student表做一个外连接查询
SELECT s.sid,s.sname,c.选课总数,c.总成绩 FROM
(SELECT sc.`SId`,COUNT(sc.`CId`) AS 选课总数,SUM(sc.`score`) AS 总成绩
FROM sc
GROUP BY sc.`SId`) AS c
RIGHT JOIN
student s
ON s.sid = c.sid;
-- 方法二：
SELECT stu.sid,stu.sname,COUNT(sc.`CId`),SUM(sc.`score`)
FROM student stu LEFT JOIN
sc ON stu.`SId` = sc.`SId`
GROUP BY stu.`SId`,stu.`Sname`;
8.查询「李」姓⽼师的数量
SELECT COUNT(tname)
FROM teacher
WHERE tname LIKE '李%';
9.查询学过「张三」⽼师授课的同学的信息
-- 思路：
-- 1、先查询张三老师教授的课程编号 02
SELECT t.`TId`,c.`CId` FROM teacher t,course c WHERE t.`TId` = c.`TId` AND t.`Tname`='张三' ;
-- 2、将1中查询的结果作为一个虚拟表，与sc表作一个外连接查询，找到学过张三老师授课的学生编号
SELECT sc.`SId` FROM
(SELECT t.`TId`,c.`CId` FROM teacher t,course c WHERE t.`TId` = c.`TId` AND t.`Tname`='张三' ) s
JOIN sc
ON s.cid = sc.`CId`;
-- 3、将2中查询的结果与student作外连接查询找到学生的信息
SELECT stu.`SId`,stu.`Sname`,stu.`Ssex` FROM
(SELECT sc.`SId` FROM
(SELECT t.`TId`,c.`CId` FROM teacher t,course c WHERE t.`TId` = c.`TId` AND t.`Tname`='张三' ) s
JOIN sc
ON s.cid = sc.`CId`) s
JOIN student stu
ON stu.sid = s.sid;
10.查询没有学全所有课程的同学的信息
SELECT
stu.`SId`,stu.`Sname`,COUNT(sc.`CId`) AS cou
FROM
student stu
LEFT JOIN sc
ON sc.`SId` = stu.`SId`
GROUP BY stu.`SId`,stu.`Sname`
HAVING cou<(SELECT COUNT(cid) FROM course);
**
11.查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息
**
思路：
-- 1、先查询学号为01同学所学的课程号
SELECT cid FROM sc WHERE sid = '01';
-- 2、查询至少有1门课与01同学所学相同的学生编号
SELECT sc.sid ,COUNT(sc.`SId`)
FROM
(SELECT cid FROM sc WHERE sid = '01') s
JOIN sc
ON sc.`CId` = s.cid
GROUP BY sc.`SId`;
-- 3、将2 的结果与student进行外连接查询，找到学生信息。
SELECT stu.sid,stu.sname FROM
(SELECT sc.sid ,COUNT(sc.`SId`)
FROM
(SELECT cid FROM sc WHERE sid = '01') s
JOIN sc
ON sc.`CId` = s.cid
GROUP BY sc.`SId`) s
JOIN student stu
ON stu.sid = s.sid;
--方法二：
SELECT DISTINCT s.`SId`,s.`Sname`
FROM student s
JOIN sc
ON sc.`SId` = s.`SId`
WHERE sc.`CId` IN
(SELECT cid FROM sc WHERE sid = '01');
ps:这个方法一是我自己想的麻烦了
12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-- 思路：
-- 1、sc表做一个自连接，条件是cid相等，并且sc1.sid = 01 ,sc2.sid != 01 , 这样能够查询出与学号01的同学选了相同课程的学生id
-- sc1表查询的是01同学的数据，sc2表查询的是非01同学的数据
SELECT *
FROM sc sc1
JOIN sc sc2
ON sc1.`CId`=sc2.`CId` AND sc1.`SId`='01' AND sc2.`SId`!='01';
-- 2、将查询的结果再做一个外连接，因为1中查询的数据是和01同学学习的课程编号中的一个相同，所以使用count（）查询出01同学选修了几门课，
-- 并且非01同学只要学习的课程编号数量与01同学相同，即完全和01同学学习的课程相同
SELECT sc2.sid,s.`Sname`
FROM sc sc1
JOIN sc sc2
ON sc1.`CId`=sc2.`CId` AND sc1.`SId`='01' AND sc2.`SId`!='01'
JOIN student s
ON s.`SId`=sc2.`SId`
GROUP BY sc2.`SId`,s.`Sname`
HAVING COUNT(sc2.`cid`) = (SELECT COUNT(*) FROM sc WHERE sc.`SId` = '01');
13.查询没学过"张三"⽼师讲授的任⼀⻔课程的学⽣姓名
–
-- 思路：
-- 1、先查询张三老师教授的课程编号
SELECT c.cid
FROM course c
JOIN teacher t
ON c.`TId` = t.`TId` AND t.`Tname`='张三';
-- 2、查询学习过张三老师教授的课程的学生id
SELECT sc.sid FROM
(SELECT c.cid
FROM course c
JOIN teacher t
ON c.`TId` = t.`TId` AND t.`Tname`='张三') s
JOIN sc
ON sc.`CId` = s.cid;
-- 3、将2作为条件，查询不在其中的学生id
SELECT DISTINCT stu.`SId`,stu.`Sname`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId` AND
sc.`SId` NOT IN
(SELECT sc.sid FROM
(SELECT c.cid
FROM course c
JOIN teacher t
ON c.`TId` = t.`TId` AND t.`Tname`='张三') s
JOIN sc
ON sc.`CId` = s.cid);
## 14.查询两⻔及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT stu.sid,stu.sname,AVG(s.`score`)
FROM student stu
JOIN (SELECT sid,score
FROM sc
WHERE sc.`score`<60) s
ON stu.`SId` = s.`SId`
GROUP BY stu.`SId`,stu.`Sname`
HAVING COUNT(s.sid) >= 2;
## 15.检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息
SELECT stu.sid,stu.sname,sc.`CId`,sc.`score`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId`
AND sc.`CId`='01'
AND sc.`score`<60
ORDER BY sc.`score` DESC;
16.按平均成绩从⾼到低显示所有学⽣的所有课程的成绩以及平均成绩
SELECT stu.`SId`,stu.`Sname`,c1.`score` AS 语文,c2.`score` AS 数学,c3.`score` AS 英语, AVG(c4.`score`) AS 平均成绩
FROM student stu
LEFT JOIN sc AS c1 ON c1.`SId` = stu.`SId` AND c1.`CId`='01'
LEFT JOIN sc AS c2 ON c2.`SId` = stu.`SId` AND c2.`CId` = '02'
LEFT JOIN sc AS c3 ON c3.`SId` = stu.`SId` AND c3.`CId` = '03'
LEFT JOIN sc AS c4 ON c4.`SId` = stu.`SId`
GROUP BY stu.`SId`,stu.`Sname`,语文,数学,英语
ORDER BY 平均成绩 DESC;
17.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列
SELECT sc.`CId`,c.cname,
MAX(sc.`score`) AS '最高分',
MIN(sc.`score`) AS '最低分',
ROUND(AVG(sc.`score`),2) AS '平均分',
COUNT(sc.`CId`) AS '选课人数',
CONCAT(ROUND(SUM(CASE WHEN sc.`score`>=60 THEN 1 ELSE 0 END)/COUNT(sc.`CId`)*100,2),'%') AS '及格率',
CONCAT(ROUND(SUM(CASE WHEN sc.`score`>=70 AND sc.`score`<80 THEN 1 ELSE 0 END)/COUNT(sc.`CId`)*100,2),'%') AS '中等率',
CONCAT(ROUND(SUM(CASE WHEN sc.`score`>=80 AND sc.`score`<90 THEN 1 ELSE 0 END)/COUNT(sc.`CId`) *100,2),'%') AS '优良率',
CONCAT(ROUND(SUM(CASE WHEN sc.`score`>=90 THEN 1 ELSE 0 END)/COUNT(sc.`CId`) *100,2),'%') AS '优秀率'
FROM sc JOIN course c ON c.cid = sc.`CId`
GROUP BY sc.`CId`,c.cname
ORDER BY 选课人数 DESC,sc.`CId`;
ps: sql所用函数介绍
concat()用作字符串拼接
concat(‘1’ , ’+’ , ’1’ , ‘=’ , ‘2’) ==> 1+1=2
case when 条件判断
CASE WHEN sc.score>=60 THEN 1 ELSE 0 END
意思是sc.score>=60这个条件成立时，返回1，不成立时返回0
18.按各科平均成绩进⾏排序，并显示排名， Score 重复时保留名次空缺
思路：
-- 1、按照各学科分组求平均值
SELECT sc.`CId`,AVG(sc.`score`) AS avg_sc FROM sc GROUP BY sc.`CId`;
-- 2、将1中查询的结果做一个自连接，条件是s1.avg_sc>s2.avg_sc, 这样做的目的是 s1.avg_sc 出现次数最多的就是 最高分，
-- 因为所有学科（包括自己）的平均分都大于等于最低分
SELECT s1.*,s2.*
FROM (SELECT sc.`CId`,AVG(sc.`score`) AS avg_sc FROM sc GROUP BY sc.`CId`) AS s1
JOIN (SELECT sc.`CId`,AVG(sc.`score`) AS avg_sc FROM sc GROUP BY sc.`CId`) AS s2
ON s1.avg_sc >= s2.avg_sc;
-- 3、按照学科进行分组，进行排列
SELECT s2.cid,s2.avg_sc,COUNT(s1.avg_sc) AS 排名
FROM (SELECT sc.`CId`,AVG(sc.`score`) AS avg_sc FROM sc GROUP BY sc.`CId`) AS s1
JOIN (SELECT sc.`CId`,AVG(sc.`score`) AS avg_sc FROM sc GROUP BY sc.`CId`) AS s2
ON s1.avg_sc >= s2.avg_sc
GROUP BY s2.cid,s2.avg_sc
ORDER BY 排名;
19.按各科平均成绩进⾏排序，并显示排名， Score 重复时不保留名次空缺
SELECT s.cid,s.avg_sc AS '平均分',@i := @i + 1 AS '排名'
FROM (SELECT sc.`CId`,AVG(sc.`score`) AS avg_sc FROM sc GROUP BY sc.`CId` ORDER BY avg_sc DESC) s,(SELECT @i := 0) AS a;
**20.查询学⽣的总成绩，并进⾏排名，总分重复时保留名次空缺
SELECT sc.`SId`,SUM(sc.`score`) AS sum_sc FROM sc GROUP BY sc.`SId`;
SELECT s1.*,s2.* FROM
(SELECT sc.`SId`,SUM(sc.`score`) AS sum_sc FROM sc GROUP BY sc.`SId`) AS s1
JOIN
(SELECT sc.`SId`,SUM(sc.`score`) AS sum_sc FROM sc GROUP BY sc.`SId`) AS s2
ON s1.sum_sc >= s2.sum_sc;
SELECT s2.sid,s2.sum_sc,COUNT( DISTINCT s1.sum_sc) AS 排名 FROM
(SELECT sc.`SId`,SUM(sc.`score`) AS sum_sc FROM sc GROUP BY sc.`SId`) AS s1
JOIN
(SELECT sc.`SId`,SUM(sc.`score`) AS sum_sc FROM sc GROUP BY sc.`SId`) AS s2
ON s1.sum_sc >= s2.sum_sc
GROUP BY s2.sid,s2.sum_sc
ORDER BY 排名;
**21.查询学⽣的总成绩，并进⾏排名，总分重复时不保留名次空缺**
```sql
SELECT s.`SId`,s.sum_sc,@i := @i + 1 AS '排名'
FROM (
SELECT sc.`SId`,SUM(sc.`score`) AS sum_sc
FROM sc
GROUP BY sc.`SId`
ORDER BY sum_sc DESC) AS s,
(SELECT @i := 0) b;
22.统计各科成绩各分数段⼈数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分⽐
SELECT sc.`CId`,c.`Cname`,
SUM(CASE WHEN sc.`score`<=100 AND sc.`score`> 85 THEN 1 ELSE 0 END) AS '[100-85]',
CONCAT(ROUND(SUM(CASE WHEN sc.`score`<=100 AND sc.`score`> 85 THEN 1 ELSE 0 END)/COUNT(sc.`score`)*100,2),'%') AS '百分比',
SUM(CASE WHEN sc.`score`<=85 AND sc.`score`> 70 THEN 1 ELSE 0 END) AS '[85-70]',
CONCAT(ROUND(SUM(CASE WHEN sc.`score`<=85 AND sc.`score`> 70 THEN 1 ELSE 0 END)/COUNT(sc.`score`)*100,2),'%') AS '百分比',
SUM(CASE WHEN sc.`score`<=70 AND sc.`score`>= 60 THEN 1 ELSE 0 END) AS '[70-85]',
CONCAT(ROUND(SUM(CASE WHEN sc.`score`<=70 AND sc.`score`>= 60 THEN 1 ELSE 0 END)/COUNT(sc.`score`)*100,2),'%') AS '百分比',
SUM(CASE WHEN sc.`score`<60 THEN 1 ELSE 0 END) AS '[60-0]',
CONCAT(ROUND(SUM(CASE WHEN sc.`score`<60 THEN 1 ELSE 0 END)/COUNT(sc.`score`)*100,2),'%') AS '百分比'
FROM sc
JOIN course c
ON sc.`CId` = c.`CId`
GROUP BY sc.`CId`,c.`Cname`;
23.查询各科成绩前三名的记录
SELECT a.* FROM (SELECT stu.`SId`,stu.`Sname`,sc.`CId`,c.`Cname`,sc.`score`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId`
JOIN course c
ON c.`CId` = sc.`CId` AND sc.`CId` = '01'
ORDER BY sc.`score` DESC
LIMIT 0,3) a
UNION ALL
SELECT b.* FROM (SELECT stu.`SId`,stu.`Sname`,sc.`CId`,c.`Cname`,sc.`score`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId`
JOIN course c
ON c.`CId` = sc.`CId` AND sc.`CId` = '02'
ORDER BY sc.`score` DESC
LIMIT 0,3) b
UNION ALL
SELECT c.* FROM (SELECT stu.`SId`,stu.`Sname`,sc.`CId`,c.`Cname`,sc.`score`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId`
JOIN course c
ON c.`CId` = sc.`CId` AND sc.`CId` = '03'
ORDER BY sc.`score` DESC
LIMIT 0,3) c;
PS:UNION用的比较多union all是直接连接，取到得是所有值，记录可能有重复 union 是取唯一值，记录没有重复
1、UNION 的语法如下：
[SQL 语句 1]
UNION
[SQL 语句 2]
2、UNION ALL 的语法如下：
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
24.查询每⻔课程被选修的学⽣数
SELECT sc.`CId`,COUNT(sc.`CId`) FROM sc GROUP BY sc.`CId`;
25.查询出只选修两⻔课程的学⽣学号和姓名
SELECT sc.`SId`,stu.sname
FROM sc
JOIN student stu
ON sc.`SId` = stu.`SId`
GROUP BY sc.`SId`,stu.`Sname`
HAVING COUNT(sc.score) = 2;
26.查询男⽣、⼥⽣⼈数
SELECT st.ssex,COUNT(1) FROM student st GROUP BY st.ssex;
27.查询名字中含有「⻛」字的学⽣信息
SELECT * FROM student WHERE student.`Sname` LIKE '%风%';
28.查询同名同性学⽣名单，并统计同名⼈数
SELECT s.sname,COUNT(s.SNAME) FROM student s GROUP BY s.sname,s.`Ssex` HAVING COUNT(s.`Sname`)>1;
29.查询 1990 年出⽣的学⽣名单
SELECT s.* FROM student s WHERE s.`Sage` BETWEEN '1990-1-1' AND '1991-1-1';
30.查询每⻔课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT sc.`CId`,c.`Cname`,AVG(sc.`score`) AS 平均成绩
FROM sc
JOIN course c
ON sc.`CId` = c.`CId`
GROUP BY sc.`CId`,c.`Cname`
ORDER BY 平均成绩 DESC,sc.`CId`;
31.查询平均成绩⼤于等于 85 的所有学⽣的学号、姓名和平均成绩
SELECT s.`SId`,s.`Sname`,AVG(sc.`score`) AS '平均成绩'
FROM student s
JOIN sc
ON sc.`SId` = s.`SId`
GROUP BY s.`SId`,s.`Sname`
HAVING 平均成绩>=85;
32.查询课程名称为「数学」，且分数低于 60 的学⽣姓名和分数
select s.`SId`,s.`Sname`,sc.`score`
from student s
join sc
on s.`SId` = sc.`SId`
join course c
on c.`CId` = sc.`CId` and c.`Cname` = '数学' and sc.`score`<60;
33.查询所有学⽣的课程及分数情况（存在学⽣没成绩，没选课的情况）
SELECT s.`SId`,s.`Sname`,s1.score AS '语文',s2.score AS '数学',s3.score AS '英语' FROM student s
LEFT JOIN
(SELECT * FROM sc WHERE sc.`CId` = '01') AS s1
ON s1.sid = s.`SId`
LEFT JOIN
(SELECT * FROM sc WHERE sc.`CId` = '02') AS s2
ON s2.sid = s.`SId`
LEFT JOIN
(SELECT * FROM sc WHERE sc.`CId` = '03') AS s3
ON s3.sid = s.`SId`;
34.查询任何⼀⻔课程成绩在 70 分以上的姓名、课程名称和分数
SELECT stu.`SId`,stu.`Sname`,sc.`CId`,c.cname,sc.`score`
FROM student stu
JOIN sc
ON stu.`SId` = sc.`SId` AND sc.`score` > 70
JOIN course c
ON c.`CId` = sc.`CId`;
35.查询不及格的课程
SELECT stu.`SId`,stu.`Sname`,sc.`CId`,c.cname,sc.`score`
FROM student stu
JOIN sc
ON stu.`SId` = sc.`SId` AND sc.`score` < 60
JOIN course c
ON c.`CId` = sc.`CId`;
36.查询课程编号为 01 且课程成绩在 80 分以上的学⽣的学号和姓名
SELECT stu.`SId`,stu.`Sname`,c.`Cname`,sc.`score`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId` AND sc.`CId` = '01' AND sc.`score` >= 80
JOIN course c
ON c.`CId` = sc.`CId`;
37.求每⻔课程的学⽣⼈数
SELECT sc.`CId`,c.cname,COUNT(sc.`SId`)
FROM sc
JOIN course c
ON sc.`CId` = c.`CId`
GROUP BY sc.`CId`,c.`Cname`;
38.成绩不重复，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩
SELECT stu.`SId`,stu.`Sname`,MAX(sc.`score`)
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId`
JOIN
(SELECT cid
FROM teacher t
JOIN course c
ON c.`TId` = t.`TId`
WHERE t.tname = '张三') t
ON t.cid = sc.`CId`;
39.成绩有重复的情况下，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩
SELECT s.`SId`,s.`Sname`,sc.`CId`,sc.`score`
FROM student s
JOIN sc
ON sc.`SId` = s.`SId` AND sc.`CId` = (SELECT cid
FROM teacher t
JOIN course c
ON c.`TId` = t.`TId`
WHERE t.tname = '张三')
AND sc.`score` =
(SELECT MAX(sc.`score`)
FROM sc
JOIN
(SELECT cid
FROM teacher t
JOIN course c
ON c.`TId` = t.`TId`
WHERE t.tname = '张三') t
ON t.cid = sc.`CId`);
40.查询不同课程成绩相同的学⽣的学⽣编号、课程编号、学⽣成绩
SELECT st.sid,st.sname,sc.cid,sc.score
FROM student st
LEFT JOIN sc ON sc.sid=st.sid
LEFT JOIN course c ON c.cid=sc.cid
WHERE (
SELECT COUNT(1)
FROM student st2
LEFT JOIN sc sc2 ON sc2.sid=st2.sid
LEFT JOIN course c2 ON c2.cid=sc2.cid
WHERE sc.score=sc2.score AND c.cid!=c2.cid
)>1;
41.查询每⻔课程成绩最好的前两名
SELECT * FROM (SELECT stu.`SId`,stu.`Sname`,sc.`CId`,sc.`score`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId`
AND sc.`CId` = '01'
ORDER BY sc.`score` DESC
LIMIT 0,2) a
UNION ALL
SELECT * FROM (SELECT stu.`SId`,stu.`Sname`,sc.`CId`,sc.`score`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId`
AND sc.`CId` = '02'
ORDER BY sc.`score` DESC
LIMIT 0,2) b
UNION ALL
SELECT * FROM (SELECT stu.`SId`,stu.`Sname`,sc.`CId`,sc.`score`
FROM student stu
JOIN sc
ON sc.`SId` = stu.`SId`
AND sc.`CId` = '03'
ORDER BY sc.`score` DESC
LIMIT 0,2) c;
42.统计每⻔课程的学⽣选修⼈数（超过 5 ⼈的课程才统计）。
SELECT sc.`CId`,COUNT(sc.`SId`) AS '选课人数'
FROM sc
GROUP BY sc.`CId`
HAVING 选课人数 > 5;
43.检索⾄少选修两⻔课程的学⽣学号
SELECT sc.`SId`,COUNT(sc.`CId`) AS '选课数量'
FROM sc
GROUP BY sc.`SId`
HAVING COUNT(sc.`CId`)>=2;
44.查询选修了全部课程的学⽣信息
SELECT s.`SId`,s.`Sname`,COUNT(sc.`CId`) AS 选课数量
FROM student s
JOIN sc
ON sc.`SId` = s.`SId`
GROUP BY s.`SId`,s.`Sname`
HAVING COUNT(sc.`CId`) =(SELECT COUNT(c.`CId`) FROM course c);
45.查询各学⽣的年龄，只按年份来算
SELECT s.*,TIMESTAMPDIFF(YEAR,s.`Sage`,NOW()) FROM student s;
46.按照出⽣⽇期来算，当前⽉⽇ < 出⽣年⽉的⽉⽇则，年龄减⼀
这道题等我解答出来再更新，抱歉了，老铁
47.查询本周过⽣⽇的学⽣
返回⽇期从范围内的数字⽇历星期1到53
select st.* from student st where week(now())=week(date_format(st.sage,'%Y%m%d'));
ps:
week()函数参见下列网址
https://blog.csdn.net/moakun/article/details/82528773
date_format()函数参见下列网址
https://www.w3school.com.cn/sql/func_date_format.asp
48.查询下周过⽣⽇的学⽣
select st.* from student st where week(now())+1=week(date_format(st.sage,'%Y%m%d'));
49.查询本⽉过⽣⽇的学⽣
select st.* from student st where month(now())=month(date_format(st.sage,'%Y%m%d'));
50.查询下⽉过⽣⽇的学⽣
select st.* from student st
where month(timestampadd(month,1,now()))=month(date_format(st.sage,'%Y%m%d'));
-- 或
select st.* from student st where (month(now()) + 1) mod 12 = month(date_format(st.sage,'%Y%m%d'));
910
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
