上机练习.docx
《上机练习.docx》由会员分享，可在线阅读，更多相关《上机练习.docx（22页珍藏版）》请在冰豆网上搜索。
上机练习
四、上机练习
四．上机练习
1．查询学生选课表中的全部数据。
答：
select*fromSC
2．查询计算机系的学生的姓名、年龄。
答：
selectsname,sagefromstudentwheresdept='计算机系'
3.查询成绩在70～80分之间的学生的学号、课程号和成绩。
答：
selectsno,cno,gradefromsconwheregradebetween70and80
1．查询计算机系年龄在18～20之间且性别为“男”的学生的姓名、年龄。
答：
selectsname,sagefromstudent
wheresdept='计算机系'andsagebetween18and20andssex='男'
2．查询“C001”号课程的最高分。
答：
selectmax（grade）fromscwherecno='C001'
6.查询计算机系学生的最大年龄和最小年龄。
答：
selectmax（sage）asmax_age,min（sage）asmin_agefromstudent
wheresdept='计算机系'
7.统计每个系的学生人数。
答：
selectsdept,count（*）fromstudentgroupbysdept
8.统计每门课程的选课人数和考试最高分。
答：
selectcno,count（*）,max（grade）fromscgroupbycno
9.统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。
答：
selectsno,count（*）,sum（grade）fromscgroupbysno
orderbycount（*）asc
10.查询总成绩超过200分的学生，要求列出学号和总成绩。
答：
selectsno,sum（grade）fromscgroupbysno
havingsum（grade）>200
11.查询选课门数超过2门的学生的学号、平均成绩和选课门数。
答：
selectsno,avg（grade）,count（*）fromschavingcount（*）>2
12.查询选了“C002”课程的学生的姓名和所在系。
答：
selectsname,sdeptfromstudentsjoinscons.sno=sc.sno
wherecno='C002'
13.查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。
答：
selectsname,cno,gradefromstudentsjoinscons.sno=sc.sno
wheregrade>80orderbygradedesc
14.查询计算机系男生修了“数据库基础”的学生的姓名、性别和成绩。
答：
selectsname,ssex,gradefromstudentsjoinscons.sno=sc.sno
joincoursecono=o
wheresdept='计算机系'andssex='男'andcname='数据库基础'
15.查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。
答：
selects.sno,sname,cno,gradefromstudents
leftjoinscons.sno=sc.sno
16.查询哪些课程没有人选，要求列出课程号和课程名。
答：
selecto,cnamefromcoursecleftjoinscono=o
whereoisnull
17．查询计算机系没有选课的学生，列出学生姓名。
答：
selectsnamefromstudentsleftjoinscons.sno=sc.sno
Wheresdept='计算机系'andsc.snoisnull
18.列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。
答：
selecttop3s.sno,sname,sdept,grade
fromStudentsjoinSCons.Sno=SC.Sno
joinCourseconc.Cno=SC.Cno
wherecname='数据库基础'
orderbygradedesc
19．查询VB考试成绩最低的学生的姓名、所在系和VB成绩。
答：
selecttop1withtiessname,sdept,gradefromstudents
joinscons.sno=sc.sno
joincoursecono=o
wherecname='VB'
orderbygradeasc
20.查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果放在一张新的永久表中，假设新表名为new_sc。
答：
selectsname,cname,gradeintonew_sc
fromstudentsjoinscons.sno=sc.sno
joincoursecono=o
wheregradeisnotnull
21.分别查询信息管理系和计算机系的学生的姓名、性别、修课名称、修课成绩，并要求将这两个查询结果合并成一个结果集，并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
答：
selectsdept系名,sname姓名,ssex性别,cname修课名称,grade修课成绩
fromstudentsjoinscons.sno=sc.sno
joincoursecono=o
wheresdept='信息管理系'
UNION
selectsdept,sname,ssex,cname,grade
fromstudentsjoinscons.sno=sc.sno
joincoursecono=o
wheresdept='计算机系'
22．查询选了VB的学生学号、姓名、所在系和成绩，并对所在系进行如下处理：
当所在系为“计算机系”时，显示“CS”；
当所在系为“信息管理系”时，显示“IS”；
当所在系为“通信工程系”时，显示“CO”；
对其他系，均显示“OTHER”。
答：
selects.sno学号,sname姓名,casesdept
when'计算机系'then'CS'
when'信息系'then'IS'
when'数学系'then'CO'
else'OTHER'
endas所在系,grade成绩
fromstudentsjoinscons.sno=sc.sno
joincoursecono=o
wherecname='vb'
23.用子查询实现如下查询:
（1）查询选了“C001”课程的学生姓名和所在系。
答：
selectsname,sdeptfromstudentwheresnoin（
selectsnofromscwherecno='C001'）
（2）查询通信工程系成绩80分以上的学生学号和姓名。
答：
selectsno,snamefromstudentwheresnoin（
selectsnofromscwheregrade>80）
andsdept='通信工程系'
（3）查询计算机系考试成绩最高的学生姓名。
答：
selectsnamefromstudentsjoinscons.sno=sc.sno
wheresdept='计算机系'andgrade=（
selectmax（grade）fromscjoinstudentsons.sno=sc.sno
wheresdept='计算机系'）
（4）查询年龄最大的男生的姓名和年龄。
答：
selectsname,sagefromstudent
Wheresage=（selectmax（sage）fromstudentandssex='男'）
andssex='男'
（5）查询“C001”课程的考试成绩高于“C001”课程的平均成绩的学生的学号和“C001”课程成绩。
答：
selectsno,gradefromscwherecno='C001'
Andgrade>（selectavg（grade）fromscwherecno='C001'）
24.创建一个新表，表名为test_t，其结构为：
（COL1,COL2,COL3），其中：
COL1：
整型，允许空值。
COL2：
字符型，长度为10，不允许空值。
COL3：
字符型，长度为10，允许空值。
试写出按行插入如下数据的语句（空白处表示空值）。
COL1
COL2
COL3
B1
1
B2
C2
2
B3
答：
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='C001'
26.将计算机系所有选修了“计算机文化学”课程的学生成绩加10分，分别用子查询和多表连接形式实现。
答：
（1）子查询
updatescsetgrade=grade+10
wheresnoin（
selectsnofromstudentwheresdept='计算机系'）
andcnoin（
selectcnofromcoursewherecname='计算机文化学'）
（2）多表连接
updatescsetgrade=grade+10
fromscjoinstudentsonsc.sno=s.sno
joincoursecono=o
wheresdept='计算机系'andcanem='计算机文化学'
27.删除修课成绩小于50分的学生的选课记录。
答：
deletefromscwheregrade<50
28.删除信息管理系考试成绩小于50分的学生的该门课程的修课纪录，分别用子查询和多表连接形式实现。
答：
（1）用连接查询实现
deletefromscfromscjoinstudentsons.sno=sc.sno
wheresdept='信息管理系'andgrade<50
（2）用子查询实现
deletefromscwheresnoin（
selectsnofromstudentwheresdept='信息管理系'）
andgrade<50
29．删除VB考试成绩最低的学生的VB修课记录。
答：
deletefromsc
wheregrade=（
selectmin（grade）fromsc
joincoursecono=o
wherecname='vb'）
andcnoin（
selectcnofromcoursewherecname='vb'）
四．上机练习
1．写出创建满足下述要求的视图的SQL语句，并执行这些语句。
将所写语句保存到一个文件中。
（1）查询学生的学号、姓名、所在系、课程号、课程名、课程学分。
答：
Createviewv1As
Selects.sno,sname,sdept,o,cname,credit
Fromstudentsjoinscons.sno=sc.sno
Joincoursecono=o
（2）查询学生的学号、姓名、选修的课程名和考试成绩。
答：
Createviewv2As
Selects.sno,sname,cname,grade
Fromstudentsjoinscons.sno=sc.sno
Joincoursecono=o
（3）统计每个学生的选课门数，列出学生学号和选课门数。
答：
Createviewv3As
Selectsno,count（*）astotal
Fromscgroupbysno
（4）统计每个学生的修课总学分，列出学生学号和总学分（说明：
考试成绩大于等于60才可获得此门课程的学分）。
答：
Createviewv4As
Selectsno,sum（credit）astotal_credit
Fromsnojoincoursecono=o
Wheregrade>=60
Groupbysno
2．利用第1题建立的视图，写出完成如下查询的SQL语句，并执行这些语句，查看执行结果。
将查询语句和执行结果保存到一个文件中。
（1）查询考试成绩大于等于90分的学生的姓名、课程名和成绩。
答：
Selectsname,cname,gradeFromv2wheregrade>=90
（2）查询选课门数超过3门的学生的学号和选课门数。
答：
Select*fromv3wheretotal>=3
（3）查询计算机系选课门数超过3门的学生的姓名和选课门数。
答：
Selectsname,totalfromv3joinstudentsons.sno=v3.sno
Wheresdept=‘计算机系’andtotal>=3
（4）查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分。
答：
Selectv4.sno,sname,sdept,total_credit
Fromv4joinstudentsons.sno=v4.sno
Wheretotal_credit>=10
（5）查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分。
答：
Selectsname,sage,sdept,total_credit
Fromv4joinstudentsons.sno=v4.sno
Wheresage>=20andtotal_credit>=10
3．修改第1题（4）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。
答：
Alterviewv4As
Selectsno,sum（credit）astotal_credit,count（*）astotal_cno
Fromscjoincoursecono=o
Groupbysno
4．写出实现下列操作的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．设有关系模式：
学生（学号，姓名，所在系，班号，班主任，系主任），其语义为：
一个学生只在一个系的一个班学习，一个系只有一个系主任，一个班只有一名班主任，一个系可以有多个班。
（1）请指出此关系模式的候选码。
（2）写出该关系模式的极小函数依赖集。
（3）该关系模式属于第几范式？
并简单说明理由。
（4）若不是第三范式的，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。
解：
（1）候选码：
学号
（2）学号→姓名，学号→所在系，学号→班号，班号→班主任，所在系→系主任
（3）第二范式，因为有：
学号→班号，班号→班主任，因此存在传递函数依赖：
学号
班主任
（4）第三范式关系模式：
学生（学号，姓名，所在系，班号），班号为引用班的外码，所在系为引用系的外码。
班（班号，班主任）
系（系名，系主任）
3．设有关系模式：
教师授课（课程号，课程名，学分，授课教师号，教师名，授课时数），其语义为：
一门课程（由课程号决定）有确定的课程名和学分，每名教师（由教师号决定）有确定的教师名，每门课程可以由多名教师讲授，每名教师也可以讲授多门课程，每名教师对每门课程有确定的授课时数。
（1）指出此关系模式的候选码。
（2）写出该关系模式的极小函数依赖集。
（3）该关系模式属于第几范式？
并简单说明理由
（4）若不属于第三范式，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。
答：
（1）候选码：
（课程号，授课教师号）
（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
学生（学号，姓名，性别，专业，社团号，参加日期），社团号为引用社团的外码。
∈3NF
2．设某工厂生产若干产品，每种产品由若干零件组成，同一种零件可用在不同的产品上。
零件由不同的原材料制成，不同的零件所用的原材料可以相同。
零件按所属产品的不同被分别存放在不同的仓库中，一个仓库可以存放多种不同的零件。
原材料按类别存放在若干仓库中，一个仓库也可以存放不同类别的材料。
画出该工厂的E-R图（注：
只画出实体和联系即可，不用标识属性）。
答：
①确定实体。
本系统共有四个实体：
产品、零件、材料、仓库。
因为只描述的是一个工厂的情况，因此不需要将工厂设为实体。
②确定实体间的联系。
⏹产品与零件：
多对多联系，可将联系命名为“组成”。
⏹零件与材料：
多对多联系，可将联系命名为“制成”。
⏹零件与仓库：
多对多联系，可将联系命名为“存放”。
⏹材料与仓库：
多对多联系，可将联系命名为“存放”。
③绘制E-R图
3．设要建立描述顾客在商店的购物情况的数据库应用系统，该系统有如下要求：
一个商店可有多名顾客购物，一个顾客可到多个商店购物，顾客每次购物有一个购物金额和购物日期。
规定每个顾客每天在每个商店最多有一次购物，每次购物可购买多种商品。
需要描述的“商店”信息包括：
商店编号、商店名、地址、联系电话；需要描述的顾客信息包括：
顾客号、姓名、住址、身份证号、性别。
请画出描述该应用系统的E-R图，并注明各实体的属性、标识属性以及联系的种类。
答：
4．
图7-28（a）~（d）所示为某企业信息管理系统中的局部E-R图，请将这些局部E-R图合并为一个全局E-R图，并指明各实
升级会员 