3，sql的常用练习题
一、表结构
1、学生表
Student(Sid,Sname,Sage,Ssex)
学生编号，学生姓名，出生年月，学生性别
2、课程表
Course(Cid,Cname,Tid)
课程编号，课程名称，教师编号
3、教师表
Teacher(Tid,Tname)
教师编号，教师姓名
4、成绩表
SC(Sid,Cid,Score)
学生编号，课程编号，分数
四个表之间的联系关系和创建sql
1、学生表 --建表语句 CREATE TABLE Student ( SID VARCHAR (10), Sname nvarchar (10), Sage datetime, Ssex nvarchar (10) ); --插入测试数据 INSERT INTO Student VALUES ('01' , '赵雷' , '1990-01-01' , '男'), ('02' , '钱电' , '1990-12-21' , '男'), ('03' , '孙风' , '1990-05-20' , '男'), ('04' , '李云' , '1990-08-06' , '男'), ('05' , '周梅' , '1991-12-01' , '女'), ('06' , '吴兰' , '1992-03-01' , '女'), ('07' , '郑竹' , '1989-07-01' , '女'), ('08' , '王菊' , '1990-01-20' , '女'); 2、课程表 --建表语句 CREATE TABLE Course ( CID VARCHAR (10), Cname nvarchar (10), TID VARCHAR (10) ); --插入测试数据 INSERT INTO Course VALUES ('01' , '语文' , '02'), ('02' , '数学' , '01'), ('03' , '英语' , '03'); 3、教师表 --建表语句 CREATE TABLE Teacher ( TID VARCHAR (10), Tname nvarchar (10) ); --插入测试数据 INSERT INTO Teacher VALUES ('01' , '张三'), ('02' , '李四'), ('03' , '王五'); 4、成绩表 --建表语句 CREATE TABLE SC ( SID VARCHAR (10), CID VARCHAR (10), score DECIMAL (18, 1) ); --插入测试数据 INSERT INTO SC VALUES ('01' , '01' , 80), ('01' , '02' , 90), ('01' , '03' , 99), ('02' , '01' , 70), ('02' , '02' , 60), ('02' , '03' , 80), ('03' , '01' , 80), ('03' , '02' , 80), ('03' , '03' , 80), ('04' , '01' , 50), ('04' , '02' , 30), ('04' , '03' , 20), ('05' , '01' , 76), ('05' , '02' , 87), ('06' , '01' , 31), ('06' , '03' , 34), ('07' , '02' , 89), ('07' , '03' , 98);
学生表对老师、课程、成绩都是是1对多
1、查询C_ID=" 01 "课程比C_ID=" 02"课程成绩高的学生的信息及课程分数
解题思路一：同一个表不能同一列去比较，于是需要把同一个表连接在一起，使同一列出现两个（注意表连接时要重新命名一个表）
--方法 SELECT a.*, b.score,c.score FROM Student a JOIN SC b ON a.SID = b.SID JOIN sc c ON a.SID = c.SID WHERE b.Cid = '01'
AND c.Cid = '02' AND b.Score > c.Score;
解题思路二：
SELECT a.* FROM Student a WHERE a.sid in ( select b.sid from sc b JOIN sc c ON b.sid = c.sid WHERE b.cid = '01' AND c.cid = '02' AND b.Score > c.Score );
2，查询出同时参加了课程编号cid=01和cid=02的学生信息和课程分数
SELECT a.*,b.score,c.score FROM Student a JOIN SC b ON a.SID=b.SID JOIN SC c ON a.SID=c.SID WHERE b.CID='01' AND c.CID='02';
3，查询存在出课程编号cid" 01 "但可能不存在"02 "的课程成绩数据
解题思路：利用left join的左侧基准原则，实现数据对应
SELECT * FROM (SELECT * FROM SC WHERE CID = '01') A LEFT JOIN (SELECT * FROM SC WHERE CID = '02') B
ON A.SID = B.SID;
4，查询成绩单中课程编号cid=02，但不是01的数据
select * from student a right join (select * from sc where cid=02) b on a.sid=b.sid where b.sid not in (select sid from sc where cid=01);
5，查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.sid,a.sname,b.avg from student a join (select sid,avg(score) avg from sc
group by sid having avg>=60) b on a.sid=b.sid;
6、查询在 SC 表存在成绩的学生信息
方法一：
select * from student where sid in (select sid from sc group by sid);
方法二： SELECT * FROM Student WHERE SID IN (SELECT DISTINCT SID FROM SC);
7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.sid,a.sname,b.cid_count,score_sum
from student a
left join (select sid,count(cid) cid_count,sum(score) score_sum
from sc group by sid) b
on a.sid=b.sid;
8、查有成绩的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.sid,a.sname,b.cnt,b.total from student a right join ( select sid,count(score) cnt,sum(score) total from sc group by sid) b on a.sid=b.sid;
9、查询「李」姓老师的数量
select count(*) from teacher where tname like '李%';
10、查询学过「张三」老师授课的同学的信息
方法一：
select * from student c join sc d on c.sid=d.sid where d.cid = (select cid from course a join teacher b on a.tid=b.tid where b.tname='张三');
方法二： select * from student where sid in (select sid from sc a
join course b on a.cid=b.cid join teacher c on b.tid=c.tid where c.tname='张三' );
11、查询没有学全所有课程的同学的信息
select * from student a where a.sid in (select sid from sc group by sid having count(cid)<3);
12、查询至少有一门课与学号为" 01 "的同学所学相同的所有学生的信息
select * from student where sid in (select sid from sc where cid in (select cid from sc b where sid=01 ));
13、查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select * from student a where sid in (select sid from sc where cid in (select cid from sc where sid=01) group by sid having count(cid)= (select count(*) from sc where sid = 01) and sid!=01);
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student where sid not in (select sid from sc a join course b on a.cid = b.cid join teacher c on b.tid=c.tid where c.tname='张三');
15、 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.sid,a.sname,b.score_avg from student a join (select sid,avg(score) score_avg from sc b where score<60 group by sid having count(*)>=2) b on a.sid=b.sid;
16、检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT A.*,B.score FROM Student A JOIN SC B ON A.SID=B.SID WHERE CID='01' AND Score<60 ORDER BY score DESC;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT SID, MAX(case CID when '01' then score else 0 end) '01', MAX(case CID when '02' then score else 0 end)'02', MAX(case CID when '03' then score else 0 end)'03', AVG(score)平均分 FROM SC GROUP BY SID ORDER BY 平均分 DESC;
18、查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT DISTINCT A.CID,Cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 FROM SC A LEFT JOIN Course on A.CID=Course.CID LEFT JOIN (SELECT CID,MAX(score)最高分,MIN(score)最低分,convert(AVG(score), decimal(5,2))平均分 FROM SC GROUP BY CID)B on A.CID=B.CID LEFT JOIN (SELECT CID,convert(sum(case when score>=60 then 1 else 0 end)/COUNT(*)*100, decimal(5,2))及格率 FROM SC GROUP BY CID)C on A.CID=C.CID LEFT JOIN (SELECT CID,convert(sum(case when score >=70 and score<80 then 1 else 0 end)/COUNT(*)*100, decimal(5,2))中等率 FROM SC GROUP BY CID)D on A.CID=D.CID LEFT JOIN (SELECT CID,convert(sum(case when score >=80 and score<90 then 1 else 0 end)/COUNT(*)*100, decimal(5,2))优良率 FROM SC GROUP BY CID)E on A.CID=E.CID LEFT JOIN (SELECT CID,convert(sum(case when score >=90 then 1 else 0 end)/COUNT(*)*100, decimal(5,2))优秀率 FROM SC GROUP BY CID)F on A.CID=F.CID;
19、查询出只选修两门课程的学生学号和姓名
select a.sid,a.sname from student a join (select sid from sc group by sid having count(*)=2) b on a.sid=b.sid;
20、查询男生、女生人数
select ssex,count(*)cnt from student group by ssex;
21、查询名字中含有「风」字的学生信息
select * from student where sname like '%风%';
22、查询同名同性别的学生名单，并统计这些人数
select sname,count(*)人数 from student group by sname,ssex having 人数>1;
23、查询 1990 年出生的学生名单
SELECT * FROM Student WHERE year(Sage)=1990;
NOW()函数 NOW()函数返回当前日期和时间。例如： SELECT NOW();
YEAR()函数 YEAR()函数返回日期或时间的年份。例如： SELECT YEAR('2022-01-01');
MONTH()函数 MONTH()函数返回日期或时间的月份。例如： SELECT MONTH('2022-01-01');
DAY()函数 DAY()函数返回日期或时间的日份。例如： SELECT DAY('2022-01-01');
DATE()函数 DATE()函数返回日期或时间的日期部分。例如： SELECT DATE('2022-01-01 15:10:15');
TIME()函数 TIME()函数返回时间的时间部分。例如： SELECT TIME('2022-01-01 15:10:15');
24、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid,avg(score)平均成绩
from sc2 a group by cid
order by 平均成绩 desc,cid;
先按平均成绩排序，再按课程编号升序
25、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select a.sid,a.sname,平均成绩 from student a join (select sid,avg(score)平均成绩 from sc group by sid) b on a.sid=b.sid where b.平均成绩>85;
26、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select a.sname,b.score from student a left join sc b on a.sid=b.sid left join course c on b.cid=c.cid where c.cname='数学'and b.score<60;
27、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select a.sid,a.sname,b.cid,c.cname,b.score from student a join sc b on a.sid=b.sid join course c on b.cid=c.cid order by a.sid,c.cid;
28、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select a.sname,c.cname,b.score from student a right join sc b on a.sid=b.sid left join course c on b.cid = c.cid where b.score>70;
29、查询不及格的课程学生姓名，课程名及分数
select a.sname,c.cname,b.score from student a
right join sc b on a.sid=b.sid left join course c on b.cid=c.cid where b.score<60;
30、查询课程编号为01且课程成绩在70分以上的学生的学号和姓名
select a.sid,a.sname,b.cid,b.score from student a right join sc b on a.sid=b.sid where b.cid=01 and b.score>70;
31、求每门课程的学生人数（假设每个学生都有参加考试且有成绩）
select a.cname,cnt from course a join (select cid,count(*)cnt from sc group by cid) b on a.cid=b.cid;
32、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
lect a.sname,b.score from student a join sc b on a.sid=b.sid join (select cid,max(score)max_score from sc group by cid having cid=(select cid from course c join teacher d on c.tid=d.tid where d.tname='张三')) e on b.cid=e.cid and b.score=e.max_score;
