【2020-2021春学期】数据库作业6：连接查询例题练习

【例3.48】查询平均成绩大于等于80分的学生学号和平均成绩（接作业5）

select Sno,AVG(Grade)
from SC
group by Sno
having AVG(Grade)>=80;

--下面的是错误的使用：

--select Sno,AVG(Grade)
--from SC
--where AVG(Grade)>=80
--group by Sno

--会提示：聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

连接查询

1.等值与非等值连接查询

当连接运算符为=时，称为等值连接，使用其他运算符为非等值连接。

【例3.49】查询每个学生及其选修课程的情况

select Student.*,SC.*  --也可直接写为select *，对本题来说结果没有影响，都是把所有属性列全部列出来了
from Student,SC
where Student.Sno=SC.Sno; --如果去掉此语句则结果为两个表的笛卡尔积

背后的逻辑是双重循环，可以再SC表Sno上建立索引，避免全盘扫描SC表。
顺手复习一下索引的建立：create unique index SCno on SC(Sno ASC);

可以发现查询结果的行数与SC表的行数相同，因为选了课的学生才会出现在结果中。

【例3.50】对例3.49用自然连接完成

自然连接就是去掉重复列，在这里只能是手动去掉的（少select一列），因此实用性不高。

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade --Sno在两个表都出现了，因此要加前缀
from Student,SC
where Student.Sno=SC.Sno;

【例3.51】查询选修2号课程且成绩在80分以上的所有学生的学号姓名

select Student.Sno,Sname
from Student,SC
where Student.Sno=SC.Sno
	and SC.Cno='2'
	and SC.Grade>80;

更高效的执行过程是先从SC找出2号课且成绩大于80的元组组成一个中间关系，再和student连接。这个我觉得应该会用到嵌套查询，下节课学完再来补上。

2.自身连接

【例3.52】查询每一门课的间接先修课

select FIRST.Cno,SECOND.Cpno
from Course FIRST,Course SECOND
where FIRST.Cpno=SECOND.Cno;

另一种可以带空格的命名方式：

select [FIRST TABLE].Cno,SECOND.Cpno
from Course [FIRST TABLE],Course SECOND
where [FIRST TABLE].Cpno=SECOND.Cno;

在T-SQL里会把NULL也写出来，标准SQL会去掉相关行

3.外连接

【例3.53】列出所有同学的选课情况

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left outer join SC on(Student.Sno=SC.Sno); --内连接inner 右连接right 完全连接full

4.多表连接

【例3.54】查询每个学生的学号、姓名、选修课程和成绩

select Student.Sno,Sname,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno
	and SC.Cno=Course.Cno;