SQL语句练习50题-彻底教会你怎么写SQL的查询（一）

大家好，欢迎来到IT知识分享网。

写在前面

1. 题目我放在最后了，大家有需要的可能自行跳转查看。
2. 由于题目的数据量比较小，所以所有的代码均未考虑性能，如果各位有更加高效的答案欢迎补充。
3. 这种题目的写法是多样的，本人的答案不一定是最优的，但是我会尽力讲清楚我的思路，让你知道这种题目应该怎么做，让你在考场上能够作对这些题目（分值还挺大的，整整30分…）
4. 所有代码均在SQL Server Management Studio 19运行实现过，MySQL的语法可能会有些许不一样，注意判别。

先序知识

基本框架

查询的基本框架：

SELECT<要查询什么就写什么> FROM <从哪个表中查？> <join_type> JOIN <right_table> --join_type可以是什么左连接，右连接等等，默认是内连接 ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number> 

正文

1、查询”01″课程比”02″课程成绩高的学生的信息及课程分数

select s.*,sc1.s_score AS score_01,sc2.s_score AS score_02 from Student s join Score sc1 ON s.s_id = sc1.s_id and sc1.c_id = '01' join Score sc2 ON s.s_id = sc2.s_id and sc2.c_id = '02' where sc1.s_score >sc2.s_score; 

2、查询”01″课程比”02″课程成绩低的学生的信息及课程分数

如法炮制即可：

select s.*,sc1.s_score AS score_01,sc2.s_score AS score_02 from Student s join Score sc1 ON sc1.s_id=s.s_id and sc1.c_id = '01' join Score sc2 ON sc2.s_id=s.s_id and sc2.c_id = '02' where sc1.s_score<sc2.s_score 

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select s.s_id,s.s_name,AVG(sc1.s_score) as avg_score from Student s join Score sc1 on sc1.s_id=s.s_id group by s.s_name,s.s_id having AVG(sc1.s_score) >= 60; 

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

select s.s_id,s.s_name,COALESCE(AVG(sc1.s_score), 0) as avg_score from Student s left join Score sc1 ON sc1.s_id=s.s_id group by s.s_id,s.s_name having COALESCE(AVG(sc1.s_score), 0)<60; 

这里使用LEFT JOIN确保包括没有成绩的学生。COALESCE函数用于处理可能存在的没有成绩的学生，将其平均成绩视为 0 。

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select s1.s_id,s1.s_name,COUNT(sc1.c_id) AS totalCourse,SUM(sc1.s_score) AS sum_score from Student s1 left join Score sc1 ON s1.s_id=sc1.s_id group by s1.s_id,s1.s_name; 

通过左连接确保包括没有选课的同学，然后使用 GROUP BY 按照学生进行分组统计。

是不是稍微有点感觉了，就是一套流程，select，看从哪个表选哪个元素，然后看需不需要使用聚集元素（COUNT、SUM、AVG等），然后from哪个表的哪个实例，然后连接另一个表，然后group by之后看需不需要筛选，需要则再套一个having。

6、查询”李”姓老师的数量

select COUNT(t1.t_id) as totalTeacherLI from Teacher t1 where t1.t_name like '李%'; 

7、查询学过”张三”老师授课的同学的信息

select s1.* from Student s1 join Score sc1 ON sc1.s_id=s1.s_id join Course c1 ON c1.c_id=sc1.c_id join Teacher t1 ON c1.t_id=t1.t_id where t1.t_name='张三'; 

直接用where就行，不需要使用groupby 和 having

8、查询没学过”张三”老师授课的同学的信息

select s1.* from Student s1 where s1.s_id not in ( select sc1.s_id from Score sc1 join Course c1 ON c1.c_id=sc1.c_id join Teacher t1 On t1.t_id=c1.t_id where t1.t_name='张三'; ) 

这个确实没办法了，必须使用两层select，里面那层用来找到是张三老师教的学生，外面的用where not in。

9、查询学过编号为”01″并且也学过编号为”02″的课程的同学的信息

select s1.* from Student s1 join Score sc1 ON sc1.s_id=s1.s_id and sc1.c_id='01' join Score sc2 ON sc2.s_id=s1.s_id and sc2.c_id='02'; 

10、查询学过编号为”01″但是没有学过编号为”02″的课程的同学的信息

