上机练习文档格式.docx

1、select s.sno,sname,cno,grade from student s left join sc on s.sno=sc.sno 16.查询哪些课程没有人选，要求列出课程号和课程名。select o,cname from course c left join sc on o=o where o is null 17查询计算机系没有选课的学生，列出学生姓名。select sname from student s left join sc on s.sno=sc.sno Where sdept=计算机系 and sc.sno is null 18.列出“数据库基础”课程考试成绩前三

2、名的学生的学号、姓名、所在系和考试成绩。select top 3 s.sno,sname,sdept,grade from Student s join SC on s.Sno=SC.Sno join Course c on c.Cno=SC.Cno where cname=数据库基础 order by grade desc 19查询 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

3、 cname=VB order by grade asc 20.查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果放在一张新的永久表中，假设新表名为 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 null 21.分别查询信息管理系和计算机系的学生的姓名、性别、修课名称、修课成绩，并要求将这两个查询结果合并成一个结果集，并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。sele

4、ct sdept 系名,sname 姓名,ssex 性别,cname 修课名称,grade 修课成绩 from student s join sc on s.sno=sc.sno join course c on o=o where sdept=信息管理系 UNION select 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的学生学号、姓名、所在系和成绩，并对所在系进行如下处理：当所在系为“计算机系”时，显示

5、“CS”；当所在系为“信息管理系”时，显示“IS”；当所在系为“通信工程系”时，显示“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=vb 23.用子查询实现如下查询:（1）查询选了“C001”课程的学生姓名和所在系。sele

6、ct sname,sdept from student 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=（selec

7、t max（grade）from sc join student s on s.sno=sc.sno where 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（grade）f

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

9、st_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=C001 26.将计算机系所有选修了“计算机文化学”课程的学生成绩加 10分，分别用子查询和多表连接形式实现。（1）子查询 update sc set grade=grade+10 where sno in（select sno from student where sdept=计算机系）and cno in（select cno from cours

10、e 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 50 28.删除信息管理系考试成绩小于 50分的学生的该门课程的修课纪录，分别用子查询和多表连接形式实现。（1）用连接查询实现 delete from sc from sc join stude

11、nt 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 sno 2利用第 1题建立的视图，写出完成如下查询的 SQL语句，并执行这些语句，查看执行结果。将查询语句和执行结果保存到一个文件中。（1）查询考试成绩大于等于 90分的学生的姓名、课程名和成绩。Select sname,cname,grade From v2 where grade=90（

12、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,sname,sdept,total_credit From v4 join student s on s.sno=v4.sno Where total_credi

13、t=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=10 3修改第 1题（4）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。Alter view v4 As Select sno,sum（credit）as total_credit,count（*）as total_cno From sc join course

14、 c on o=o Group by sno 4写出实现下列操作的 SQL语句，执行这些语句，并在 SSMS 工具中观察语句执行结果。（1）在 Student 表的 Sdept 列上建立一个按降序排序的非聚集索引，索引名为：Idx_Sdept。Create index Idx_Sdept on student（Sdept DESC）（2）在 Student 表的 Sname列上建立一个唯一的非聚集索引，索引名为：Idx_Sname。Create unique index Idx_Sname on student（Sname）（3）在 Course表上为 Cname列建立一个非聚集索引，索引名为

15、：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设有关系模式：学生修课（学号，姓名，所在系，性别，课程号，课程名，学分，成绩）。设一个学生可以选多门课程，一门课程可以被多名学生选。一个学生有唯一的所在系，每门课程有唯一的课程名和

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

17、，姓名，所在系，班号，班主任，系主任），其语义为：一个学生只在一个系的一个班学习，一个系只有一个系主任，一个班只有一名班主任，一个系可以有多个班。解：学号（2）学号姓名，学号所在系，学号班号，班号班主任，所在系系主任（3）第二范式，因为有：学号班号，班号班主任，因此存在传递函数依赖：学号 班主任（4）第三范式关系模式：学生（学号，姓名，所在系，班号），班号为引用班的外码，所在系为引用系的外码。班（班号，班主任）系（系名，系主任）3设有关系模式：教师授课（课程号，课程名，学分，授课教师号，教师名，授课时数），其语义为：一门课程（由课程号决定）有确定的课程名和学分，每名教师（由教师号决定）有确定的

18、教师名，每门课程可以由多名教师讲授，每名教师也可以讲授多门课程，每名教师对每门课程有确定的授课时数。（1）指出此关系模式的候选码。并简单说明理由（4）若不属于第三范式，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。（课程号，授课教师号）（2）课程号课程名，课程号学分，授课教师号教师名，（课程号，授课教师号）授课时数（3）属于第一范式。因为有：课程号课程名，因此存在部分函数依赖关系：（课程号，授课教师号）课程名（4）第三范式关系模式：课程（课程号，课程名，学分）教师（教师号，教师名）授课（课程号，教师号，授课时数），课程号为引用课程的外码，教师号为引用教师的外码。一设计

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

