上机练习文档格式.docx

上机练习文档格式.docx

《上机练习文档格式.docx》由会员分享，可在线阅读，更多相关《上机练习文档格式.docx（19页珍藏版）》请在冰豆网上搜索。

selects.sno,sname,cno,gradefromstudentsleftjoinscons.sno=sc.sno16.查询哪些课程没有人选，要求列出课程号和课程名。

selecto,cnamefromcoursecleftjoinscono=owhereoisnull17查询计算机系没有选课的学生，列出学生姓名。

selectsnamefromstudentsleftjoinscons.sno=sc.snoWheresdept=计算机系andsc.snoisnull18.列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。

selecttop3s.sno,sname,sdept,gradefromStudentsjoinSCons.Sno=SC.SnojoinCourseconc.Cno=SC.Cnowherecname=数据库基础orderbygradedesc19查询VB考试成绩最低的学生的姓名、所在系和VB成绩。

selecttop1withtiessname,sdept,gradefromstudentsjoinscons.sno=sc.snojoincoursecono=owherecname=VBorderbygradeasc20.查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果放在一张新的永久表中，假设新表名为new_sc。

selectsname,cname,gradeintonew_scfromstudentsjoinscons.sno=sc.snojoincoursecono=owheregradeisnotnull21.分别查询信息管理系和计算机系的学生的姓名、性别、修课名称、修课成绩，并要求将这两个查询结果合并成一个结果集，并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

selectsdept系名,sname姓名,ssex性别,cname修课名称,grade修课成绩fromstudentsjoinscons.sno=sc.snojoincoursecono=owheresdept=信息管理系UNIONselectsdept,sname,ssex,cname,gradefromstudentsjoinscons.sno=sc.snojoincoursecono=owheresdept=计算机系22查询选了VB的学生学号、姓名、所在系和成绩，并对所在系进行如下处理：

当所在系为“计算机系”时，显示“CS”；

当所在系为“信息管理系”时，显示“IS”；

当所在系为“通信工程系”时，显示“CO”；

对其他系，均显示“OTHER”。

selects.sno学号,sname姓名,casesdeptwhen计算机系thenCSwhen信息系thenISwhen数学系thenCOelseOTHERendas所在系,grade成绩fromstudentsjoinscons.sno=sc.snojoincoursecono=owherecname=vb23.用子查询实现如下查询:

（1）查询选了“C001”课程的学生姓名和所在系。

selectsname,sdeptfromstudentwheresnoin（selectsnofromscwherecno=C001）

（2）查询通信工程系成绩80分以上的学生学号和姓名。

selectsno,snamefromstudentwheresnoin（selectsnofromscwheregrade80）andsdept=通信工程系（3）查询计算机系考试成绩最高的学生姓名。

selectsnamefromstudentsjoinscons.sno=sc.snowheresdept=计算机系andgrade=（selectmax（grade）fromscjoinstudentsons.sno=sc.snowheresdept=计算机系）（4）查询年龄最大的男生的姓名和年龄。

selectsname,sagefromstudentWheresage=（selectmax（sage）fromstudentandssex=男）andssex=男（5）查询“C001”课程的考试成绩高于“C001”课程的平均成绩的学生的学号和“C001”课程成绩。

selectsno,gradefromscwherecno=C001Andgrade（selectavg（grade）fromscwherecno=C001）24.创建一个新表，表名为test_t，其结构为：

（COL1,COL2,COL3），其中：

COL1：

整型，允许空值。

COL2：

字符型，长度为10，不允许空值。

COL3：

字符型，长度为10，允许空值。

试写出按行插入如下数据的语句（空白处表示空值）。

COL1COL2COL3B11B2C22B3答：

createtabletest_t（COL1int,COL2char（10）notnull,COL3char（10）insertintotest_tvalues（NULL,B1,NULL）insertintotest_tvalues（1,B2,C2）insertintotest_t（COL1,COL2）values（2,B3）25.将“C001”课程的考试成绩加10分。

updatescsetgrade=grade+10wherecno=C00126.将计算机系所有选修了“计算机文化学”课程的学生成绩加10分，分别用子查询和多表连接形式实现。

（1）子查询updatescsetgrade=grade+10wheresnoin（selectsnofromstudentwheresdept=计算机系）andcnoin（selectcnofromcoursewherecname=计算机文化学）

（2）多表连接updatescsetgrade=grade+10fromscjoinstudentsonsc.sno=s.snojoincoursecono=owheresdept=计算机系andcanem=计算机文化学27.删除修课成绩小于50分的学生的选课记录。

deletefromscwheregrade5028.删除信息管理系考试成绩小于50分的学生的该门课程的修课纪录，分别用子查询和多表连接形式实现。

（1）用连接查询实现deletefromscfromscjoinstudentsons.sno=sc.snowheresdept=信息管理系andgrade50

（2）用子查询实现deletefromscwheresnoin（selectsnofromstudentwheresdept=信息管理系）andgrade=60Groupbysno2利用第1题建立的视图，写出完成如下查询的SQL语句，并执行这些语句，查看执行结果。

将查询语句和执行结果保存到一个文件中。

