10.16

假设教学管理系统数据库有3个基本表：

S（ S# ，SNAME，AGE，SEX）

SC（ S#，C#， CNAME）

C（ C# ，CNAME，TEACHER）

（说明：SC 基本表中的 S# 是外码，引用了 S 基本表的 S# ；SC 基本表中的 C# 是外码，引用了 C 基本表的 C# ）

完成如下题目（每个题目限用一个SQL语句实现）：

1) 写出SC表的建表语句（数据类型自己定义）。（3分）

复制代码
1 CREATE TABLE SC (
2 S# CHAR(10),
3 C# CHAR(10),
4 PRIMARY KEY (S#, C#),
5 FOREIGN KEY (S#) REFERENCES S(S#),
6 FOREIGN KEY (C#) REFERENCES C(C#)
7 );
复制代码
2) 检索‘LIU’老师所授课程的课程号和课程名。（3分）

1 SELECT C.C#, C.CNAME
2 FROM C
3 WHERE C.TEACHER = 'LIU';
3) 检索年龄大于23岁的男学生的学号和姓名。（3分）

1 SELECT S.S#, S.SNAME
2 FROM S
3 WHERE S.AGE > 23 AND S.SEX = '男';
4) 检索学号为‘S3’的学生所学课程的课程名与任课教师名。（3分）

1 SELECT C.CNAME, C.TEACHER
2 FROM SC
3 JOIN C ON SC.C# = C.C#
4 WHERE SC.S# = 'S3';
5) 检索至少选修‘LIU’老师所授课程中一门课的女学生姓名。（3分）

1 SELECT DISTINCT S.SNAME
2 FROM S
3 JOIN SC ON S.S# = SC.S#
4 JOIN C ON SC.C# = C.C#
5 WHERE S.SEX = '女' AND C.TEACHER = 'LIU';
6) 检索‘WANG’同学不学的课程的课程名。（3分）

1 SELECT C.CNAME
2 FROM C
3 LEFT JOIN SC ON C.C# = SC.C#
4 WHERE SC.S# IS NULL AND SC.S# != 'WANG';
7) 检索至少选修两门课的学生学号。（3分）

1 SELECT SC.S#
2 FROM SC
3 GROUP BY SC.S#
4 HAVING COUNT(SC.C#) >= 2;
8) 检索全部学生都选修的课程的课程号与课程名。（3分）

复制代码
1 SELECT C.C#, C.CNAME
2 FROM C
3 WHERE NOT EXISTS (
4 SELECT S.S#
5 FROM S
6 WHERE NOT EXISTS (
7 SELECT SC.S#
8 FROM SC
9 WHERE SC.C# = C.C# AND SC.S# = S.S#
10 )
11 );
复制代码
9) 检索选修课程包含‘LIU’老师所授课程的学生学号。（3分）

1 SELECT SC.S#
2 FROM SC
3 JOIN C ON SC.C# = C.C#
4 WHERE C.TEACHER = 'LIU';
10) 用触发器实现删除学生，则删除该学生所选修的课程。（3分）

3 CREATE TRIGGER DeleteStudent
4 BEFORE DELETE ON S
5 FOR EACH ROW
6 BEGIN
7 DELETE FROM SC WHERE SC.S# = OLD.S#;
8 END