课本习题

对于简易教学管理数据库有如下3个基本表：S(SNO,SN,AGE,SEX)、SC(SNO,CNO,SCORE)、C(CNO,CN,TH)，其含义为SNO(学号),SN(姓名),AGE(年龄),SEX(性别)，SCORE(成绩)，CNO(课程号),CN(课程名),TH(教师名)。试用SQL语言表达如下查询及操作：
(1)检索年龄大于16岁的女学生的学号和姓名。
SELECT SNO,SN
FROM S
WHERE AGE>16 AND SEX='女';
(2)检索姓刘的学生选修的所有课程名与教师名。
方法一(联接查询方式):
SELECT CN,TH
FROM S,SC,C
WHERE S.SNO=SC.SNO AND O=O AND SN like '刘%';
方法二(嵌套查询方式)：
SELECT CN，TH
FROM C
WHERE CNO IN
(SELECT CNO
FROM SC
WHERE SNO IN
(SELECT SNO
FROM S
WHERE SN like '刘%'));

(3)检索没有选修数据库课程的学生的学号与姓名。
SELECT SNO,SN
FROM S
WHERE SNO NOT IN
(SELECT SNO
FROM S
WHERE SNO IN
(SELECT SNO
FROM SC
WHERE CNO IN
(SELECT CNO
FROM C
WHERE CN='数据库')));
或
SELECT SNO,SN
FROM S
WHERE SNO NOT IN
(SELECT SNO FROM S,SC,C WHERE S.SNO=SC.SNO AND O=O AND CN='数据库');
(4)检索至少选修两门课程的学生的学号与姓名。
方法一：SELECT DISTINCT X.SNO
FROM SC X
WHERE SNO IN
(SELECT SNO
FROM SC Y
WHERE X.SNO=Y.SNO AND O<>O);
方法二：SELECT DISTINCT X.SNO
FROM SC X,SC Y
WHERE X.SNO=Y.SNO AND O<>O);
方法三：SELECT SNO
FROM SC
GROUP BY SNO HAVING COUNT(*)>=2;
(5)检索选修课程包含姓张老师所授全部课程的学生的学号与姓名。
SELECT DISTINCT SX.SNO,SN
FROM SC X，S SX
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE TH LIKE '张%'
AND NOT EXISTS
(SELECT *
FROM SC Y
WHERE X.SNO=Y.SNO AND O=O)) AND X.SNO=SX.SNO ;
或
SELECT DISTINCT X.SNO,X.SN
FROM S X
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE TH LIKE '张%' AND NOT EXISTS
(SELECT *
FROM SC Y
WHERE X.SNO=Y.SNO AND O=O));

(6)把王非同学的学生信息及其选课情况等全部删除。
DELETE FROM SC
WHERE SNO =
(SELECT SNO
FROM S
WHERE SN='王非');
DELETE FROM S WHERE SN='王非';
(7)在课程表中添加一门新课程，其信息为：（'C8','信息系统概论','孙力'）。
INSERT INTO C VALUES('C8'，'信息系统概论'，'孙力')
(8)在选修关系表SC中添加所有学生对'C8'课程的选修关系记录，成绩暂定为60，请用一条命令完成本批量添加任务。
INSERT INTO SC(SNO,CNO,SCORE) SELECT SNO,'C8',60 FROM S
(9)把选“信息系统概论”课程的男学生的成绩暂全部初始化重新设置为0。
UPDATE SC
SET SCORE=0
WHERE CNO='C8' AND SNO IN
(SELECT SNO
FROM S
WHERE SEX= '男')
或
UPDATE SC
SET SCORE=0
WHERE CNO='C8' AND '男'=
(SE

LECT SEX
FROM S
WHERE SNO=SC.SNO)
或
-- 最好为如下表示
UPDATE SC
SET SCORE=0
WHERE SNO IN
(SELECT SNO
FROM S
WHERE SEX= '男') AND CNO IN
(SELECT CNO
FROM C
WHERE CN= '数据库')
或
UPDATE SC
SET SCORE=0
WHERE '男'=
(SELECT SEX
FROM S
WHERE SNO=SC.SNO) AND '数据库'=
(SELECT CN
FROM C
WHERE CNO=O)