3．试用SQL查询语句表达下列对上题中3个基本表S、SC、C的查询： 1）统计有学生选修的课程门数。 SELECT COUNT(DISTINCT CNO) FROM SC 2）求选修C4课程的女学生的平均年龄。 SELECT AVG(AGE) FROM S , SC WHERE S.SNO=SC.SNO AND CNO=’C4’ AND SEX=’女’ 3）求LIU老师所授课程的每门课程的平均成绩。 SELECT C.CNO ,AVG(GRADE) FROM SC ,C WHERE SC.CNO=C.CNO AND TEACHER=’LIU’ GROUP BY C.CNO
5） 统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，
若人数相同，按课程号升序排列。
SELECT CNO ,COUNT(SNO) FROM SC GROUP BY CNO HAVING COUNT(*)>10 ORDWE BY 2 DESC,1
6） 检索学号比WANG同学大，而年龄比他小的学生姓名。
SELECT SNAME FROM S
WHERE SNO>ALL(SELECT SNO FROM S WHERE SNAME =’WANG’)AND AGE
SELECT SNO,CNO FROM SC WHERE GRADE IS NULL
8） 检索姓名以L打头的所有学生的姓名和年龄。
SELECT SNAME ,AGE FROM S WHERE SNAME LIKE ‘L%’ 9） 求年龄大于女同学平均年龄的男学生姓名和年龄。
SELECT SNAME ,AGE FROM S WHERE SEX=’男’ AND AGE> (SELECT AVG(AGE) FROM S WHERE SEX=’女’) 9）求年龄大于所有女同学年龄的男学生姓名和年龄。
SELECT SNAME ,AGE FROM S WHERE SEX=’男’ AND AGE>ALL(SELECT AGE FROM S WHERE SEX=’女’)
4．用SQL更新语句表达对上题教学数据库中表S、SC、C作如下更新操作： 1）往表C中插一个课程 。 课程号：‘C8’， 课程名：‘VC++’ 授课教师：‘BAO’
16
INSERT INTO C VALUES(‘C8’,’VC++’,’BAO’) 2）SC中删除尚无成绩的选课元组。 BELETE FROM SC WHERE GRADE IS NULL 3）把选修LIU老师课程的女同学选课元组全部删去。 DELETE FROM SC WHERE SNO IN (SELECT SNO FROM S WHERE SEX=’女’)AND CNO IN (SELECT CNO FROM C WHERE TEACHER=’LIU’) 4）把MATHS课不及格的成绩全改为60分。 UPDATE SC SET GRADE =60 WHERE GRADE<60 AND CNO IN (SELECT CNO FROM C WHERE CNAME =’MATHS’) 5）把低于所有课程总平均成绩的女同学成绩提高5%。 UPDATE SC SET GRADE =GRADE*1.05 WHERE SNO IN (SELECT SNO FROM S WHERE SEX=’女’)AND GRADE<(SELECT AVG(GRADE) FROM SC)
6）在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。 UPDATE SC SET GRADE =GRADE *1.05 WHERE GRADE <(SELECT AVG(GRADE) FROM SC)
五、数据库设计
1、设有系、教师、学生、课程等实体，其中每一个系包括系名、系址、系主任姓名、办公电话等属性，教师实体包括工作证号码、教师名、出生日期、党派等属性。学生实体包括学号、姓名、出生日期、性别等属性。课程实体包括课程号、课程名、预修课号等属性。设一个系可以有多名教师，每个教师教多门课程， 一门课程由一个教师教。其中有的教师指导多个研究生。每一个学生可选多门课程，每门课程只有一个预修课程，每一个学生选修一门课程有一个成绩，试根据以上语义完成下述要求。 1）试画出ER图
17
2）将ER图转换成关系模式集，并指出每个关系模式的主键和外键。
2、学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干
研究生，每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用E-R图画出此学校的概念模型。
试根据上述业务规则： 1） 试画出ER图
2）将ER图转换成关系模式集，并指出每个关系模式的主键和外键。
3．某商业集团数据库中有3个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品号、商品名、规格、单价等；三是“职工”实体集，三是有职工编号、姓名、性别、业绩等。
商店与商品间存在“销售”关系，每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店每销售一种商品，有月销售量；商店与职工间存在着“聘用”联系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。
1） 试画出ER图，并在图上注明属性、联系的类型。
18
2） 将ER图转换成关系模式集，并指出每个关系模式主键和外键。
商店（商店编号，商店名，地址） 商店编号为主码
职工（职工编号，姓名，性别，业绩，商店编号，聘期，工资） 职工编号为主码，商店编号为外码。 商品（商品号，商品名，规格，单价） 商品号为主码
销售（商店编号，商品号，月销售量） 商店编号+商品号为主码 商店编号，商品号均为外码
4．设某商业集团数据库中有3个实体集。一是“公司”实体集，属性有公司编号、公司名、地址等；二是“仓库”实体集，属性有仓库编号、仓库名、地址等；三是“职工”实体集，属性有职工编号、姓名、性别等。
公司与仓库间存在“隶属”联系，没个攻击管辖若干仓库，每个仓库只能属于一个公司管辖；仓库与职工间存在“聘用”联系，每个仓库可聘用多个职工，每个职工只能在一个仓库铬镍钢做，仓库聘用职工有聘期和工资。 1） 画出ER图，并在图上注明属性、联系的类型。
19
2）将ER图转换成关系模式集，并指出每个关系模式的主键和外键。
5．设某工商业集团数据库有3个实体集。一是“商品”实体集，属性有商品号、商品名、规格、单价等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“供应商”实体集，属性有供应商编号、供应商名、地址等。
供应商与商品之间存在“供应”关系，每个供应商可供应多种商品，每种商品可向多个供应商订购，每个供应商供应每种商品有个月供应量；商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品可在多个商店销售，没个商店销售每种商品有个月计划数。 1） 试画出ER图
2） 将ER图转换成关系模式集，并指出每个关系模式的主键和外键。
6．某医院病房计算机管理中需要如下信息: 科室：科名，科地址，科电话，医生姓名 病房：病房号，床位数，所属科室号
医生：姓名，职称，所属科室名，年龄，工作证号 病人：病历号，姓名，性别，诊断，主管医生，病房号
其中，一个科室有多个病房，多个医生。一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。 完成入下设计： 1） 试画出ER图
20