数据库原理及应用实验指导书
(Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ); go
/*表Student的主码为Sno，属性列Sname取唯一值*/ create table Course
(Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); go
/*表Course的主码为Cno，属性列Cpno(先修课)为外码，被参照表为Course，被参照列是Cno*/ create table SC (Sno CHAR(9), Cno CHAR(4), Grade SMALLINT,
primary key (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ); go
/*表SC的主码为(Sno, Cno), Sno和Cno均为外码，被参照表分别为Student和Course，被参照列分别为Student.Sno和Course.Cno*/
insert into student values('200215121','李勇','男',20,'CS'); insert into student values('200215122','刘晨','女',19,'CS'); insert into student values('200215123','王敏','女',18,'MA'); insert into student values('200215125','张立','男',19,'IS'); go
/*为表Student添加数据*/
insert into course values('1', '数据库', NULL,4); insert into course values('2', '数学', NULL,2); insert into course values('3', '信息系统', NULL,4); insert into course values('4', '操作系统', NULL,3); insert into course values('5', '数据结构', NULL,4); insert into course values('6', '数据处理', NULL, 2); insert into course values('7', 'PASCAL语言', NULL,4); go
update Course set Cpno = '5' where Cno = '1'; update Course set Cpno = '1' where Cno = '3'; update Course set Cpno = '6' where Cno = '4'; update Course set Cpno = '7' where Cno = '5'; update Course set Cpno = '6' where Cno = '7'; /*为表Course添加数据*/ go
insert into SC values('200215121', '1',92); insert into SC values('200215121', '2',85); insert into SC values('200215121', '3',88);
11
数据库原理及应用实验指导书
insert into SC values('200215122', '2',90); insert into SC values('200215122', '3',80); /*为表SC添加数据*/ go
也可以将上述SQL语句序列预先保存在S_T.sql文件中，在SSMS中打开并执行该文件中的sql语句序列。
4.2 对学生关系Student、课程关系Course和选修关系SC进行多表查询
4.2.1 基本练习
（1）等值连接查询与自然连接查询
例如：查询每个学生及其选修课的情况。
SELECT Student.*, SC.* FROM Student, SC
WHERE Student.Sno = SC.Sno; /* 一般等值连接 */ 又如：查询每个学生及其选修课的情况（去掉重复列）。
SELECT Student.Sno, Sname, Ssex, Sage, Cno, Grade FROM Student, SC
WHERE Student.Sno = SC.Sno; /* 自然连接--特殊的等值连接 */ （2）自身连接查询
例如：查询每一门课的间接先修课。
SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno; （3）外连接查询
例如：查询每个学生及其选修课的情况（要求输出所有学生--含未选修课程的学生的情况）SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student LEFT OUTER JOIN SC ON(Student.Sno = SC.Sno); （4）复合条件连接查询
例如：查询选修了2号课程而且成绩在90以上的所有学生的学号和姓名。
SELECT Student.Sno, Sname FROM Student, SC
WHERE Student.Sno = SC.Sno AND
SC.Cno = ‘2' AND SC.Grade >= 90;
又如：查询每个学生的学号、姓名、选修的课程名及成绩。
SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno; （5）嵌套查询（带有IN谓词的子查询）
例如：查询与“刘晨”在同一个系学习的学生的学号、姓名和所在系。
SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN
(SELECT Sdept FROM Student
WHERE Sname = '刘晨'); /* 解法一*/
12
数据库原理及应用实验指导书
可以将本查询中的IN谓词用比较运算符‘=’来代替：
SELECT Sno, Sname, Sdept FROM Student WHERE Sdept =
(SELECT Sdept FROM Student
WHERE Sname = '刘晨'); /* 解法二*/ 也可以使用自身连接完成以上查询：
SELECT s1.Sno, s1.Sname, s1.Sdept FROM Student s1, Student s2 WHERE s1.Sdept = S2.Sdept AND
s2.Sname = '刘晨'; /* 解法三*/ 还可以使用EXISTS谓词完成本查询：
SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS (SELECT *
FROM Student S2
WHERE S2.Sdept=S1.Sdept AND S2.Sname='刘晨'); /* 解法四*/
又如：查询选修了课程名为“信息系统”的学生号和姓名。
SELECT Sno, Sname FROM Student WHERE Sno IN
(SELECT Sno FROM SC
WHERE Cno IN
(SELECT Cno FROM Course
WHERE Cname = '信息系统' ) );
也可以使用连接查询来完成上述查询：
SELECT Student.Sno, Sname FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = '信息系统';
（6）嵌套查询（带有比较运算符的子查询）
例如：找出每个学生超过他所选修课程平均成绩的课程号。
SELECT Sno, Cno FROM SC x
WHERE Grade >= ( SELECT AVG(Grade) FROM SC y
13
数据库原理及应用实验指导书
WHERE y.Sno = x.Sno); （7）嵌套查询（带有ANY或ALL谓词的子查询）
例如：查询其他系中比计算机系某个学生年龄小的学生的姓名和年龄。
SELECT Sname, Sage FROM Student
WHERE Sage
WHERE Sdept = 'CS') AND Sdept <> 'CS';
本查询也可以使用聚集函数来实现：
SELECT Sname, Sage FROM Student
WHERE Sage < (SELECT MAX(Sage) FROM Student
WHERE Sdept = 'CS') AND Sdept <> 'CS';
又如：查询其他系中比计算机系所有学生年龄都小的学生的姓名和年龄。
SELECT Sname, Sage FROM Student
WHERE Sage
WHERE Sdept = 'CS') AND Sdept <> 'CS'; 也可以使用聚集函数来实现：
SELECT Sname, Sage FROM Student
WHERE Sage < (SELECT MIN(Sage) FROM Student
WHERE Sdept = 'CS') AND Sdept <> 'CS';
（8）嵌套查询（带有EXISTS谓词的子查询） 例如：查询所有选修了1号课程的学生姓名。
SELECT Sname FROM Student WHERE EXISTS
(SELECT * FROM SC
WHERE Sno=Student.Sno AND Cno='1');
又如：查询所有未选修1号课程的学生姓名。
SELECT Sname FROM Student WHERE NOT EXISTS
(SELECT * FROM SC
14
数据库原理及应用实验指导书
WHERE Sno=Student.Sno AND Cno='1');
可以使用带有EXISTS谓词的子查询实现全称量词或蕴涵逻辑运算功能： 例如：查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course
WHERE NOT EXISTS (SELECT * FROM SC
WHERE Sno=Student.Sno AND Cno=Course.Cno));
又如：查询至少选修了学生200215122选修的全部课程的学生号码。 SELECT DISTINCT Sno FROM SC SCX
WHERE NOT EXISTS (SELECT * FROM SC SCY
WHERE SCY.Sno='200215122' AND NOT EXISTS (SELECT * FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)); （9）集合查询
例如：查询计算机系的学生以及年龄不大于19岁的的学生。
SELECT * FROM Student WHERE Sdept='CS'
UNION /*并集运算*/ SELECT * FROM Student WHERE Sage<=19;
可以改用多重条件查询：
SELECT * FROM Student
WHERE Sdept='CS' OR Sage<=19;
又如：查询既选修了课程1又选修了课程2的学生（交集运算）。 SELECT Sno FROM SC
WHERE Cno='1'
INTERSECT /*交集运算*/ SELECT Sno
15