上机练习.docx

1、上机练习四、上机练习四上机练习1查询学生选课表中的全部数据。答：select * from SC2查询计算机系的学生的姓名、年龄。答：select sname,sage from student where sdept = 计算机系3. 查询成绩在7080分之间的学生的学号、课程号和成绩。答：select sno,cno,grade from sc on where grade between 70 and 801 查询计算机系年龄在1820之间且性别为“男”的学生的姓名、年龄。答：select sname,sage from student where sdept = 计算机系 and sa

2、ge between 18 and 20 and ssex = 男2 查询“C001”号课程的最高分。答：select max(grade) from sc where cno = C0016. 查询计算机系学生的最大年龄和最小年龄。答：select max(sage) as max_age, min(sage) as min_age from student where sdept = 计算机系7. 统计每个系的学生人数。答：select sdept,count(*) from student group by sdept8. 统计每门课程的选课人数和考试最高分。答：select cno,

3、count(*),max(grade) from sc group by cno9. 统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。答：select sno,count(*), sum(grade) from sc group by sno order by count(*) asc10. 查询总成绩超过200分的学生，要求列出学号和总成绩。答：select sno,sum(grade) from sc group by sno having sum(grade) 20011. 查询选课门数超过2门的学生的学号、平均成绩和选课门数。答：select sno, avg(grade

4、), count(*) from sc having count(*) 212. 查询选了“C002”课程的学生的姓名和所在系。答：select sname,sdept from student s join sc on s.sno = sc.sno where cno = C00213. 查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。答：select sname,cno,grade from student s join sc on s.sno = sc.sno where grade 80 order by grade desc14. 查询计算机系男生修了“数据库基础

5、”的学生的姓名、性别和成绩。答：select sname,ssex,grade from student s join sc on s.sno = sc.sno join course c on o = owhere sdept = 计算机系 and ssex = 男 and cname = 数据库基础15. 查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。答：select s.sno,sname,cno,grade from student s left join sc on s.sno = sc.sno16. 查询哪些课程没有人

6、选，要求列出课程号和课程名。答：select o,cname from course c left join sc on o = owhere o is null17查询计算机系没有选课的学生，列出学生姓名。答：select sname from student s left join sc on s.sno = sc.sno Where sdept = 计算机系 and sc.sno is null18. 列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。答：select top 3 s.sno, sname, sdept, grade from Student s

7、join SC on s.Sno = SC.Sno join Course c on c.Cno = SC.Cno where cname = 数据库基础 order by grade desc19查询VB考试成绩最低的学生的姓名、所在系和VB成绩。答：select top 1 with ties sname,sdept,grade from student s join sc on s.sno = sc.sno join course c on o = o where cname = VB order by grade asc 20. 查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求

8、将查询结果放在一张新的永久表中，假设新表名为new_sc。答：select sname, cname, grade into new_sc from student s join sc on s.sno = sc.sno join course c on o = o where grade is not null21. 分别查询信息管理系和计算机系的学生的姓名、性别、修课名称、修课成绩，并要求将这两个查询结果合并成一个结果集，并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。答：select sdept 系名, sname 姓名, ssex 性别, cname 修课名称, grade 修

9、课成绩 from student s join sc on s.sno=sc.sno join course c on o=o where sdept = 信息管理系UNIONselect sdept , sname, ssex, cname, grade from student s join sc on s.sno=sc.sno join course c on o=o where sdept = 计算机系22查询选了VB的学生学号、姓名、所在系和成绩，并对所在系进行如下处理： 当所在系为“计算机系”时，显示“CS”； 当所在系为“信息管理系”时，显示“IS”； 当所在系为“通信工程系”时

10、，显示“CO”；对其他系，均显示“OTHER”。答：select s.sno 学号,sname 姓名, case sdept when 计算机系 then CS when 信息系 then IS when 数学系 then CO else OTHER end as 所在系,grade 成绩 from student s join sc on s.sno = sc.sno join course c on o = o where cname = vb23. 用子查询实现如下查询:（1）查询选了“C001”课程的学生姓名和所在系。答：select sname,sdept from student

