Nested Query

---带有IN 谓词的子查询
---039查询与刘晨在同一个系的学生-----------------------------------------
 USE student_course
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(
    SELECT Sdept
    FROM Student
    WHERE Sname='刘晨'
);
USE student_course 
SELECT S1.Sno,S1.Sname,S1.Sdept
FROM S1.Student,S2.Student
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';
SELECT * FROM Student ;
---040查询选修了课程名为信息系统的学生学号和姓名----------------------------
SELECT Sno,Sname
FROM Student
WHERE Sno IN 
(
    SELECT Sno
    FROM SC 
    WHERE Cno IN 
    (
        SELECT Cno
        FROM Course
        WHERE Cname='信息系统'
    )
);
---采用复合查询--------------------------------------------------------
SELECT Student.Sno,Student.Sname
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname='信息系统';
/*
当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=
或< >）。
*/
---041找出每个学生超过他选修课程平均成绩的课程号----------------------------
SELECT Sno,Cno,Grade
FROM SC X
WHERE Grade>=
(
    SELECT AVG(Grade)
    FROM SC Y
    WHERE Y.Sno=X.Sno
);
SELECT * FROM SC;
SELECT SC.Sno,AVG (Grade)AS 平均分
FROM SC 
GROUP BY Sno;
/*
带有ANY、ALL 谓词的子查询
谓词语义ANY：任意一个值；ALL：所有值。需要配合使用比较运算符：
> ANY 大于子查询结果中的某个值
> ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值
>= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值
*/
---042查询其它系中比计算机科学某一学生年龄小的学生姓名和年龄------------------
SELECT Sname,Sage
FROM Student
WHERE Sdept<>'CS' AND Sage<ANY 
(
    SELECT Sage
    FROM Student
    WHERE Sdept='CS'
)
SELECT Sname,Sage
FROM Student 
WHERE Sdept<>'CS' AND Sage<
(
    SELECT MAX(Sage)
    FROM Student
    WHERE Sdept='CS'
);
SELECT * FROM Student ;
UPDATE Student
SET Sdept='IS'
WHERE Sno='200215125';
---[例] 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄-------------
USE Student_course;
SELECT Sname,Sage
FROM Student
WHERE Sdept<>'CS'
AND Sage<ALL
(
    SELECT  Sage
    FROM Student
    WHERE Sdept='CS'
)
SELECT  Sname,Sage
FROM  Student
WHERE Sdept<>'CS'
AND Sage<
(
    SELECT  MIN(Sage)
    FROM  Student
    WHERE Sdept='CS'
);
SELECT  * FROM  Student;
/*
带有EXISTS 谓词的子查询
1. EXISTS 谓词
EXISTS 谓词代表存在量词 £¬带有EXISTS 谓词的子查询不返回任何数据，
只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的
WHERE 子句返回真值；若内层查询结果为空，则外层的WHERE 子句返回假值。
由EXISTS 引出的子查询，其目标列表达式通常都用* ，因为带EXISTS 的
子查询只返回真值或假值，给出列名无实际意义。
2. NOT EXISTS 谓词
若内层查询结果非空，则外层的WHERE 子句返回假值
若内层查询结果为空，则外层的WHERE 子句返回真值
*/
---[例]查询所有选修了号课程的学生姓名。------------------------------------
SELECT Sname
FROM Student
WHERE EXISTS
(
    SELECT Sno --Sno 可以改为---
    FROM SC
    WHERE Cno='1' and Student.Sno=SC.Sno
);
SELECT * FROM  Student;
SELECT * FROM SC;
---[例]查询没有选修号课程的学生姓名。--------------------------------------
SELECT Sname
FROM  Student
WHERE NOT EXISTS
(
    SELECT Sno --Sno 可以改为*---
    FROM SC
    WHERE Cno='1' and Student.Sno=SC.Sno
);
SELECT * FROM Student;
SELECT * FROM SC;
---[例] 查询选修了全部课程的学生姓名。-------------------------------------
INSERT INTO SC VALUES('200215121','4',100)
INSERT INTO SC VALUES('200215121','5',90)
INSERT INTO SC VALUES('200215121','6',800)
INSERT INTO SC VALUES('200215121','7',70)
SELECT Sname
FROM Student
WHERE NOT  EXISTS
(
    SELECT *
    FROM Course
    WHERE NOT EXISTS
    (
        SELECT *
        FROM SC
        WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno
    )
);
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM SC;
---删除四条数据----
DELETE
FROM SC
WHERE Sno='200215121' AND Cno INT('4','5','6','7'); --保持原始记录不变----
---[例]查询至少选修了学生选修的全部课程的学生号码。--------------------------
---不存在这样的课程y，学生选修了y，而学生x 没有选
/*解题思路：用逻辑蕴函表达：查询学号为x 的学生，对所有的课程y，只要
200215122 学生选修了课程y，则x 也选修了y。*/
/*形式化表示：
用P 表示谓词“学生选修了课程y”
用q 表示谓词“学生x 选修了课程y”
则上述查询为: ( ∀ y) p→q
*/
/*等价变换：
p→q ⇔ ¬p ∨q
( ∀ y) (p→q) ⇔ ( ∀ y)( ¬p ∨q)
⇔ (( )( )) y p q ¬ ∃ ∧¬ 变换后语义：不存在这样的课程y，学生选修了y，而学生x 没有选。
*/
---不存在这样的课程y，学生选修了y，而学生x没有选-----------------------------
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(
    SELECT *
    FROM SC SCY
    WHERE Sno='200215122' AND NOT EXISTS
    (
        SELECT *
        FROM SC SCZ
        WHERE SCZ.Sno=SCX.sno
        AND SCZ.Cno=SCY.Cno
    )
);
SELECT * FROM  SC;