select s1.* from Student s1 join Score sc1 ON sc1.s_id=s1.s_id and sc1.c_id='01' where s1.s_id not in( select s_id from Score where c_id='02' ); 

11、查询没有学全所有课程的同学的信息

select s1.* from Student s1 join Score sc1 ON sc1.s_id=s1.s_id group by s1.s_id,s1.s_name,s1.s_sex,s1.s_birth having COUNT(sc1.c_id)<(select COUNT(*) from Course); 

还是使用的连接来做的，但是其实这样写虽然没有错但是不是特别清楚，还可以这样写：

SELECT S.* FROM Student S WHERE S.s_id IN ( SELECT s_id FROM ( SELECT s_id, COUNT(c_id) AS course_count FROM Score GROUP BY s_id ) AS Temp WHERE course_count < (SELECT COUNT(*) FROM Course) ); 

12、查询至少有一门课与学号为”01″的同学所学相同的同学的信息

select s1.* from Student s1 where s1.s_id in( select s_id from Score where c_id in( select c_id from Score where s_id ='01' ) and s_id <> '01' ); 

这个用了三层select，首先是挑选出学号为01的学生的选课，然后挑选选了这些选课的并且学号不是01的学生。

13、查询和”01″号的同学学习的课程完全相同的其他同学的信息

SELECT s1.* FROM Student s1 WHERE s_id in( SELECT s_id FROM score WHERE s_id!='01'AND c_id IN( SELECT c_id FROM score WHERE s_id='01') GROUP BY s_id HAVING COUNT(*) =(SELECT COUNT(*) FROM score WHERE s_id='01')) 

这个是借鉴的别人的，但是这个也只能保证选的数量是一致的，不能保证所有的选课信息都是一致的，完全一致可能需要用GROUP_CONCAT或者NOT EXISTS来写，想到更好了的话，到时候补充在评论区吧。

14、查询没学过”张三”老师讲授的任一门课程的学生姓名

select s1.s_name from Student s1 where s1.s_id not in( select sc1.s_id from Score sc1 join Course c1 ON c1.c_id=sc1.c_id join Teacher t1 ON t1.t_id=c1.t_id where t1.t_name='张三' ); 

两层，内层用来找到上过张三老师课程的学生的id，外层用来找不在里面的

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s1.s_id,s1.s_name,AVG(sc1.s_score) as avg_score from Student s1 join Score sc1 ON s1.s_id=sc1.s_id where sc1.s_score<60 group by s1.s_id,s1.s_name having COUNT(sc1.s_score)>=2; 

16、检索”01″课程分数小于60，按分数降序排列的学生信息

select s1.* from Student s1 join Score sc1 ON sc1.s_id=s1.s_id and c_id='01' where sc1.s_score<60 order by sc1.s_score DESC 

DESC代表降序，ASC是升序，默认也是升序

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s1.s_id,AVG(sc1.s_score) AS avg_score from Student s1 join Score sc1 ON s1.s_id=sc1.s_id group by s1.s_id order by avg_score DESC 

这是个比较基础的，join之后先groupby再orderby。要写进阶的需要把Course表也join进来然后SELECT S.s_id, C.c_name, Sc.s_score, AVG(Sc.s_score) OVER (PARTITION BY S.s_id) AS average_score

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90)

