SQL查询、编程习题

关系代数、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.punMoneyσvehicleNo='赣A001088'(Driver⋈Vehicle⋈PunishMaster⋈PunishDetail⋈ViolationType⋈Policeman) （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 @punishMony<500 rollback end fetch myCur into @noticeNo,@modeNo,@punishMony end close myCur deallocate myCurend关系代数、SQL查询、编程综合训练（二）   假设有三张表：    C（cno，cname）   SC（sno，cno，g）   S（sno，sname，sex，sdate，scity，smz，stotal） 1、为SC建立trgger，当对SC表更新时自动修改S表中的stotal内容。

stotal为该同学的总分）要求使用游标和循环语句编程create trigger sc_update on scfor updateasbegin declare @sno char(5),@cno char(3) declare @oldGrade tinyint,@newGrade tinyint declare getGrade cursor for select sno,cno,grade from inserted open getGrade fetch getGrade into @sno,@cno,@newGrade while (@@fetch_status=0) begin select @oldGrade = grade from deleted where sno=@sno and cno=@cno update student set sstotal=sstotal+@newGrade-@oldGrade where sno=@sno fetch getGrade into @sno,@cno,@newGrade end close getGrade deallocate getGradeendcreate trigger sc_update on scfor updateasbegin declare @sno char(5),@cno char(3) declare @oldGrade tinyint,@newGrade tinyint declare getGrade cursor for select sno,cno,grade from inserted open getGrade fetch getGrade into @sno,@cno,@newGrade while (@@fetch_status=0) begin update student set sstotal=sstotal+@newGrade where sno=@sno fetch getGrade into @sno,@cno,@newGrade end close getGrade deallocate getGrade declare getGrade1 cursor for select sno,cno,grade from deleted open getGrade1 fetch getGrade1 into @sno,@cno,@oldGrade while (@@fetch_status=0) begin update student set sstotal=sstotal-@oldGrade where sno=@sno fetch getGrade1 into @sno,@cno,@oldGrade end close getGrade1 deallocate getGrade1end2、 创建一个存储过程，当输入任何一门课程号时显示该门课程同学选课的同学学号、姓名和成绩，如果该同学为少数民族同学，显示时成绩自动加5分，最后计算该门课程的平均成绩（要求用光标和循环语句编程）create procedure proSearchByCno (@cno char(3))asbegin declare @sno char(5),@grade tinyint declare @sname char(10),@smz char(10) declare @count tinyint,@sum decimal(7,2) select @count=0,@sum=0 declare mySearch cursor for select sno,grade from sc where cno=@cno open mySearch fetch mySearch into @sno,@grade while (@@fetch_status=0) begin select @sname=sname,@smz=smz from student where sno=@sno if @smz != '汉族' set @grade=@grade+5 select @sno,@sname,@smz,@grade set @count=@count+1 set @sum=@sum+@grade fetch mySearch into @sno,@grade end close mySearch deallocate mySearch if @count=0 select '平均分：0' else select '平均分：'+str(@sum/@count,7,2)end3、 用SQL语句完成①查找同时选修了《计算机原理》和《数据库原理》课程的同学姓名及选课成绩。

解法一：select sname,cname,gfrom s a, sc b, c cwhere a.sno=b.sno and o=o and cname='数据库原理' and a.sno in (select sno from sc x,c y where o=o and ame='计算机原理')unionselect sname,cname,gfrom s a, sc b, c cwhere a.sno=b.sno and o=o and cname='计算机原理' and a.sno in (select sno from sc x,c y where o=o and ame='数据库原理')解法二：select sname,cname,gfrom s a, sc b, c cwhere a.sno=b.sno and o=o and a.sno in (select sno from sc x,c y where o=o and ame='计算机原理') and a.sno in (select sno from sc x,c y where o=o and ame='数据库原理')解法三：select sname,cname,gfrom s a, sc b, c cwhere a.sno=b.sno and o=o and cname='数据库原理' and a.sno in (select sno from sc x,c y where o=o and ame='计算机原理')解法四：select sname,cname,gfrom s a, sc b, c cwhere a.sno=b.sno and o=o and a.sno in (select x.sno from sc x,sc y where x.sno=y.sno and o in (select cno from c where cname='数据库原理') and o in ( select cno from c where cname='计算机原理') )结果同解法二：解法五：select sname,cname,gfrom s a, sc b, c cwhere a.sno=b.sno and o=o and a.sno in (select x.sno from sc x,sc y where x.sno=y.sno and o in (select cno from c where cname='数据库原理') and o in ( select cno from c where cname='计算机原理') ) and (o in (select cno from c where cname='数据库原理' or cname='计算机原理') )　②查找少数民族同学的总分和平均分（按学号分组）。

select a.sno,sum(g),avg(g)from s a,sc bwhere smz !='汉族' and a.sno=b.snogroup by a.sno③查找每个城市至少有10个同学以上的城市名称及同学人数select scity,count(*)from sgroup by scityhaving count(*)>2④将选修了《计算机原理》课程的同学每人加5分update sc set g=g+5from sc a,c bwhere o=o and cname='计算机原理' 'update s set stotal=stotal+5from sc a,c b,s cwhere o=o and cname='计算机原理' and c.sno=a.sno⑤统计student表中的总分update s set stotal=gradefrom s a,(select sno,sum(g) grade from sc group by sno) bwhere a.sno=b.sno。