2022年SQL数据库作业 .pdf

第二次书面作业要求：使用学校要求统一的作业纸（包括封面），作业只能手写，不可以打印（打印为零分）。下周上小课交给小课老师。第 1 题：人力资源部门决定希望能保留求职者的历史记录，以便于在新职位出现时可联系过去未成功的求职者， 如果随着时间的推移他们申请了不同的职位，则可以比较求职者提供的信息。 在与人力资源团队进行协商之后，高级数据库开发人员决定需要一个新表来存储这些历史信息。高级数据库开发人员要求您：创 建 一 个 名 为HumanResources.JobCandidateHistory 的 新 表 。JobCandidateHistory 表有以下列和约束：JobCandidateID 。不可包含null 值的 int 列。此列中的值必须是惟一的。Resume。可包含 null 值的 nvarcher 列。Rating。不可包含null 值的 int 列。此列中的值必须介于1 到 10 之间，默认值为5。RejectedDate 。不可包含null 值的 datetime 列。ContactID 。可包含null 值的 int 列。此列是Person.Contact 表中的ContactID 列的外键。测试 JobCandidateHistory 表和约束在 HumanResources.JobCandidate 表上创建一个名为dJobCandidate 的新的DELETE 触发器，它在有人删除求职者时将该求职者的信息复制到HumanResources.JobCandidateHistory 表中。JobCandidateID 和 Resume 列必须直接复制过去， 并且 RejectedDate 通过使用getdate 函数设置为当前日期。Rating 应保留为其默认值，并且ContactDetails 应设置为NULL。CREATETABLE HumanResources. JobCandidateHistory ( JobCandidateID intNOT NULLprimarykey , Resume varchar( 20 )NULL, Rating INTNOT NULLCHECK( Rating=1 AND Rating=1 AND Rating=10 )DEFAULT( Rating=5), RejectedDate datetimeNOT NULL, ContactDetails varchar( 20 )NULL ) CREATETRIGGER dJobCandidate ON HumanResources. JobCandidate AFTER DELETE ASBEGIN SETNOCOUNT ON DECLARE JobCandidateID INT DECLARE Resume varchar( 20 ) DECLARE RejectedDate datetime SELECTJobCandidateID=DELETED. JobCandidateID, Resume=DELETED . Resume, RejectedDate=GETDATE() INSERTINTO HumanResources. JobCandidateHistory ( JobCandidateID,Resume , RejectedDate) VALUES( JobCandidateID, Resume, RejectedDate) END第 2 题：用下述语句创建视图CREATE VIEW ProductsView AS SELECT ProductID, Price, Company FROM Supplier INNER JOIN Products ON Suppliers.ID = Products.SupplierID ORDER BY ProductID 出现错误，为什么，如何修改。请写出原因与修改后的创建视图的T-SQL命令。ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，去掉 ORDER BY。CREATEVIEW ProductsView AS名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 6 页 - - - - - - - - - SELECT ProductID, Price, Company FROM Supplier INNERJOIN Products ON Suppliers. ID = Products. SupplierID第 3 题：Adventure Works 维护着一份各种产品在全年中的特价供应和折扣的列表，该列表同时适用于客户和分销商。目前，此信息只能直接从Sales.SpecialOffer 表中获得。一项新提出的要求是能够 使用存储过程和用户定义函数检索此信息，并且使用存储过程插入新的特价供应。要求如下：1. 在 Sales 架构中创建名为GetDiscountsForCategoryAndDate 的存储过程，该过程接受Category 参数，此参数为nvarchar 数据类型，并且最多接受50 个字符，和另一个DateToCheck datetime 输入参数。o DateToCheck 参数必须能够接受NULL 默认值。如果为DateToCheck 参数指定了NULL 值，则使用GETDATE 函数将该参数值设置为当前日期和时间。o 该过程从Sales.SpecialOffer 中检索以下列：Description、DiscountPct、Type、Category、StartDate、EndDate、MinQty 和MaxQty 。应该基于Category 和 DateToCheck 参数筛选行。CREATEPROC Sales. GetDiscountsForCategoryAndDate Category varchar( 50 ), DateToCheck datetimeAS IF ( DateToCheck ISNULL) SELECT DateToCheck=GETDATE()SELECTDescription, DiscountPct, Type , Category, StartDate, EndDate, MinQty, MaxQty FROM Sales. SpecialOffer WHERE Category=Category ANDStartDate=DateToCheck 2.在 Sales 架构中创建名为AddDiscount 的存储过程，该过程向Sales.SpecialOffer 表插入新记录。下表指定了插入操作所需要的参数。参数名数据类型（除非另有指定，否则都为输入参数）Descriptionnvarchar(255)DiscountPctsmallmoney名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 6 页 - - - - - - - - - Typenvarchar(50)Categorynvarchar(50)StartDate datetime EndDate datetime MinQty int MaxQty int NewProductID int OUTPUT 该INSERT 语句必须由相应的错误处理进行保护，并且任何错误都必须记录在dbo.ErrorLog 表 中 。 如 果 新 的 插 入 成 功 ， 则NewProductID 参 数 必 须 以SCOPE_IDENTITY 函数值进行更新。返回值还必须指示插入是成功还是失败。BEGINTRY CREATEPROC Sales. AddDiscount Description nvarchar( 255 ) DiscountPct smallmoney Type nvarchar( 50 ) Category nvarchar( 50 ) StartDate datetime EndDate datetime MinQty int MaxQty int NewProductID intOUTPUT RES varchar( 2) AS INSERTINTO Sales. SpecialOffer ( UserID,Name, Sex , Birth, Mobile,Address , Email) VALUES( Description, DiscountPct, Type , Category, StartDate,EndDate , MinQty , MaxQty , NewProductID) execSCOPE_IDENTITY NewProductID END TRY BEGINCATCH INSERTINTO dbo . ErrorLog END CATCH名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 6 页 - - - - - - - - - 3.在 Sales 架构中创建名为GetDiscountedProducts 的多语句表值用户定义函数，该函数使用复杂查询检索有折扣的产品。此复杂查询将现成提供给您。该函数接受IncludeHistory bit 参数以根据是需要折扣历史信息还是只需要当前信息来筛选返回的表。返回的表将包含下列定义。列名数据类型ProductIDintNamenvarchar(50)ListPricemoneyDiscountDescription nvarchar(255) DiscountPercentage smallmoney DiscountAmount money DiscountedPrice money CREATEFUNCTION Sales. GetDiscountedProducts( IncludeHistory bit) RETURNS TABLE AS BEGIN RETURN ( SELECTProductID, Name, ListPrice, DiscountDescription, DiscountPercentage, DiscountAmount, DiscountedPrice FROM Sales. SpecialOffer)第 4 题：高级AdventureWorks 数据库管理员要求你实现AdventureWorks 数据库的灾难恢复策略。该策略必须满足下列要求：需要可重用的备份设备。创建一个名为“AdventureWorksBak ”的备份设备，存放在 D 盘的 Backups 文件夹下，保存的文件名为“AdventureWorks.bak ”将 AdventureWorks 数据库进行完整备份和日志备份，备份到AdventureWorksBak设备上。对 AdverntureWorks 数据库进行一次差异备份。AdventureWorks 数据库文件若损坏了，就有必要进行还原该数据库操作。请将AdventureWorks 数据库还原到最后一次差异备份。USE master GO EXEC master. dbo . sp_addumpdevice devtype = Ndisk, logicalname = N AdventureWorks.bak, physicalname =ND:BackupsAdventureWorks.bak 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 6 页 - - - - - - - - - GO BACKUP DATABASE AdventureWorks TO AdventureWorks.bak WITHNOFORMAT , NOINIT,NAME = N AdventureWorks-完整数据库备份 , SKIP,NOREWIND, NOUNLOAD, STATS = 10 GO BACKUP DATABASE AdventureWorks TO AdventureWorks.bak WITHDIFFERENTIAL , NOFORMAT, NOINIT,NAME = N AdventureWorks-差异数据库备份 , SKIP, NOREWIND , NOUNLOAD , STATS = 10 GO RESTORE DATABASE AdventureWorks FROMDISK=ND:BackupsAdventureWorks.bakWITHFILE= 1 , NORECOVERY ,NOUNLOAD,REPLACE, STATS = 10 GO RESTORE DATABASE AdventureWorks FROMDISK=ND:BackupsAdventureWorks.bakWITHFILE= 2 , NOUNLOAD ,REPLACE, STATS = 10 GO 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 6 页 - - - - - - - - -