全国计算机三级数据库第二道大题及答案汇总.pdf

全国计算机三级数据库第二道大题汇总全国计算机三级数据库第二道大题汇总 1-2、设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单价） 。其中， 商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。 请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量（销售单价单价） 。要求商品号和年份为输入参 数，总毛利用输出参数返回。 CREATE PROCEDURE PRODUCT 商品号 int，年份 int，毛利 int output AS DECLARE 某商品销售量 int,某商品进价 int，某商品销售单价 int /*中间变量定义*/ BEGIN Select 某商品进价=单价 from 商品表 where 商品号=商品号 Select 某商品销售单价=销售单价，某商品销售量=count(*) from 销售表 where 商品号=商品号 and 销售时间=年份 IF 某商品进价 is NULL THEN /*判断该商品是否存在*/ ROLLBACK; RETURN; END IF IF 某商品销售单价 is NULL THEN /*判断该商品是否可卖*/ ROLLBACK; RETURN; END IF SET 毛利=（某商品销售单价-某商品进价）*某商品销售量 GO 【解题思路】 存储过程是由 PL/SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。使用 存储过程具有以下优点： 其已经编译和优化过了， 所以运行效率高， 提供了在服务器端快速执行 SQL 语句的有效途径； 存储过程降低了客户端和服务器之间的通信量；方便实施企业规则，当企业规则发生变化时只要修改存储过程，而无 需修改其他应用程序。 创建存储过程： create procedure 过程名 参数名 类型,参数名 类型 /*过程首部*/ As Declare/*as 下面对应的块为过程体*/ begin end 如上所示，存储过程包括过程首部和过程体。过程名是数据库服务器合法的对象标识；参数列表：用名字来标识调用时 给出的参数值，必须指定值的数据类型。参数可以是输入参数或输出参数，默认为输入参数。 2-2、在 SQL Server 2008 中，设某数据库中有商品表（商品号，商品名，进货价格），商品号为主码；销售表 （商品号，销售时间，销售数量，销售价格，本次利润），商品号和销售时间为主码，销售价格为本次销售商品的单 价。现要求每当在销售表中插入前 4 列数据时（假设一次只插入一行数据），系统自动计算本次销售产生的利润，并 将该利润赋给销售表的第 5 列“本次利润“。请编写实现上述功能的后触发型触发器代码。（10 分） 【参考答案】 CREATE TRIGGER calcu_product AFTER INSERT ON 销售表 FOR EACH ROW AS BEGIN DECLARE PurchasePrise float /*对应商品的进价的参数*/ SELECT PurchasePrise=进货价格 FROM 商品表 WHERE 商品号=new.商品号 UPDATE 销售表 SET 本次利润=new.销售数量*（new.销售价格-PurchasePrise ） WHERE 商品号=new.商品号 AND 销售时间=new.销售时间 /*因为是行级触发器，所以可以使用更新后的新值，用 new*/ END 【解题思路】 触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自 动激活相应的触发器，在 DBMS 核心层进行集中的完整性控制。触发器类似于约束，但比约束更加灵活，可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作，具有更精细和强大的数据控制能力。 触发器的创建格式： CREATE TRIGGER Schema-name.trigger-name ONtableview FOR|AFTERINSTEAD OF INSERT , UPDATE , DELETE ASSql-statement ; 对应于本题中，触发事件为在销售表中插入前四列，条件为无条件执行，触发体动作为计算本次利润并插入表中。 3-2、设在 SQL Server 2008 某数据库中，已建立了四个文件组：fg1、fg2、fg3 和 fg4，以及一个分区函数 RangePF1。 RangePF1 的定义代码如下： CREATE PARTITION FUNCTION RangePF1(int) AS RANGE LEFT FOR VALUES(100,200,300) （1）请基于 RangePF1 创建一个分区方案 RangePS1，每个分区对应一个文件组。 （5 分） （2）请使用 RangePS1 创建一个分区表 PartitionT(Gid, GoodName, Price)，该表基于 Gid 列创建分区。其中 Gid：int 类 型，取值大于等于 1；GoodName：统一字符编码可变长类型，最多可存储 20 个汉字；Price：定点小数类型，小数部分 1 位，整数部分到千位。 （5 分） （1）【参考答案】 create partition scheme RangePS1 as partition RangePF1 to (fg1, fg1, fg1, fg2) 【解题思路】 数据实际上是依附于表而存在，我们将表放入到文件组中，而文件组是一个逻辑的概念，其实体是辅助数据库文件 (ndf)，所以就等于将我们指定的数据放入到了指定的辅助数据库文件中，然后将这些辅助数据库文件放入不同的磁 盘分区中，就可以有针对性的对相应的数据实现性能的优化。 （2）【参考答案】 创建分区表： create table orders ( GID int identity(1,1) primary key, GoodName varchar(40), Price float ) on RangePS1（GID） 【解题思路】 创建分区表可通过以下几个步骤实现： 创建分区函数 创建分区方案 使用分区方案创建表 4-2、设在 SQL Server 2008 某数据库中有商品表和销售表，两个表的定义如下： CREATE TABLE 商品表( 商品号 char(10）PRIMARY KEY, 商品名 varchar(40), 类别 varchar(20), 进货单价 int ) CREATE TABLE 销售表( 商品号 char(10), 销售时间 datetime, 销售数量 int, 销售单价 int, PRIMARY KEY(商品号,销售时间）) 下面是一个用户定义的多语句表值函数， 它接受类别作为输入参数， 返回该类别下的每种商品在2012年的销售总利润， 并将结果按照销售总利润的降序输出。请补全该函数定义代码。 （10 分） CREATE FUNCTION f_Profit (lb char(10) 【1】ProfitTable【2】( 商品号 char(10), 总利润 int ) AS BEGIN INSERT INTO ProfitTable 【3】 【4】 END 【参考答案】 【1】RETURNS 【2】table 【3】 SELECT a.商品号,SUM(销售数量*(销售单价-进货单价) AS 总利润 FROM 销售表 a JOIN 商品表 b ON a.商品号=b.商品号 WHERE a.商品号 IN(SELECT 商品号 FROM 商品表 WHERE 类别=lb) GROUP BY a.商品号 ORDER BY 总利润 DESC 【4】RETURNProfitTable 【解题思路】 用户定义的多语句表值函数的命令格式为： CREATE FUNCTION schema_name. function name ( parameter_name AStype_schema_nameparameter_data_type = default ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN function_body RETURN END ; := ( ,n ) 题目要求返回指定类别下的每种商品在 2012 年的销售总利润，并且将结果按照销售总利润的降序输出。采用复合 SQL 语句的格式，先查出指定类别的所有商品号：SELECT 商品号 FROM 商品表 WHERE 类别=lb，其中lb 为函 数的传入参数，然后在销售表中用 GROUP BY 对商品号进行分组，并采用 SUM 计算每个分组的总和。 5-2、设有图书管理数据库，包含三张表： 图书明细表（图书编号，图书类别，图书名称，作者，出版社，出版日期，定价） ； 读者表（借书证号，姓名，系别，办证日期） ； 借出信息表（借出编号，借书证号，图书编号，借书日期） ； 完成下列操作： （10 分） 定义一个多语句表值函数，用于查询学生借书情况，只需提供参数：借书证号，就可以通过调用函数返回此学生的借书 情况，若有借书籍，则返回所借书籍的编号、书籍名称、定价和借书日期；若没有在借书籍，则返回记录为空。设函数 名为：f_BorrowBook(jszh char(20)。 【参考答案】 CREATE FUNCTION BorrowBook (jszh char(20) RETURNS jsqkb TABLE (书籍编号 char(20), 书籍名称 char(50), 定价 float, 借书日 期 datetime) AS BEGIN INSERT jsqkb SELECT 图书明细表.图书编号,图书名称,定价,借出信息表.借书日期 FROM 借出信息表,图书明细表 WHERE 借出信息表.图书编号=图书明细表.图书编号 AND 借出信息表.借书证号=jszh RETURN END 【解题思路】 SQL Server 2008 多语句表值函数的格式为： CREATE FUNCTION schema_name. function_name ( parameter_name AS type_schema_name.parameter_data_type = default ,.n ) RETURNS return_variable TABLE AS BEGIN function_body RETURN END ; := ( | table_constraint ,.n ) 采用三表联合查询，通过传入的借书证号查出借出信息表中的图书编号，然后查出图书明细表的信息。 6-2、某书店采用了 SQL Server 2008 数据库管理系统，该书店有一个需求，需要统计指定年份中每一本书的销售 总额，例如：查询 2012 年所有书的销售总额。 已知图书结构如下： 图书表(书号 BOOK_ID，书名 BOOK_NAME，单价 BOOK_PRICE) 销售表(书号 BOOK_ID，销售时间 SALE_TIME，销售数量 SALE_NUM)。 假设单价和销售数量均为 int 型，书号和书名均为 varchar(50)类型，销售时间为 datetime 型。请给出满足如下要求 的多语句表值函数，该函数统计指定年份中每本书的销售总额。（10 分） 设函数名为：BOOK_PROFIT(year int)，函数的返回结果格式如下： 书号 销售总额 B001 60000 A004 50000 【参考答案】 CREATE FUCNTION BOOK_PROFIT (year int) RETURNS f_ BOOK_PROFIT table( 书号 varchar(50)， 销售总额 int) AS BEGIN INSERT INTO f_ BOOK_PROFIT SELECT a.书号，SUM(单价*销售数量) FROM 图书表 a JOIN 销售表 b ON a.书号=b.书号 WHERE year(销售时间)=year GROUP BY a.书号 RETURN END 或者 CREATE FUCNTION BOOK_PROFIT (year int) RETURNS f_ BOOK_PROFIT table( BOOK_ID varchar(50)， PROFIT int) AS BEGIN INSERT INTO f_ BOOK_PROFIT SELECT a. BOOK_ID，SUM(BOOK_PRICE*SALE_NUM) FROM BOOK a JOIN SALE b ON a. BOOK_ID=b. BOOK_ID WHERE year(SALE_TIME)=year GROUP BY a. BOOK_ID RETURN END 【解题思路】 采用 JOIN 联合查询，先用 WHERE 条件查出符合销售时间=year 的记录，再将找出的记录和图书表合并，并采用单价* 销售数量计算出联合查询的表数据，最后根据 GROUP BY 统计每种书的销售价格总和。 7-2、设在采用 SQL Server 2008 数据库的图书馆应用系统中有三个基本表，表结构如下所示，请用 SQL 语句完成 下列两个查询： （1）检索至少借了 5 本书的同学的借书证号、姓名、系名和借书数量。（5 分） （2）检索借书和王丽同学所借图书中的任意一本相同的学生姓名、系名、书名和借书日期。（5 分） 【参考答案】 （1）SELECT LOANS.借书证号，姓名，系名，COUNT(*) AS 借书数量 FROM BORROWER,LOANS WHERE BORROWER.借书证号=LOANS.借书证号 GROUP BY LOANS.借书证号 HAVING COUNT(*)=5; （2）SELECT 姓名，系名，书名，借书日期 FROM BORROWER,LOANS , BOOKS WHERE BORROWER.借书证号=LOANS.借书证号 AND LOANS.图书馆登记号=BOOKS.图书登记号 AND 索书号 IN ( SELECT 索书号 FROM BORROWER,LOANS,BOOKS WHERE BORROW.借书证号=LOANS.借书证号 AND LOANS.图书馆登记号=BOOKS.图书登记号 AND 姓名=“王丽“ ) 【解题思路】 （1）采用两表联合查询。以两表“借书证号“为相等条件，在结果集中用 GROUP BY 按照“借书证号“来分类，并且用 HAVING 关键字统计出符合条件的记录数。 （2）采用 IN 关键字进行两表联合查询，在 BORROWER 和 LOANS 联合的结果集中查找满足第三个表指定的条件。 8-2、设在 SQL Server2008 某数据库中有商品表和销售表，两个表的定义如下： CREATE TABLE 商品表( 商品号 char(10) PRIMARY KEY, 商品名 varchar(40), 类别 varchar(20), 进货单价 int ); CREATETABLE 销售表( 商品号 char(10) REFERENCES 商品表(商品号), 销售时间 datetime, 销售数量 int, 销售单价 int, PRIMARYKEY(商品号,销售时间) ); 现要创建一个存储过程：查询指定类别的每种商品当前年销售总金额（销售总金额=销售单价* 销售数量）。请补全 下列代码。(10 分) CREATEPROC p_TotalProfit lb varchar(20) AS SELECT 商品名,(SELECT【1】FROM 销售表 t1 WHERE【2】and【3】= year(Getdate() AS 销售总金额 FROM 商品表 t2 WHERE【4】 参考答案】 【1】销售单价*SUM（销售数量) 【2】t1.商品号=t2.商品号 【3】t1.销售时间 【4】t2.类别=lb 【解题思路】 整个 select 语句的含义是： 根据 where 子句的条件表达式， 从 From 子句指定的基本表或视图中找出满足条件的元组， 再按 select 子句中的目标列表达式，选出元组中的属性值形成结果表。 该题中(SELECT【】 FROM 销售表 t1 WHERE 【】 and 【】= year(Getdate()是个嵌套，别名叫做销售总金额。 语句执行过程： 先从商品表中按照第二个 where 形成一个初步查询结果。 由于只有商品表中有类别， 因此可知 【4】 填写： t2.类别=lb(t2 是商品表的别名，t1 是销售表的别名)。由此可知该步是按给定类别查询。 销售总金额语句就应该在第一步的基础上求某年的总额。因此可以推断【3】是获得销售时间语句。因此该空填写 t1.销 售时间。由