Nested Query

1 -- -带有IN 谓词的子查询
2 -- -039查询与刘晨在同一个系的学生-----------------------------------------
3   USE student_course
4 SELECT Sno,Sname,Sdept
5 FROM Student
6 WHERE Sdept IN
7 (
8 SELECT Sdept
9 FROM Student
10 WHERE Sname = ' 刘晨 '
11 );
12 USE student_course
13 SELECT S1.Sno,S1.Sname,S1.Sdept
14 FROM S1.Student,S2.Student
15 WHERE S1.Sdept = S2.Sdept AND S2.Sname = ' 刘晨 ' ;
16 SELECT * FROM Student ;
17 -- -040查询选修了课程名为信息系统的学生学号和姓名----------------------------
18 SELECT Sno,Sname
19 FROM Student
20 WHERE Sno IN
21 (
22 SELECT Sno
23 FROM SC
24 WHERE Cno IN
25 (
26 SELECT Cno
27 FROM Course
28 WHERE Cname = ' 信息系统 '
29 )
30 );
31 -- -采用复合查询--------------------------------------------------------
32 SELECT Student.Sno,Student.Sname
33 FROM Student,Course,SC
34 WHERE Student.Sno = SC.Sno AND Course.Cno = SC.Cno AND Cname = ' 信息系统 ' ;
35 /*
36 当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=
37 或< >）。
38 */
39 -- -041找出每个学生超过他选修课程平均成绩的课程号----------------------------
40 SELECT Sno,Cno,Grade
41 FROM SC X
42 WHERE Grade >=
43 (
44 SELECT AVG (Grade)
45 FROM SC Y
46 WHERE Y.Sno = X.Sno
47 );
48 SELECT * FROM SC;
49 SELECT SC.Sno, AVG (Grade) AS 平均分
50 FROM SC
51 GROUP BY Sno;
52 /*
53 带有ANY、ALL 谓词的子查询
54 谓词语义ANY：任意一个值；ALL：所有值。需要配合使用比较运算符：
55 > ANY 大于子查询结果中的某个值
56 > ALL 大于子查询结果中的所有值
57 < ANY 小于子查询结果中的某个值
58 < ALL 小于子查询结果中的所有值
59 >= ANY 大于等于子查询结果中的某个值
60 >= ALL 大于等于子查询结果中的所有值
61 <= ANY 小于等于子查询结果中的某个值
62 <= ALL 小于等于子查询结果中的所有值
63 = ANY 等于子查询结果中的某个值
64 =ALL 等于子查询结果中的所有值（通常没有实际意义）
65 !=（或<>）ANY 不等于子查询结果中的某个值
66 !=（或<>）ALL 不等于子查询结果中的任何一个值
67 */
68 -- -042查询其它系中比计算机科学某一学生年龄小的学生姓名和年龄------------------
69 SELECT Sname,Sage
70 FROM Student
71 WHERE Sdept <> ' CS ' AND Sage <ANY
72 (
73 SELECT Sage
74 FROM Student
75 WHERE Sdept = ' CS '
76 )
77 SELECT Sname,Sage
78 FROM Student
79 WHERE Sdept <> ' CS ' AND Sage <
80 (
81 SELECT MAX (Sage)
82 FROM Student
83 WHERE Sdept = ' CS '
84 );
85 SELECT * FROM Student ;
86 UPDATE Student
87 SET Sdept = ' IS '
88 WHERE Sno = ' 200215125 ' ;
89 -- -[例] 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄-------------
90 USE Student_course;
91 SELECT Sname,Sage
92 FROM Student
93 WHERE Sdept <> ' CS '
94 AND Sage <ALL
95 (
96 SELECT Sage
97 FROM Student
98 WHERE Sdept = ' CS '
99 )
100 SELECT Sname,Sage
101 FROM Student
102 WHERE Sdept <> ' CS '
103 AND Sage <
104 (
105 SELECT MIN (Sage)
106 FROM Student
107 WHERE Sdept = ' CS '
108 );
109 SELECT * FROM Student;
110 /*
111 带有EXISTS 谓词的子查询
112 1. EXISTS 谓词
113 EXISTS 谓词代表存在量词 £¬带有EXISTS 谓词的子查询不返回任何数据，
114 只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的
115 WHERE 子句返回真值；若内层查询结果为空，则外层的WHERE 子句返回假值。
116 由EXISTS 引出的子查询，其目标列表达式通常都用* ，因为带EXISTS 的
117 子查询只返回真值或假值，给出列名无实际意义。
118 2. NOT EXISTS 谓词
119 若内层查询结果非空，则外层的WHERE 子句返回假值
120 若内层查询结果为空，则外层的WHERE 子句返回真值
121 */
122 -- -[例]查询所有选修了号课程的学生姓名。------------------------------------
123 SELECT Sname
124 FROM Student
125 WHERE EXISTS
126 (
127 SELECT Sno -- Sno 可以改为---
128 FROM SC
129 WHERE Cno = ' 1 ' and Student.Sno = SC.Sno
130 );
131 SELECT * FROM Student;
132 SELECT * FROM SC;
133 -- -[例]查询没有选修号课程的学生姓名。--------------------------------------
134 SELECT Sname
135 FROM Student
136 WHERE NOT EXISTS
137 (
138 SELECT Sno -- Sno 可以改为*---
139 FROM SC
140 WHERE Cno = ' 1 ' and Student.Sno = SC.Sno
141 );
142 SELECT * FROM Student;
143 SELECT * FROM SC;
144 -- -[例] 查询选修了全部课程的学生姓名。-------------------------------------
145 INSERT INTO SC VALUES ( ' 200215121 ' , ' 4 ' , 100 )
146 INSERT INTO SC VALUES ( ' 200215121 ' , ' 5 ' , 90 )
147 INSERT INTO SC VALUES ( ' 200215121 ' , ' 6 ' , 800 )
148 INSERT INTO SC VALUES ( ' 200215121 ' , ' 7 ' , 70 )
149 SELECT Sname
150 FROM Student
151 WHERE NOT EXISTS
152 (
153 SELECT *
154 FROM Course
155 WHERE NOT EXISTS
156 (
157 SELECT *
158 FROM SC
159 WHERE Student.Sno = SC.Sno AND Course.Cno = SC.Cno
160 )
161 );
162 SELECT * FROM Student;
163 SELECT * FROM Course;
164 SELECT * FROM SC;
165 -- -删除四条数据----
166 DELETE
167 FROM SC
168 WHERE Sno = ' 200215121 ' AND Cno INT ( ' 4 ' , ' 5 ' , ' 6 ' , ' 7 ' ); -- 保持原始记录不变----
169 -- -[例]查询至少选修了学生选修的全部课程的学生号码。--------------------------
170 -- -不存在这样的课程y，学生选修了y，而学生x 没有选
171 /* 解题思路：用逻辑蕴函表达：查询学号为x 的学生，对所有的课程y，只要
172 200215122 学生选修了课程y，则x 也选修了y。 */
173 /* 形式化表示：
174 用P 表示谓词“学生选修了课程y”
175 用q 表示谓词“学生x 选修了课程y”
176 则上述查询为: ( ∀ y) p→q
177 */
178 /* 等价变换：
179 p→q ⇔ ¬p ∨q
180 ( ∀ y) (p→q) ⇔ ( ∀ y)( ¬p ∨q)
181 ⇔ (( )( )) y p q ¬ ∃ ∧¬ 变换后语义：不存在这样的课程y，学生选修了y，而学生x 没有选。
182 */
183 -- -不存在这样的课程y，学生选修了y，而学生x没有选-----------------------------
184 SELECT DISTINCT Sno
185 FROM SC SCX
186 WHERE NOT EXISTS
187 (
188 SELECT *
189 FROM SC SCY
190 WHERE Sno = ' 200215122 ' AND NOT EXISTS
191 (
192 SELECT *
193 FROM SC SCZ
194 WHERE SCZ.Sno = SCX.sno
195 AND SCZ.Cno = SCY.Cno
196 )
197 );
198 SELECT * FROM SC;
199

 

转载于:https://www.cnblogs.com/FYSUIT/archive/2010/11/30/1891895.html