11、where sno in( select sno from sc where cno = C001)（2）查询通信工程系成绩80分以上的学生学号和姓名。答：select sno,sname from student where sno in( select sno from sc where grade 80) and sdept = 通信工程系（3）查询计算机系考试成绩最高的学生姓名。答：select sname from student s join sc on s.sno = sc.sno where sdept = 计算机系 and grade = (select max(grade)

12、 from sc join student s on s.sno = sc.snowhere sdept = 计算机系)（4）查询年龄最大的男生的姓名和年龄。答：select sname,sage from student Where sage = (select max(sage) from student and ssex = 男) and ssex = 男（5）查询“C001”课程的考试成绩高于“C001”课程的平均成绩的学生的学号和“C001”课程成绩。答：select sno,grade from sc where cno = C001 And grade (select avg(g

13、rade) from sc where cno = C001) 24. 创建一个新表，表名为test_t，其结构为：（COL1, COL 2, COL 3），其中：COL1：整型，允许空值。COL2：字符型，长度为10 ，不允许空值。COL3：字符型，长度为10 ，允许空值。试写出按行插入如下数据的语句（空白处表示空值）。COL1COL2COL3B11B2C22B3答：create table test_t ( COL1 int, COL2 char(10) not null, COL3 char(10) )insert into test_t values(NULL, B1, NULL)in

14、sert into test_t values(1, B2, C2)insert into test_t(COL1, COL2) values(2, B3)25. 将 “C001”课程的考试成绩加10分。 答：update sc set grade = grade + 10 where cno = C00126. 将计算机系所有选修了“计算机文化学”课程的学生成绩加10分，分别用子查询和多表连接形式实现。答：（1）子查询update sc set grade = grade + 10 where sno in( select sno from student where sdept = 计算机

15、系) and cno in(select cno from course where cname = 计算机文化学)（2）多表连接update sc set grade = grade + 10 from sc join student s on sc.sno = s.sno join course c on o = o where sdept = 计算机系 and canem = 计算机文化学27. 删除修课成绩小于50分的学生的选课记录。答：delete from sc where grade 5028. 删除信息管理系考试成绩小于50分的学生的该门课程的修课纪录，分别用子查询和多表连接形

16、式实现。答：（1） 用连接查询实现 delete from sc from sc join student s on s.sno=sc.sno where sdept = 信息管理系 and grade 50 （2）用子查询实现 delete from sc where sno in ( select sno from student where sdept = 信息管理系 ) and grade = 60 Group by sno2 利用第1题建立的视图，写出完成如下查询的SQL语句，并执行这些语句，查看执行结果。将查询语句和执行结果保存到一个文件中。（1） 查询考试成绩大于等于90分的学生

17、的姓名、课程名和成绩。答：Select sname,cname,grade From v2 where grade = 90（2） 查询选课门数超过3门的学生的学号和选课门数。答：Select * from v3 where total = 3（3） 查询计算机系选课门数超过3门的学生的姓名和选课门数。答：Select sname,total from v3 join student s on s.sno = v3.sno Where sdept = 计算机系 and total = 3（4） 查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分。答：Select v4.sno,sn

18、ame,sdept,total_credit From v4 join student s on s.sno = v4.sno Where total_credit = 10（5） 查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分。答：Select sname,sage,sdept,total_credit From v4 join student s on s.sno = v4.sno Where sage = 20 and total_credit = 103 修改第1题（4）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。答： Alt

19、er view v4 AsSelect sno,sum(credit) as total_credit,count(*) as total_cno From sc join course c on o = o Group by sno4 写出实现下列操作的SQL语句，执行这些语句，并在SSMS工具中观察语句执行结果。（1） 在Student表的Sdept列上建立一个按降序排序的非聚集索引，索引名为：Idx_Sdept。答：Create index Idx_Sdept on student(Sdept DESC)（2） 在Student表的Sname列上建立一个唯一的非聚集索引，索引名为：Idx

20、_Sname。答：Create unique index Idx_Sname on student(Sname)（3） 在Course表上为Cname列建立一个非聚集索引，索引名为： Idx_Cname答：Create index Idx_Cname on Course(Cname)（4） 在SC表上为Sno和Cno建立一个组合的非聚集索引，索引名为：Idx_SnoCno。答：Create index Idx_SnoCno on SC(Sno,Cno)（5） 删除在Sname列上建立的Idx_Sname索引。答：drop index Idx_Sname on Student一 设计题1 设有关

21、系模式：学生修课（学号，姓名，所在系，性别，课程号，课程名，学分，成绩）。设一个学生可以选多门课程，一门课程可以被多名学生选。一个学生有唯一的所在系，每门课程有唯一的课程名和学分。每个学生对每门课程有唯一的成绩。（1） 请指出此关系模式的候选码。（2） 写出该关系模式的极小函数依赖集。（3） 该关系模式属于第几范式？并简单说明理由。（4） 若不是第三范式的，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。答：（1）候选码：（学号，课程号）（2）学号姓名，学号所在系，学号性别，课程号课程名，课程号学分， （学号，课程号）成绩（3）属于第二范式，因为存在部分函数依赖：学号姓