SELECT C.c_id, C.c_name, MAX(Sc.s_score) AS max_score, MIN(Sc.s_score) AS min_score, AVG(Sc.s_score) AS average_score, SUM(CASE WHEN Sc.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS pass_rate, SUM(CASE WHEN Sc.s_score >= 70 AND Sc.s_score <= 80 THEN 1 ELSE 0 END) / COUNT(*) AS medium_rate, SUM(CASE WHEN Sc.s_score >= 80 AND Sc.s_score <= 90 THEN 1 ELSE 0 END) / COUNT(*) AS good_rate, SUM(CASE WHEN Sc.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS excellent_rate FROM Course C JOIN Score Sc ON C.c_id = Sc.c_id GROUP BY C.c_id, C.c_name; 

其实就是复杂，并不是很难，注意就是这里只用sc表和course表即可，不需要student表了

19、按各科成绩进行排序，并显示排名(实现不完全)

SELECT c_id, s_score, RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS rank FROM Score; 

稍微难了点，考试应该不会考

20、查询学生的总成绩并进行排名

求总成绩：

select sc1.s_id,SUM(sc1.s_score) AS total_score from Score sc1 group by sc1.s_id 

带排名：

SELECT s_id, DENSE_RANK() OVER (ORDER BY SUM(s_score) DESC) AS rank, SUM(s_score) AS total_score FROM Score GROUP BY s_id; 

21、查询不同老师所教不同课程平均分从高到低显示

select t1.t_name,c1.c_name,AVG(sc1.s_score) AS avg_score from Score sc1 join Course c1 ON c1.c_id=sc1.c_id join Teacher t1 ON c1.t_id=t1.t_id group by t1.t_name,c1.c_name ORDER BY avg_score DESC; 

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select s1.*,sc1.s_score from Student s1 join Score sc1 ON s1.s_id=sc1.s_id where ( select COUNT(*) from Score sc2 where sc2.c_id = sc1.c_id and sc2.s_score>sc1.s_score ) BETWEEN 1 AND 2 

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select Course.c_name, sc.c_id, sum(case when sc.s_score<=100 and sc.s_score>85 then 1 else 0 end) as "[100-85]", sum(case when sc.s_score<=85 and sc.s_score>70 then 1 else 0 end) as "[85-70]", sum(case when sc.s_score<=70 and sc.s_score>60 then 1 else 0 end) as "[70-60]", sum(case when sc.s_score<=60 and sc.s_score>0 then 1 else 0 end) as "[60-0]" from Score sc left join Course on sc.c_id = Course.c_id group by sc.c_id,Course.c_name; 

24、查询学生平均成绩及其名次

SELECT s_id, AVG(s_score) AS average_score, RANK() OVER (ORDER BY AVG(s_score) DESC) AS rank FROM Score GROUP BY s_id; 

25、查询各科成绩前三名的记录

SELECT sc1.c_id, sc1.s_id, sc1.s_score FROM Score sc1 WHERE ( SELECT COUNT(*) FROM Score sc2 WHERE sc2.c_id = sc1.c_id AND sc2.s_score > sc1.s_score ) < 3 ORDER BY sc1.c_id, sc1.s_score DESC; 

题目

建表

--建表 --学生表 CREATE TABLE Student( s_id VARCHAR(20), s_name VARCHAR(20) NOT NULL DEFAULT '', s_birth VARCHAR(20) NOT NULL DEFAULT '', s_sex VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(s_id) ); --课程表 CREATE TABLE Course( c_id VARCHAR(20), c_name VARCHAR(20) NOT NULL DEFAULT '', t_id VARCHAR(20) NOT NULL, PRIMARY KEY(c_id) ); --教师表 CREATE TABLE Teacher( t_id VARCHAR(20), t_name VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(t_id) ); --成绩表 CREATE TABLE Score( s_id VARCHAR(20), c_id VARCHAR(20), s_score INT, PRIMARY KEY(s_id,c_id) ); 

插入的数据

--建表 --学生表 CREATE TABLE Student( s_id VARCHAR(20), s_name VARCHAR(20) NOT NULL DEFAULT '', s_birth VARCHAR(20) NOT NULL DEFAULT '', s_sex VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(s_id) ); --课程表 CREATE TABLE Course( c_id VARCHAR(20), c_name VARCHAR(20) NOT NULL DEFAULT '', t_id VARCHAR(20) NOT NULL, PRIMARY KEY(c_id) ); --教师表 CREATE TABLE Teacher( t_id VARCHAR(20), t_name VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(t_id) ); --成绩表 CREATE TABLE Score( s_id VARCHAR(20), c_id VARCHAR(20), s_score INT, PRIMARY KEY(s_id,c_id) ); 

题目

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数  -- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的) -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 -- 6、查询"李"姓老师的数量  -- 7、查询学过"张三"老师授课的同学的信息  -- 8、查询没学过"张三"老师授课的同学的信息  -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 -- 11、查询没有学全所有课程的同学的信息  -- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息  -- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息  -- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 -- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩  -- 16、检索"01"课程分数小于60，按分数降序排列的学生信息 -- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 -- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 --及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 -- 19、按各科成绩进行排序，并显示排名(实现不完全) -- 20、查询学生的总成绩并进行排名 -- 21、查询不同老师所教不同课程平均分从高到低显示  -- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 -- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 -- 24、查询学生平均成绩及其名次  -- 25、查询各科成绩前三名的记录