SQL查询、编程习题.doc

关系代数、SQL查询、编程综合训练（一）1、现有交通违章处罚管理ER图如题1所示。驾驶员车辆违章处罚单违章类型警察驾驶违章处罚明细违章日期违章地点处罚日期处罚金额处罚 图1交通违章处罚ER图 根据ER图，设计数据库相关表结构为：驾驶员表Driver驾照编号驾驶员姓名性别电话住址信誉度driverNodriverNamesextelephoneaddresscreditchar(7)Varchar(10)Char(2)Char(10)Varchar(50)Char(1) 车辆表Vehicle车牌号车辆型号生产厂家生产日期购买时间价格vehicleNovehicleModefactorymakeDatebuyDatepriceChar(7)Varchar(30)Varchar(30)datetimedatetimeNumeric(8, 2)警察表Policeman警察编号警察姓名所属部门所属地区policeNopoliceNamedepartmentareaChar(7)Varchar(10)Varchar(30)Varchar(30)违章类型ViolationType违章类型编号违章类型规定处罚金额vioTypeNovioTypepunMoneyChar(3)Varchar(20)Numeric(8, 2)违章类型包括：闯红灯、逆行、超速、超载、醉驾等。违章处罚单主表PunishMaster罚单编号驾照编号车牌号警察编号违章日期违章地点违章描述处罚日期处罚总额punNodriverNovehicleNopoliceNovioDateplacevioDescribepunDatepunTotalChar(7)Char(7)Char(7)Char(7)datetimeVarchar(40)Varchar(50)datetimeNumeric(8, 2)违章处罚单明细表PunishDetail罚单编号违章类型编号处罚金额（实际处罚金额）punNovioTypeNopunMoneyChar(7)Char(3)Numeric(8, 2)注：一次违章处罚可能包含多种违章类型。请完成下面各题：（1）关系代数查询车牌号为“赣A001088”车辆的车辆型号以及该车的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名、处罚总额和违章类型、处罚金额。（2）SQL查询查询车牌号为“赣A001088”车辆的车辆型号以及该车的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名、处罚总额和违章类型、处罚金额。查询被违章处罚车辆中没有“闯红灯”和“醉驾”违章的车辆的车辆型号以及它的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名和违章类型。（3）存储过程创建存储过程proTotal()，不使用聚合函数，统计有“醉驾”违章的驾驶员的驾照编号、驾驶员姓名以及他所驾驶车辆的违章次数序号、车牌号、违章日期和违章类型、处罚金额，按如下格式输出（仅输出有“醉驾”违章驾驶员的违章信息）。驾照编号 驾驶员姓名P000005 李宏伟No 车牌号 违章日期 违章类型 处罚金额1 赣A001088 2009-12-30 醉驾 500.001 赣A001088 2009-12-30 超速 200.002 赣A002566 2010-02-16 醉驾 800.00总次数 总罚款金额3 2300.00（4）触发器设计插入触发器insTrigger，保证“醉驾”违章的实际处罚金额不得低于500元。关系代数、SQL查询、编程综合训练参考答案（一）1、现有交通违章处罚管理ER图如题1所示。驾驶员车辆违章处罚单违章类型警察驾驶违章处罚明细违章日期违章地点处罚日期处罚金额处罚 图1交通违章处罚ER图 根据ER图，设计数据库相关表结构为：驾驶员表Driver驾照编号驾驶员姓名性别电话住址信誉度driverNodriverNamesextelephoneaddresscreditchar(7)Varchar(10)Char(2)Char(10)Varchar(50)Char(1) 车辆表Vehicle车牌号车辆型号生产厂家生产日期购买时间价格vehicleNovehicleModefactorymakeDatebuyDatepriceChar(7)Varchar(30)Varchar(30)datetimedatetimeNumeric(8, 2)警察表Policeman警察编号警察姓名所属部门所属地区policeNopoliceNamedepartmentareaChar(7)Varchar(10)Varchar(30)Varchar(30)违章类型ViolationType违章类型编号违章类型规定处罚金额vioTypeNovioTypepunMoneyChar(3)Varchar(20)Numeric(8, 2)违章类型包括：闯红灯、逆行、超速、超载、醉驾等。违章处罚单主表PunishMaster罚单编号驾照编号车牌号警察编号违章日期违章地点违章描述处罚日期处罚总额punNodriverNovehicleNopoliceNovioDateplacevioDescribepunDatepunTotalChar(7)Char(7)Char(7)Char(7)datetimeVarchar(40)Varchar(50)datetimeNumeric(8, 2)违章处罚单明细表PunishDetail罚单编号违章类型编号处罚金额（实际处罚金额）punNovioTypeNopunMoneyChar(7)Char(3)Numeric(8, 2)注：一次违章处罚可能包含多种违章类型。请完成下面各题：（1）关系代数查询车牌号为“赣A001088”车辆的车辆型号以及该车的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名、处罚总额和违章类型、处罚金额。vehicleMode ,vioDate, driverNo, driverName, place, policeName ，punTotal, vioType, PunishDetail.punMoneyvehicleNo=赣A001088(DriverVehiclePunishMasterPunishDetailViolationTypePoliceman) （2）SQL查询查询车牌号为“赣A001088”车辆的车辆型号以及该车的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名、处罚总额和违章类型、处罚金额。select vehicleMode ,vioDate, c.driverNo, driverName, place, policeName ，punTotal, vioType, d.punMoneyfrom Driver a,Vehicle b,PunishMaster c,PunishDetail d, ViolationType e, Policeman fwhere a.driverNo=c.driverNo and b.vehicleNo=c.vehicleNo and c. punNo =d. punNo and d. vioTypeNo =e. vioTypeNo and c. policeNo=f. policeNoand b.vehicleNo=赣A001088查询被违章处罚车辆中没有“闯红灯”和“醉驾”违章的车辆的车辆型号以及它的违章日期、驾照编号、驾驶员姓名、违章地点、警察姓名和违章类型。select vehicleMode ,vioDate, c.driverNo, driverName, place, policeName ，vioTypefrom Driver a,Vehicle b,PunishMaster c,PunishDetail d,ViolationType e,Policeman fwhere a.driverNo=c.driverNo and b.vehicleNo=c.vehicleNo and c. punNo =d. punNo and d. vioTypeNo =e. vioTypeNoand c.policeNo=f.policeNo and b.vehicleNo not in ( select vehicleNo from PunishMaster c,PunishDetail d, ViolationType e where c. punNo =d. punNo and d. vioTypeNo =e. vioTypeNoand e. vioType =闯红灯) and b.vehicleNo not in ( select vehicleNo from PunishMaster c,PunishDetail d, ViolationType e where c. punNo =d. punNo and d. vioTypeNo =e. vioTypeNoand e. vioType =醉驾)（3）存储过程创建存储过程proTotal()，不使用聚合函数，统计有“醉驾”违章的驾驶员的驾照编号、驾驶员姓名以及他所驾驶车辆的违章次数序号、车牌号、违章日期和违章类型、处罚金额，按如下格式输出（仅输出有“醉驾”违章驾驶员的违章信息）。驾照编号 驾驶员姓名P000005 李宏伟No 车牌号 违章日期 违章类型 处罚金额1 赣A001088 2009-12-30 醉驾 500.001 赣A001088 2009-12-30 超速 200.002 赣A002566 2010-02-16 醉驾 800.00总次数 总罚款金额3 2300.00create procedure proTotalasbegin declare driverNo char(7),driverName varchar(30),vehicleNo char(7) declare peccancyDate datetime,peccancyDescribe varchar(50) declare punishMoney numeric(8,2),count tinyint,sumMoney numeric(10,2) declare oldDriverNo char(7),punNo char(7), oldPunNo char(7) declare myCur cursor for select a.driverNo,driverName,c.vehicleNo, vioDate, vioType, d.punMoney, c. punNo from Driver a,PunishMaster c,PunishDetail d, ViolationType e where a.driverNo=c.driverNo and c. punNo =d. punNo and d. vioTypeNo =e. vioTypeNo and vioType =醉驾 order by a.driverNo,c. punNo open myCur fetch myCur into 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 = punNoset count=1while(fetch_status=0 and 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 fetch myCur into driverNo,driverName,vehicleNo,peccancyDate, peccancyDescribe,punishMoney, punNo end select count 总次数,sumMoney 总罚款金额 set oldDriverNo=driverNo end close myCur deallocate myCurend（4）触发器设计插入触发器insTrigger，保证“醉驾”违章的实际处罚金额不得低于500元。create trigger insTriggeron PunishDetailfor insertasbegin declare noticeNo char(7),modeNo char(3),punishMony numeric(5,1) declare myCur cursor for select * from inserted open myCur fetch myCur into noticeNo,modeNo,punishMony while(fetch_status=0) begin if (exists (select * from ViolationType where vioTypeNo =modeNo and vioType =醉驾) begin if punish