经典练习-SQL-经典题目（1）

本文没有全部收录网上博客的题目，放了一些个人觉得有价值的题目，建议使用的时候反查题目，数据及字段来源：https://blog.csdn.net/weixin_44167164/article/details/85090602

题目：

1、查询Score表中成绩在60到80之间的所有记录。
查询表中某范围的记录
解法1：操作符 between and
解法2：操作符 >= and <=

SELECT * FROM score WHERE s_s_score BETWEEN 60 and 80;

2、查询Score表中成绩为85，86或88的记录。
查询表中某些个特定值或者某些个特定条件的记录
解法：操作符 in（特定条件1、特定条件2、特定条件3…）

SELECT * FROM score WHERE s_s_score IN(85,86,88);

3、查询Score表中的最高分的学生学号和课程号
查询表中最大最小记录
解法1：order by …desc/asc +limit 只能输出一条记录
解法2：聚合函数+子查询

#解法一
SELECT s_id,c_id FROM score ORDER BY s_s_score DESC LIMIT 1; 
#解法二
SELECT s_id,c_id FROM score WHERE s_s_score=(SELECT MAX(s_s_score) FROM score);

4、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
有时候，题干读得晕乎乎的，需要进行题干翻译和分布：
查询score表中至少有5名学生选修的（课程），并以3开头的课程的平均分数
查询的字段：平均分数——>AVG(s_s_score)
表：score
条件：至少5名学生选修课程——>COUNT(s_id)>5
以3开头的课程的——>WHERE c_id LIKE’3%’

SELECT c_id,AVG(s_s_score) FROM score WHERE c_id LIKE'3%' GROUP BY c_id HAVING COUNT(s_id)>5;

5、假设使用如下命令建立了一个grade表

#建表
create table grade
(
low  int(3),
upp  int(3),
rank  char(1)
);

#插数据
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');

现查询所有同学的Sno、Cno和rank列。

SELECT s.s_id,s.c_id,g.rank FROM score s,grade g WHERE s.s_s_score BETWEEN g.low AND g.upp ORDER BY rank;

这个题说两点：
（1）rank是一个SQL查询语句里的关键词，题干插入表用rank命名是错误的方式。
（2）这种排等级的语句，形式新颖，值得标记

6、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
子查询和自联结
解法一（子查询）
表：score
字段：score.*
条件：（1）课程号为‘3-105’ 的所有学生记录
（2）课程号为‘3-105’且学生号为‘109’的成绩
步骤：1、搜索出3-105课程号的所有记录。2、搜索出3-105课程号且109学号的学生成绩。3、做比较。

SELECT a.* 
FROM score s 
WHERE s.c_id='3-105' AND s.s_s_score>ALL(SELECT s_s_score 
                                         FROM score  
                                         WHERE s_id=109 AND c_id='3-105');

解法二（自联结-虽然只涉及一个表，但想象成本表和副本相互关联，在性能上和可读性上也更容易理解）
表：score s，score sc
字段：s.*
条件：（1）在表s中，查询课程号为‘3-105’ 的所有学生记录
（2）在表sc中，查询课程号为‘3-105’且学生号为‘109’的成绩
步骤：1、搜索出3-105课程号的所有记录。2、搜索出3-105课程号且109学号的学生成绩。3、做比较。

SELECT a.* 
FROM score s
JOIN score sc
ON s.c_id=sc.c_id 
where s.c_id='3-105' AND s.s_s_score>sc.s_s_score AND sc.s_id='109';

7、查询score中选学多门课程的同学中各科分数为非最高分成绩的记录
翻译成自己学过的知识点，等价代换，逆向思维
（1）把题干中不懂的问题进行自然语言翻译：
选多门课程=等价代换为：选课数目大于1
非最高分成绩=等价代换为：小于最高分
（2）把自然语言翻译成SQL语言：
选学多门课程=选课数目大于1=不止一门=‘count（s_id）>1’
非最高分=小于最高分=‘max(degree)>’

SELECT *
FROM score a   
WHERE 
s_id IN (SELECT s_id FROM score GROUP BY  s_id HAVING count(s_id)>1)
AND
a.s_s_score <(SELECT MAX(b.s_s_score) FROM score b WHERE a.s_id=b.s_id) ；

8、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
这个题，题干很有意思。
有歧义，对应的SQL也不太一样：
（1）以计算机系为准，查询那个与电子工程系不同的 #只有计算机系

SELECT tname,prof 
FROM teacher 
WHERE depart='计算机系' AND prof NOT IN 
(SELECT prof FROM teacher WHERE depart='电子工程系');

（2）查出两个系中职称不同的 #包含两个系的内容

解法一：
SELECT * 
FROM teacher 
WHERE prof NOT IN(SELECT prof FROM teacher WHERE depart ='计算机系' 
AND prof IN(SELECT prof FROM teacher WHERE depart ='电子工程系'))；

解法二：
SELECT Tname,Prof
FROM Teacher 
WHERE prof IN(SELECT prof FROM Teacher WHERE Depart ='计算机系' 
AND prof NOT IN (SELECT prof FROM Teacher WHERE Depart = '电子工程系'))
UNION
SELECT Tname,Prof 
FROM Teacher 
WHERE prof IN(SELECT prof FROM Teacher WHERE Depart ='电子工程系' 
AND prof NOT IN (SELECT prof FROM Teacher WHERE Depart = '计算机系'));

9、查询成绩比该课程平均成绩低的同学的成绩表。
子查询：

SELECT * 
FROM score a 
WHERE s_s_score<(SELECT AVG(s_s_score) FROM score b WHERE a.c_id=b.c_id);

自联结：

SELECT A.* 
FROM SCORE A 
INNER JOIN 
(SELECT c_id,AVG(s_s_score) AVG FROM SCORE GROUP BY c_id) B----->这是一个视图
ON A.c_id=B.c_id 
WHERE s_s_score<AVG;

10、查询所有未讲课的教师的name和depart
逻辑：讲课的教师有cno（课程编号），tname，depart。未讲课的教师没有cno,但有tname，depart。
搜索条件：在teacher表里有的tno，但在course里tno没有的

select t.tname，t.depart
from teacher t
where tno not in (select tno from course)；