数据库作业7：SQL练习4 - SELECT（连接查询、嵌套查询）

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

SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;

当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。
【例3.50】对例3.49用自然连接完成。

 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
 FROM Student,SC
 WHERE Student.Sno = SC.Sno;

一条SQL语句可同时完成选择和连接查询。
【例 3.51】查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

自身连接
连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。
【例3.52】查询每一门课的间接先修课（即先修课的先修课）
要为Course表取两个别名，一个是FIRST，另一个是SECOND。

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

为了实现题目的效果，对课程表进行了一些调整。

查询结果：

【例3.53】

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);

左外连接：列出左边关系中所有的元组 。
右外连接：列出右边关系中所有的元组 。

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

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

嵌套查询
类似于双重for循环。
【例3.55】查询与“刘晨”在同一个系学习的学生。

SELECT Sdept  
FROM Student                            
WHERE Sname='刘晨'; 
                         
SELECT Sno,Sname,Sdept 
FROM Student                 
WHERE Sdept='CS';

【例3.56】查询选修了课程名为“信息系统”的学生学号和姓名。

SELECT Sno,Sname
FROM Student
WHERE Sno IN
         (SELECT Sno
          FROM SC
          WHERE Cno IN
                 (SELECT Cno
                  FROM Course
                  WHERE Cname= '信息系统'
                  )
          );

本查询同样可以使用连接查询实现：

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname='信息系统';

【例3.57】找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade) 
	  		  FROM SC y
      		  WHERE y.Sno=x.Sno);

【例3.58】查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
               FROM Student
               WHERE Sdept='CS')
 AND Sdept<>'CS';   

本查询也可以用聚集函数实现

 SELECT Sname,Sage
 FROM Student
 WHERE Sage<(SELECT MAX(Sage)
             FROM Student
             WHERE Sdept= 'CS ')
   AND Sdept<>'CS';

【例3.59】查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage < ALL(SELECT Sage
                     FROM Student
                     WHERE Sdept='CS')
AND Sdept<>'CS';

聚集函数实现：

SELECT Sname,Sage
FROM Student
WHERE Sage < (SELECT MIN(Sage)
                      FROM Student
                       WHERE Sdept='CS')
AND Sdept<>'CS';

上面几个例子说明，查询的方式不止一种，但是不同方法的代码量与资源消耗不同，根据需要自行选择。