SQL Server数据库技术及应用(第2版) 习题答案.docx

1、习题答案子项目 1 安装和体验数据库一、选择题1.C2.C3.D4. A5.D二、填空题1. 工作组版、开发人员版、开发版2. 关系型3. 控制台命令、“计算机管理”对话框窗口、SQL Server 配置管理器。4.MSSQL Server5.网络协议三、简答题1.（1）数据库管理系统（DBMS）：是一种操纵和管理数据库的软件，是用于建立、使用和维护数据库。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。（2） 数据库（DB）：是由文件管理系统发展起来的，是依照某种数据模型组织起来的数据集合。这种数据集合具有如下特点：尽可能不重复，以最优方式为某个特定组织的多种应用服务，其数据结

2、构独立于使用它的应用程序，对数据的增、删、改和检索由统一软件进行管理和控制。（3） 数据库系统（DBS）：是存储介质、处理对象和管理系统的集合体，通常由软件、数据库和数据管理员组成。（4） 数据库管理员（DBA）：负责创建、监控和维护整个数据库，使数据能被任何有权使用的人有效使用。2. 常见数据库管理系统除了 Microsoft SQL Server 外，还有甲骨文公司的Oracle 系统，IBM 公司的 DB2 系统和 Informix 系统，赛贝斯公司的 Sybase ASE 系统，微软公司 Access 系统，以及 MySQL 公司的 MySQL 开源数据库系统。3. 需要在本地计算机上

3、注册远程计算机的SQL Server 数据库服务器。成功注册服务器的前提条件：首先确保服务器端的 SQL Server 数据库服务已经启动，其次在SQL Server 配置管理器中已经启用 TCP/IP 通讯协议。子项目 2 创建教学管理系统数据库与数据表一、选择题1.D 2.C 3.A 4.A 5.A 6.B 7.C 8.A 9.D 10.D 11.A 12.D二、填空题1. int2. 引用（参照）完整性3. 输入列的值4. 主键5. 检查约束 、PRIMARYKEYS三、实训操作1、CREATE DATABASE library1 ON PRIMARY() LOG ON (NAME =

4、library,FILENAME = c:librarylibrary.mdf, SIZE = 5MB,MAXSIZE = 1000MBNAME = library_log,FILENAME = c:librarylibrary_log.ldf, MAXSIZE = 5MB,FILEGROWTH = 10%) GO2、USE Library1 GOCREATE TABLE Readers (RIDINTNOT NULL,-读者编号RNameNVARCHAR(50) NOT NULL,-读者姓名RSex RTypeIdCHAR(2)INTNOT NULL,NOT NULL,-读者性别-读者类型编

5、号RAddressNVARCHAR(50)NULL,-家庭住址Email) GONVARCHAR(50)NULL-电子邮件CREATE TABLE ReaderType (RTypeId RTypeINTIDENTITY(1,1) NVARCHAR(30)NOT NULL, NOT NULL,-读者类型编号-读者类型名称NumTINYINT) GOCREATE TABLE Books (NOT NULL-可借数量BIDINTIDENTITY(1,1)TitleNVARCHAR(200)AuthorNVARCHAR(50)PubIdINTNOT NULL, NOT NULL, NOT NULL,

6、 NOT NULL,-图书编号-图书名称-图书作者-出版社编号PubDateSMALLDATETIME ISBNNVARCHAR(200)PriceMONEYCategoryIdINT) GONOT NULL,-出版时间NOT NULL,-ISBNNOT NULL,-单价NOT NULL-图书种类编号CREATE TABLE BookCategory (CateoryIdINTIDENTITY(1,1) NOT NULL,-图书类型编号NameNVARCHAR(20)NOT NULL-图书类型名称) GOCREATE TABLE Publishers (PIDINTIDENTITY(1,1)

7、NOT NULL,-出版社编号PNameNVARCHAR(50)NULL-出版社名称) GOCREATE TABLE Borrow (RIDINTNOT NULL,-读者编号BIDINTNOT NULL,-图书编号LendDateDATETIMENOT NULL,-借阅日期ReturnDateDATETIMENULL,-实际归还日期) GOCREATE TABLE Penalty (RIDINTNOT NULL,-读者编号BIDINTNOT NULL,-图书编号PDateDATETIMENOT NULL,-罚款日期PTypeINTNOT NULL,-罚款类型Amount MONEYNULL-罚

8、款金额) GO3、-创建各类约束-Reader 表的约束-RSexchar(2)只取男、女，默认值为男 读者性别ALTER TABLE ReadersADD CONSTRAINT CK_RSex CHECK(RSex=男 OR RSex=女)ALTER TABLE ReadersADD CONSTRAINT DF_RSex DEFAULT 男 FOR RSexALTER TABLE ReadersADD CONSTRAINT DF_Raddress DEFAULT 地址不详 FOR Raddress ALTER TABLE ReadersADD CONSTRAINT CK_Email CHEC