20、址，联系电话）3NF 订购（商店编号，厂家编号，商品编号，订购日期，订购数量），商店编号为引用商店关系模式的外码，厂家编号为引用厂家关系模式的外码，商品编号为引用商品关系模式的外码。3NF 图 7-26 商品订购 E-R 图（3）图 7-27 为描述学生参加学校社团的 E-R 图。图 7-27 学生参加社团 E-R 图 答：社团（社团号，社团名，电话，性质）3NF 学生（学号，姓名，性别，专业，社团号，参加日期），社团号为引用社团的外码。3NF 2设某工厂生产若干产品，每种产品由若干零件组成，同一种零件可用在不同的产品上。零件由不同的原材料制成，不同的零件所用的原材料可以相同。零件按所属产品的

21、不同被分别存放在不同的仓库中，一个仓库可以存放多种不同的零件。原材料按类别存放在若干仓库中，一个仓库也可以存放不同类别的材料。画出该工厂的 E-R图（注：只画出实体和联系即可，不用标识属性）。确定实体。本系统共有四个实体：产品、零件、材料、仓库。因为只描述的是一个工厂的情况，因此不需要将工厂设为实体。确定实体间的联系。产品与零件：多对多联系，可将联系命名为“组成”。零件与材料：多对多联系，可将联系命名为“制成”。零件与仓库：多对多联系，可将联系命名为“存放”。材料与仓库：绘制 E-R 图 3设要建立描述顾客在商店的购物情况的数据库应用系统，该系统有如下要求：一个商店可有多名顾客购物，一个顾客可

22、到多个商店购物，顾客每次购物有一个购物金额和购物日期。规定每个顾客每天在每个商店最多有一次购物，每次购物可购买多种商品。需要描述的“商店”信息包括：商店编号、商店名、地址、联系电话；需要描述的顾客信息包括：顾客号、姓名、住址、身份证号、性别。请画出描述该应用系统的 E-R 图，并注明各实体的属性、标识属性以及联系的种类。4 图 7-28（a）（d）所示为某企业信息管理系统中的局部 E-R 图，请将这些局部E-R 图合并为一个全局 E-R 图，并指明各实体以及联系的属性，标明联系的种类（注：为使图形简洁明了，在全局 E-R 图中可只画出实体和联系，属性单独用文字描述）。将合并后的 E-R 图转换

23、为符合 3NF要求的关系模式，并说明主码和外码。（c）（d）图 7-28 各局部 E-R 图 答：分析：1 工人实体可以与职工实体合并，将工人的属性合并到职工实体中，并在职工实体中增加“身份”属性，来标识是工人还是其他职工。2设备处可合并到部门实体中，因设备处也是一个部门。可在实现时增加约束：只有设备处才能管理设备。3合并后的设备实体属性是图（c）和（d）中设备属性的并集。合并后的结果：关系模式（主码用下划线标识）部门（部门号，部门名，联系电话，办公地点）职工（职工编号，姓名，身份，职务，性别，年龄，所属部门号），所属部门号为引用部门的外码 设备（设备号，设备名，规格，价格，被管部门号），被管

24、部门号为引用部门的外码 零件（零件号，零件名，规格，价格）厂商（厂商号，厂商名，地址，电话）装配（设备号，零件号，零件数量），设备号为引用设备的外码，零件号为引用零件的外码 生产（厂商号，零件号，生产日期）或 生产（厂商号，零件号，生产日期）四上机练习 以下各题均利用第 3、4章建立的 Students 数据库以及 Student、Course和 SC 表实现。1创建满足下述要求的存储过程，并查看存储过程的执行结果。（1）查询每个学生的修课总学分，要求列出学生学号及总学分。create proc p1 as select sno,SUM（credit）as 总学分 from SC join C

25、ourse c on c.Cno=SC.Cno group by sno（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系”。执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。create proc p2 dept varchar（20）=计算机系 as select s.sno,sname,o,cname,credit from Student s join SC on s.Sno=SC.Sno join Course c on c.Cno=SC.Cno where Sdept=dept 执行示例 1：EXEC P2 执行示例

26、2：EXEC P2 通信工程系（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。create proc p3 dept varchar（20）,rs int output as select rs=COUNT（*）from Student where Sdept=dept and Ssex=男（4）删除指定学生的修课记录，其中学号为输入参数。create proc p4 sno char（7）as delete from SC where Sno=sno（5）修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。create proc p5 cno char（6）,x ti

27、nyint as update Course set Semester=x where Cno=cno 2创建满足下述要求的触发器（前触发器、后触发器均可），并验证触发器执行情况。（1）限制学生的年龄在 1545之间。create trigger tri1 on student after insert,update as if exists（select*from inserted where sage not between 15 and 45）rollback（2）限制学生所在系的取值范围为计算机系，信息管理系，数学系，通信工程系 create trigger tri2 on student after insert,update as if exists（select*from student where sdept not in （计算机系,信息管理系,数学系,通信工程系）Rollback（3）限制每个学期开设的课程总学分在 2030 范围内。create trigger tri3 on course after insert,update as if exists（select sum（credit）from course where semester in（selec