2021-02-25 20:31 已编辑北京化工大学 Java

关注

数据库练习（二）

文章目录

首先我们来康康表：

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