22、名。（4）第三范式关系模式：学生（学号，姓名，所在系，性别）课程（课程号，课程名，学分）考试（学号，课程号，成绩），学号为引用学生的外码，课程号为引用课程的外码。2 设有关系模式：学生（学号，姓名，所在系，班号，班主任，系主任），其语义为：一个学生只在一个系的一个班学习，一个系只有一个系主任，一个班只有一名班主任，一个系可以有多个班。（1） 请指出此关系模式的候选码。（2） 写出该关系模式的极小函数依赖集。（3） 该关系模式属于第几范式？并简单说明理由。（4） 若不是第三范式的，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。解：（1）候选码：学号（2）学号姓名，学号所

23、在系，学号班号，班号班主任，所在系系主任（3）第二范式，因为有：学号班号，班号班主任，因此存在传递函数依赖：学号班主任（4）第三范式关系模式：学生（学号，姓名，所在系，班号），班号为引用班的外码，所在系为引用系的外码。班（班号，班主任）系（系名，系主任）3 设有关系模式：教师授课（课程号，课程名，学分，授课教师号，教师名，授课时数），其语义为：一门课程（由课程号决定）有确定的课程名和学分，每名教师（由教师号决定）有确定的教师名，每门课程可以由多名教师讲授，每名教师也可以讲授多门课程，每名教师对每门课程有确定的授课时数。（1） 指出此关系模式的候选码。（2） 写出该关系模式的极小函数依赖集。（3

24、） 该关系模式属于第几范式？并简单说明理由（4） 若不属于第三范式，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。答：（1）候选码：（课程号，授课教师号）（2）课程号课程名，课程号学分，授课教师号教师名，（课程号，授课教师号）授课时数（3）属于第一范式。因为有：课程号课程名，因此存在部分函数依赖关系：（课程号，授课教师号）课程名（4）第三范式关系模式：课程（课程号，课程名，学分）教师（教师号，教师名）授课（课程号，教师号，授课时数），课程号为引用课程的外码，教师号为引用教师的外码。一 设计题1 将给定的E-R图转换为符合3NF的关系模式，并指出每个关系模式的主码和外码

25、。（1）图7-25所示为描述图书、读者以及读者借阅图书的E-R图。图7-25 图书借阅E-R图答：下列各关系模式中用下划线标识主码。图书（书号，书名，出版日期，作者名）读者（读者号，读者名，联系电话，所在单位）借阅（书号，读者号，借书日期，还书日期），书号为引用图书关系模式的外码，读者号为引用读者关系模式的外码。（2）图7-26所示为描述商店从生产厂家订购商品的E-R图。答：下列各关系模式中用下划线标识主码。商店（商店编号，商店名，联系电话）3NF商品（商品编号，商品名称，库存量，商品分类）3NF厂家（厂家编号，联系地址，联系电话）3NF订购（商店编号，厂家编号，商品编号，订购日期，订购数量）

26、，商店编号为引用商店关系模式的外码，厂家编号为引用厂家关系模式的外码，商品编号为引用商品关系模式的外码。3NF图7-26 商品订购E-R图（3）图7-27为描述学生参加学校社团的E-R图。图7-27 学生参加社团E-R图答：下列各关系模式中用下划线标识主码。 社团（社团号，社团名，电话，性质）3NF 学生（学号，姓名，性别，专业，社团号，参加日期），社团号为引用社团的外码。3NF2 设某工厂生产若干产品，每种产品由若干零件组成，同一种零件可用在不同的产品上。零件由不同的原材料制成，不同的零件所用的原材料可以相同。零件按所属产品的不同被分别存放在不同的仓库中，一个仓库可以存放多种不同的零件。原材

27、料按类别存放在若干仓库中，一个仓库也可以存放不同类别的材料。画出该工厂的E-R图（注：只画出实体和联系即可，不用标识属性）。答： 确定实体。本系统共有四个实体：产品、零件、材料、仓库。 因为只描述的是一个工厂的情况，因此不需要将工厂设为实体。 确定实体间的联系。 产品与零件：多对多联系，可将联系命名为“组成”。 零件与材料：多对多联系，可将联系命名为“制成”。 零件与仓库：多对多联系，可将联系命名为“存放”。 材料与仓库：多对多联系，可将联系命名为“存放”。 绘制E-R图3 设要建立描述顾客在商店的购物情况的数据库应用系统，该系统有如下要求：一个商店可有多名顾客购物，一个顾客可到多个商店购物，顾客每次购物有一个购物金额和购物日期。规定每个顾客每天在每个商店最多有一次购物，每次购物可购买多种商品。需要描述的“商店”信息包括：商店编号、商店名、地址、联系电话；需要描述的顾客信息包括：顾客号、姓名、住址、身份证号、性别。请画出描述该应用系统的E-R图，并注明各实体的属性、标识属性以及联系的种类。答：4 图7-28（a）（d）所示为某企业信息管理系统中的局部E-R图，请将这些局部E-R图合并为一个全局E