发布时间 : 星期三 文章SQL数据库复习题更新完毕开始阅读
C-S n 学生（STUDENT） n 成绩 课程号 课程名称 学时数 S-C 年龄 m 课程（COURSE） 学号 姓名 性别 班级：班号 学生：学号 课程：课程号 成绩： 学号 班级名称 人数 姓名 性别 年龄 课程名称 学时数 课程号 成绩
班号
四、综合题
1、假设要建立一个企业数据库，该企业有多个下属单位，每一单位有多个职工，一个职工仅隶属于一个单位，且一个职工仅在一个工程中工作，但一个工程中有很多职工参加工作，有多个供应商为各个工程供应不同设备。单位的属性有：单位名、电话。职工的属性有：职工号、姓名、性别。设备的属性有：设备号、设备名、产地。供应商的属性有：姓名、电话。工程的属性有：工程号、地点。请完成如下处理： （1）设计满足上述要求的E-R图。
（2）将该E-R图转换为等价的关系模式，并标示出主码
单位：单位名 设备：设备号 工程：工程名
电话； 职工：职工号 姓名 性别 设备名 产地； 供应商：姓名 电话 地点 供应：姓名 工程名 设备号 数量
2、在学校中，一个学生只能属于一个院系，一个院系有多名学生，一个学校有
多个社团，每个学生可以加入多个社团，每个社团有多名学生，有一个学生设计了一个关系模式：R(学号，姓名，所属院系编号，院系名称，参加社团的编号，社团名称，参加社团的日期)
（1） 请描述该系统的最小函数依赖集。 （2） 请指出该学生的关系模式R的码。
（3） 请说明该学生的关系模式R属于几范式，并说明理由。
（4） 如果该学生的关系模式R不属于BCNF，请对该关系模式R进行模式分
解，要求分解后的所有关系模式满足BCNF。 3、有一个“学生－课程”数据库（stu_c），数据库中包括三个表：
(1) “学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept) Sno 为关键字, 类型char(8)。
(2) “课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) Cno为关键字，类型char(8)。
(3) “学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为： SC(Sno,Cno,Grade) (Sno, Cno) 为关键字。
（1）用SQL语句实现SC表的创建，要求定义相应的主键和外键约束
Create table SC( Sno char(8), Cno char(8), Grade int,
Primary key (Sno,Cno) );
（2）“学生”表上以学生姓名和年龄建立一个唯一性索引（升序）。 create index snamesage on student(sname,sage);
（3）在以上三个表中查询Ccredit为5并且Grade大于60的学生的学号、姓名和性别
select 学生.学号,学生.姓名,学生.性别 from 学生 join 学生选课 on 学生.学号=学生选课.学号 join 课程on 课程.课程号=学生选课.课程号 where 课程.学分=5 and 学生选课.成绩>60
（4）统计每个同学所修课程的全部学分（不及格课程不统计）。
select 学生.学号,学生.姓名,课程.课程名,课程.学分 from 学生 join 学生选课 on 学生.学号=学生选课.学号 join 课程 on 课程.课程号=学生选课.课程号 where 学生选课.成绩>=60
（5）修改“李立”同学的数据库成绩为75分。
update 学生选课set 成绩=75 where 学号in (select 学号 from 学生where 姓名='李立') and 课程号 in (select 课程号 from 课程 where 课程名='数据库')
（6）删除“数据库”课程的选课信息（条件是其先修课不及格）
delete from 学生选课where 课程号=any(select 课程号from 课程where 课程名='数据库') and 学号in(select 学号from 学生选课 where 成绩<60 and 课程号in(select 先修课号from 课程where 课程名='数据库')
（7）年龄大于23岁的女学生的学号和姓名；
select 学号,姓名from 学生where 年龄>23 and 性别='女'
（8）“李小波”所选修的全部课程名称；
select 课程.课程名from 学生 join 学生选课on 学生.学号=学生选课.学号 join 课程on 课程.课程号=学生选课.课程号where 学生.姓名='李小波'
（9）所有成绩都在80分以上的学生姓名及所在系；
select 学号,姓名,所在系 from 学生where 学号 in
((select distinct 学生.学号 from 学生 inner join 学生选课 on 学生.学号=学生选课.学号where 学生选课.成绩>=80) except
(select distinct 学生.学号from 学生inner join 学生选课 on 学生.学号=学生选课.学号where 学生选课.成绩<80))
（10）英语成绩比数学成绩好的学生；
select 学生.学号,学生.姓名,成绩 as 英语 into temp1 from 学生 inner join 学生选课 on 学生.学号=学生选课.学号
inner join 课程 on 课程.课程号=学生选课.课程号 where 课程.课程名='英语'
select 学生.学号,学生.姓名,成绩 as 数学 into temp2 from 学生 inner join 学生选课 on 学生.学号=学生选课.学号
inner join 课程 on 课程.课程号=学生选课.课程号 where 课程.课程名='数学'
select temp1.学号,temp1.姓名,temp1.英语,temp2.数学 into temp3 from temp1 left join temp2 on temp1.学号=temp2.学号
select 学号,姓名 from temp3 where 英语>数学
（11）“操作系统”课程得最高分的学生姓名、性别、所在系；
select 姓名,性别,所在系 from 学生 inner join 学生选课 on 学生.学号=学生选课.学号
inner join 课程 on 课程.课程号=学生选课.课程号 where 课程名='操作系统' and 成绩=
(select max(成绩) from 学生选课 where 课程号in (select 课程号from 课程 where 课程名='操作系统'))
（12）至少选修两门以上课程的学生姓名、性别；
select 姓名,性别 from 学生 where 学号 in
(select 学号 from 学生选课 group by 学号 having count(学号)>=2)
（13）创建视图，显示每个学生的学号，姓名，选课门数，补考门数
select 学号,count(学号) as 选课门数 into temp1 from 学生选课 group by 学号 select 学号,count(学号) as 补考门数 into temp2 from 学生选课 where 成绩<80 group by 学号
select 学生.学号,学生.姓名,选课门数,补考门数 from 学生 join temp1 on 学生.学号=temp1.学号 inner join temp2 on temp1.学号=temp2.学号
（14）查找选课包括“李丽”所有选课的学生
select 课程.课程号 into temp1 from 学生inner join 学生选课 on 学生.学号=学生选课.学号
inner join 课程 on 课程.课程号=学生选课.课程号 where 学生.姓名='李立'
select 学生选课.学号,学生选课.课程号,成绩 into temp2 from temp1 left join 学生选课 on temp1.课程号=学生选课.课程号
select 学号,姓名 from 学生 where 学号in
(select 学号from temp2 group by 学号having count(学号)=(select count(*) from temp1))
法1：
select 课程.课程号 into temp1 from 学生inner join 学生选课 on 学生.学号=学生选课.学号
inner join 课程 on 课程.课程号=学生选课.课程号 where 学生.姓名='李四方' /*选出李四方的选课课程号，并将其放到新表temp1中*/
select 学生选课.学号,学生选课.课程号,成绩 into temp2 from temp1 left join 学生选课 on temp1.课程号=学生选课.课程号
/*将所有选了课的学生的学号，对应的课程号，成绩。通过左连接放到temp2中，只要和李四方选中的课程号一样的学生都能连上。这样就不需要考虑其他人比李四方选课选的更多的情况了*/
select 学号,姓名 from 学生 where 学号 in
(select 学号from temp2 group by 学号 having count(学号)=(select count(*) from temp1))
/*(select count(*) from temp1)----代表李四方选课的门数。select 学号from temp2 group by 学号having count(学号)----代表其他学生选了和李四方相同课程的门数。只要二者相等就能说明其他同学（至少）也选了和（李四方相同的所有选的课）*/
法2：