上机练习.docx
- 文档编号:12014593
- 上传时间:2023-04-16
- 格式:DOCX
- 页数:22
- 大小:146.16KB
上机练习.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图,并指明各实
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 上机 练习