FROM 作者)；
4．学校有多名学生，财务处每年要收一次学费。为财务处收学费工作设计一个数据库，包括两个关系：
学生 (学号，姓名，专业，入学日期) 收费 (学年，学号，学费，书费，总金额)
假设规定属性的类型：学费、书费、总金额为数值型数据；学号、姓名、学年、专业为字符型数据；入学日期为日期型数据。列的宽度自定义。
试用SQL语句定义上述表的结构。(定义中应包括主键子句和外键子句) CREATE TABLE 学生 (学号 CHAR (8)， 姓名 CHAR (8)， 专业 CHAR (13)， 入学日期 DATE，
PRIMARY KEY (学号))； CREATE TABLE 收费 (学年 CHAR (10)， 学号 CHAR (8)， 学费 NUMERIC (4)， 书费 NUMERIC (5，2)， 总金额 NUMERIC (7，2)， PRIMARY KEY (学年，学号)，
FOREIGN KEY (学号) REFERENCES 学生 (学号))；
5．在“学生一选课一课程”数据库中的3个关系如下：
学生(学号，姓名，年龄，性别，班级) 选课(学号，课程号，成绩)
课程(课程号，课程名，学分，学时，任课教师) .
（一）试用关系代数表达式和SQL语言表示下列查询要求。(1) 查询选修了课程名为DB的学生姓名和所在班级。
Π姓名,班级(学生∞选课∞ σ课程名='DB' (课程)) SELECT 姓名，姓名, 班级 FROM 学生
WHERE 学号 IN （SELECT 学号 FROM 选课，课程 WHERE 选课.课程号=课程.课程号 AND 课程名='DB'）(2) 查询?刘军?老师所授课程的课程号、课程名。
π课程号,课程名(σ任课教师='刘军'(课程))
26
SELECT 课程号，课程名 FROM 课程 WHERE 任课教师=?LIU? (3) 求选修课程包含“刘军”老师所授课程之一的学生学号 (4) 查询年龄大于23岁的男学生的学号与姓名。 π学号,姓名(σ年龄＞'23'∧性别='男'(学生))
SELECT 学号，姓名 FROM S WHERE (年龄>23) AND (性别=?男?) (5) 查询学号为S3学生所学课程的课程名与任课教师名。
π课程名,任课教师(σ学号='S3'∧选课.课程号=C.课程号(选课×课程))
(也可将两个关系自然连接后选择、投影)
π课程名,任课教师(σ学号='S3' ( 学生∞选课))
(6) 查询至少选修LIU老师所授课程中一门课程的女学生的姓名。
π姓名(σ性别='女'∧任课教师='刘军'(学生∞选课∞课程))
SELECT 姓名 FROM 学生 WHERE 性别=?女? AND 学号 IN (SELECT 学号 FROM 选课 WHERE 课程号 IN
(SELECT 课程号 FROM 课程 WHERE 任课教师=?LIU?)
(7) 查询WANG同学不学的课程号。
π课程号(选课)- π课程号(σ姓名='WANG'∧学生.学号=选课.学号(学生×选课))
SELECT 课程号 FROM C WHERE 课程号 NOT IN (SELECT 课程号 FROM 选课 WHERE 学号 IN (SELECT 学号 FROM S WHERE 姓名='WANG'))
(8) 查询至少选修两门课程的学生学号。
π学号(σ1=4∧2≠5(选课×选课)) (选课自乘之后，同一个学号下两个课程号不同的元组)
SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(课程号)>＝2 (9) 查询全部学生都选修的课程的课程号与课程名。
π课程号, 课程名 (选课∞(π学号,课程号(选课)?π学号(学生)))
(涉及到全部值时，应用除法，“除数”是全部量。)
SELECT 课程号,课程名 FROM 课程 WHERE NOT EXISTS (SELECT * FROM 学生 WHERE NOT EXISTS
(SELECT * FROM 选课 WHERE 选课.学号=学生.学号 AND 选课.课程号=课程.课程号))
(10) 查询选修课程包含刘军老师所授课程的学生学号。 π学号(σ任课教师='刘军'(学生∞选课∞课程))
27
SELECT 学号 FROM 学生 WHERE 学号 IN
(SELECT 学号 FROM 选课 WHERE 学生.学号=选课.学号 AND 课程号 IN (SELECT 课程号 FROM 课程 WHERE 任课教师='LIU')) （请讨论一下，本题是否可如此：SELECT DISTINGCT
学号 FROM 选课 WHERE EXISTS (SELECT 课程号 FROM 课程 WHERE 任课教师='LIU')）
（二）试用SQL查询语句表达下列对教学数据库中三个基本表学生、选课、课程的查询要求：
（1）统计有学生选修的课程门数。
SELECT COUNT(DISTINCT 课程号) FROM 选课 （2）求选修C4课程的学生的平均年龄。
SELECT AVG(年龄) FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 选课 WHERE 课程号='C4') （3）求LIU老师所授课程的每门课程的学生平均成绩。
SELECT 课程名,AVG(成绩) FROM 选课 ,课程 WHERE 课程号 IN (SELECT 课程号 FROM 课程 WHERE 任课教师='LIU') GROUP BY 课程号
（4）统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修
人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。 SELECT DISTINCT 课程号,COUNT(学号) FROM 选课
GROUP BY 课程号 HAVING COUNT(学号)>10 ORDER BY 2 DEC,课程号 （5）查询学号比WANG同学大，而年龄比他小的学生姓名。
SELCET 姓名 FROM 学生 AS X WHERE X.学号>SOME
(SELECT 学号 FROM 学生 AS Y WHERE Y.姓名='WANG' AND X.年龄 SELECT 姓名,年龄 FROM 学生 WHERE 姓名 LIKE 'WANG%' （7）在选课中检索成绩为空值的学生学号和课程号。 SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL （8）求年龄大于女同学平均年龄的男学生姓名和年龄。 SELECT 姓名,年龄 FROM 学生 AS X WHERE X.性别='男' AND X.年龄>(SELECT AVG(年龄) FROM 学生 AS Y WHERE Y.性别='女') （9）求年龄大于所有女同学年龄的男学生姓名和年龄。 SELECT 姓名,年龄 FROM 学生 AS X WHERE X.性别='男' AND X.年龄>ALL (SELECT 年龄 FROM 学生 AS Y WHERE Y.性别='女') （10）查询?刘军?老师所教的所有课程的课号和学时 （11）查询所有学生的基本情况 （12）求在本学期任课的全部教师姓名，担任两门课以上的教师只出现一次。 28 （13）查询23岁以上男生的学号和姓名。 （14）求所有男生的学号、姓名、年龄。要求按年龄递增排序。 （15）求关系S中学生总数 （16）求学习?C2?课的学生总数： （17）将关系C中课程号为C2的课程任课教师改为?WANG? （18）求任课教师总数. （19）求学生S1的总分数. （20）求学生的最大年龄. （21）求出年龄等于最大年龄的所有学生的姓名。 SELECT 学号，姓名 FROM 学生 WHERE 年龄=(SELECT MAX(年龄) FROM 学生 ) （22）求选修课程号为“C2”的课程的学生的姓名和专业 （23）求至少选修两门课程的学生学号 （三）试用SQL更新语句表达对教学数据库中三个基本表学生、选课、课程的各个更新操作： （1）往基本表学生中插入一个学生元组（?S9?，?WU?，18）。 INSERT INTO 学生(学号,姓名,年龄) VALUES('59','WU',18) （2）在基本表学生中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别， 并把检索到的值送往另一个已存在的基本表STUDENT（学号，姓名，性别）。 INSERT INTO STUDENT(学号,姓名,性别) SELECT 学号,姓名,性别 FROM 学生 WHERE NOT EXISTS (SELECT * FROM 选课 WHERE 成绩>=80 AND 学生.学号=选课.学号) （3）在基本表选课中删除尚无成绩的选课元组。 DELETE FROM 选课 WHERE 成绩 IS NULL （4）把WANG同学的学习选课和成绩全部删去。 DELETE FROM 选课 WHERE 学号 IN (SELECT 学号 FROM 学生 WHERE 姓名='WANG') （5）把选修MATHS课不及格的成绩全改为空值。 UPDATE 选课 SET 成绩=NULL WHERE 学号 IN (SELECT 学号 FROM 课程 WHERE 课程名='MATHS') AND 选课.成绩<60 （6）把低于总平均成绩的女同学成绩提高5%。 UPDATE 选课 SET 成绩=成绩*1.05 WHERE 成绩<(SELECT AVG(成绩) FROM 选课)AND 学号 IN (SELECT 学号 FROM S WHERE 性别='女') （7）在基本表选课中修改C4课程的成绩，若成绩小于等于75分时提高5%，若成绩 大于75分时提高4%（用两个UPDATE语句实现）。 UPDATE 选课 SET 成绩=成绩*1.05 WHERE 课程号='C4' AND 成绩<=75 UPDATE 选课 SET 成绩=成绩*1.04 WHERE 课程号='C4' AND 成绩>75 29 （四）对于教学数据库中基本表选课，已建立下列视图： CREATE VIEW S＿成绩（学号，课程数，平均成绩） AS SELECT 学号，COUNT(C＃)，AVG（成绩） FROM 选课 GROUP BY 学号 试判断下列查询和更新是否允许执行。若允许，写出转换到基本表选课上的相应操作。 （1） SELECT * FROM S＿成绩 允许:SELECT 学号,COUNT(课程号),AVG(成绩) FROM 选课 GROUP BY S＃ （2） SELECT S＃，C＿NUM FROM S＿成绩 WHERE AVG＿成绩＞80 允许:SELECT 学号,COUNT(课程号) FROM 选课 GROUP BY S＃ HAVING AVG(成绩)>80 （3） SELECT S＃，AVG＿成绩 FROM S＿成绩 WHERE C＿NUM＞(SELECT C＿NUM FROM S＿成绩 WHERE S＃＝ ?S4?) 允许:SELECT 学号,AVG(成绩) FROM 选课 AS X WHERE COUNT(X.课程号)>(SELECT COUNT(Y.课程号) FROM 选课 AS Y WHERE Y.学号='S4') （4） UPDATE S＿成绩 SET C＿NUM＝C＿NUM＋1 WHERE S＃＝?S4? 不允许 （5） DELETE FROM S＿成绩 WHERE C＿NUM＞4 不允许 （五） (1) 建立一个视图V—SSC(学号，姓名，课程号，课程名，成绩)，并按课程号升序排序； (2) 从视图V—SSC上查询平均成绩在90分以上的学号、课程号和成绩。 (计算题) (1) CREATEVIEWV—SSC(学号，姓名，课程号，课程名，成绩) AS SELECT学号，姓 名，课程号，课程名，成绩FROM 学生，选课 WHERE 学生．学号=选课．学号 ORDER BY 课程号； (2) SELECT 学号，课程号，成绩 FROM V—SSC GROUP BY 学号 HAVING AVG（成 绩）>90； 6． 现有关系数据库如下： 学生(学号，姓名，性别，年龄，专业)， 课程(课程号，课程名称，学分)， 选修(学号，课程号，成绩) 用关系代数表达式实现下列1—4小题，用SQL语句实现5-8小题： 1.查询“国际贸易”专业的学生的学号、姓名； 2.查询学生成绩得过满分(100分)的课程的课程号、课程名称和学分； 3.查询选修了“数据库原理”这门课程的学生的学号、姓名和专业； 4.查询至少选修了学号为200201001这位学生选修了的课程的学生的学号及姓名； 5.查询“数据库原理”这门课成绩在90分以上的学生人数； 6.查询各个课程号及其相应的选课人数； 7.查询选修了3号课程的学生的学号及其成绩，查询结果按成绩降序排列； 30