SQL查询编程习题.docx
- 文档编号:30058027
- 上传时间:2023-08-04
- 格式:DOCX
- 页数:19
- 大小:149.45KB
SQL查询编程习题.docx
《SQL查询编程习题.docx》由会员分享,可在线阅读,更多相关《SQL查询编程习题.docx(19页珍藏版)》请在冰豆网上搜索。
SQL查询编程习题
关系代数、SQL查询、编程综合训练
(一)
1、现有交通违章处罚管理ER图如题1所示。
图1交通违章处罚ER图
根据ER图,设计数据库相关表结构为:
驾驶员表Driver
驾照编号
驾驶员姓名
性别
电话
住址
信誉度
driverNo
driverName
sex
telephone
address
credit
char(7)
Varchar(10)
Char
(2)
Char(10)
Varchar(50)
Char
(1)
车辆表Vehicle
车牌号
车辆型号
生产厂家
生产日期
购买时间
价格
vehicleNo
vehicleMode
factory
makeDate
buyDate
price
Char(7)
Varchar(30)
Varchar(30)
datetime
datetime
Numeric(8,2)
警察表Policeman
警察编号
警察姓名
所属部门
所属地区
policeNo
policeName
department
area
Char(7)
Varchar(10)
Varchar(30)
Varchar(30)
违章类型ViolationType
违章类型编号
违章类型
规定处罚金额
vioTypeNo
vioType
punMoney
Char(3)
Varchar(20)
Numeric(8,2)
违章类型包括:
闯红灯、逆行、超速、超载、醉驾等。
违章处罚单主表PunishMaster
罚单编号
驾照编号
车牌号
警察编号
违章日期
违章地点
违章描述
处罚日期
处罚总额
punNo
driverNo
vehicleNo
policeNo
vioDate
place
vioDescribe
punDate
punTotal
Char(7)
Char(7)
Char(7)
Char(7)
datetime
Varchar(40)
Varchar(50)
datetime
Numeric(8,2)
违章处罚单明细表PunishDetail
罚单编号
违章类型编号
处罚金额(实际处罚金额)
punNo
vioTypeNo
punMoney
Char(7)
Char(3)
Numeric(8,2)
注:
一次违章处罚可能包含多种违章类型。
请完成下面各题:
(1)关系代数
查询车牌号为“赣A001088”车辆的车辆型号以及该车的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名、处罚总额和违章类型、处罚金额。
(2)SQL查询
查询车牌号为“赣A001088”车辆的车辆型号以及该车的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名、处罚总额和违章类型、处罚金额。
查询被违章处罚车辆中没有“闯红灯”和“醉驾”违章的车辆的车辆型号以及它的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名和违章类型。
(3)存储过程
创建存储过程proTotal(),不使用聚合函数,统计有“醉驾”违章的驾驶员的驾照编号、驾驶员姓名以及他所驾驶车辆的违章次数序号、车牌号、违章日期和违章类型、处罚金额,按如下格式输出(仅输出有“醉驾”违章驾驶员的违章信息)。
驾照编号驾驶员姓名
P000005李宏伟
No车牌号违章日期违章类型处罚金额
1赣A0010882009-12-30醉驾500.00
1赣A0010882009-12-30超速200.00
2赣A0025662010-02-16醉驾800.00
……
总次数总罚款金额
32300.00
(4)触发器
设计插入触发器insTrigger,保证“醉驾”违章的实际处罚金额不得低于500元。
关系代数、SQL查询、编程综合训练参考答案
(一)
1、现有交通违章处罚管理ER图如题1所示。
图1交通违章处罚ER图
根据ER图,设计数据库相关表结构为:
驾驶员表Driver
驾照编号
驾驶员姓名
性别
电话
住址
信誉度
driverNo
driverName
sex
telephone
address
credit
char(7)
Varchar(10)
Char
(2)
Char(10)
Varchar(50)
Char
(1)
车辆表Vehicle
车牌号
车辆型号
生产厂家
生产日期
购买时间
价格
vehicleNo
vehicleMode
factory
makeDate
buyDate
price
Char(7)
Varchar(30)
Varchar(30)
datetime
datetime
Numeric(8,2)
警察表Policeman
警察编号
警察姓名
所属部门
所属地区
policeNo
policeName
department
area
Char(7)
Varchar(10)
Varchar(30)
Varchar(30)
违章类型ViolationType
违章类型编号
违章类型
规定处罚金额
vioTypeNo
vioType
punMoney
Char(3)
Varchar(20)
Numeric(8,2)
违章类型包括:
闯红灯、逆行、超速、超载、醉驾等。
违章处罚单主表PunishMaster
罚单编号
驾照编号
车牌号
警察编号
违章日期
违章地点
违章描述
处罚日期
处罚总额
punNo
driverNo
vehicleNo
policeNo
vioDate
place
vioDescribe
punDate
punTotal
Char(7)
Char(7)
Char(7)
Char(7)
datetime
Varchar(40)
Varchar(50)
datetime
Numeric(8,2)
违章处罚单明细表PunishDetail
罚单编号
违章类型编号
处罚金额(实际处罚金额)
punNo
vioTypeNo
punMoney
Char(7)
Char(3)
Numeric(8,2)
注:
一次违章处罚可能包含多种违章类型。
请完成下面各题:
(1)关系代数
查询车牌号为“赣A001088”车辆的车辆型号以及该车的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名、处罚总额和违章类型、处罚金额。
∏vehicleMode,vioDate,driverNo,driverName,place,policeName,punTotal,vioType,PunishDetail.punMoney
σvehicleNo='赣A001088'(Driver⋈Vehicle⋈PunishMaster⋈PunishDetail⋈ViolationType⋈Policeman)
(2)SQL查询
查询车牌号为“赣A001088”车辆的车辆型号以及该车的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名、处罚总额和违章类型、处罚金额。
selectvehicleMode,vioDate,c.driverNo,driverName,place,policeName,
punTotal,vioType,d.punMoney
fromDrivera,Vehicleb,PunishMasterc,PunishDetaild,ViolationTypee,Policemanf
wherea.driverNo=c.driverNoandb.vehicleNo=c.vehicleNo
andc.punNo=d.punNoandd.vioTypeNo=e.vioTypeNoandc.policeNo=f.policeNo
andb.vehicleNo='赣A001088'
查询被违章处罚车辆中没有“闯红灯”和“醉驾”违章的车辆的车辆型号以及它的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名和违章类型。
selectvehicleMode,vioDate,c.driverNo,driverName,place,policeName,vioType
fromDrivera,Vehicleb,PunishMasterc,PunishDetaild,
ViolationTypee,Policemanf
wherea.driverNo=c.driverNoandb.vehicleNo=c.vehicleNo
andc.punNo=d.punNoandd.vioTypeNo=e.vioTypeNo
andc.policeNo=f.policeNo
andb.vehicleNonotin(
selectvehicleNo
fromPunishMasterc,PunishDetaild,ViolationTypee
wherec.punNo=d.punNoandd.vioTypeNo=e.vioTypeNo
ande.vioType='闯红灯')
andb.vehicleNonotin(
selectvehicleNo
fromPunishMasterc,PunishDetaild,ViolationTypee
wherec.punNo=d.punNoandd.vioTypeNo=e.vioTypeNo
ande.vioType='醉驾')
(3)存储过程
创建存储过程proTotal(),不使用聚合函数,统计有“醉驾”违章的驾驶员的驾照编号、驾驶员姓名以及他所驾驶车辆的违章次数序号、车牌号、违章日期和违章类型、处罚金额,按如下格式输出(仅输出有“醉驾”违章驾驶员的违章信息)。
驾照编号驾驶员姓名
P000005李宏伟
No车牌号违章日期违章类型处罚金额
1赣A0010882009-12-30醉驾500.00
1赣A0010882009-12-30超速200.00
2赣A0025662010-02-16醉驾800.00
……
……
总次数总罚款金额
32300.00
createprocedureproTotal
as
begin
declare@driverNochar(7),@driverNamevarchar(30),@vehicleNochar(7)
declare@peccancyDatedatetime,@peccancyDescribevarchar(50)
declare@punishMoneynumeric(8,2),@counttinyint,@sumMoneynumeric(10,2)
declare@oldDriverNochar(7),@punNochar(7),@oldPunNochar(7)
declaremyCurcursorfor
selecta.driverNo,driverName,c.vehicleNo,vioDate,
vioType,d.punMoney,c.punNo
fromDrivera,PunishMasterc,PunishDetaild,ViolationTypee
wherea.driverNo=c.driverNoandc.punNo=d.punNo
andd.vioTypeNo=e.vioTypeNoandvioType='醉驾'
orderbya.driverNo,c.punNo
openmyCur
fetchmyCurinto@driverNo,@driverName,@vehicleNo,@peccancyDate,
@peccancyDescribe,@punishMoney,@punNo
set@oldDriverNo=@driverNo
while(@@fetch_status=0)
begin
set@sumMoney=0
select@driverNo执照号,@driverName驾驶员姓名
select'No车牌号','违章日期','违章类型','处罚金额'
set@oldPunNo=@punNo
set@count=1
while(@@fetch_status=0and@oldDriverNo=@driverNo)
begin
if@oldPunNo<>@punNo
begin
set@count=@count+1
set@oldPunNo=@punNo
end
select@count,@vehicleNo,@peccancyDate,@peccancyDescribe,@punishMoney
set@sumMoney=@sumMoney+@punishMoney
fetchmyCurinto@driverNo,@driverName,@vehicleNo,@peccancyDate,
@peccancyDescribe,@punishMoney,@punNo
end
select@count总次数,@sumMoney总罚款金额
set@oldDriverNo=@driverNo
end
closemyCur
deallocatemyCur
end
(4)触发器
设计插入触发器insTrigger,保证“醉驾”违章的实际处罚金额不得低于500元。
createtriggerinsTrigger
onPunishDetail
forinsert
as
begin
declare@noticeNochar(7),@modeNochar(3),@punishMonynumeric(5,1)
declaremyCurcursorfor
select*
frominserted
openmyCur
fetchmyCurinto@noticeNo,@modeNo,@punishMony
while(@@fetch_status=0)
begin
if(exists(select*fromViolationType
wherevioTypeNo=@modeNoandvioType='醉驾'))
begin
if@punishMony<500
rollback
end
fetchmyCurinto@noticeNo,@modeNo,@punishMony
end
closemyCur
deallocatemyCur
end
关系代数、SQL查询、编程综合训练
(二)
假设有三张表:
C(cno,cname)
SC(sno,cno,g)
S(sno,sname,sex,sdate,scity,smz,stotal)
1、为SC建立trgger,当对SC表更新时自动修改S表中的stotal内容。
(stotal为该同学的总分)要求使用游标和循环语句编程。
createtriggersc_updateonsc
forupdate
as
begin
declare@snochar(5),@cnochar(3)
declare@oldGradetinyint,@newGradetinyint
declaregetGradecursorfor
selectsno,cno,grade
frominserted
opengetGrade
fetchgetGradeinto@sno,@cno,@newGrade
while(@@fetch_status=0)
begin
select@oldGrade=grade
fromdeleted
wheresno=@snoandcno=@cno
updatestudentset
sstotal=sstotal+@newGrade-@oldGrade
wheresno=@sno
fetchgetGradeinto@sno,@cno,@newGrade
end
closegetGrade
deallocategetGrade
end
createtriggersc_updateonsc
forupdate
as
begin
declare@snochar(5),@cnochar(3)
declare@oldGradetinyint,@newGradetinyint
declaregetGradecursorfor
selectsno,cno,grade
frominserted
opengetGrade
fetchgetGradeinto@sno,@cno,@newGrade
while(@@fetch_status=0)
begin
updatestudentset
sstotal=sstotal+@newGrade
wheresno=@sno
fetchgetGradeinto@sno,@cno,@newGrade
end
closegetGrade
deallocategetGrade
declaregetGrade1cursorfor
selectsno,cno,grade
fromdeleted
opengetGrade1
fetchgetGrade1into@sno,@cno,@oldGrade
while(@@fetch_status=0)
begin
updatestudentset
sstotal=sstotal-@oldGrade
wheresno=@sno
fetchgetGrade1into@sno,@cno,@oldGrade
end
closegetGrade1
deallocategetGrade1
end
2、创建一个存储过程,当输入任何一门课程号时显示该门课程同学选课的同学学号、姓名和成绩,如果该同学为少数民族同学,显示时成绩自动加5分,最后计算该门课程的平均成绩(要求用光标和循环语句编程)
createprocedureproSearchByCno(@cnochar(3))
as
begin
declare@snochar(5),@gradetinyint
declare@snamechar(10),@smzchar(10)
declare@counttinyint,@sumdecimal(7,2)
select@count=0,@sum=0
declaremySearchcursorfor
selectsno,gradefromsc
wherecno=@cno
openmySearch
fetchmySearchinto@sno,@grade
while(@@fetch_status=0)
begin
select@sname=sname,@smz=smzfromstudent
wheresno=@sno
if@smz!
='汉族'
set@grade=@grade+5
select@sno,@sname,@smz,@grade
set@count=@count+1
set@sum=@sum+@grade
fetchmySearchinto@sno,@grade
end
closemySearch
deallocatemySearch
if@count=0
select'平均分:
0'
else
select'平均分:
'+str(@sum/@count,7,2)
end
3、用SQL语句完成
①查找同时选修了《计算机原理》和《数据库原理》课程的同学姓名及选课成绩。
解法一:
selectsname,cname,g
fromsa,scb,cc
wherea.sno=b.snoando=o
andcname='数据库原理'anda.snoin
(selectsno
fromscx,cy
whereo=oandame='计算机原理')
union
selectsname,cname,g
fromsa,scb,cc
wherea.sno=b.snoando=o
andcname='计算机原理'anda.snoin
(selectsno
fromscx,cy
whereo=oandame='数据库原理')
解法二:
selectsname,cname,g
fromsa,scb,cc
wherea.sno=b.snoando=o
anda.snoin
(selectsno
fromscx,cy
whereo=oandame='计算机原理')
anda.snoin
(selectsno
fromscx,cy
whereo=oandame='数据库原理')
解法三:
selectsname,cname,g
fromsa,scb,cc
wherea.sno=b.snoando=o
andcname='数据库原理'anda.snoin
(selectsno
fromscx,cy
whereo=oandame='计算机原理')
解法四:
selectsname,cname,g
fromsa,scb,cc
wherea.sno=b.snoando=oand
a.snoin
(selectx.sno
fromscx,scy
wherex.sno=y.snoand
x.c
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 查询 编程 习题