数据库习题1.docx
《数据库习题1.docx》由会员分享,可在线阅读,更多相关《数据库习题1.docx(26页珍藏版)》请在冰豆网上搜索。
数据库习题1
第2章
【1】查询所有计算机系的学生信息。
【2】查询成绩在90分以上的学生学号
【3】查询选修1号课程且成绩在90分以上的学生学号
【4】查询选修数据库原理且成绩在85分以上的学生姓名
【5】查询不选修1号课程的学生学号
【6】查询没有选修课程的学生学号
【7】查询选修1号和3号课程的学生学号
【8】查询选修全部课程的学生姓名。
【9】查询至少选修了S1所选的课程的学生学号。
答案:
(1)σSdept='计算机’(student)
(2)πSno(σGrade>90(SC))
(3)πSno(σGrade>90^Cno=’1’(SC))
(4)πSname(σGrade>85^Cname=’数据库原理’(StudentSCCourse))
(5)πSno(Student)-πSnoσCno=’1’(SC)
(6)πSno(Student)-πSno(SC)
(7)πSnoσCno=’1’(SC)∩πSnoσCno=’3’(SC)
(8)πSname((πSno,Cno(SC)÷πCno(Couse))Student))
(9)πSno,Cno(SC)÷πCno(σSno=’s1’(SC))
第3章
一、单表查询
【例1】输出学生表中的学生的姓名和年龄。
【例2】查询学生表中的所有数据。
【例3】查询选课的学生学号。
【例4】查询学生表中的系别。
【例5】查询学生表中的姓名和出生年份。
【例6】查询学生表中女生的信息。
【例7】在学生表中检索出年龄在18到20之间的学生信息。
【例8】查询姓王同学的记录。
【例9】查询学生表中计算机系男同学的学号、姓名。
【例10】查询全体男学生情况,要求结果按年龄降序排列。
【例11】查询学生总人数。
【例12】查询选课的学生人数。
【例13】统计系别的总数目。
【例14】统计计算机系学生的平均年龄。
【例15】统计每个系的学生人数。
【例16】查询选修3门以上课程的学生学号。
【例17】输出学生人数超过800人的系别和学生人数。
连接查询:
【例1】查询选课的学生学号和姓名。
【例2】查询每位学生的班长姓名。
【例3】查询选修数据库原理的学生学号。
【例4】查询选修数据库原理的学生学号和姓名。
【例6】查询选修3门以上课程的学生学号和姓名。
【例7】查询选修数据库原理的学生学号和成绩,并按成绩降序排列。
嵌套查询:
【例1】查询选修1号课程的学生学号和姓名。
【例2】查询刘雅所在系的学生信息。
【例3】查询没有选课的学生姓名。
【例4】查询其他系中比计算机系某一学生年龄大的学生姓名和年龄。
【例5】查询所有选修1号课程的学生学号和姓名。
(用存在量词EXIST)
【例6】查询没有选修1号课程的学生姓名。
【例7】查询选修全部课程的学生姓名。
【例8】查询至少选修了S1所选的课程的学生学号。
Sql综合练习
今天是你上班的第一天,职务是系部的教学秘书,职责之一是回答领导、老师、家长们的各种询问......
你的前任已经把数据库建好,数据库表也已输入过数据,你所知的情况如下:
在“学生”数据库中有三张表文件,表名以及主要字段名如下:
学生(学号、姓名、性别、年龄)年龄是数值型,其余为字符型
成绩(学号、课程号、成绩)成绩是数值型,其余为字符型
课程(课程号、课程名)均为字符型
现在请你利用在学校学过的知识,用SQL命令快速解决以下问题:
首先设置默认目录至“上机文件夹”
1、系里决定给年龄较小的同学开一个座谈会,请你查询所有年龄在19岁以下的学生姓名及年龄(提示:
用“学生”表)
2、辅导员要找考试不及格的同学谈话,请你查询考试成绩不及格的学生的学号和课程名(成绩,课程)
3、李华的父母来电询问李华的学习情况,请你查询李华所选修课程的课程号和成绩(学生,成绩)
4、有学生捡到一本书,上面只写了一个字“张”,请你查询所有姓张的学生信息
5、带003号课程的教师想要课程考试情况清单,请你查询选修了003号课程的学生的学号及成绩,结果按成绩的降序排列(成绩排序)
6、系里要开会总结学生选课率,请你查询选修了课程的学生人数(成绩计数)
7、001号课程是学校的精品课程,学校想知道这次考试中最高分是多少,请你查询选修001号课程的学生最高分数(成绩求最大值)
8、选修2门以上课程的同学可以申请不参加某些集体活动,请你查询选修了2门以上课程的学生学号(分组having计数)
9、辅导员想知道有哪些学生选了002号课程,请你查询所有选修了002号课程的学生姓名。
(in)
10、辅导员想知道有哪些学生没有选002号课程,查询没有选修002号课程的学生姓名.(notin)
11、有学生转学过来,请你向学生表中插入一条记录("48","刘佩佩",19,"女")
12、学号为48的学生还未报到,暂时将她的信息从学生表中删除
13、学号“12”的学生受到处分,所以将成绩表中学号为“12”的成绩减去10
【例】查询选课的学生学号。
SELECT学号,姓名;
FROM学生
【例】查询学生表中的所有数据。
SELECT*;
FROM学生
【例】输出学生表中的学生的姓名和年龄。
SELECT姓名,YEAR(DATE())-YEAR(出生日期)AS年龄;
FROM学生
【例】查询学生表中的系别。
SELECTDISTINCT系别;
FROM学生
【例】查询学生表中女生的信息。
【例】在学生表中检索出年龄在18到20之间的学生信息。
【例】查询姓王同学的记录。
SELECT*;
FROM学生;
WHERE姓名LIKE“王%”
【例】查询学生表中D06系男同学的学号、姓名。
SELECT学号,姓名;
FROM学生;
WHERE系别=“D01”AND性别=“男”
【例】查询全体男学生情况,要求结果按出生日期降序排列。
SELECT*;
FROM学生;
WHERE性别=“男”ORDERBY出生日期DESC
【例】查询学生总人数。
SELECTCOUNT(*);
FROM学生
【例】查询选课的学生人数。
【例】统计系别的总数目。
SELECTCOUNT(DISTINCT系别);
FROM学生
【例】统计借书的学生人数。
【例】统计D06系学生的平均年龄。
SELECTAVG(YEAR(DATE())-YEAR(出生日期)AS年龄;
FROM学生;
WHERE系别=’D06’
【例】统计每个系的学生人数。
SELECT系别,COUNT(*)AS学生人数;
FROM学生;
GROUPBY系别
【例】统计学生人数超过800人的系部。
SELECT系别,COUNT(*)AS学生人数;
FROM学生;
GROUPBY系别HAVINGCOUNT(*)>800
连接查询:
【例1】查询选课的学生学号和姓名。
SELECTdistinct,Sname
FROMStudent,SC
WHERE=
【例2】查询每位学生的班长姓名。
SELECT,
FROMStudentFIRST,StudentSECOND
WHERE=;
【例3】查询选修数据库原理的学生学号。
SELECTSno
FROMCourse,SC
WHERE=andCname=’数据库原理’
【例4】查询选修数据库原理的学生学号和姓名。
SELECTSno,Sname
FROMCourse,SC,Student
WHERE=and=andCname=’数据库原理’
【例6】查询选修3门以上课程的学生学号和姓名。
SELECTSno,Sname
FROMStudent
WHERESnoin(
SELECTSno
FROMSC
GroupbySnohavingcount(*)>=3)
【例7】查询选修数据库原理的学生学号和成绩,并按成绩降序排列。
SELECTSno,Grade
FROMCourse,SC
WHERE=andCname=’数据库原理’
ORDERbyGradeDESC
嵌套查询:
【例1】查询选修1号课程的学生学号和姓名。
SELECTSno,Sname
FROMStudent
WHERESnoin(
SELECTSno
FROMSC
WHERECno=’1’)
【例2】查询刘雅所在系的学生信息。
SELECT*
FROMStudent
WHERESdeptIN
(SELECTSdept
FROMStudent
WHERESname=‘刘雅’)
【例3】查询没有选课的学生姓名。
SELECTSname
FROMStudent
WHERENOTEXISTS
(SELECT*
FROMSC
WHERESno=);
【例4】查询其他系中比计算机系某一学生年龄大的学生姓名和年龄。
【例5】查询所有选修1号课程的学生学号和姓名。
(用存在量词EXIST)
【例6】查询没有选修1号课程的学生姓名。
【例7】查询选修全部课程的学生姓名。
【例8】查询至少选修了S1所选的课程的学生学号。
在“学生管理”数据库中有三张表文件,表名以及主要字段如下:
学生(学号、姓名、性别、年龄)年龄是数值型,其余为字符型
成绩(学号、课程号、成绩)成绩是数值型,其余为字符型
课程(课程号、课程名)均为字符型
注意在使用SQL命令前无需使用use命令打开表.
1、查询所有年龄在19岁以下的学生姓名及年龄
命令:
select姓名,年龄from学生where年龄<19
2、查询考试成绩不及格的学生的学号和课程名
命令:
select学号,课程名from课程,成绩;
where成绩<60.and.课程.课程号=成绩.课程号
3、查询李华所选修课程的课程号和成绩
命令:
Select课程号,成绩from成绩where学号in;
(select学号from学生where姓名="李华")
或:
Select课程号,成绩from学生,成绩;
Where姓名="李华"and学生.学号=成绩.学号
4、查询所有姓张的学生信息
命令:
select*from学生where姓名like“张%”
或:
select*from学生where姓名=“张”
5、查询选修了003号课程的学生的学号及成绩,结果按成绩的降序排列
命令:
select学号,成绩from成绩where课程号=”003”orderby成绩desc
6、查询选修了课程的学生人数
命令:
selectcount(distinct学号)from成绩
思考:
查询每门课程选修的学生人数.7、查询选修001号课程的学生最高分数
命令:
selectmax(成绩)from成绩where课程号="001"
8、查询选修了2门以上课程的学生学号
命令:
select学号,count(*)as选修门数;
from成绩groupby学号havingcount(*)>2
“as选修门数”意思是把计算结果声明成一个临时字段
9、查询所有选修了002号课程的学生姓名。
命令:
方法一:
select姓名from学生where学号in;
(select学号from成绩where课程号=”002”)
方法二:
用谓词(exists)查询
select姓名from学生whereexists;
(select*from成绩where课程号="002"and学号=学生.学号)
10、查询没有选修002号课程的学生姓名.
命令:
方法一:
select姓名from学生where学号notin;
(select学号from成绩where课程号=”002”)
方法二:
用谓词(exists)查询
select姓名from学生wherenotexists;
(select*from成绩where课程号="002"and学号=学生.学号)
11、向学生表中插入一条记录("48","刘佩佩",19,"女")
命令:
insertinto学生values("48","刘佩佩",19,"女")
12、删除学生表中学号为“48”的学生信息
命令:
deletfrom学生where学号="48"
13、将成绩表中学号为“12”的成绩减去8分
命令:
update成绩set成绩=成绩-8where学号=“12”
学生(学号、姓名、性别、年龄)
成绩(学号、课程号、成绩)
课程(课程号、课程名)
第五章
1.设有供应商关系S和零件关系P,如下图所示,S和P的主键分别是“供应商号”和“零件号”,P的外键是“供应商号”。
SP
供应商号
供应商名
所在城市
A1
A2
A3
A4
红星
宇宙
黎明
立新
北京
上海
天津
重庆
零件号
颜色
供应商号
P1
P2
P3
红
蓝
白
A1
A3
A4
分析下列3个操作能否被正确执行,并说明理由。
Ⅰ.在P中插入元组(’P2’,’白’,’A2’)
Ⅱ.在P中插入元组(NULL,’绿’,’A1’)
Ⅲ.在P中插入元组(’P4’,’白’,’A5’)
第六章
1.设关系模式R(S#,C#,GRADE,TNAME,TADDR),其属性分别表示学生学号、选修课的编号、成绩、任课教师姓名、教师地址等意义。
如果规定,每个学生每学一门课只有一个成绩;每门课只有一个教师任教;每个教师只有一个地址(此处不允许教师同名)。
(1)试写出关系模式R基本的函数依赖和候选键。
(2)试把R分解成3NF模式集,并说明理由。
2.设有关系模式R(C,T,H,I,S,G),其上的函数依赖集
F={CS→G,C→T,TH→I,HI→C,HS→I}
求:
(1)R的候选键;(2分)
(2)将R分解成3NF,并保持函数依赖性。
(2分)
3.有关系:
教学(学号、教工号、课程号)
假定每个学生可以选修多门课程,每门课程可以由多名学生来选修,每个老师可以讲授多门课程,每门课程只能由一个老师来讲授,那么该关系的主键是()
A.(课程号,教工号)B.(学号,课程号)
C.(学号,教工号)D.(学号,教工号,课程号)
1.在关系数据库中,从关系规范化的意义看,如果关系R中的非主属性对码有部分函数依赖,那么R至多是()
2.设有关系模式R(S,D,M),其函数依赖集:
F={S→D,D→M},则关系模式R的规范化程度最高达到()。
3.设有关系模式R(A,B,C,D),其数据依赖集:
F={(A,B)→C,C→D},则关系模式R的规范化程度最高达到()。
→Y,当下列哪一条成立时,称为平凡的函数依赖()。
∈Y∈X∩Y=Φ∩Y≠Φ
5.在关系模式R(A,B,C,D)中,存在函数依赖关系{A→B,A→C,A→D,(B,C)→A},则候选码是___________,关系模式R(A,B,C,D)属于____________
6在关系模式R(D,E,G)中,存在函数依赖关系{E→D,(D,G)→E},则候选码是__________,关系模式R(D,E,G)属于____________。
1.有关系模式R(ABC),F是R上成立的函数依赖集,F={A->B,C->B},写出R的候选码。
2.有关系模式R(ABCDE),F是R上成立的函数依赖集,F={AB->C,CD->E,DE->B},写出R的候选码。
3.设有关系模式R(A,B,C,D,E),F={A→C,C→D,B→C,DE→C,CE→A}。
求R的所有候选键。
4.关系模式R(ABC),F是R上成立的函数依赖集,F={B->C,C->A},那么分解ρ={AB,AC}是否为无损分解和保持函数依赖的分解
5.设有关系模式R(职工编号,日期,日营业额,部门名,部门经理),该模式记录了商店里每个职工的日营业额,以及职工所在的部门和经理信息。
如果规定:
每个职工每天只有一个营业额;每个职工只在一个部门工作;每个部门只有一个经理。
试回答下列问题:
(1)根据上述规定,写出模式R的基本FD和关键码;
(2)判断R是否达到3NF,若不是,请分解成3NF。
6.设有关系模式R(运动员编号,比赛项目,成绩,比赛类别,比赛主管),该模式记录了运动员比赛成绩及比赛类别、主管等信息。
如果规定:
每个运动员每参加一个项目只有一个成绩;每个比赛项目只属于一个比赛类别;每个比赛类别只有一个比赛主管。
试回答下列问题:
(1)根据上述规定,写出模式R的基本FD和关键码;
(2)判断R是否达到3NF,若不是,请分解成3NF。
7.假设某公司销售业务中使用的订单格式如下:
订单号:
1145订货日期:
09/15/2002客户名称:
ABC客户电话:
8141763
产品编号
品名
价格
数量
金额
A
电源
20
B
电表
40
C
卡尺
50
总金额:
公司的业务规定:
(1)订单号是唯一的,每张订单对应一个订单号;
(2)一张订单可以订购多种产品,每一种产品可以在多个订单中出现;
(3)一张订单有一个客户,且一个客户可以有多张订单;
(4)每一个产品编号对应一种产品的品名和价格;
(5)每一个客户有一个确定的名称和电话号码。
试根据上述表格和业务规则设计关系模式:
R(订单号,订货日期,客户名称,客户电话,产品编号,品名,价格,数量)问:
(1)写出R的基本函数依赖集。
(2)找出R的候选码。
(3)判断R最高可达到第几范式为什么、
(4)给出一个可能的3NF分解。
答案:
1.AC2.ADBADEADC3.BE
4.不具有无损连接也不具有函数依赖保持
5.
(1)F:
{(职工编号,日期)->日营业额,职工编号->部门名,部门名->部门经理}
关键码:
(职工编号,日期)
(2)未达到第3NF,分解为以下三个关系模式:
R1(职工编号,日期,日营业额)
R2(职工编号,部门名)
R3(部门名,部门经理)
6.
(1)F:
{(运动员编号,比赛项目)->成绩,比赛项目->比赛类别,比赛类别->比赛主管}
关键码:
(运动员编号,比赛项目)
(2)未达到第3NF,分解为以下三个关系模式:
R1(运动员编号,比赛项目,成绩)
R2(比赛项目,比赛类别)
R3(比赛类别,比赛主管)
7.
(1)F:
{订单号->(客户名称,订货日期),产品编号->(品名,价格),客户名称->客户电话,(订单号,产品编号)->数量}
(2)候选码:
(订单号,产品编号)
(3)R属于第1NF。
(4)R1(订单号,产品编号,数量)
R2(订单号,客户名称,订货日期)
R3(产品编号,品名,价格)
R4(客户名称,客户电话)
第七章
一、设某汽车运输公司数据库中有3个实体集。
描述车队的信息有:
车队号,车队名。
描述车辆的信息有:
牌照号,厂家,出厂日期。
描述司机的信息有:
司机编号,姓名,电话。
有关语义如下:
每个车队可聘用若干司机,但每个司机只能应聘于一个车队,车队聘用司机有个聘用期;每个车队可拥有若干车辆,但每辆车只能属于一个车队;每个司机可使用多辆汽车,每辆汽车可被多个司机使用,司机使用车辆有使用日期和公里数。
(1).试画出E—R图。
(2).将E—R图转换成关系模型,并指出每个关系模式的主码、外码。
(3).对关系模式进行分析并进行分解,使之3NF满足的要求。
二、将下图1所示的一个关于系、学生、学会等信息的E—R模型转换为关系模型,并给出每个关系模式的主码,以及提出可能存在的外码。
最后讨论每个关系模式是否是3NF的若不是,则要求将其规范化为3NF的。
1
系办公地点
姓名
年龄
系主任
m
n
系D
学会A
学生S
SA
DS
系号
学会名
地点
系名
性别
入会年份
学号
p
图1
三、假设要根据某商业集团的商店、商品和职工等信息建立一个数据库。
描述商店的信息有:
商店编号,商店名,地址。
描述商品的信息有:
商品号,商品名,规格,价格。
描述职工的信息有:
职工编号,姓名,性别。
有关语义如下:
每个商店可销售多种商品,每种商品也可放在多个商店销售,每个商店销售一种商品有月销售量;每个商店由许多职工,每个职工只能在一个商店工作,商店聘用职工有聘期和月薪。
1.试画出E—R图,并在图上注明属性及联系的类型。
(4分)
2.将E—R图转换成关系模型,并指出每个关系模式的主码、外码。
(4分)
3.对关系模式进行分析并进行分解,使之3NF满足的要求。
(4分)
四、建立一个关于产品生产加工情况的关系数据库。
其中,
描述车间的信息有:
车间号,车间名,电话。
描述设备的信息有:
设备号,设备名,型号。
描述产品的信息有:
产品号,产品名,规格,姓名,型号。
有关语义如下:
同一种产品,可以在不同的车间有不同的设备加工,加工工时可能不同;每一车间有多台设备,可以加工多种产品;同一种类型的设备有多台,分别归不同的车间所有和使用。
(1).试画出E—R图。
(2).将E—R图转换成关系模型,并指出每个关系模式的主码、外码。
(3).对关系模式进行分析并进行分解,使之3NF满足的要求。
五、设计一个图书馆数据库,此数据库中对每个借阅者保存读者记录,包括:
读者号,姓名,地址,性别,年龄,单位;对每本书存有:
书号,书名,作者,出版社;对每本借出的书存有读者号、借出日期和应还日期。
要求:
1.画出E—R图。
2.将E—R图转换成关系模型,并指出每个关系模式的主码。
六、假设某公司在多个地区设有销售部经销本公司的各种产品,每个销售部聘用多名职工,且每名职工只属于一个销售部。
销售部有部门名称、地区和电话等属性,产品有产品编码、品名和单价等属性,职工有职工号、姓名和性别等属性,每个销售部销售产品有数量属性。
(1)根据上述语义画出ER图,要求在图中画出属性并注明联系的类型。
(2)试将ER模型转换成关系模型,并指出每个关系模式的主键和外键。
七、某学校有若干系,每个系有若干学生,若干课程,每个学生选修若干课程,每门课有若干学生选修,每个学生选修每门课对应一个选修学期和成绩,某一门课可以为不同的系开设。
要建立该校学生选修课程的数据库。
系部信息:
系代号,系名,系主任姓名,电话
学生:
学号,姓名,年龄,性别
课程:
课程号,课程名
请设计:
1.关于此学校数据库的E-R模型;
2.将该E-R模型转换为关系模型,并指出每个关系模式的码及存在的外码;
3.判断每个关系模式是否达到第三范式,若不是,请分解。
八、某医院病房计算机管理中需要如下信息:
科室:
科室名称,科室地址,科室电话;
病房:
病房号,床位号,所属科室名;
医生:
姓名,职