9、K(Email LIKE %)ALTER TABLE ReadersADD CONSTRAINT PK_RID PRIMARY KEY (RID)-ReaderType 的约束ALTER TABLE ReaderTypeADD CONSTRAINT PK_RTypeId PRIMARY KEY (RTypeId)GOALTER TABLE ReaderTypeADD CONSTRAINT CK_Num CHECK (Num=0)GO-Books 的约束ALTER TABLE BooksADD CONSTRAINT PK_BID PRIMARY KEY (BID) GOALTER TABLE B

10、ooksADD CONSTRAINT CK_PubDate CHECK (PubDate0) GO-BookCategory 的约束ALTER TABLE BookCategoryADD CONSTRAINT PK_CateoryId PRIMARY KEY (CateoryId) ALTER TABLE PublishersADD CONSTRAINT PK_PID PRIMARY KEY (PID) GO-图书借阅信息表 Borrow 表中的约束ALTER TABLE BorrowADD CONSTRAINT PK_borrow PRIMARY KEY(RID,BID,LendDate)A

11、LTER TABLE BorrowADD CONSTRAINT DF_LendDate DEFAULT GETDATE() FOR LendDate-图书罚款信息表 Penalty 中的约束ALTER TABLE PenaltyADD CONSTRAINT PK_penalty PRIMARY KEY(RID,BID,PDate)ALTER TABLE PenaltyADD CONSTRAINT DF_PDate DEFAULT GETDATE() FOR PDateALTER TABLE PenaltyADD CONSTRAINT CK_Amount CHECK (Amount0)-创建外键

12、约束ALTER TABLE ReadersADDCONSTRAINTFK_Readers_ReaderTypeFOREIGNKEY(RTypeId)REFERENCES ReaderType(RTypeId)ALTER TABLE BooksADD CONSTRAINT FK_Books_Publishers FOREIGN KEY(PubId) REFERENCES Publishers(PID) (外键约束以此为例，其他略)4、插入记录此处使用操作方式略。子项目 3 数据管理一、选择题1.A 2.B 3.C 4.D二、 简答题1.（1）可以使用 INSERT INTOSELECT 语句，并

13、使用 UNION 集合运算将多条记录同时插入到数据表中。如插入三条记录到班级表，代码如下：INSERT INTO ClassSELECT 12010312,软件 1212,35,0103 UNION SELECT 12010411,安全 1211,45,0104 UNION SELECT 12020212,会计 1212,42,0202（2）使用 INSERT INTO 语句，逐条插入，代码略。2. TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志

14、资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。3. 两者都可以进行数据的复制。（1） INSERT INTO SELECT 语句要求目标表必须存在，语句格式为： Insert into Table2(field1,field2,.) select value1,value2,. from Table1。（2） SELECT INTO FROM 语句目标表可以不存在，语句格式为： SELECT vale1, value2 into Table2 from Table1。三、实训操作1. 请将 Books 数据表中的 BID 列改为非标志列。插入数据不顺利的原因是因

15、为该表与之关联的表中都没有记录，要先添加 Publishers、BookCategory 中的记录后，才能插入。INSERT INTO Books(BID,Title,Author,PubId,PubDate,ISBN,Price,CategoryId)VALUES(4939,企划手册,屈云波,16,2003-09-01,9787115145543,32.80,79)INSERT INTO Books(BID,Title,Author,PubId,PubDate,ISBN,Price,CategoryId)VALUES(4942,FireworksMX2008标准教程,胡松,28,2004-0

16、1-01,9787115155108,29.00,82)INSERT INTO Books(BID,Title,Author,PubId,PubDate,ISBN,Price,CategoryId)VALUES(4942,DREAMWEAVERMX标准教程,胡海,3,2003-05-01,978711515888,35.00,82)2. UPDATE BooksSET PubDate = 2008-09-01 WHERE BID=49393. SELECT *INTO Books1 FROM Books4. TRUNCATE TABLE Books1DELETE BooksDELETE 删除不

