文章目录
首先我们来康康表：
teachers:
+------+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday | prof | depart |
+------+-------+------+---------------------+--------+------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
+------+-------+------+---------------------+--------+------------+
students:
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
cources:
+-------+------------+-----+
| cno | cname | tno |
+-------+------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数据电路 | 856 |
| 9-888 | 高等数学 | 100 |
+-------+------------+-----+
scores:
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86.0 |
| 105 | 3-245 | 75.0 |
| 109 | 3-245 | 68.0 |
| 103 | 3-105 | 92.0 |
| 105 | 3-105 | 88.0 |
| 109 | 3-105 | 76.0 |
| 101 | 3-105 | 64.0 |
| 107 | 3-105 | 91.0 |
| 108 | 3-105 | 78.0 |
| 101 | 6-166 | 85.0 |
| 107 | 6-106 | 79.0 |
| 108 | 6-166 | 81.0 |
+-----+-------+--------+
题目：
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、查询“95033”班所选课程的平均分。
18、假设使用如下命令建立了一个grade表：
create table grade(low number(3,0),upp number(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’);
commit;
现查询所有同学的Sno、Cno和rank列。
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
23、查询“张旭“教师任课的学生成绩。
24、查询选修某课程的同学人数多于5人的教师姓名。
25、查询95033班和95031班全体学生的记录。
26、查询存在有85分以上成绩的课程Cno.
27、查询出“计算机系“教师所教课程的成绩表。
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
31、查询所有教师和同学的name、sex和birthday.
32、查询所有“女”教师和“女”同学的name、sex和birthday.
33、查询成绩比该课程平均成绩低的同学的成绩表。
34、查询所有任课教师的Tname和Depart.
35 查询所有未讲课的教师的Tname和Depart.
36、查询至少有2名男生的班号。
37、查询Student表中不姓“王”的同学记录。
38、查询Student表中每个学生的姓名和年龄。
39、查询Student表中最大和最小的Sbirthday日期值。
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
41、查询“男”教师及其所上的课程。
42、查询最高分同学的Sno、Cno和Degree列。
43、查询和“李军”同性别的所有同学的Sname.
44、查询和“李军”同性别并同班的同学Sname.
45、查询所有选修“计算机导论”课程的“男”同学的成绩表
开始做题
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
方法一
这个就开始操作了，我们想想这个逻辑哈，
要选的是啥？同学的记录。
这个同学的记录有啥特点？
- 课程是3-105
- 成绩高于选择3-105课程、学号109号的同学
那么我们可以写一个基础的逻辑
SELECT *
FROM scores
WHERE
sno='3-105' AND degree > 109号选择3-105课程的同学的成绩
ORDER BY ······
那么现在的问题是，进入子选择流程，选择109号同学的成绩
怎么选？这个就简单了
SELECT degree
FROM scores
WHERE sno='109' AND cno='3-105'
合起来
SELECT *
FROM scores
WHERE
sno='3-105' AND degree > (
SELECT degree
FROM scores
WHERE sno='109' AND cno='3-105'
)
ORDER BY ······
如果我们再深入考虑一下，它要的是学生的记录，我们要不要再跟学生表联系到一起？说干就干
SELECT *
FROM students INNER JOIN
(SELECT *
FROM scores
WHERE cno = '3-105'
AND degree > (SELECT degree FROM scores
WHERE sno = '109' AND cno = '3-105')) AS s2
ON (students.sno=s2.sno)
ORDER BY students.sno;
看看结果
方法二
大量使用as语句
SELECT s1.Sno,s1.Degree
FROM Scores AS s1 INNER JOIN Scores AS s2
ON(s1.Cno=s2.Cno AND s1.Degree>s2.Degree)
WHERE s1.Cno='3-105' AND s2.Sno='109'
ORDER BY s1.Sno;
跟上图对比，确实一致，不过是没有具体学生的信息。
*20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
恶心是真滴恶心，读着就拗口。
错误答案一
说实话我刚一开始理解错含义了，以为这个是要找到选择一门以上课程的同学，然后找到所有同学成绩的最高分，把所有选择一门以上的同学的低于最高分的成绩都弄出来。所以我写的是这个dio样：
select *
from scores
where sno in
(select sno
from scores
group by sno
having count(cno) > 1)
and degree < (select max(degree)
from scores);
where后面的两个条件
#寻找所有课程数量多于1的学号
sno in
(select sno
from scores
group by sno
having count(cno) > 1)
#并且成绩低于所有人的最高分
degree < (select max(degree)
from scores)
最后就去掉了一个考92分的那个，我就意识到事情有些不对劲
开始参照网上的答案
正如我在（一）开头说的，本文里的题目是从这篇文章里面获得的，我看了一下这个答案
SELECT *
FROM Scores
GROUP BY Sno
HAVING COUNT(cno)>1 AND Degree!=MAX(Degree);
额，这个好像跟我的套路一样，但是他这个报错，感觉这个答案有点忒老了😓
错误答案二
后来我忘了从哪弄到一个答案
select *
from scores
where sno in
(select sno
from scores
group by sno
having count(cno) > 1)
and degree not in (select max(degree)
from scores
group by sno);
结果如下：
感觉没问题啊，但是总觉得哪里怪怪的，我们先来分析一下哈
他的答案跟我的差别就在第二个限制条件里
degree not in (select max(degree)
from scores
group by sno);
利用学号，把成绩按照学号分成了几组，然后从这几组里选每个组最高的成绩
利用这个语句，我们选出来的就是每个学号的非最高的那个成绩——————————————————————————————————————————————————————吗？
Stop!，如果我们认为这个选择是对的，那我们就错了，再看语句
select *
from scores
where
#这个学生的学号对应的选课超过1
sno in
(select sno
from scores
group by sno
having count(cno) > 1)
and
#这个学生的成绩不在刚刚选出来的那几个成绩里
degree not in (select max(degree)
from scores
group by sno);
它只是粗鲁地规定了几个范围，如同这篇文章说的那样，它只是挑出来了几个选课比较多的少年，只要他的某个课程的分数不在86，75，68，64，78，79里面，就算成功了。
所以他错了。
正确答案一(白嫖的）
这个思路是啥呢，就是同时选两个表，一个表t2，就是scores表，一个是t1，所有选课数超过1的学生的学号和最大成绩的记录，然后用这两个表联系，t2的学号等于t1的学号就把学生限定成了选课数超过1的，t2.degree < t1.degree（t1的每个学号只有一个成绩，就是他的最大成绩）就把学生的最大成绩剔除了。
select t1.sno,t2.cno,t2.degree
from scores t2,(select sno,max(degree) as degree
from scores group by sno having count(sno)>1 )as t1
where t1.sno=t2.sno and t2.degree<t1.degree;
#原文链接：https://blog.csdn.net/yiyayiya777/article/details/79847296
select sno,max(degree) as degree
from scores group by sno having count(sno)>1
t1表
select t1.sno,t2.cno,t2.degree
from scores t2,(select sno,max(degree) as degree
from scores group by sno having count(sno)>1 )as t1
where t1.sno=t2.sno;
把所有的选课超过1的弄出来
select t1.sno,t2.cno,t2.degree
from scores t2,(select sno,max(degree) as degree
from scores group by sno having count(sno)>1 )as t1
where t1.sno=t2.sno and t2.degree<t1.degree;
加上最后一步限制条件，成功选出上个表中每个学号小于自己最高成绩的记录
正确答案二（题目来源的文章下面的评论）
SELECT scores.sno,cno,degree,md FROM scores INNER JOIN
(SELECT sno,MAX(degree) md FROM scores GROUP BY sno HAVING COUNT(*) > 1) max
on scores.sno = max.sno and degree < md ORDER BY sno;
#作者：yuexiaqiying
#博客地址：https://me.csdn.net/yuexiaqiying
题目来源的文章下面好多对20题的评论，大家可以看看去
—>指路
本来是想着两天做完这些题，因为老师布置了其中的一部分，现在看来是完不成了，下午就要上课了，我先再开个（三）把老师布置的作业统一做了😭，这个就先这样了
京公网安备 11010502036488号