数据库原理与应用（SQL Server）练习题 - 26 -
通过子查询实现：——IN运算符
SELECT st_id, st_nm, st_sex FROM student WHERE LEFT(st_dpid,6) IN
( SELECT Left(st_dpid,6) FROM student WHERE st_nm='东方红' )
ORDER BY st_nm DESC
通过自连接查询实现：——JOIN
SELECT s1.st_id, s1.st_nm, s1.st_sex FROM student s1 JOIN student s2
ON LEFT(s1.st_dpid,6) = LEFT(s2.st_dpid,6) WHERE s2.st_nm='东方红' ORDER BY s2.st_nm
操作10.5：查询其它班比070511班某一学生的1002号课程成绩高的学生信息（ANY/ALL）
SELECT * FROM slt_couse WHERE score > ANY
( SELECT score FROM slt_couse
WHERE cs_id = '1002' AND LEFT(st_id,6)='070511' ) AND LEFT(st_id,6) <> '070511' AND cs_id = '1002' 操作10.6：查询其它班比070511班任一学生的1002号课程成绩高的学生信息（ANY/ALL）
SELECT * FROM slt_couse WHERE score > ALL
数据库原理与应用（SQL Server）练习题 - 27 -
( SELECT score FROM slt_couse
WHERE cs_id = '1002' AND LEFT(st_id,6)='070511' ) AND LEFT(st_id,6) <> '070511' AND cs_id = '1002' 操作10.7：查询大于等于60分且且比1003课程平均成绩低的学生课程信息（Between?And）
SELECT * FROM slt_couse a WHERE a.score BETWEEN 60 AND
( SELECT AVG(b.score) FROM slt_couse b WHERE b.cs_id='1003' )
操作10.8：查询系主任为“赵虎”的系的所有学生信息 通过子查询实现：——IN运算符 SELECT * FROM student WHERE EXISTS
( SELECT * FROM dept
WHERE st_dpid = dp_id AND dp_drt='赵虎' ) 通过子查询实现：——=运算符 SELECT * FROM student WHERE st_dpid =
( SELECT dp_id FROM dept WHERE dp_drt='赵虎' )
实验11 数据查询（7）——数据更新与子查询
操作11.1：将070511班所有学生信息插入到表
数据库原理与应用（SQL Server）练习题 - 28 -
student01(st_id,st_nm,st_sex)
INSERT INTO student01
SELECT st_id,st_nm,st_sex FROM student WHERE LEFT(st_id,6)='070511'
操作11.2：生成1002号课程的成绩单student02(st_id,st_nm, score)
INSERT INTO student02
SELECT a.st_id,st_nm,score FROM student a, slt_couse b WHERE a.st_id=b.st_id AND cs_id='1002'
操作11.3：将有不及格成绩的学生的st_mnt值更改为3 UPDATE student SET st_mnt=3 WHERE st_id IN
( SELECT DISTINCT st_id FROM slt_couse WHERE score < 60 )
操作11.4：将没有被选修的课程的学分更改为0 UPDATE couse SET cs_sc=0 WHERE cs_id NOT IN
( SELECT DISTINCT cs_id FROM slt_couse ) 操作11.5：删除5系学生的选课信息 DELETE FROM slt_couse WHERE st_id IN
( SELECT st_id FROM student WHERE st_dpid = 5 ) 操作11.6：删除学分为0的选课信息
数据库原理与应用（SQL Server）练习题 - 29 -
DELETE FROM slt_couse WHERE cs_id IN
( SELECT DISTINCT cs_id FROM slt_couse WHERE cs_id=0 )
实验12 事务和锁
实验13 索引
实验14 视图
实验15 游标
实验16 存储过程
实验17 自定义函数
实验18 触发器