实验4 SQL语言——SELECT查询操作
From student X
Where S.sno=X.sno and X.sno<>T.sno and
T.ssex IN
( Select ssex
From student Y
Where X.sno=Y.sno and Y.sno<>T.sno)));
[例15] 查询选修了课程名为“数据库系统”的学生学号、姓名和所在系。
SELECT Sno,Sname,Sdept FROM Student -- IN嵌套查询方法
WHERE Sno IN
( SELECT Sno FROM SC
WHERE Cno IN
( SELECT Cno FROM Course WHERE Cname='数据库系统'));
或 SELECT Sno,Sname,Sdept FROM Student -- IN、= 嵌套查询方法
WHERE Sno IN
( SELECT Sno FROM SC
WHERE Cno=
( SELECT Cno FROM Course WHERE Cname='数据库系统'));
或 SELECT Student.Sno,Sname,Sdept --连接查询方法
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND http://www.77cn.com.cno=http://www.77cn.com.cno
AND http://www.77cn.com.cname='数据库系统';
或 Select Sno,Sname,Sdept From Student -- Exists嵌套查询方法
Where Exists( Select * From SC
Where SC.Sno=Student.Sno And
Exists( Select * From Course
Where http://www.77cn.com.cno = http://www.77cn.com.cno And Cname='数据库系统'));
或 Select Sno,Sname,Sdept From Student -- Exists嵌套查询方法
Where Exists( Select * From course
Where Cname='数据库系统' and
Exists( Select * From SC
Where sc.sno=student.sno and http://www.77cn.com.cno = http://www.77cn.com.cno));
[例16] 检索至少不学2和4两门课程的学生学号与姓名。
SELECT Sno,Sname FROM Student
WHERE Sno NOT IN
(SELECT Sno FROM SC WHERE Cno IN ('2','4'));
[例17] 查询其他系中比信息系IS所有学生年龄均大的学生名单,并排序输出。 SELECT Sname FROM Student
WHERE Sage>All(SELECT Sage FROM Student
WHERE Sdept='IS') AND Sdept <> 'IS'
ORDER BY Sname;
本查询实际上也可以用集函数实现：
5