SQL习题
tips:简书不支持页内跳转，页内跳转链接都是参照markdown语法书写
且题目来源于知乎作者：TOMOCAT链接：https://zhuanlan.zhihu.com/p/53302593。
个人仅做题目解析和做答。
目录
1.环境搭建
1.1表结构
1.2生成各表
2.题目目录
1.环境搭建sqlite
1.1表结构
--学生表
Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--课程表
Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号
--教师表
Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名
--成绩表
SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数
1.2生成各表
--创建Student表
create table Student(sid varchar(10) PRIMARY KEY NOT NULL, 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('08' , '王菊' , '1990-01-20' , '女');
insert into Student values('09', '李云', '1990-05-06', '女');
--创建Teacher表
create table Teacher(tid varchar(10) PRIMARY KEY NOT NULL,tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--创建Course
create table Course(cid varchar(10) PRIMARY KEY NOT NULL, cname varchar(10), tid varchar(10),
FOREIGN KEY(tid) REFERENCES Teacher(tid));
--插入数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--创建SC[student course]
create table SC(sid varchar(10) ,cid varchar(10),score decimal(18,1),
--外键分别与Student，Course表相关联
FOREIGN KEY(sid) REFERENCES Student(sid),
FOREIGN KEY(cid) REFERENCES Course(cid));
--插入数据
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);
2.题目目录
6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名
8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名
11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
16、检索"01"课程分数小于60，按分数降序排列的学生信息
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
26、查询每门课程被选修的学生数
27、查询出只选修了一门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询名字中含有"风"字的学生信息
30、查询同名同性学生名单，并统计同名人数
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
37、查询不及格的课程，并按课程号从大到小排列
38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名
40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
42、查询每门功课成绩最好的前两名
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46、查询各学生的年龄
47、查询本周过生日的学生
48、查询下周过生日的学生
49、查询本月过生日的学生
50、查询下月过生日的学生
1、查询“01”课程比“02”课程成绩高的所有学生的学号
--查询SC表，且满足S01.score > S02.score
--使用JOIN自联结
--条件：s1.sid = s2.sid AND s1.cid = '01' AND s2.cid = '02'
--WHERE s1.score > s2.score
SELECT s1.sid
FROM SC AS s1 JOIN SC AS s2
ON s1.sid = s2.sid AND s1.cid = '01' AND s2.cid = '02'
WHERE s1.score > s2.score;
2、查询平均成绩大于60分的同学的学号和平均成绩
--从SC表中查询，可以根据学号分组，使用AVG()求平均值
--条件：AVG(score)
SELECT sid, AVG(score)
FROM SC
GROUP BY sid
HAVING AVG(score) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩
--从Student表中获取sid,name
--JOIN，并根据sid对SC表进行分组，用COUNT(*)计算选课数，SUM(score)计算总成绩
SELECT s.sid, s.Sname, COUNT(sc.cid) AS Courses, sum(sc.score) AS TotalScore
FROM student AS s
LEFT JOIN SC AS sc
ON s.sid = sc.sid
GROUP BY s.sid;
4、查询姓“李”的老师的个数
--从Teacher表中查询Tname = '李%'，需要使用通配符进行模糊查询
SELECT COUNT(*) AS 'numofli'
FROM Teacher
WHERE Tname LIKE '李%';
5、查询没学过“张三”老师课的同学的学号、姓名；
--使用嵌套查询
--在子查询中使用外联结，Teacher, SC, Course表
--T.Tname = '张三', 找出上了张老师课程的学生号
--sid NOT IN (these who are in the course)
SELECT sid, Sname
FROM Student
WHERE sid not in (
SELECT SC.sid
FROM SC
LEFT JOIN Course AS C ON SC.cid = C.cid
LEFT JOIN Teacher AS T ON C.tid = T.tid
WHERE T.Tname = '张三'
);
6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名
--嵌套查询
--子查询中自联结SC表，条件为：SC1.sid = SC2.sid AND SC1.cid = '01'
--AND SC2.cid = '02'
--find sid sname in (subsearch)
SELECT sid, sname
FROM Student
WHERE sid in (
SELECT SC1.sid
FROM SC AS SC1
JOIN SC AS SC2
ON SC1.sid = SC2.sid AND SC1.cid = '01' AND SC2.cid = '02'
);
7、查询学过“张三”老师所教的的同学的学号、姓名
--和题五思路相似
--使用外联结，Teacher, SC, Course表
--T.Tname = '张三', 找出上了张老师课程的学生号
SELECT S.sid, S.Sname
FROM SC
LEFT JOIN Course AS C ON SC.cid = C.cid
LEFT JOIN Teacher AS T ON C.tid = T.tid
LEFT JOIN Student AS S ON SC.sid = S.sid
WHERE T.Tname = '张三';
8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名
--和题一类似考察联结
SELECT st.sid,st.sname
FROM SC AS s1 JOIN SC AS s2
ON s1.sid = s2.sid AND s1.cid = '01' AND s2.cid = '02'
LEFT JOIN Student AS st ON s1.sid = st.sid
WHERE s1.score < s2.score;
tip:根据联结条件是筛选出同时有这两门成绩的行进行比较，不知道需不需要考虑’02‘为null的情况。
9、查询所有课程成绩小于60分的同学的学号、姓名；
--从SC表中查询，可以根据学号分组，使用AVG()求平均值
--条件：AVG(score)
SELECT s.sid, s.sname
FROM Student AS s
LEFT JOIN SC ON s.sid = SC.sid
GROUP BY s.sid
HAVING AVG(score) < 60;
10、查询没有学全所有课的同学的学号、姓名；
--先C1=COUNT(*)从Course中获取课程数
--查询SC，根据sid分组，条件COUNT(sid)< C1
--联结Student获取SC.sid = Student.sid
SELECT s.sid, s.sname
FROM Student AS s
LEFT JOIN SC ON SC.sid = s.sid
GROUP BY s.sid
HAVING COUNT(*) < (
SELECT COUNT(cid)
FROM Course
);
11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
--子查询出01的课程，再联结SC筛选满足条件的行，再到Student获取学生信息
SELECT DISTINCT s.sid, s.sname
FROM (
SELECT cid
FROM sc
where sid='01'
) AS t1
LEFT JOIN SC
ON t1.cid=sc.cid
LEFT JOIN Student AS s
ON s.sid = SC.sid
WHERE s.sid != '01';
12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
--思路如题11
SELECT DISTINCT s.sid, s.sname
FROM SC AS t1
LEFT JOIN SC
ON t1.cid=sc.cid AND t1.sid = '01'
LEFT JOIN Student AS s
ON s.sid = SC.sid
WHERE s.sid != '01'
--前面筛选出满足课程相同的行(这里指单科相同)，并排除01
--按照学号分组，查看选课程数是否和01的同学相同
GROUP BY SC.sid
HAVING COUNT(*) = (
SELECT COUNT(cid) AS num
FROM sc
where sid='01'
);
13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
--两个子查询，一个计算出AVG(张老师课所有的成绩)作为替换值
--一个查出张三课程cid为筛选条件
--替换所有cid属于张老师的score
UPDATE SC
SET score = ( SELECT AVG(SC.score) AS avgz FROM SC
LEFT JOIN Course AS c
ON SC.cid = C.cid
LEFT JOIN Teacher AS t
ON t.tname = '张三' AND C.tid = t.tid
)
WHERE SC.cid = ( SELECT SC.cid FROM SC
LEFT JOIN Course AS c
ON SC.cid = C.cid
LEFT JOIN Teacher AS t
ON t.tname = '张三' AND C.tid = t.tid
) ;
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
--先找出“张三”的所有课程cid，联结SC
--使用NOT IN
SELECT DISTINCT s.sid
FROM Student AS s
LEFT JOIN SC
ON s.sid = SC.sid
WHERE s.sid NOT IN(
SELECT t.sid
FROM
(SELECT SC.sid
FROM Teacher AS t
LEFT JOIN Course AS c
ON t.tid = c.tid
LEFT JOIN SC
ON c.cid = SC.cid
--也可以这里只留下张三的行
WHERE t.zhi = '张三') AS t
--除去结果集中的空值，防止子查询不返回任何值
WHERE t.sid IS NOT NULL
);
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
--联结student和sc
--通过学号分组，筛选出score<60 & count(score) >= 2
SELECT s.sid, s.sname, AVG(SC.score) AS avg_score
FROM Student AS s
LEFT JOIN SC
ON s.sid = SC.sid
WHERE SC.score < 60
GROUP BY s.sid
HAVING COUNT(SC.score) >= 2;
16、检索"03"课程分数小于60，按分数降序排列的学生信息
--联结Student和SC表做
--使用ORDER BY DESC排序
SELECT s.sid, s.sname, SC.score
FROM Student AS s
LEFT JOIN SC
ON s.sid = SC.sid AND SC.cid = '03'
WHERE SC.score < 60
ORDER BY SC.score DESC;
17、按平均成绩从高到低显示所有学生的平均成绩
SELECT s.sid, s.Sname, AVG(sc.score) AS AVGScore
FROM student AS s
LEFT JOIN SC AS sc
ON s.sid = sc.sid
GROUP BY s.sid
ORDER BY AVGScore DESC;
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
--子查询获取及格人数
--按照课程cid分组
--使用ROUND函数调整小数点位数
--在子查询中的及格人数*1.00保证计算passrate时进行浮点运算，不然被四舍五入为0
SELECT SC.cid, C.cname, MAX(SC.score) AS topscore, MIN(SC.score) lowscore,
AVG(SC.Score) AVGScore, ROUND(t1.pass_num / COUNT(*),2) AS pass_rate
FROM SC
LEFT JOIN Course c
ON SC.cid = c.cid
LEFT JOIN (
SELECT cid, COUNT(score)*1.00 AS pass_num
FROM SC
WHERE score >= 60
GROUP BY cid
) AS t1
ON t1.cid = SC.cid
GROUP BY SC.cid
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
--同18题，这题考察一个ORDER BY的用法
--使用CASE语句代替上面的子查询
SELECT SC.cid, C.cname, MAX(SC.score) AS topscore, MIN(SC.score) lowscore,
AVG(SC.Score) AVGScore,
ROUND(
COUNT(
CASE WHEN SC.score >=60
THEN sid
ELSE null
END
)*1.00 / COUNT(*),
2) AS pass_rate
FROM SC
LEFT JOIN Course c
ON SC.cid = c.cid
GROUP BY SC.cid
ORDER BY AVGScore, pass_rate DESC;
20、查询学生的总成绩并进行排名
--从Student表中获取sid,name
--JOIN，并根据sid对SC表进行分组,SUM(score)计算总成绩
--使用ROW_Number() OVER( ORDER BY )新增一个自增列
SELECT s.sid, s.Sname, sum(sc.score) AS TotalScore,
--这里排序条件好像不支持别名TotalScore
ROW_Number() OVER( ORDER BY sum(sc.score) DESC ) AS rank
FROM student AS s
LEFT JOIN SC AS sc
ON s.sid = sc.sid
GROUP BY s.sid
ORDER BY TotalScore DESC;
21、查询不同老师所教不同课程平均分从高到低显示
--三个表：course,teacher , sc
--先找出每门课对应的老师和平均分
SELECT sc.cid , c.cname, t.tname, AVG(sc.score) AS avgc
FROM sc
LEFT JOIN Course AS c
ON sc.cid = c.cid
LEFT JOIN Teacher AS t
ON t.tid = c.tid
GROUP BY sc.cid
ORDER BY avgc DESC;
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
--问题：并列第一和并列二三时会少输出数据或多输出
SELECT sid, rank_num, score, cid
FROM(
SELECT rank() OVER(PARTITION BY cid ORDER BY score DESC) as rank_num
,sid , score, cid
FROM sc
) t
where rank_num in (2,3);
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
--考察静态行转列
select
sc.cid
,cname
,ROUND(COUNT(CASE WHEN SC.score BETWEEN 85 AND 100
THEN sid ELSE null END
)*1.00 / COUNT(sid), 2) AS '[85,100]'
,ROUND(COUNT(CASE WHEN SC.score BETWEEN 70 AND 85
THEN sid ELSE null END
)*1.00 / COUNT(sid), 2) AS '[70, 85]'
,ROUND(COUNT(CASE WHEN SC.score BETWEEN 60 AND 70
THEN sid ELSE null END
)*1.00 / COUNT(sid), 2) AS '[60, 70]'
,ROUND(COUNT(CASE WHEN SC.score BETWEEN 0 AND 60
THEN sid ELSE null END
)*1.00 / COUNT(sid), 2) AS '[0, 60]'
from sc
left join course
on sc.cid=course.cid
group by sc.cid,cname;
24、查询学生平均成绩及其名次
--子查询得出包含学生id和平均成绩的结果集
--使用rank函数进行排序
select
sid
,avg_score
,rank() over (order by avg_score desc) AS rank
from
(
select
sid
,avg(score) as avg_score
from sc
group by sid
)t;
25、查询各科成绩前三名的记录
--和题22类似
SELECT sid, rank_num, score, cid
FROM(
SELECT rank() OVER(PARTITION BY cid ORDER BY score DESC) as rank_num
,sid , score, cid
FROM sc
) t
where rank_num <= 3;
26、查询每门课程被选修的学生数
SELECT cid, COUNT(sid) AS s_num
FROM sc
GROUP BY cid;
27、查询出只选修了一门课程的全部学生的学号和姓名
SELECT s.sid, s.sname
FROM Student s
LEFT JOIN SC
ON s.sid = SC.sid
GROUP BY s.sid
HAVING COUNT(SC.cid) = 1;
28、查询男生、女生人数
SELECT ssex, COUNT(*)
FROM Student
GROUP BY ssex;
29、查询名字中含有"风"字的学生信息
SELECT sid, sname, ssex
FROM Student
WHERE sname LIKE '%风%';
30、查询同名同性学生名单，并统计同名人数
SELECT sname,ssex, COUNT(sid) AS num
FROM Student
GROUP BY sname, ssex
HAVING COUNT(*) > 1;
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT sid, sname, sage
FROM Student
--WHERE sage BETWEEN '1990-01-01' AND '1990-12-31';
--使用strftime函数的做法
WHERE strftime('%Y',sage) = '1990';
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT cid, AVG(score) AS avgs
FROM SC
GROUP BY cid
ORDER BY avgs, cid DESC;
37、查询不及格的课程，并按课程号从大到小排列
--题目有问题啊，课程里不一定每个学生都及格了。
SELECT cid, score
FROM SC
WHERE score < 60
ORDER BY cid DESC, sid;
38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
SELECT s.sid, s.sname
FROM Student s
LEFT JOIN SC
ON s.sid = SC.sid
WHERE SC.cid = '01' AND SC.score > 60;
40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT s.sid, s.sname, SC.score
FROM Student s
LEFT JOIN SC
ON s.sid = SC.sid
LEFT JOIN Course c
ON SC.cid = c.cid
LEFT JOIN Teacher t
ON c.tid = t.tid
WHERE t.tname = '张三'
ORDER BY SC.score DESC
LIMIT 1;
42、查询每门功课成绩最好的前两名
SELECT sid, rank_num, score, cid
FROM(
SELECT rank() OVER(PARTITION BY cid ORDER BY score DESC) as rank_num
,sid , score, cid
FROM sc
) t
WHERE rank_num < 3;
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT
cid
,count(sid) as cnt
FROM sc
GROUP BY cid
HAVING cnt>=5
ORDER BY COUNT(sid) DESC, cid;
44、检索至少选修两门课程的学生学号
SELECT sid
FROM SC
GROUP BY sid
HAVING COUNT(cid) >= 2;
45、查询选修了全部课程的学生信息
SELECT sid
FROM SC
GROUP BY sid
HAVING COUNT(cid) = (
SELECT COUNT(cid)
FROM Course
);
46、查询各学生的年龄
--MYSQL可以使用DATEDIFF()，这里不行
--还是用回strftime()
SELECT sid, sname, (strftime('%Y','now') - strftime('%Y', sage)) as age
FROM Student;
47、查询本周过生日的学生
--查询一年第几周，只要在这周内的生日就筛选出来
--先获取当前周数
SELECT sid, sname
FROM Student
WHERE strftime('%W', sage) = strftime('%W', 'now');
48、查询下周过生日的学生
--同上题，使用关键字star of week, +1 week,变成下周
--查询一年第几周，只要在这周内的生日就筛选出来
--先获取当前周数
SELECT sid, sname
FROM Student
WHERE strftime('%W', sage) = strftime('%W', 'now')+1;
49、查询本月过生日的学生
SELECT sid, sname
FROM Student
WHERE strftime('%m', sage) = strftime('%m', 'now');
50、查询下月过生日的学生
SELECT sid, sname
FROM Student
WHERE strftime('%m', sage) = strftime('%m', 'now','+1 month');
网友评论