【2019-2020春学期】数据库作业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]查询每一门课的间接先修课（即先修课的先修课）

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

[例 3. 53] 改写[例 3.49]

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM  Student  LEFT OUT 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;

[例 3.55] 查询与“刘晨”在同一个系学习的学生。
1.分布完成
① 确定“刘晨”所在系名

SELECT  Sdept  
                    FROM     Student                            
                    WHERE  Sname= ' 刘晨 ';

结果为： CS
② 查找所有在CS系学习的学生。

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

2.将第一步查询嵌入到第二步查询的条件中

SELECT Sno, Sname, Sdept
 FROM Student
WHERE Sdept  IN
              (SELECT Sdept
               FROM Student
               WHERE Sname= ' 刘晨 ');

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

SELECT Sno,Sname                         
FROM    Student                                  
WHERE Sno  IN
         (SELECT Sno                     
          FROM    SC                         
          WHERE  Cno IN
                 (SELECT Cno             
                   FROM Course           
                   WHERE 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 ' ;           

[例 3.59] 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
方法一：用ALL谓词

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 ';