（1）查询考试成绩大于等于90分的学生的姓名、课程名和成绩。

Selectsname,cname,gradeFromv2wheregrade=90

（2）查询选课门数超过3门的学生的学号和选课门数。

Select*fromv3wheretotal=3（3）查询计算机系选课门数超过3门的学生的姓名和选课门数。

Selectsname,totalfromv3joinstudentsons.sno=v3.snoWheresdept=计算机系andtotal=3（4）查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分。

Selectv4.sno,sname,sdept,total_creditFromv4joinstudentsons.sno=v4.snoWheretotal_credit=10（5）查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分。

Selectsname,sage,sdept,total_creditFromv4joinstudentsons.sno=v4.snoWheresage=20andtotal_credit=103修改第1题（4）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。

Alterviewv4AsSelectsno,sum（credit）astotal_credit,count（*）astotal_cnoFromscjoincoursecono=oGroupbysno4写出实现下列操作的SQL语句，执行这些语句，并在SSMS工具中观察语句执行结果。

（1）在Student表的Sdept列上建立一个按降序排序的非聚集索引，索引名为：

Idx_Sdept。

CreateindexIdx_Sdeptonstudent（SdeptDESC）

（2）在Student表的Sname列上建立一个唯一的非聚集索引，索引名为：

Idx_Sname。

CreateuniqueindexIdx_Snameonstudent（Sname）（3）在Course表上为Cname列建立一个非聚集索引，索引名为：

Idx_Cname答：

CreateindexIdx_CnameonCourse（Cname）（4）在SC表上为Sno和Cno建立一个组合的非聚集索引，索引名为：

Idx_SnoCno。

CreateindexIdx_SnoCnoonSC（Sno,Cno）（5）删除在Sname列上建立的Idx_Sname索引。

dropindexIdx_SnameonStudent一设计题1设有关系模式：

学生修课（学号，姓名，所在系，性别，课程号，课程名，学分，成绩）。

设一个学生可以选多门课程，一门课程可以被多名学生选。

一个学生有唯一的所在系，每门课程有唯一的课程名和学分。

每个学生对每门课程有唯一的成绩。

（1）请指出此关系模式的候选码。

（2）写出该关系模式的极小函数依赖集。

（3）该关系模式属于第几范式？

并简单说明理由。

（4）若不是第三范式的，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。

（1）候选码：

（学号，课程号）

（2）学号姓名，学号所在系，学号性别，课程号课程名，课程号学分，（学号，课程号）成绩（3）属于第二范式，因为存在部分函数依赖：

学号姓名。

（4）第三范式关系模式：

学生（学号，姓名，所在系，性别）课程（课程号，课程名，学分）考试（学号，课程号，成绩），学号为引用学生的外码，课程号为引用课程的外码。

2设有关系模式：

学生（学号，姓名，所在系，班号，班主任，系主任），其语义为：

一个学生只在一个系的一个班学习，一个系只有一个系主任，一个班只有一名班主任，一个系可以有多个班。

解：

学号

（2）学号姓名，学号所在系，学号班号，班号班主任，所在系系主任（3）第二范式，因为有：

学号班号，班号班主任，因此存在传递函数依赖：

学号班主任（4）第三范式关系模式：

学生（学号，姓名，所在系，班号），班号为引用班的外码，所在系为引用系的外码。

班（班号，班主任）系（系名，系主任）3设有关系模式：

教师授课（课程号，课程名，学分，授课教师号，教师名，授课时数），其语义为：

一门课程（由课程号决定）有确定的课程名和学分，每名教师（由教师号决定）有确定的教师名，每门课程可以由多名教师讲授，每名教师也可以讲授多门课程，每名教师对每门课程有确定的授课时数。

（1）指出此关系模式的候选码。

并简单说明理由（4）若不属于第三范式，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。

（课程号，授课教师号）

（2）课程号课程名，课程号学分，授课教师号教师名，（课程号，授课教师号）授课时数（3）属于第一范式。

因为有：

课程号课程名，因此存在部分函数依赖关系：

（课程号，授课教师号）课程名（4）第三范式关系模式：

课程（课程号，课程名，学分）教师（教师号，教师名）授课（课程号，教师号，授课时数），课程号为引用课程的外码，教师号为引用教师的外码。

一设计题1将给定的E-R图转换为符合3NF的关系模式，并指出每个关系模式的主码和外码。

（1）图7-25所示为描述图书、读者以及读者借阅图书的E-R图。

图7-25图书借阅E-R图答：

下列各关系模式中用下划线标识主码。

图书（书号，书名，出版日期，作者名）读者（读者号，读者名，联系电话，所在单位）借阅（书号，读者号，借书日期，还书日期），书号为引用图书关系模式的外码，读者号为引用读者关系模式的外码。

（2）图7-26所示为描述商店从生产厂家订购商品的E-R图。

商店（商店编号，商店名，联系电话）3NF商品（商品编号，商品名称，库存量，商品分类）3NF厂家（厂家编号，联系地址，联系电话）3NF订购（商店编号，厂家编号，商品编号，订购日期，订购数量），商店编号为引用商店关系模式的外码，厂家编号为引用厂家关系模式的外码，商品编号为引用商品关系模式的外码。