17、成功，因为 Books 表与 Penalty 表有联系。子项目 4 数据简单查询一、选择题1.C2.D3.B4.B5.C6.D7.A8.B9.B10.A二、填空题1、 删除字符表达式的前导空格。2、 ORDER BY3、 ab4、 DISTINCT5、 LIKE6、 F5三、简答题1. SELECT 语句的基本语法格式SELECT select_listINTO new_table_nameFROM table_listWHERE search_condition1 GROUP BY group_by-list HAVING search_condition2ORDER BY order_li

18、stASC DESC（1） SELECT 子句相当于关系代数中的投影运算，后面可以跟随 DISTINCT 关键词来消除重复列，select_list 是结果里面的属性列。（2） FROM 子句是要进行投影操作的基本表或视图，可以针对一个表或者多个表操作。（3） WHERE 子句是条件表达式，即属性列要满足的条件，通过筛选选出满足条件的元组。（4） GROUP BY 句的作用是将查询结果按分组属性划分为若干组，同组内的所有元组在分组属性上具有相同值。（5） HAVING 子句是将分组后的结果按照条件进行选择。（6） ORDER BY 子句的作用是将结果按照目标列升序(ASC）或降序(DESC)排

19、列2.（1）数学函数能够对数字表达式进行数学运算,并能够将结果返回给用户。（2） 字符函数可以实现字符串的查找、转换等。（3） 日期时间函数用来对日期或时间型数据进行转换。（4） 系统函数用来获取 SQL Server 中对象和设置的系统信息。3.聚合函数AVG COUNT MAX MIN SUM STDEVVAR功能返回组中值的平均值返回组中项目的数量返回表达式的最大值返回表达式的最小值返回表达式中所有值的和返回表达式中所有值的统计标准偏差返回表达式中所有值的统计标准方差四、实训操作1.（1）SELECT Title,Author FROM BooksWHERE Price BETWEEN

20、40 AND 60（2） SELECT Title,PubDate FROM BooksWHERE Author=赵增敏（3） SELECT PIDFROM PublishersWHERE PName=北京大学出版社 SELECT Title,PriceFROM BooksWHERE PubId=1 AND MONTH (PubDate)=5（4） SELECT * FROM ReaderWHERE Phone IS NULL（5） SELECT categoryidFROM BookCategory WHERE Name=计算机 SELECT *FROM BooksWHERE Categor

21、yId=82 ORDER BY PubDate DESC（6） SELECT COUNT(*)读者的总人数FROM Reader（7） SELECT PubId,COUNT(*) 图书数量FROM Books GROUP BY PubIdHAVING COUNT(*)=8（8） SELECT MAX(price) 最高价格,MIN(Price) 最低价格,AVG(price) 平均价格FROM bookS2. CREATE NONCLUSTERED INDEX IX_PriceON Books(Price)WITH FILLFACTOR =30GO SELECT *FROM Books WIT

22、H(INDEX=IX_Price)WHERE Price BETWEEN 20 AND 40子项目 5 数据复杂查询一、选择题1.D2.D3.C4.A5.B6.D二、填空题1、 AVG()2、 insert3、 GROUP BY4、 分组三、简答题1.（1）内连接仅选出两张表中互相匹配的记录。（2） 外连接将不满足条件的记录的相关值变为 NULL 加以显示。外连接有三类：左外连接、右外连接和全外连接。（3） 交叉连接将左表作为主表，并与右表中的所有记录进行连接。2.子查询比较灵活、方便，适合用于作为查询的筛选条件；而表连接更适合与查看多表的数据。3、视图（view）是在基本表之上建立的表，它的

23、结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表， 也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。四、实训操作1. 使用前面项目中的图书管理系统中的数据表，根据需求分别编写查询语句。（1） SELECT RID=(SELECT RID FROM Reader WHERE Rname= 刘晶晶),SUM(Amount)罚款总金额FROM PenaltyGROUP BY Penalty.RID（2） SELECT Title 图书名称,Author 作者,ISBN,Price 价格FROM Books INNER JO

24、IN BookCategoryON Books.CategoryId=BookCategory.CategoryId WHERE Name=计算机ORDER BY PubDate DESC（3） SELECT Title,Price FROM BooksWHERE PubId=(SELECT PIDFROM PublishersWHERE PName=北京大学出版社) AND MONTH (PubDate)=52.（1）CREATE VIEW view_Borrow ASSELECT Reader.RNAME 读者姓名,Books.Title 图书名称,Author 图书作者FROM Borr

25、ow INNER JOIN Books ON Borrow.BID=Books.BID INNER JOIN ReaderON Borrow.RID=Reader.RID（2） SELECT 图书名称,图书作者FROM view_borrowWHERE 读者姓名=刘晶晶子项目 6 数据库高级应用一、选择题1.C2.B3.C4.C二、简答题1（. 1）存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。（2） 当对数据库进行复杂操作时(如对多个表进行增、删、改、查时），可将此复杂操作用存储过程封装起来与数

26、据库提供的事务处理结合一起使用。这些操作，如果用程序来完成， 就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储过程，只需要连接一次数据库就可以了。（3） 存储过程可以重复使用，可减少数据库开发人员的工作量。（4） 安全性高，可设定只有某此用户才具有对指定存储过程的使用权。2. 触发器（trigge）r是SQL server提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。3. 局部变量是在当前批处理中有效的变量，批处理完成就失去作用，在SQL 中以 开头。全局变量就是在整个MSSQL中

27、都可以访问到的变量，以 开头。三、实训操作1. SELECT 书名=Title借,阅者=RNAME, 借阅日期=PDate,罚款金额=Amount,罚款类型= CASEWHEN PType=1 THEN 过期 WHEN PType=2 THEN 损坏 ELSE 遗 失ENDFROM Penalty,Books,ReaderWHERE Penalty.BID=Books.BID AND Penalty.RID=Reader.RID2. CREATE PROC proc_Pub(PNAME VARCHAR(200)ASSELECT Title,Author,PubDate,ISBN,Price F

28、ROM Books INNER JOIN PublishersON Books.PubID=Publishers.PID WHERE PNAME=PNAMEGOEXEC proc_Pub北京大学出版社3. IF EXISTS(SELECT * FROM sysobjects WHERE NAME=trigger_Title) DROP TRIGGER trigger_Titl-e-如果数据库中存在触发器，则删除GOCREATE TRIGGER trigger_Title-创建触发器 ON BooksFOR UPDATE ASIF UPDATE(Title) BEGINPRINT 不 能修改图书

29、名称ROLLBACK TRANSACTION-回滚事务ENDGOUPDATE BooksSET Title=企划手册 WHERE BID=4942子项目 7 报表服务一、简答题1.（1）打开 SQL Server Business Intelligence Development Studio，选择报表向导（2） 建立连接并选择创建报表的数据源（3） 选择报表类型及分组方式，选择报表的格式（4） 设置部署的选项2. （1）预览报表完成的效果（2）将其发布到配置好的报表服务器上二、实训操作1. 核心语句为：SELECT Cno,Sno,Result FROM ResultWHERE Sno LI

30、KE 11%2. 核心语句为：SELECTS.Sno, S.Sname, S.Ssex, C.ClassName FROMdbo.Class AS C INNER JOINdbo.Professional AS P ON C.Pno = P.Pno INNER JOIN dbo.Student AS S ON C.Classno = S.Classno INNER JOIN dbo.Department AS D ON P.DeptNo = D.DeptNoWHERE(D.DeptName = 软件与服务外包学院)项目三 管理教学管理系统数据库一、选择题1. B2. C3.D4. B二、填空题

31、1 存储2. SQL Server3. 当数据库出错时，使用备份文件还原数据库4. for xml5. RAW三、简答题1. 数据库角色，指一组固定的有某些权限的数据库用户。使用角色的概念管理数据库访问权限，角色就像 WINDOWS 中的组一样，可以将用户加入到某种角色，同种角色中的帐户拥有的权限。2. 数据备份类型分为完整数据库备份、差异备份、事务日志备份三种。3. 通过 openxml 函数和 SQL server 中的两个系统存储过程sp_xml_preparedocument 和sp_xml_removedocument 将 XML 文档映射为相应的字段，插入到数据库。四、实训操作题1

32、. （1）SQL Server Management Studio 中找到数据库实例，展开“安全性”分支树，右击“登录名”，在弹出的快捷菜单中单击“新建登录名”命令，打开“登录名-新建”对话框，在常规页的“默认数据库”选择“Library”数据库，登录名中选择“SQL Server 身份认证”单选按钮，并设置登录密码，去除“强制密码过期”和“用户下次登录时必须修改密码”的默认选择，然后点击【确定】按钮，完成登录名的创建。展开 Library 数据库，展开“安全性”分支树，右击“用户”，打开的快捷菜单中，单击“新建用户”，打开“数据库用户-新建”对话框，在用户名文本框中填写“Mike”，在登录名

33、中填写“Mike”，可选择对应的用户架构，点击【确定】按钮，完成用户名设置。（2）RAW 模式将查询结果集中的每一行转换为带有通用标识符row 或可能提供元素名称的 xml 元素。AUTO 模式将查询结果作为嵌套 XML 元素返回。使用 RAW 模式提取数据库 Library 中表 Books 中的数据的 T-SQL 语句如下所示：USE Library GOSELECT Title as 图书名称,Author as 作者 FROM BooksFOR XML RAW使用 AUTO 模式提取数据库 Library 中表 books 中的数据的 T-SQL 语句如下所示：USE LibraryS

34、ELECT Title as 图书名称,Author as 作者 FROM BooksFOR XML AUTO2.（1）SELECT * FROM sys.sql_logins（2） USE Library GOEXEC sp_addlogin yuguohong,123456（3） EXEC sp_adduser yuguohong,yuguohong（4） 定义数据库角色的语法格式：CREATE ROLE role_name AUTHORIZATION owner_name role_name ：要创建的角色的名称owner_name：该角色拥有者的名字，默认为dbo。其中 owner_n

35、ame 必须是当前数据库里的用户或角色代码为：CREATE ROLE departmentadministratorroleGRANT create table TO departmentadministratorrole项目四使用 C#开发教学管理数据库应用程序一、选择题1.A2.C3.C4.D5.C6.D7.C8.D二、填空题1. 菱形、椭圆2. 一对多、多对多3. 需求分析4. 逻辑设计5. 关系6. 学号7. 实体联系（E-R） 三、简答题1. E-R 图又称 E-R 模型，它是直接从现实世界中抽取出实体类型及实体间联系图（Entity-Relationship 图）。一个 E-R 图

36、（Entity-Relationship）由实体、属性和联系三种基本要素组成。实体用矩形框表示；属性用椭圆形表示；联系用菱形表示。其中联系可分为一对一联系(1:1)，一对多联系(1:n)和多对多联系(m:n)。2. 数据库规范化理论是进行数据库设计的理论基础，只有在数据库设计过程中按照规 范化理论方法才能够设计出科学合理的数据库逻辑结构和物理结构，避免数据冗余、删除冲突和数据不一致性等问题。三大范式：第一范式（1NF）：表中的每个列属性只包含一个属性值。第二范式（2NF）：在满足第一范式前提下，当表中的主键是由两个及两个以上的列复合而成时，表中的每个非主键列必须依赖表的主键列（列的集合）的整体

37、， 不能只依赖于主键列（列的集合）的子集。第三范式（3NF）：在满足第一范式和第二范式的前提下，表中的所有非主键列必须依赖表中的主键，而且表中的非主键列不能依赖表中的其它非主键列。供应商编号供应商名称联系人商品编号商品名称型号mn供应商采购商品联系电话邮编地址进货单号进货日期进货价格3. E-R 图如下所示：关系模式：商品（商品编号，商品名称，型号）供应商（供应商编号，供应商名称，联系人，联系电话，邮编，地址） 采购（进货单号，商品编号，供应商编号，进货日期，进货价格）4. E-R 图此处略。关系模式如下：读者（读者编号，读者姓名，读者性别，读者类型编号，家庭住址，电子邮件） 读者类型（读者类

38、型编号，读者类型名称，可借数量）图书（图书编号，图书名称，图书作者，出版社编号，出版时间，ISBN，单价，图书种类编号）图书类型（图书类型编号，图书类型名称） 出版社（出版社编号，出版社名称）借阅（图书编号，读者编号，借阅时间，归还时间）罚款（读者编号，图书编号，罚款日期，罚款类型，罚款金额）5. 具有 4 个关系模式如下： 车队（车队编号，车队名称）车辆（车牌号，生产厂家，生产日期，车队编号）司机（司机编号，姓名，电话，车队编号，聘用时间，聘期） 使用（司机编号，车牌号，使用日期，公里数）四、实训操作存储过程 StudentInfosByProName：输入专业和姓名，完成学生信息（学号、姓

39、名、性别、出生日期、入学时间、邮箱、地址和所在班级信息）的查询，其中姓名可以模糊查询。 存储过程代码如下：USE StudentDB GOCREATE PROCEDURE proc_StudentInfo (Pname nvarchar(80),Sname varchar(50)ASSELECTA.Sno,A.Sname,A.Ssex,A.Sbirthday,A.EntranceTime,A.Email,A.Address,B.Classname AS class FROM Student AS A INNER JOIN Class AS BON A.Classno=B.ClassnoINNER JOIN Professional AS C ON B.Pno=C.PnoWHERE Pname=Pname AND Sname Like %+Sname+%