sql期末复习题.doc

本次考试以“订单管理”数据库为基础，六个数据表分别为：1、 销售人员表销售工号部门号姓名地址电话性别char(4)char(2)VARCHAR(10)VARCHAR(50)VARCHAR(13)ch ar(2)2、 客户信息表客户编号姓名地址电话char(8)VARCHAR(10)VARCHAR(50)VARCHAR(13)3、 供应商信息表供应商编码名称联系人地址电话char(6)VARCHAR(50)VARCHAR(10)VARCHAR(50)VARCHAR(13)4、 货品信息表货品编码名称库存量供应商编码状态售价成本价char(24)CHAR(20)INTchar(6)BITMONEY,MONEY5、 部门信息表部门号名称经理人数char(2)CHAR(20)char(4)INT6、 订单信息表订单号销售工号货品编码客户编号数量总金额订货日期交货日期char(8)char(4)char(24)char(8)INTMONEYDATETIMEDATETIME一、问答1、表一和表二分别表示两个关系R1和R2，试判断 （1）R1是否为2NF? 若不是，怎样将之改为2NF?不是2NF 把姓名删除 ( 2) R2是否为3NF？若不是，怎样将之改为3NF?不是3NF 把专业号或者是专业名删除表一学号姓名课程号成绩学分01张三00280302李斯003903表二专业号课程号课程名称专业名学分01003C语言计算机应用302002数据库软件32、请写出E-R图的三要素，结合下图说明教师这一实体的属性和存在的联系。三要素：1、实体 2、属性3、联系矩形：实体 椭圆：属性 菱形：联系教师的属性：姓名和专业号编号名称学号姓名班级课目课程教师授课学生姓名专业号nn1n3、简述数据完整性的完整含义数据完整性包含三个含义：实体完整性、域完整性、约束含义实体完整性：1、实体不能少，2、实体不能多域完整性：1、实体属性的含义是不可再分的 2、实体属性的起值不能有两义约束含义：1、违约性约束 2、检查性约束 3、操作约束4、 何谓索引？它有哪些好处？索引是一个在表上或视图上创建的独立的物理数据库结构，在视图上创建索引只能针对架构绑定的视图好处：1、加快数据查询 2、加快表的连接、排序和分组工作5、简述如何备份和恢复数据库。备份数据库第一步：利用sp_addumpdevice语句创建备份设备。第二步：利用BACKUO DATABASE 语句备份数据库恢复数据库第一步:恢复数据库前的准备(验证、断开用户、查看日志）第二步：利用restore database语句恢复数据库。第三步：根据需要恢复日志、文件组等二、填空1、 create table 部门信息 （ 部门编号 CHAR(2) PRIMARY KEY ， -定义为主键 名称 VARCHAR(20) NOT NULL ， -非空约束 经理 char(4) FOREIGN KEY REFERENCES 销售人员（销售工号） -定义为外键，主键是销售人员表中的销售工号 ）2、以下语句将销售人员表中销售工号为3的员工姓名改为“张三” update 销售人员 set 姓名=张三 where 销售工号 3、 以下语句显示订单信息表中的前20条记录，请填写完整 SELECT TOP 20 货品编码 AS 货品, 数量 AS 订购量 FROM 订单信息4、以下语句查找订货数量大于20的订单信息，请填写完整 SELECT * FROM 订单信息 WHERE 数量20 5、以下语句查找订货日期在2008/01/01到2009/01/01之间的订单信息SELECT * FROM 订单信息 WHERE 订货日期 BETWEEN 2008/01/01 AND 2009/01/01 6、以下语句实现对订单信息根据货品编码来分组，并求出同一货品编码的订货数量之和，请填写完整。SELECT 货品编码, 订货数量=SUM(数量) FROM 订单信息 Group by 货品编码 7、 以下语句查找同一货品编码中的最大订单 SELECT * FROM 订单信息 AS a WHERE 数量= ALL (SELECT 数量 FROM 订单信息 AS b WHERE a.货品编码=b.货品编码) 8、以下程序实现显示当前日期，请填写完整 USE 订单管理GODECLARE todayDate char(10)Set todayDate=GETDATE( ) PRINT todayDate9、以下程序根据变量SelectKey的值，求TableNam的值，并根据该值打开相应的数据表，请填写完整USE 订单信息GODECLARE TableNam VARCHAR(20), SelectKey VARCHAR(6)SET SelectKey = 供应 SELECT TableNam = CASE WHEN SelectKey LIKE %客户%THEN客户信息 WHEN SelectKey LIKE %销售%THEN销售人员 WHEN SelectKey LIKE %供应%THEN供应商信息 ELSE NULL ENDIF TableNam IS NULL PRINT 没有找到对应的表！ELSE EXECUTE(SELECT * FROM +TableNam) GO10、以下程序定义一个后触发器，当修改客户信息表中的客户编码时，显示相应的信息，并禁止修改。请填写完整。USE 订单管理GOCREATE TGRIGGER Check_CstNo ON 客户信息AFTER UPDATE ASIF UPDATE(编号) BEGIN RAISERROR(客户编号不能进行修改！,7,2)-显示信息 ROLLBACK TRANSACTION ENDGO三、判断（T）1、数据库中的数据可为用户共享。（F）2、SQL Server默认安装示例数据库。（F）3、定义money数据需要给出数据长度。（F）4、“delete 客户信息” 语句将删除数据表“客户信息”（T）5、“DROP 订单信息”语句将删除数据表“订单信息”（T）6、视图中的数据可以被修改（F）7、视图就是数据表8、以下是SQL语句，请判断是否书写正确？若不正确，请改正。（1）i=i+1 改成SETi=i+1（2）select sum(数量）from 订单信息 where 销售工号=01 （3）select * from 订单信息 where 销售工号=销售工号改成as a inner join 订单信息 as b where a. 订单信息=b. 订单信息（4）PRINT 今天的日期+2010+12+26改成今天的日期+2010+12+26（5）SELECT 货品编码, 订货数量=SUM(数量) FROM 订单信息 WHERE 订货日期 = 2008/01/01 -记录筛选GROUP BY 货品编码 HAVING COUNT(销售工号)1改成SELECT *FROM 订单信息 GROUP BY 货品编码 HAVING COUNT(货品编码)1（6）Declare i int,#sum_1 intwhile i=10 begin set sum_1=sum_1+1 set i=i+1 end改成while i= 2008/01/01 GROUP BY 销售工号HAVING COUNT(销售工号)1 ORDER BY 销售工号3、查询供应商信息中来自南京的相关信息，并形成一个新的数据表“南京供应商”4、找出订货量大于10的货品信息 select 货品编码from 订单信息as a where(select SUM(数量) from 订单信息as b where a. 货品编码=b. 货品编码)10select * from 货品信息where 货品编码IN (select 货品编码from 订单信息as a where(select SUM(数量) from 订单信息as b where a. 货品编码=b. 货品编码)10)5、查询每种货品订货量大于最小一次订货量的订单信息 select *from 订单信息as a where 数量any(select 数量from 订单信息as b where a.货品编码=b.货品编码)6、查找每种货品订货量最小的订单信息 select *from 订单信息as a where 数量=all(select 数量from 订单信息as b where a.货品编码=b.货品编码)8、利用COMPUTE子句对同一货品编码的订货数量进行求和。SELECT * FROM 订单信息WHERE 订货日期= 2008/01/01 ORDER BY 货品编码compute SUM(数量) BY 货品编码9、统计每个销售人员的销售业绩的销售业绩 select sum(总金额)from 订单信息 group by 销售工号10、比较两个数的大小 declare var1 int,var2 intset var1=58set var2=80if var1!=var2if var1var2 select第一个数比第二个数大else select第一个数比第二个数小else select两个数相同11、张三的生日为1988/8/18，使用日期函数计算张三的年龄和天数，并输出。 Declare var!1 char(14)Set var1=1998/8/18 Print 年龄 +getdiff(yy ,var1,getdate)Print 天数+getdiff(dd,var1,getdate)12、创建销售人员的视图并将其中销售工号为1的人员姓名改为“余则成”UPDATE 销售人员视图SET 姓名=余则成 WHERE 销售工号=113、利用视图实现查询每种货品订货量大于最小一次订货量的订单信息SELECT * FROM 销售人员视图GOcreate view 订单信息视图asselect *from 订单信息goselect *from 订单信息视图as a where 数量any(select 数量from 订单信息视图as b where a.货品编码=b.货品编码)14、使用游标的cursor_rows变量，计算“客户信息”表中客户的数量。假设每个客户有一个唯一的纪录 DECLARE customers CURSOR FOR SELECT 地址FROM 客户信息OPEN customers -打开游标DECLARE Address VARCHAR(50), City VARCHAR(20)DECLARE Loop BIT, Count INTSET City=深圳SET Count=0IF ERROR = 0 BEGIN WHILE 1=1-控制循环 BEGIN FETCH NEXT FROM customers INTO Address IF FETCH_STATUS = 0 -判断读入是否正确 BEGIN IF CHARINDEX(City, Address)0SET Count=Count+1 END ELSE BREAK END PRINT 表中+City+的客户数量为：+CONVERT(VARCHAR(3), Count) ENDCLOSE customers-关闭游标DEALLOCATE customers-释放游标15、创建一个简单的存储过程，查询销售工号=09的订单信息 goIF EXISTS(SELECT name FROM sysobjects WHERE name=proc_订单信息 AND type=P)DROP PROCEDURE DispTabGOcreate proc proc_订单信息scale_no char(4)asselect *from 订单信息where 销售工号=scale_nogoexec proc_订单信息08goIF EXISTS(SELECT name FROM sysobjects WHERE name=DispTab AND type=P)DROP PROCEDURE DispTab16、创建一个存储过程，实现统计“客户信息”表中指定城市客户的数量，用输入参数来指定城市，用RETURN返回数量declare customers cursorfor select 地址from 客户信息open customersdeclare address varchar(50)declare loop bitdeclare count intset count=0if error=0 begin whILE 1=1 begin fetch next from customers into address if fetch_status=0 begin if charindex(city,address)0 set count=count+1 end else BREAK end print 表中+city+的客户数量为：+convert(varchar(3),count) endclose customersdeallocate customersset countout=countRETURN COUNTgo declare count1 int,count