3NF图7-26商品订购E-R图（3）图7-27为描述学生参加学校社团的E-R图。

图7-27学生参加社团E-R图答：

社团（社团号，社团名，电话，性质）3NF学生（学号，姓名，性别，专业，社团号，参加日期），社团号为引用社团的外码。

3NF2设某工厂生产若干产品，每种产品由若干零件组成，同一种零件可用在不同的产品上。

零件由不同的原材料制成，不同的零件所用的原材料可以相同。

零件按所属产品的不同被分别存放在不同的仓库中，一个仓库可以存放多种不同的零件。

原材料按类别存放在若干仓库中，一个仓库也可以存放不同类别的材料。

画出该工厂的E-R图（注：

只画出实体和联系即可，不用标识属性）。

确定实体。

本系统共有四个实体：

产品、零件、材料、仓库。

因为只描述的是一个工厂的情况，因此不需要将工厂设为实体。

确定实体间的联系。

产品与零件：

多对多联系，可将联系命名为“组成”。

零件与材料：

多对多联系，可将联系命名为“制成”。

零件与仓库：

多对多联系，可将联系命名为“存放”。

材料与仓库：

绘制E-R图3设要建立描述顾客在商店的购物情况的数据库应用系统，该系统有如下要求：

一个商店可有多名顾客购物，一个顾客可到多个商店购物，顾客每次购物有一个购物金额和购物日期。

规定每个顾客每天在每个商店最多有一次购物，每次购物可购买多种商品。

需要描述的“商店”信息包括：

商店编号、商店名、地址、联系电话；

需要描述的顾客信息包括：

顾客号、姓名、住址、身份证号、性别。

请画出描述该应用系统的E-R图，并注明各实体的属性、标识属性以及联系的种类。

4图7-28（a）（d）所示为某企业信息管理系统中的局部E-R图，请将这些局部E-R图合并为一个全局E-R图，并指明各实体以及联系的属性，标明联系的种类（注：

为使图形简洁明了，在全局E-R图中可只画出实体和联系，属性单独用文字描述）。

将合并后的E-R图转换为符合3NF要求的关系模式，并说明主码和外码。

（c）（d）图7-28各局部E-R图答：

分析：

1工人实体可以与职工实体合并，将工人的属性合并到职工实体中，并在职工实体中增加“身份”属性，来标识是工人还是其他职工。

2设备处可合并到部门实体中，因设备处也是一个部门。

可在实现时增加约束：

只有设备处才能管理设备。

3合并后的设备实体属性是图（c）和（d）中设备属性的并集。

合并后的结果：

关系模式（主码用下划线标识）部门（部门号，部门名，联系电话，办公地点）职工（职工编号，姓名，身份，职务，性别，年龄，所属部门号），所属部门号为引用部门的外码设备（设备号，设备名，规格，价格，被管部门号），被管部门号为引用部门的外码零件（零件号，零件名，规格，价格）厂商（厂商号，厂商名，地址，电话）装配（设备号，零件号，零件数量），设备号为引用设备的外码，零件号为引用零件的外码生产（厂商号，零件号，生产日期）或生产（厂商号，零件号，生产日期）四上机练习以下各题均利用第3、4章建立的Students数据库以及Student、Course和SC表实现。

1创建满足下述要求的存储过程，并查看存储过程的执行结果。

（1）查询每个学生的修课总学分，要求列出学生学号及总学分。

createprocp1asselectsno,SUM（credit）as总学分fromSCjoinCourseconc.Cno=SC.Cnogroupbysno

（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系”。

执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。

createprocp2deptvarchar（20）=计算机系asselects.sno,sname,o,cname,creditfromStudentsjoinSCons.Sno=SC.SnojoinCourseconc.Cno=SC.CnowhereSdept=dept执行示例1：

EXECP2执行示例2：

EXECP2通信工程系（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。

createprocp3deptvarchar（20）,rsintoutputasselectrs=COUNT（*）fromStudentwhereSdept=deptandSsex=男（4）删除指定学生的修课记录，其中学号为输入参数。

createprocp4snochar（7）asdeletefromSCwhereSno=sno（5）修改指定课程的开课学期。

输入参数为：

课程号和修改后的开课学期。

createprocp5cnochar（6）,xtinyintasupdateCoursesetSemester=xwhereCno=cno2创建满足下述要求的触发器（前触发器、后触发器均可），并验证触发器执行情况。

（1）限制学生的年龄在1545之间。

createtriggertri1onstudentafterinsert,updateasifexists（select*frominsertedwheresagenotbetween15and45）rollback

（2）限制学生所在系的取值范围为计算机系，信息管理系，数学系，通信工程系createtriggertri2onstudentafterinsert,updateasifexists（select*fromstudentwheresdeptnotin（计算机系,信息管理系,数学系,通信工程系）Rollback（3）限制每个学期开设的课程总学分在2030范围内。

createtriggertri3oncourseafterinsert,updateasifexists（selectsum（credit）fromcoursewheresemesterin（selec