SQL复习题1.doc

创建数据库、创建表、创建视图、创建触发器的代码保存在考试结果文件夹下，文件名为create.sql。1创建数据库为“商品供应系统”创建后台数据库：（1）数据库名为SPGY。（2）主数据文件逻辑名称为spgy_data，物理名称为spgy_data.mdf，存放在C盘根目录下，初始大小为3MB，文件增长不受限制，增长量为1MB。（3）事务日志文件逻辑名称为spgy_log，物理名称为spgy_log.ldf，存放在C盘根目录下，初始大小为1MB，文件增长最大为5MB，增长量为10%。2创建表在SPGY数据库中创建如下三张表，表结构如下：（1）表名：GYS字段名数据类型长度是否为空约束说明GYSHChar10否主键供应商号GYSMVarchar50否供应商名称GYSDVarchar50否供应商地址TELChar15是联系电话（2）表名：SP字段名类型长度是否为空约束说明TXMVarchar30否主键条形码SPMVarchar50否商品名称SPLXVarchar20是商品类型KCSLNumeric，精度10，小数位数2否默认值为0库存数量创建名为CK_SP的check约束，设置库存数量字段的值必须大于等于0。（3）表名：JG字段名类型长度是否为空约束说明TXMVarchar30否 主属性外键（参照SP表的TXM，关系名为FK_JG_SP）条形码LSJmoney否零售价HYJmoney否会员价QSSJDatetime否默认为当前系统时间Getdate()起始时间JZSJDatetime否主属性，默认为当前系统时间截止时间创建名为CK_JG的check约束，设置截止时间（JZSJ）必须大于等于起始时间（QSSJ）。创建名为CK_JG_1的check约束，设置零售价（LSJ）必须大于等于会员价（HYJ）。（4）表名：GY字段名类型长度是否为空约束说明GYHChar20否主属性供应号TXMVarchar30否主属性，外键（参照SP表的TXM，关系名为FK_GY_SP）条形码GYSHChar10是外键（参照GYS表的GYSH，关系名为FK_GY_GYS）供应商号GYSLNumeric，精度10，小数位数2否供应数量GYSJDatetime否默认为当前系统时间：getdate（）供应时间JJmoney是进价创建名为CK_GY的check约束，设置供应数量（GYSL）字段的值必须大于0。3.创建视图（1）视图View1：查询“精品红富士”本年的供应情况。（2）视图View2：查询“精品红富士”最近一次进货的进价。提示：可按照进货时间排序。（3）视图View3：查询库存充足的商品基本信息，库存充足指现库存量大于100。该视图定义加密，即带with encryption参数。（4）视图View4：查询统计各商品所有供应的供应总数量,给查询结果的字段取别名，为：条形码、供应总数量。（5）视图View5：查询统计各类商品的商品数目。给查询结果的字段取别名，为：商品类型、商品数目。4.创建触发器创建触发器，实现当有商品供应时，该商品的现库存数量自动增加。触发器名为TJGY。提示：在GY表中创建insert触发。不用判断SP表中是否有该商品信息。CREATE TRIGGER TJGY ON dbo.gy FOR INSERTASDeclare gysl numeric(10,2),txm varchar(30)Select gysl=gysl,txm=txm from insertedUpdate sp set kcsl=kcsl+gysl Where TXM=txm创建触发器scgy，实现当删除某次供应信息时（即删除供应表的某条记录），库存自动更新。提示：在供应表中创建delete触发器。CREATE TRIGGER scgy ON dbo.gy FOR deleteASDeclare gysl numeric(10,2),txm varchar(30)Select gysl=gysl,txm=txm from deletedUpdate sp set kcsl=kcsl-gysl Where TXM=txm创建触发器xxgy，实现当修改某次供应信息时（修改供应数量或者商品条形码），库存自动更新。提示：在供应表中创建update触发器。CREATE TRIGGER xxgy ON dbo.gy FOR UPDATEASIf update(GYSL) or update(TXM)BeginDeclare yuangysl numeric(10,2), xingysl numeric(10,2),yuantxm varchar(30), xintxm varchar(30)Select yuangysl=gysl,yuantxm=txm from deletedSelect xingysl=gysl,xintxm=txm from insertedUpdate sp set kcsl=kcsl-yuangysl Where TXM=yuantxmUpdate sp set kcsl=kcsl+xingysl Where TXM=xintxmend5.在查询分析中编写SQL脚本，代码保存在考生文件夹中。文件名为1. SQL（1）往GY表中添加如下记录。GY表中的数据GYHTXMGYSLJJGYSJ20070012000000341316803.002009-6-120070016930504300198301.702010-6-1（2）查询本月各商品的供应情况，查询结果包括GYH、GYSH、GYSL、GYSJ。（3）根据实际情况为SP表的SPM字段创建索引，索引名为SPINDEX。（4）将“精品红富士”的库存数量增加10。（5）查询所有供应信息，结果按照商品条形码降