数据库原理与应用实验指导
[例11] 查询学生及其课程成绩与课程及其学生选修成绩的明细情况（要求学生与课程均需全部列出）。
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, http://www.77cn.com.cno, Grade, cname, cpno, ccredit
FROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno Full Outer join Course on http://www.77cn.com.cno=http://www.77cn.com.cno;
[例12] 查询性别为男、课程成绩及格的学生信息及课程号、成绩。
SELECT Student.*,Cno,Grade
FROM STUDENT INNER JOIN ON Student.Sno=SC.Sno
WHERE SSEX=’男’ AND GRADE >=60
[例13] 查询与“钱横”在同一个系学习的学生信息。
SELECT * FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='钱横');
或 SELECT * FROM Student
WHERE Sdept =
( SELECT Sdept
FROM Student
WHERE Sname='钱横'); -- 当子查询为单列单行值时可以用“=”
或 SELECT S1.*
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='钱横';
一般来说，连接查询可以替换大多数的嵌套子查询。
SQL-92支持“多列成员”的属于(IN)条件表达，例：
[例14] 找出同系、同年龄、同性别的学生。
Select * from Student as T
Where (T.sdept,T.sage,T.ssex) IN
(Select sdept,sage,ssex
From student as S
Where S.sno<>T.sno); -- SQL Server 2000可能不支持
它等价于逐个成员IN的方式表达，如下（能在SQL Server 2000中执行）：
Select * from Student T
Where T.sdept IN
( Select sdept
From student S
Where S.sno<>T.sno and
T.sage IN
( Select sage
4