SQL Server 2000数据库程序设计

第1章 SQL Server概述

1. 有个用户的计算机不能连接到中心机房的SQL Server 2000上。你在调试过程中发现这个用户的计算机的网络功能是正常的，而且其他用户都能正常地连接到SQL Server 2000。下面的哪些工具有助于你诊断和解决该问题？（多选）
A． Enterprise Manager    
B． Server Network Utility
C． Profiler
D． Query Analyzer
E． Client Network Utility

答:B和E
2. 你在SQL Server 2000创建了酒店管理系统的数据库HotelDB，并创建了表CustInfo。当下面的哪些数据库被删除的情况下，仍能正确地执行“SELECT * FROM CustInfo？
A． Model
B． Tempdb
C． Msdb
D． Master
E． Pubs

答:A、C、E

3. 你正在使用SQL Server 2000开发银行交易系统，为了保证商业数据在网络传输（用TCP/IP协议）时不会被窃取，你在SQL Server 2000中启用了网络加密功能。请问该功能在哪一层被实现？
A． TCP/IP协议软件
B． 超级套接字层
C． 开放式数据服务
D． 关系引擎
E． 存储引擎

答:B

4. 你正在使用SQL Server 2000开发超市收银系统。在客户端编写软件时使用SQL语句“SELECT * FROM Products”来查询商品的信息，但是不小心把Products输入成Product。请问该错误在哪一层被发现？
A． 客户端的数据库API
B． 客户端的NET-LIBRARY
C． 服务器端的开放式数据服务
D． 服务器端的关系引擎
E． 服务器端的存储引擎

答:D

5. 你要为中小型商场开发一个商场收银软件，该软件由多个收银员在各自的收银台使用。后端数据库是SQL Server 2000，所有收银员的收银信息集中存放在几个表内。在采用两层架构（2-Tier）的软件开发时，商场收银软件需要数据库的帐号和密码（SQL Server认证）或Windows认证才能连接和访问数据库，就象SQL Server 2000的查询分析器在刚运行的时候要求你输入帐号和密码或使用Windows认证才能访问数据库。显然，你希望只有商场收银软件才有权力访问数据库，而收银员不应当有使用查询分析器等其它软件直接修改数据库数据的权力，否则可能会导致贪污和恶意破坏的发生。所以，
（1） 软件使用的数据库帐号采用Windows认证还是SQL Server认证，如何实现该目标？
（2）在采用三层架构（3-Tier）的软件开发时，客户端程序会不会出现该问题？

答:（1）不能采用Windows认证，只能采用SQL Server认证。因为商场收银软件若采用Windows认证，收银员登录到Windows系统后，不光商场收银软件可以访问数据库，收银员也可以用SQL Server 2000的查询分析器等软件以Windows认证方式来访问数据库。
       如果采用SQL Server认证方式，商场收银软件需要帐号和密码，当然这不能由收银员来提供。一种典型的做法是：将数据库的帐户和密码放在商场收银软件中（或在Windows的注册表中），并禁止在数据库中改动该帐号和密码。帐号和密码在软件中要适当加密，就很难从软件的可执行代码内获得帐号和密码，这种安全级别对一般的商场收银系统已经足够。为了检验收银员的身份，你要自己编写管理收银员的软件代码，在收银软件刚开始运行时要求输入收银员的代号和密码，这些代号和密码可以放在数据库的一个表中。
     （2）在采用三层架构（3-Tier）的软件开发时，客户端程序不会出现该问题。三层架构分为：客户端（收银员操作界面），应用服务器（商业逻辑），数据库服务器（SQL Server 2000）。客户端只向应用服务器发出请求，应用服务器接收请求后，根据商业逻辑对数据库服务器发出命令来访问数据库，最后将结果传送给客户端。因为客户端不直接访问数据库服务器，它不需要数据库服务器的帐号和密码。

6. 已经在SQL Server中为Windows 2000用户创建了一个新的登录账户。希望该组的成员能够使用SQL Server。那么你还需要完成其他哪些额外的安全设置任务？

答: 首先，必须把Windows 2000用户组添加到希望用户访问的数据库中，之后，将权限授予希望用户访问的数据库对象。

7. 希望浏览SQL Server数据库中对象的元数据。那么你应该使用什么方法？

答: 可以查询信息架构视图、执行系统存储过程或使用系统函数。也可以直接查询系统表，但不推荐使用这种方法，原因是在产品的后续版本中，这些表可能会发生变化。

8. 希望引用同一个服务器上的不同数据库中的两个表。从Inventory数据库中，希望引用Sales数据库中的某个表。那么如何在一个查询中引用Sales数据库中的该表？

答:应该使用完整修饰名称引用Sales数据库中的表。例如，应该指定Sales.dbo.tablename或Sales.tablename。

 

第2章 创建和管理数据库

1. 数据库操作时服务器突然掉电，当重新启动后，您如何恢复已经完成但还没把数据写入硬盘的事务，以及正在进行一半的事务？
A、 运行ROLL FORWARD ALL TRANSACTION
B、 不用。SQL Server 2000自动恢复，即通过日志恢复所有已经完成但还没把数据写入硬盘的事务到硬盘，并撤消正在进行一半的事务。
C、 先备份事务日志，再将数据库恢复到上一完全数据库备份，再把刚备份的事务日志恢复到当前数据库的日志中。
D、 没有办法。只能使用数据库以前的备份。

答:B

2. 你使用SQL Server 2000数据库开发一个小型的酒店管理系统。你用下面的语句创建数据库：
CREATE DATABASE JiuDian
ON PRIMARY
( NAME = JiuDianData,
        FILENAME = 'c:/cyj/JiuDianData.mdf',
        SIZE = 100,
        MAXSIZE = 200,
  FILEGROWTH = 10 )
LOG ON
( NAME = JiuDianLog,
  FILENAME = 'c:/cyj/JiuDianLog',
  SIZE =50,
  MAXSIZE =100,
  FILEGROWTH = 10
现在酒店营业的数据为150MB。对其中50%的数据进行备份并删除后，要把数据库立即缩小为初始的大小（即100 MB）。下面哪条语句能够实现该功能？

A.  DBCC SHRINKFILE( JiuDianData, NOTRUNCATE )
B.  DBCC SHRINKDATABASE( JiuDianData, 25 )
C.  DBCC SHRINKDATABASE(JiuDianData, 100 )
D.  ALTER DATABASE JiuDianData SET AUTO_SHRINK ON

答:B

3.  用SQL语句创建满足如下要求的数据库：
（1） 数据库名称为Library；
（2） 主文件组有两个数据文件。
第一个数据文件：逻辑名为LibraryData1，文件名为“c:/cyj/LibraryData_1.mdf”，文件初始大小为50MB，文件的最大大小不受限制，文件的增长率为20%。
第二个数据文件：逻辑名为LibraryData2，文件名为“c:/cyj/LibraryData_2.ndf”，文件初始大小为50MB，文件的最大大小不受限制，文件的增长率为10MB。
（3） 日志只有一个数据文件，逻辑名为LibraryLog，文件名为“c:/cyj/LibraryLog.ldf”，文件初始大小为10MB，文件的最大大小为50MB，文件的增长率为10MB；

答:
CREATE DATABASE Library
ON  PRIMARY ( NAME = LibraryData1,
                      FILENAME = 'c:/cyj/LibraryData_1.mdf',
                      SIZE = 50MB, MAXSIZE = UNLIMITED,
FILEGROWTH = 20% ),
     ( NAME = LibraryData2,
                      FILENAME = 'c:/cyj/Library_2.ndf',
              SIZE = 50MB, MAXSIZE = UNLIMITED,
FILEGROWTH = 10)
LOG ON
    ( NAME = LibraryLog,
          FILENAME = 'c:/cyj/LibraryLog.ldf',
          SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10MB )

4.   您现在有四个物理硬盘来存放一个很大的数据库，该数据库存放大量的销售数据。数据库主要用于数据读取和统计，以便市场经理决定经营策略。为了提高数据的读取性能，数据库内的表进行了大量索引。如何生成数据库，使性能最佳？

答:可以创建用户自定义的文件组fgroup1，同时分别在三个硬盘上创建三个文件（Data1.ndf、Data2.ndf 和 Data3.ndf），并将这三个文件指派到文件组 fgroup1 中。然后，指定文件组 fgroup1 为默认的文件组。在创建表时，如果没有特别声明，都自动创建在默认的文件组，以后存放在表中的数据将自动分散在三个硬盘上。这样，以后对表中数据的查询也将分散到三个磁盘上，因而性能得以提高。另外，在第四个硬盘创建日志文件。

5. SQL Server在故障（如掉电）或服务器关闭之后重启时对数据库的恢复有三个阶段：分析阶段，重做阶段，取消阶段。参考本章讨论该问题时的附图，请举例说明为什么取消阶段会涉及到最后一个检查点以前的事务，而不是只涉及最后一个检查点以后的事务？

 答:因为可能有一些事务在最后一个检查点到来之前就已经开始，但是到故障（如掉电）或  服务器关闭时该事务还没有结束，这些事务必须取消。

6. 你正在创建一个不经常修改的数据库，该数据库主要用于决策支持和只读查询。你会为事务日志分配多大的数据库空间百分比？

  答: 答案并不唯一。可以在10%到20%之间。不应该超过20%。由于该数据库的更改活动相当少，分配的空间百分比接近10%是合理的。

7. 使用文件组有那些优点？

 答:可以把表放在指定的硬盘上。能够独立地备份大型表。

8. 你正在负责管理你的机构中关键任务的帐务记录。哪一种数据恢复模型适合你的数据库？

 答:应该使用Full Recovery（完全恢复）模型。

9. GAM、SGAM和IAM页都跟踪数据分配。那么，IAM页与GAM和SGAM页有什么不同？

 答:GAM和SGAM页跟踪所有对象。IAM页只跟踪一个特定表或索引的分配。

 

第3章 创建数据类型和表

1. 在学生成绩表tblCourseScore中的列Score用来存放某学生学习某课程的考试成绩（0~100分，没有小数）, 用下面的哪种类型最节省空间？
A、 int
B、 smallint
C、 tinyint
D、 decimal(3,0)

答:C

2. 你在SQL Server 2000数据库中建立了一些相似的表,其格式如下,只是表名和列名不同。
CREATE TABLE OneTable
  ( pk    uniqueidentifier,
    name  varchar(20),
          other  uniqueidentifier,
  )
应用程序开发人员对这些表编写了一些相似的查询。因为列的名称相似，他们喜欢用ROWGUIDCOL关键字来引用列名。当执行这些查询时，会产生什么结果？
A、 SQL Server 2000会返回错误，因为表包含两个类型为uniqueidentifier的列；
B、 当执行的查询在引用ROWGUIDCOL关键字的时候，SQL Server 2000会返回错     误；
C、 SQL Server 2000会返回错误，因为列pk没有声明为关键字；
D、 SQL Server 2000不会产生错误。

答:B

3. 定单表Orders的列OrderID的类型是小整型（smallint），根据业务的发展需要改为整型（integer），应该使用下面的哪条语句？
A、ALTER COLUMN OrderID integer FROM Orders
B、ALTER TABLE Orders ( OrderID integer )
C、ALTER TABLE Orders ALTER COLUMN OrderID integer
D、ALTER COLUMN Orders.OrderID integer

 答:C

4. 一个数据库中的用户定义数据类型能够用于同一个服务器上的另一个数据库中吗？

答:不能。用户定义数据类型局限于单个数据库。你可以在另一个数据库中创建一个与之匹配的数据类型，也可以在model数据库中创建用户自定义数据类型。

5. 你正在设计一个要存储数百万种不同产品的信息数据库，而且想以最少的空间存储产品信息。每一个产品在products表中都有一行描述。有时候，产品描述需要200个字符，但绝大多数产品描述只需要50个字符。那么，你应该使用哪一种数据类型？

答:使用varchar(200)数据类型，因为它既保持了行的紧凑，同时也能够容纳偶然情况下所需要的200个字符的产品描述。

6. 在Employees表中的列Remarks用来记录员工的备注信息，该列大部分不到800字节，但有时会达到20000字节。如何处理以提高读取性能？

答:用行中text列的方式。因为20000字节超过一个记录的长度（约8000字节），所以该列只能用text类型。为了提高读取性能，当记录中该列不到800字节时，把它直接放在该行。具体做法是：
CREATE TABLE Employees
(   ………..,
   Remarks  text  NULL )
EXECUTE sp-tableoption Employees, ‘text in row’, 800)

7. 有家企业要用表tblCustomerInfo来存储客户的信息。客户的信息包括：代号（整型IDENTITY，从100001开始，每次增加5），名称（最长40个汉字），电话（20个字符），传真（20个字符），备注（最长1000个汉字）。电话号码和传真号码要用同一自定义类型type_TelphoneNum。
（1）请写出创建该表的SQL语句。
（2）后来因手机流行，需要在表tblCustomerInfo中再添加列“手机”，该列的类型也是type_TelphoneNum。请写出添加该列的SQL语句。

答:要注意存放一个汉字要用两个字节，所以“名称”和“备注”的长度要乘2。具体的SQL语句如下：
EXECUTE sp_addtype   type_TelphoneNum, 'varchar(20)', NULL
CREATE TABLE tblCustomerInfo
( 代号  integer IDENTITY(10001,5) NOT NULL,
      名称  varchar(80)           NOT NULL,
      电话  type_TelphoneNum ,
      传真  type_TelphoneNum ,
备注  varchar(2000)                NULL
   )

ALTER TABLE tblCustomerInfo
        ADD 手机 type_TelphoneNum

8. 你需要运行一个使用SQL Server企业管理器创建的脚本。那么你该如何做呢？

          答: 使用SQL查询分析器或osql，打开并运行脚本。

 

第4章 实现数据完整性

1. 在学生管理系统中使用下面的学生信息表：
   CREATE TABLE 学生信息表
     ( 学号          char(8)      PRIMARY KEY NONCLUSTERED,
       姓名          varchar(20)  NOT NULL,
       身份证号码    varchar(30)  NOT NULL,
       出生日期      datetime         NULL,
       系号          char(2)      NOT NULL
     )
学生的身份证号码是唯一的。你想把每个学生的信息按照身份证号码的顺序物理地存放在数据库文件中。下面的哪个约束可以完成改任务？
A、UNIQUE CLUSTERED
B、UNIQUE NONCLUSTERED
C、PRIMARY KEY CLUSTERED
D、PRIMARY KEY NONCLUSTERED

答:A

2. 在SQL Server 2000中创建一些新对象的脚本如下：
CREATE TABLE Publishers
( publisher_id     int            PRIMARY KEY,
      publisher_name  varchar(100)    not null
)
CREATE TABLE Books
( book_id        int            CONSTRAINT PK_title_id PRIMARY KEY,
      book_title       nvarchar(2000)  not null,
      book_content    ntext          not null,
      field01         varchar(100)    unique,
      field02         char(1000),
      field03         char(1000),
      field04         char(1000),
      publisher_id     int            not null,
         CONSTRAINT FK_publisher_id
            FOREIGN KEY (publisher_id)  REFERENCES Publishers(publisher_id)
)
CREATE TABLE ThirdTable
( third_id    int  IDENTITY(45,17) PRIMARY KEY,
     field01     varchar(100)   FOREIGN KEY REFERENCES Books(field01),
)
从下面中选择正确的说法。
A. 上面的创建语句没有错误；
B. 表Books的一行的长度超过允许的最大长度；
C. 表ThirdTable的列的third_id的种子要能被增量整除；
D. 列field01的类型是varchar，不能作为外部关键字；
E. 不能直接在表Books的列book_id上使用CONSTRAINT关键字，使用CONSTRAINT关键字要象该表的CONSTRAINT FK_publisher_id一样另起一行。

答:A

3. 在登记学生成绩时要保证列Score的值在0到100之间，下面的方法中哪种最简单？
A. 编写一个存储过程，管理插入和检查数值，不允许直接插入；
B. 生成用户自定义类型type_Score和规则，将规则与数据类型type_Score相关联，然后设置列Score的数据类型类型为type_Score；
C. 编写一个触发器来检查Score的值，如果不在0和100之间，则撤消插入；
D. 在Score列增加检查限制。

答:D

4. 在数据库中应该将哪种约束添加到Country字段中，以确保你的印度尼西亚子公司只与其他印度尼西亚公司进行贸易。
答:CHECK约束（或规则）。

5. 在实现问题4的约束（或规则）后，数据录入操作员抱怨他们不得不一遍又一遍地输入Indonesia这个单词， 有什么办法可以解决它吗？
答:创建一个DEFAULT约束（或默认值）。

6.   假设你的业务改变了并且你也不在印度尼西亚工作。你的子公司迁移到了马来群岛与其他几个东亚国家做生意。现在，在country字段中包括印度尼西亚在内的记录有四百五十万条。如何加入新的国家而且保留包含印度尼西亚的行呢？
答:修改表来删除现有的约束，然后添加新的约束。在添加新约束时，使用WITH NOCHECK选项。

7. 在学生管理系统中已经创建了学生信息表tblStudInfo和系信息表tblDeptInfo，而且这两个表都存放了不少数据。创建表的语句如下：
CREATE TABLE tblDeptInfo
(
  DeptID    char(2)      not null PRIMARY KEY,
  DeptName  varchar(20)  not null,
  Remarks   varchar(255)
)
CREATE TABLE tblStudInfo
(
  StudID    char(8)     not null  PRIMARY KEY,
  Name     varchar(20)  not null,
  Birthday   datetime       null,
  DeptID    char(2)     not null
)
学生信息表tblStudInfo的列DeptID，用来存放学生所在系的代号。该列引用系信息表tblDeptInfo的列DeptID，但是在创建表时没有创建该约束。请在不删除表的情况下，写出创建该约束的SQL语句。
答:可以使用ALTER TABLE语句来创建该约束，
ALTER TABLE tblStudInfo
                            ADD CONSTRAINT FK_DeptID
FOREIGN KEY (DeptID)  REFERENCES tblDeptInfo(DeptID)

8.  某公司使用数据库进行内部管理：表tblEmployees存储雇员的代号（4位字符，唯一）、身份证号码（18个字符）、名字（最长20个字符）和工资等信息；表tblDepartments存储部门的部门号（2个字符，唯一）、部门名称（30个字符）等信息；表tblWork每一行表示某雇员在某部门工作过及其开始工作时间和备注。请写出创建这三个表的SQL语句，要保证：工资的值大于0，身份证号码唯一，并且当删除某雇员时该雇员在表tblWork的所有信息自动删除。

答:
CREATE TABLE tblEmployees
( 雇员代号    char(4)      not null PRIMARY KEY,
  姓名        varchar(20)  not null,
  身份证号码  char(18)     not null UNIQUE,
  工资        integer      not null CHECK(工资>0)
)

CREATE TABLE tblDepartments
( 部门号    char(2)        not null PRIMARY KEY,
  部门名称  varchar(30)    not null
)

CREATE TABLE tblWork
(
      雇员代号   char(4) not null,
      部门号     char(2)  not null,
      开始时间   datetime not null,
      备注       varchar(1000) null,
                   CONSTRAINT PK_tblWork PRIMARY KEY(雇员代号,部门号, 开始时间),
                   CONSTRAINT FK_tblEmployees FOREIGN KEY (雇员代号) REFERENCES tblEmployees(雇员代号) ON DELETE CASCADE,
                    CONSTRAINT FK_tblDepartments FOREIGN KEY (部门号) REFERENCES tblDepartments(部门号) 
)

9.  定单录入系统有两个主要的表：Orders和Customers。如果希望惟一地标识每个定单和客户，应该考虑哪种数据完整性组件？如何管理这两个表之间的关系？
答:确定在Customers表中定义了PRIMARY KEY约束。在Orders表中使用FOREIGN KEY约束来引用Customers表。

第5章 Transact-SQL介绍

1. 执行下面脚本，共有多少条记录插入到表tblTemp中？
CREATE TABLE tblTemp
( TempID integer not null,
  TempDate datetime not null)
go
DECLARE @count integer
SET @count = 1
WHILE @count <=10
  BEGIN
    IF (SELECT COUNT(*) FROM tblTemp ) > 8
      BEGIN BREAK END
    ELSE
      INSERT tblTemp VALUES(@count,GETDATE())
    SET @count = @count + 1
  END
A、0  B、8   C、9   D、10
答:C

2. 你的数据库存放产品的序列号，序列号以整型存储。你需要把序列号按如下的格式显示：
[phone]
假设你已经把一个序列号放在一个变量中，该变量的定义如下：
              DECLARE @SerialNumber  int
下面哪个语句可以正确地完成该任务？
A． 
SELECT 'Serial Number' = '('
+ SUBSTRING(CONVERT(varchar(10),@SerialNumber),3,0)+')'
      + SUBSTRING(CONVERT(varchar(10),@SerialNumber),3,3)
      + '-' + SUBSTRING(CONVERT(varchar(10),@SerialNumber),4,6)

B． 
SELECT 'Serial Number' = '('
+ SUBSTRING(CONVERT(varchar(10),@SerialNumber),3,1)+')'
      + SUBSTRING(CONVERT(varchar(10),@SerialNumber),3,4)
      + '-' + SUBSTRING(CONVERT(varchar(10),@SerialNumber),4,7)

C． 
SELECT 'Serial Number' = '('
+ SUBSTRING(CONVERT(varchar(10),@SerialNumber),0,3)+')'
      + SUBSTRING(CONVERT(varchar(10),@SerialNumber),3,3)
      + '-' + SUBSTRING(CONVERT(varchar(10),@SerialNumber),6,4)

D． 
SELECT 'Serial Number' = '('
+ SUBSTRING(CONVERT(varchar(10),@SerialNumber),1,3)+')'
      + SUBSTRING(CONVERT(varchar(10),@SerialNumber),4,3)
      + '-' + SUBSTRING(CONVERT(varchar(10),@SerialNumber),
答:D

3. 描述Transact-SQL语句的基本类型及它们的用法。

答:数据定义语言(DDL)语句允许在数据库中创建对象。数据控制语言(DCL)语句能够决定谁能查看和修改数据。数据操纵语言(DML)语句允许查询和修改数据。

4. Transact-SQL和 ANSI SQL-92的关系是什么？

答:Transact-SQL符合 ANSI SQL-92入口级规范，并通过SQL Server特定的扩展而且具有附加的功能。

5. 下面的语句错在什么地方？
DECLARE @a integer
SET @a = 1
SELECT @a
GO
SET @a=@a+1
答:当执行GO命令时，GO前面的批处理就结束，该批处理中的局部变量@a已经被清除。当执行SET @a=@a+1语句时，会因为变量@a没有定义而产生错误。

6. 写出Transact-SQL语句，将SQL Server 2000 服务器的名称放在局部变量@a中（用两种方法）。

答:
DECLARE @a varchar(300)
SET @a=@@SERVERNAME
和 
DECLARE @a varchar(300)
SELECT @a = @@SERVERNAME

7. 表tblTemp的定义如下：
CREATE TABLE tblTemp
(  TempID integer not null,
         TempDate datetime not null)
（1）请写出SQL语句来显示列TempID是偶数的记录中列TempDate的年份。（2）请写出几条SQL语句显示列TempID的值是该列的最大值的所有记录。

答:
select year(TempDate)
from tblTemp
where TempID % 2 = 0

DECLARE @Temp integer
SELECT @Temp = max(TempID)
FROM tblTemp
SELECT * FROM tblTemp
WHERE TempID = @Temp

第6章  使用Transact-SQL查询工具

1. 一个脚本有如下代码：
CREATE TABLE Employees                   --第一条语句
( EmployeeID  int         IDENTITY(10001,1) PRIMARY KEY,
  Name      varchar(40)  not null,
  Birthday    datetime       null
)
SELECT * FROM Employees                 --第二条语句
CREATE DEFAULT phone_no_default --第三条语句
 AS '[phone]'
CREATE TABLE Customers                  --第四条语句
( CustomerID  int         IDENTITY(100001,1) PRIMARY KEY,
  Name      varchar(40)  not null,
  Phone      varchar(20)     null
)
EXECUTE sp_bindefault phone_no_default, 'Customers.Phone'   --第五条语句
在执行过程中会因为缺少GO语句而出现错误，下面哪些是必须的？
A. 在“第一条语句”和“第二条语句”之间添加GO语句
B. 在“第二条语句”和“第三条语句”之间添加GO语句
C. 在“第三条语句”和“第四条语句”之间添加GO语句
D. 在“第四条语句”和“第五条语句”之间添加GO语句
答:B, C

2. 为了捕获文本文件中的查询结果，在批处理文件中最好使用什么查询工具？为什么？

答:最好使用osql命令行实用工具执行查询，然后通过使用命令行选项–o filename.txt把结果保存到一个文本文件中。
在SQL 查询分析器中，使用适当的命令行选项也能完成上述操作。

3.  要想将来重新使用Transact-SQL语句，创建并使用它的最好方法是什么？

答:使用对象浏览器直接从对象生成Transact-SQL 语句脚本，或者从模板生成Transact-SQL 语句脚本。也可以把Transact-SQL脚本保存到文件中，供日后修改或使用。

4.  Transact-SQL批处理和Transact-SQL脚本有何不同？

答:Transact-SQL批处理是由GO语句描绘的一系列语句，这些语句将立即被分析和执行。Transact-SQL脚本是一个文件，此文件包含一个或多个要执行的批处理。

5. 您打算用SQL Server 2000的查询分析器来浏览部门信息表tblDepartments中的记录信息，可以采用哪些方法？

答:在SQL Server 2000的查询分析器中执行SQL语句 SELECT * FROM tblDepartments，或者在SQL Server 2000的查询分析器的对象浏览窗口选中表tblDepartments，按鼠标右键并选中快捷菜单中的“打开表”功能。

6. 在WINDOWS系统中，一个程序A（严格讲应称为“进程”）可以启动运行带参数的另外一个程序B，程序B运行结束后会返回结果（一个整数）给程序A，供程序A判断程序B运行是否正常。假设您在编写一个商场管理信息系统的安装软件，该安装软件要在SQL Server 2000服务器上创建数据库和表等对象。您已经把创建数据库和表等对象的SQL语句放在install.sql文件中。如何轻松地完成创建数据库和表等对象的任务？

答:可以通过运行osql.exe来执行创建数据库和表等对象的SQL语句。程序A可以运行如下命令：
如果使用Windows 身份验证模式，则执行
        osql.exe –S 服务器名称 –E –i e:/setup/install.sql
如果使用SQL Server身份验证模式，则执行
        osql.exe –S 服务器名称 –U sa –P 密码 –i e:/setup/install.sql
更详细的信息请看SQL Server的联机帮助：osql 实用工具。

7. 请比较SQL Server 2000的查询分析器和企业管理器的优缺点。

答:企业管理器使用向导的方式，比较简单和直观，适合初学和维护数据库时使用。但是，企业管理器也有缺点。比如，创建数据库等对象后若不小心删除这些对象，重新生成这些对象比较费劲，容易缺漏。
查询分析器使用文本命令行（Transact-SQL语句）的方式，比较适合在开发实际系统时使用。在开发阶段，您可以使用Transact-SQL语句在开发环境中创建数据库和表等对象，并保存这些语句到文本文件中，以后在实际运行环境中只要运行这些命令行就可以重新创建数据库和表等对象。同时，使用Transact-SQL语句还可以生成大量的测试数据。使用查询分析器的缺点在于要记住很多Transact-SQL语句的格式和使用方法。

第7章  检索数据

1. 您需要显示从2001年1月1日到2001年12月31日雇佣的所有职员的姓名和雇佣日期。职员信息表tblEmployees包含列Name和列HireDate，下面哪些语句能完成该功能？

A、 SELECT Name, HireDate FROM tblEmployees
B、  SELECT Name, HireDate FROM tblEmployees
        WHERE HireDate =’2001-01-01’ OR ‘2001-12-31’
C、  SELECT Name, HireDate FROM tblEmployees
        WHERE HireDate BETWEEN ’2000-12-31’ AND ‘2002-01-01’
D、  SELECT Name, HireDate FROM tblEmployees
        WHERE HireDate DATEPART(yy, HireDate) =2001

答:D

2. 在SQL Server 2000 创建了定单表，创建语句如下：
   CREATE TABLE 定单表
      (  定单代号   int       IDENTITY(1,1) PRIMARY KEY,
         客户代号   int       not null,  
         雇员代号   int       not null,    --经手该笔业务的雇员的代号
         定单日期   datetime  not null,   
         销售金额   money    not null,   
         备注       varchar(200)  null
      )
      你需要获得定单信息列表，包括雇员代号、销售金额和定单日期。你想按日期从近到 早的顺序显示，并且对于每一天的定单，按销售金额从大到小的顺序排序。
假设列“销售日期”的时间部分的值都是0，下面哪条语句能够准确地完成该任务？
A.  SELECT 雇员代号, 销售金额, 定单日期
FROM  定单表
ORDER BY销售金额, 定单日期 DESC

B.  SELECT 雇员代号, 销售金额, 定单日期
FROM  定单表
ORDER BY 定单日期, 销售金额DESC

C.  SELECT 雇员代号, 销售金额, 定单日期
FROM  定单表
ORDER BY 定单日期 DESC, 销售金额 DESC

D.  SELECT 雇员代号, 销售金额, 定单日期
FROM  定单表
ORDER BY 销售金额 DESC, 定单日期 DES

答: C

      3.   你是一位健康护理计划的数据库管理员。使用下面语句创建physicians表：
CREATE TABLE dbo.physicians (
physician_no int IDENTITY (100, 2) NOT NULL ,
f_name varchar (25) NOT NULL ,
l_name varchar (25) NOT NULL ,
street varchar (50) NULL ,
city varchar (255) NULL ,
state varchar (255) NULL ,
postal_code varchar (7) NULL ,
co_pay money NOT NULL CONSTRAINT phys_co_pay DEFAULT (10)
)
a)   如何检索在纽约州（NY）、华盛顿州（WA）、弗吉尼亚州（VA）、或加利福尼亚州   （CA）实习的医生信息？

答:用下面类型的WHERE子句编写一个SELECT语句。
WHERE state = 'NY' OR state = 'WA' OR state = …
Or, use a WHERE clause that includes the IN keyword:
WHERE state in ('NY', 'WA', 'VA', 'CA')

b)    在结果集中，如何产生一个没有重复州的列表？

 答:在SELECT语句中使用DISTINCT关键字。

c)   在结果集中，如何产生一个列，包含co_pay值加每位医生$5.00的服务费，并且给该列取一个别名Amt_Due?

 答:在选择列表中使用计算列。使用列别名’Amt_Due’=(co_pay+5)。

4.  学生成绩表包含列“学号”、列“课程代号”、列“成绩”、列“考试时间”等。列“成绩”的值为NULL表示该学生缺考这门课。请用SELECT语句查询学号为“98120001”的学生所有缺考的课程数。

答:SELECT COUNT(*)
FROM 学生成绩表
WHERE 学号 = '98120001'  AND 成绩 IS NULL

5.  雇员信息表包含列“雇员代号”、列“雇员姓名”、列“出生日期”、列“基本工资”、列“津贴”，总工资=基本工资+津贴，请用SELECT语句查询1972年以后出生的雇员的姓名、出生日期和总工资，按总工资递减的顺序显示。

 答:SELECT 雇员姓名,基本工资+津贴 AS 总工资,出生日期
FROM 雇员信息表
WHERE 出生日期>='1972-01-01'
ORDER BY 总工资 DESC

       6.  现在要做一个电话查询网页，供人们查询某大学的教职工的家庭电话号码。使用者在网页输入教职工的部分姓名，网页就返回匹配该查询条件的所有教职工的姓名和电话号码。输入的部分姓名可以多种多样，比如，查找“张小明”，可以输入“张”、“小”、“明”、“张明”、“小明”等等。假设教职工的姓名和电话号码存放在表tblTelphone，局部变量@PartialName存放输入的部分姓名，请用Transact-SQL语句写一段代码完成该查询。（请参考第五章的字符串函数SUBSTRING，和SQL Server联机丛书。）

答:
DECLARE @SearchCondition varchar(200) 
DECLARE @i smallint,@Count smallint

SET @SearchCondition = '%'
SET @Count = LEN(@PartialName)
SET @i = 1
WHILE @i <= @Count
  BEGIN
    SET @SearchCondition = @SearchCondition +SUBSTRING( @PartialName, @i, 1 )+'%'
    SET @i = @i + 1
  END
SELECT *
FROM tblTelphone
WHERE 姓名 like @SearchCondition

第8章  数据分组与汇总

1. 你在SQL Server 2000数据库中创建了定单表，其创建语句如下：
    CREATE TABLE 定单表
       ( 定单号    int IDENTITY(1,1) PRIMARY KEY,
         雇员代号  int       NOT NULL,
         地区代号  int       NOT NULL,
         订购日期  datetime  NOT NULL,
         订购金额  money    NOT NULL
       )
        销售经理想要获得总的销售金额和按地区分组的总销售金额。下面哪条语句能完成该任务？
A.  SELECT 雇员代号,地区代号,订购金额
      FROM 定单表
      ORDER BY 地区代号
      COMPUTE SUM(订购金额)

B.  SELECT 雇员代号,地区代号,订购金额
      FROM 定单表
      ORDER BY 地区代号
      COMPUTE SUM(订购金额) BY 地区代号
      COMPUTE SUM(订购金额)

C.  SELECT 雇员代号,地区代号,SUM(订购金额)
      FROM 定单表
      GROUP BY 雇员代号,地区代号
 
D.  SELECT 雇员代号,地区代号,SUM(订购金额)
      FROM 定单表
      GROUP BY 雇员代号,地区代号

答:B

2. 下面哪些关键字能影响SELECT语句返回的结果的行数？（多选）
A. TOP n
B. WHER
C. ORDER BY  
D. DISTINCT

答:A、B和D

3. 一个营销部的雇员想让你提供关于产品销售的汇总数据。她需要对早餐的谷类食品按类别（如冷、热或低脂类）、厂商和销售产品的商店的规模（小、中或大）分类。假设一个表中保存了所有这些信息，你的SELECT语句将使用什么样的子句或运算符？为什么？

答:最好使用GROUP BY子句和CUBE运算符。GROUP BY和HAVING子句只能提供一种级别的汇总（或分组）。ROLLUP运算符可以提供一种类型的汇总。CUBE运算符可以提供多种类型的汇总。也可以使用COMPUTE或COMPUTE BY子句生成基本报表。

4. 你的经理要求你把包含从问题3得到的所有数据的文件提供给另外一个开发小组使用，此开发小组负责报表生成和图形化工具。使用COMPUTE和COMPUTE BY子句适合这个任务吗？为什么或为什么不适合？

答:不合适。因为COMPUTE和COMPUTE BY子句生成的是非关系型格式的附加的汇总数据。尽管这些数据便于查看，所返回的结果集却不适合出现在其它应用程序中。应该使用GROUP BY子句和CUBE或ROLLUP运算符提供标准关系格式的数据，这样其他客户比较容易使用。

5. 某班级的学生成绩表包含列“学号”、列“课程代号”、列“成绩”、列“学期号”，请统计学期号为“第三学期”的每个学生所有科目的平均成绩。

答:SELECT 学号,AVG(成绩)
FROM 学生成绩表
WHERE 学期='第三学期'
GROUP BY 学号

6. 接上题，现在想评定该班级第二学年（包括第三学期和第四学期）的优秀学生奖学金，要求从该班级选出平均成绩排前5名的同学。请用SELECT语句显示这些同学的学号和平均成绩。（要考虑可能有多个同学并列第5名）

答:SELECT TOP 5 WITH TIES 学号, AVG(成绩) AS 平均成绩
FROM 学生成绩表
WHERE 学期='第三学期' OR 学期='第四学期'
GROUP BY 学号
ORDER BY 平均成绩 DESC

7. 接上题，学校规定前三学年（第一学期到第六学期）总平均分在90分以上而且没有一次考试不及格（成绩>=60分）的同学可以参加学校最高级别的评奖。请用SELECT语句显示该班级中满足评奖条件的候选人的学号和前三学年的总平均分。

答:
SELECT 学号, AVG(成绩) AS 三年平均成绩
FROM 学生成绩表
WHERE 学期='第一学期' OR 学期='第二学期'
                   OR 学期='第三学期' OR 学期='第四学期'
   OR 学期='第五学期' OR 学期='第六学期'
GROUP BY 学号  HAVING  AVG(成绩)>=90 AND MIN(成绩)>=60
ORDER BY 三年平均成绩 DESC

8. 查看在SELECT语句中使用GROUP BY子句和CUBE运算符的结果。在结果集中你将发现空值，而且空值在SELECT语句使用的表中是允许出现的。你是如何用空值区分详细数据行与汇总数据行。

答:在允许空值的列中使用GROUPING函数。如果是一个汇总行，通过GROUPING函数产生的列值会出现数字１。

9. 需要提供前１００个产品和销售倒数百分之五的产品的列表。可以使用SELECT TOP n [PERCENT]语句解答每个问题吗？还有其他方法来解答这些问题吗？

答:可以。可以使用SELECT TOP n[PERCENT]语句解答每个问题。第一个问题可以用SELECT TOP 100…ORDER BY…DESC语句，这样会使销售量最高的列在上面。
第二个问题可以用SELECT TOP 5 PERCENT…ORDER BY…ASC语句，这样会使销售量最低的列在上面。

 

第9章  多表联接

1. 你在SQL Server 2000数据库中创建了如下两个表：
    CREATE TABLE 雇员表
      ( 雇员代号  int  IDENTITY(10001,1) PRIMARY KEY  NONCLUSTERED,
        雇员姓名  varchar(20)  NOT NULL,
        通信地址  varchar(200)      NULL
      )
    CREATE TABLE 定单表
      ( 定单号   int       IDENTITY(1,1) PRIMARY KEY,
        雇员代号 int       NOT NULL,
        客户代号 int       NOT NULL,
        订购日期 datetime  NOT NULL,
        订购金额 money    NOT NULL
      )
你需要获得2003年9月1日每个雇员的最高的一笔销售金额，要求列出“雇员代号”、“雇员姓名”、“订购日期”、最高的“订购金额”。下面哪个语句能完成该功能？
A.
  SELECT a.雇员代号,a.雇员姓名,b.订购日期,b.订购金额
      FROM 雇员表 AS a LEFT OUTER JOIN 定单表 AS b
        ON a.雇员代号 = b.雇员代号
      WHERE b.订购日期 = '09/01/2003'
        AND 订购金额 IN (SELECT MAX(订购金额) FROM 定单表)

B.
  SELECT a.雇员代号,a.雇员姓名,b.订购日期,MAX(订购金额)
      FROM 雇员表 AS a LEFT OUTER JOIN 定单表 AS b
        ON a.雇员代号 = b.雇员代号 AND b.订购日期 = '09/01/2003'
      GROUP BY a.雇员代号,a.雇员姓名,b.订购日期

C.
  SELECT a.雇员代号,a.雇员姓名,b.订购日期,MAX(订购金额)
      FROM 雇员表 AS a INNER JOIN 定单表 AS b
        ON a.雇员代号 = b.雇员代号
      WHERE b.订购日期 = '09/01/2003'
      GROUP BY a.雇员代号,a.雇员姓名,b.订购日期,b.定单号

D.
  SELECT a.雇员代号,a.雇员姓名,b.订购日期,MAX(订购金额)
      FROM 雇员表 AS a INNER JOIN 定单表 AS b
         ON a.雇员代号 = b.雇员代号
      WHERE b.订购日期 = '09/01/2003'
         AND 订购金额 IN (SELECT MAX(订购金额) FROM 定单表)

答:B

2.  Duluth Mutual Life健康保健组织有一个用于跟踪医生和他们的病人的信息的数据库。这个数据库有下列数据表。
表9- 1  医生数据表
列 数据类型和约束
doc_id char(9), PRIMARY KEY
Fname char(20)
Lname char(25)
Specialty char(25)
Phone char(10)
表9- 2  病人数据表
列 数据类型和约束
pat_id char(9), PRIMARY KEY
Fname char(20)
Lname char(25)
insurance_company char(25)
Phone char(10)
表9- 3  病历表
列 数据类型和约束
admission_date datetime, PRIMARY KEY (composite)
pat_id char(9), PRIMARY KEY (composite),
FOREIGN KEY to patient.pat_id
doc_id char(9), FOREIGN KEY to doctor.doc_id
Diagnosis varchar(150)
根据这个表结构，回答下列问题。
a) 如何生成一个信息表，列出病人的姓名和在医院治疗的日期？

答:用它们共同拥有的列pat_id，联接病人表和病历表。

b) 如何查询某个特定医生所诊治过的所有病人？

答:您需要联接所有的表。病人和医生的关系属于多对多的关系。尽管您只需要病人表和医生表中的数据，您也必须借助于casefile表（病历表），因为此表关联医生与病人。用列doc_id把医生表和病历表联起来，用列 pat_id把病人表和病历表联接起来。用WHERE子句确定返回哪一个医生的行。

c) 如何查询具有相同专业的医生？

答:需要把医生表进行**接。使用列specialty进行**接，将结果集限定于doc_id不匹配的行。注意需要在WHERE子句中使用操作符（>）,以消除查询中的镜像行。

d) 如何为医生和病人生成一个姓名和电话列表？

答:先编写写从医生表中查询医生姓名和电话号码的查询。再写从病人表中查询病人姓名和电话号码的查询，使用操作符UNION合并两个查询。

3.  学生信息系统中有两个表：“学生信息表”和“系信息表”。在学生信息表中有列“学号”、列“姓名”、列“出生日期”、列“系号”，在系信息表中有列“系号”、列“系名”、列“系主任”。列“系号”是学生信息表引用系信息表的外键。请用SELECT语句列出所有学生的学号、姓名和所在的系名。

答:SELECT a.学号, a.姓名, b.系名
FROM 学生信息表 AS a INNER JOIN 系信息表 AS b ON a.系号=b.系号

4.  接上题，请用SELECT语句统计并显示每个系的系号、系名以及每个系学生的人数。要求：如果新创建的系还没有学生，就不要显示该系的信息。

答:SELECT b.系号, b.系名, count(*) AS 系学生人数
FROM 学生信息表 AS a INNER JOIN 系信息表 AS b ON a.系号=b.系号
GROUP BY b.系号, b.系名

说明：用 count(学号) 或 count(*) 都可以。

5.  接上题，请用SELECT语句统计并显示每个系的系号、系名以及每个系学生的人数。要求：如果新创建的系还没有学生，该系也要显示，并且该系的学生人数为0。

答:SELECT b.系号, b.系名, count(学号) AS 系学生人数
FROM 学生信息表 AS a RIGHT JOIN 系信息表 AS b ON a.系号=b.系号
GROUP BY b.系号, b.系名

说明：只能用 count(学号)，不能用count(*)，否则对新创建还没有学生的系，学生个数会为1。

       6.  接上题，教师信息表有列“教师代号”、列“教师姓名”、列“出生日期”、列“系号”，其中列“系号”是教师信息表引用系信息表的外键。请用SELECT语句列出系号为‘12’的系的所有人员的姓名和出生日期（包括学生和教师）。

答:SELECT 姓名,出生日期  FROM 学生信息表 WHERE 系号='12'
UNION
SELECT 教师姓名,出生日期  FROM 教师信息表 WHERE 系号='12'

 

第10章  使用子查询

1.  学生信息系统中有两个表：“学生信息表”和“系信息表”。在学生信息表中有列“学号”、列“姓名”、列“出生日期”、列“系号”，在系信息表中有列“系号”、列“系名”、列“系主任”。列“系号”是学生信息表引用系信息表的外键。请用“相关子查询作为表达式”方法写出SELECT语句，来统计并显示每个系的系号、系名以及每个系学生的人数。要求：如果新创建的系还没有学生，则显示该系的学生人数为0。

答:
SELECT b.系号, b.系名,'系学生人数'=(SELECT count(*) FROM 学生信息表ASa
                                                                  WHERE a.系号=b.系号 )
FROM 系信息表 AS b

 

2.  接上题，使用派生表方法写出SELECT语句，来统计并显示每个系的系号、系名以及每个系学生的人数。（1）要求：如果新创建的系还没有学生，则不显示该系；（2）要求：如果新创建的系还没有学生，则显示该系的学生人数为0。

         答:
 SELECT a.系号, a.系名, b.系学生人数
       FROM 系信息表 AS a INNER JOIN
                 ( SELECT 系号,count(*) AS 系学生人数
                   FROM 学生信息表
                   GROUP BY 系号 ) AS b
                 ON a.系号=b.系号

       SELECT a.系号, a.系名, ISNULL(b.系学生人数, 0)
       FROM 系信息表 AS a LEFT OUTER JOIN
                 ( SELECT 系号,count(*) AS 系学生人数
                   FROM 学生信息表
                   GROUP BY 系号 ) AS b
                   ON a.系号=b.系号

 
3. 接上题，请显示学生人数最多的系的系号、名称和学生人数。

答:
  SELECT b.系号, b.系名, count(学号) AS 系学生人数
      FROM 学生信息表 AS a INNER JOIN 系信息表 AS b ON a.系号=b.系号
      GROUP BY b.系号, b.系名
      HAVING count(学号)  = (SELECT max(系学生人数)
                            FROM ( SELECT 系号, count(学号) AS 系学生人数
                                    FROM 学生信息表 
                                    GROUP BY 系号) AS T )

4. 在SQL Server 2000数据库中创建了如下的一些表：
CREATE TABLE 部门表
( 部门号    char(2)        not null PRIMARY KEY,
        部门名称  varchar(30)    not null
)
CREATE TABLE 雇员表
( 雇员代号    char(4)      not null PRIMARY KEY,
  雇员姓名    varchar(20)  not null
)
CREATE TABLE 任职表
( 雇员代号   char(4)      not null,
        部门号     char(2)      not null,
        开始时间   datetime     not null,
        备注       varchar(1000)    null,
CONSTRAINT PK_Works PRIMARY KEY(雇员代号,部门号, 开始时间),
           CONSTRAINT FK_Employees FOREIGN KEY (雇员代号)
REFERENCES 雇员表(雇员代号) ON DELETE CASCADE,
           CONSTRAINT FK_Departments FOREIGN KEY (部门号)
 REFERENCES 部门表(部门号) 
)
现在人事经理需要获得在每个部门都工作过的雇员的名单，请写出带有子查询的SQL语句来完成改任务。

答:
SELECT 雇员姓名
FROM 雇员表 AS a
WHERE NOT EXISTS( SELECT b.部门号
                        FROM 部门表 AS b
                        WHERE b.部门号 NOT IN ( SELECT c.部门号
                                                FROM 任职表 AS c
                                                WHERE a.雇员代号=c.雇员代号) )

5.   实验方案
Duluth Mutual Life健康保健组织有一个用于跟踪医生和他们的病人的信息的数据库。这个数据库有下列数据表。
? 医生数据表
表 0 1
列 数据类型和约束
doc_id char(9), PRIMARY KEY
Fname char(20)
Lname char(25)
Specialty char(25)
Phone char(10)

? 病人数据表
表 0 2
列 数据类型和约束
pat_id char(9), PRIMARY KEY
Fname char(20)
Lname char(25)
Insurance_company char(25)
Phone char(10)
? 病历表
表 0 3
列 数据类型和约束
Admission_date datetime, PRIMARY KEY (composite)
pat_id char(9), PRIMARY KEY (composite),
FOREIGN KEY to patient.pat_id
doc_id char(9), FOREIGN KEY to doctor.doc_id
Diagnosis varchar(150)
根据这个表结构，回答下列问题。
a) 如何用一个查询语句，查询出数据库中最初入院治疗的病历？

答:在单值子查询中使用函数MIN来确定最早入院治疗的日期。用WHERE子句把子 查询的结果同每个病例的入院治疗日期进行比较。

b) 想要知道入院治疗的总数，按病人姓名排序，如何做到这一点？你的方法有哪些优点和缺点？

答:你可以写一个含有相关子查询的SELECT语句，用COUNT函数计算每个病人入院治病的总次数。
SELECT pat_id, pat_name
 ,(SELECT count(*) FROM casefile C WHERE C.pat_id = P.pat_id)
FROM patient AS P
也可以用含有GROUP BY子句和COUNT函数的联接语句。用子查询的方法可能查询效率不如用GROUP BY，但逻辑上更清晰。

第11章  修改数据

1. 你在SQL Server 2000数据库中创建了定单表：
    CREATE TABLE 定单表
      ( 定单号     int IDENTITY(100001,1) PRIMARY KEY,
        雇员代号   int        NOT NULL,
        客户代号   int        NOT NULL,
        订购日期   datetime   NOT NULL,
        订购金额   money     NOT NULL
      )
  由于业务量很大，定单表已经存放了大量的数据。你想删除3年以前的订购信息，      下面哪个语句能够完成改任务？
A.  DELETE FROM 定单表 WHERE 定单日期 < DATEADD( YY, -3, GETDATE() )
B.  DELETE FROM 定单表 WHERE 定单日期 < DATEADD( YY, 3, GETDATE() )
C.  DELETE FROM 定单表 WHERE 定单日期 < GETDATE() - 3
D.  DELETE FROM 定单表 WHERE 定单日期 < GETDATE() + 3

答:A

2.  学生信息系统中有两个表：“学生信息表”和“系信息表”。在学生信息表中有列“学号”、列“姓名”、列“出生日期”、列“系号”、列“学生备注”，在系信息表中有列“系号”、列“系名”、列“系主任”、列“系简介”。列“系号”是学生信息表引用系信息表的外键。假设成立了一个新的系，“系号”为“18”，“系名”为“生命科学系”，“系主任”和“系简介”为空值。请用INSERT语句添加该系到系信息表，要求写出两种形式的INSERT语句。

答:
INSERT INTO 系信息表(系号,系名)
VALUES('18','生命科学系')
或
INSERT INTO 系信息表
VALUES('18','生命科学系',NULL,NULL)

3.  接上题，有一个新学生报到，该学生的学号为“03120001”，姓名为“张三”，出生日期为“1982-01-01”，所在的系名为“计算机科学系”，没有“学生备注”。请用INSERT语句添加该学生到学生信息表。提示：使用局部变量‘@系号’。

答:
DECLARE @系号 varchar(20)
SELECT @系号=系号 FROM 系信息表 WHERE 系名='计算机科学系'
INSERT INTO 学生信息表
VALUES('03120001','张三','1982-01-01',@系号,NULL)

说明：INSERT 语句中要插入的值不允许使用子查询，只允许使用标量表达   式，即下面是错误的。
INSERT INTO 学生信息表
VALUES('03120001','张三','1982-01-01',
( SELECT @系号=系号 FROM 系信息表
WHERE 系名='计算机科学系' )
,NULL)

        4.  接上题，请用DELETE语句删除所在系的系名为“计算机科学系”的所有学生的纪录。

答:
SELECT * FROM 学生信息表
DELETE 学生信息表
WHERE 系号= (SELECT 系号 FROM 系信息表
 WHERE 系名='计算机科学系')

       5.  接上题，由于学号为“03120001”的学生获得国际象棋世界冠军，要同时在学生信息表中该学生纪录的列“学生备注”和系信息表的列“系简介”添加一句话：“2006年学生”+该学生姓名+“荣获国际象棋世界冠军。”。请用一个事务完成该任务。

答:
DECLARE @系号 varchar(2),@姓名 varchar(20), @新备注 varchar(200)
BEGIN TRANSACTION
     SELECT @系号=系号,@姓名=姓名
FROM 学生信息表 WHERE 学号='03120001'
     SET @新备注='2006年学生'+@姓名+'荣获国际象棋世界冠军。'
     UPDATE 学生信息表
         SET 学生备注= ISNULL(学生备注,'')+@新备注
         WHERE 学号='03120001'
IF @@ERROR <> 0
         BEGIN
             RAISERROR ('错误，不能更新学生备注！', 16, -1)
             ROLLBACK TRANSACTION
             RETURN
         END
      UPDATE 系信息表
         SET 系简介= ISNULL(系简介,'')+@新备注
         WHERE 系号=@系号
      IF @@ERROR <> 0
         BEGIN
             RAISERROR ('错误，不能更新系简介！', 16, -1)
             ROLLBACK TRANSACTION
             RETURN
         END
COMMIT TRANSACTION

       6.  你是一个关注健康计划的数据库管理员。使用下面的语句来创建physicians表：
CREATE TABLE dbo.physicians (
 physician_no int IDENTITY (100, 2) NOT NULL
,f_name varchar (25) NOT NULL
,l_name varchar (25) NOT NULL
,street varchar (50) NULL
,city varchar (255) NULL
,state varchar (255) NULL
,postal_code varchar (7) NULL
,co_pay money NOT NULL CONSTRAINT phys_co_pay DEFAULT (10)
)
GO
a)     必须提供最少多少个列值才能把一个新的行添加到表中？

答:必须提供至少两列的数据。至少INSERT语句要包含f_name和 l_name的值。所有其它列都允许空值或存在为它们产生的默认值。

b)   已增加了参加活动的医师的服务报酬。你如何为所有医生的co_pay字段值增加12%？

答:用如下形式的UPDATE语句：
UPDATE physicians SET co_pay = (co_pay + co_pay * .12)

c)     你如何从 physicians表中删除所有行？

答:使用DELETE语句或TRUNCATE TABLE语句。

第12章  全文索引查询

1. 你在SQL Server 2000数据库中创建作文表来记录学生的作文和老师的评语：
CREATE TABLE 作文表
    （ 作文代号    int      IDENTITY(1,1) PRIMARY KEY,
       作文内容    text     NOT NULL,
       学生代号    char(8)  NOT NULL,
       教师评语    text     NOT NULL,
       教师代号    char(4)  NOT NULL,
     )
你运行下面的查询语句：
     SELECT * FROM 作文表 WHERE CONTAINS(作文内容,'李白')
你确信有满足该查询的记录，但是执行该语句后发现没有任何记录返回。
你应该怎么做？(选择两个)
    Ａ.  为列“作文内容”创建非聚集索引；
    Ｂ.  为列“作文内容”创建聚集索引；
C.   把作文表加入到全文目录中
D.   创建一个调度作业来更新全文目录。

答:C、D

2. 下面是出版商信息表Publishers和书信息表Books的创建语句。
CREATE TABLE Publishers
        ( publisher_id     int          PRIMARY KEY,
publisher_name  varchar(100)  not null )
CREATE TABLE Books
        ( book_id      int  CONSTRAINT PK_title_id PRIMARY KEY,
         book_title     nvarchar(200) not null,
book_content  ntext        not null,
publisher_id   int          not null,
CONSTRAINT FK_publisher_id
FOREIGN KEY (publisher_id)  REFERENCES Publishers(publisher_id)
)
你已经把表Books加入全文搜索目录。请在书信息表Books中的列book_title查询包含单词“概率”和“统计”的记录。

答: 
SELECT * FROM Books
       WHERE CONTAINS(book_title, '概率 AND 统计 ' )

        3.  接上题，请在该书信息表Books中的列book_content查询有关“Microsoft Office XP允许通过使用数字证书”的记录。

答:
 SELECT * FROM Books
      WHERE FREETEXT( description,'" Microsoft Office XP允许通过使用数字证书"')

        4.  接上题，请检索书的内容包含“计算机 冲浪 网页 点击 财富”字样的书的书名及其  出版商的名称，要求返回前100个最相似的记录。

答:
SELECT b.book_title, a.publisher_name
 FROM Publishers AS a
 INNER JOIN CONTAINSTABLE (Books, book_content,
            '计算机 AND 冲浪 AND 网页 AND点击 AND财富',100) AS b
  ON a. publisher_id = b. publisher_id
 ORDER BY b.key DESC

5.  为什么应该使用全文检索扩展来查询文本数据，而不用标准的Transact-SQL语法？

答:标准的Transact-SQL语法只能返回匹配的文本域，不能使用常规索引。而全文查询对字和模糊匹配进行了索引和优化。

6.  使用全文检索查询后，返回的结果却是空记录，实际上该表中应该含有匹配的数据，该如何解决？

答:通过检查PopulateCompletionAge的属性值，确定上一次全文索引已经更新。也可以检查 PopulateStatus和 ItemCount属性的适当值。

7.  要检索garden description列中位于“roses”附近的任何形式的 “cultivate”，在SELECT语句中需要使用哪些关键字来创建这个检索？

答: CONTAINS、FORMSOF和 NEAR。

8.  如要你想在结果集中生成排序，那么应该使用CONTAINS的哪种形式？

答: CONTAINSTABLE。

9.  当创建一个新项目并且要加载数据库结构或向一个已存在的项目插入一个新表时，为了定义表的语义关系，需要表的什么特性？

 答:  所有表需要定义一个主键

 

第13章  规划索引

        1.  在一个非聚集索引的B-树中，已知它有4级（根结点为第一级，叶级为第四级）。若其中有一级的索引指针指向的是真实的行所在的位置，请问它处于第几级？
        A　第一级
        B　第二级
        C　第三级
        D　第四级

答:D

        2.   在一张客户信息表中，已知在客户的姓的列上有非聚集索引，且它有3级（根结点为第一级，叶级为第三级）。假如在其上使用非聚集索引定位一条记录要进行N次的页面读取。那么如果在其客户ID列上新建一个聚集索引，且它有4级（根结点为第一级，叶级为第四级），那第在其上使用非聚集索引定位一条记录要进行M次的页面读取,那么有：
        A    M比N多2
        B　M比N多3
        C　M比N多4
        D    N比M多2
        E　N比M多3
        F　N比M多4

答:B

        3.  你是一家电信公司的数据库管理员，准备在一张顾客信息表Customers上创建索引来优化查询。已知表上最经常被查询到的列有三个，一个是客户的ID即：customerID，每个ID值都是不重复的；第二个是用户类型customerType,总共有10种可能值；另外一个是客户所用的设备customerDevice，有200种可能值，那么应该创建怎么样的索引才最有助于优化查询？
A. 分别在三个列上创建非聚集索引　
B. 分别在三个列上创建聚集索引
C. customerID、customerDevice、customerType上创建非聚集索引
D. customerID、customerDevice、customerType上创建聚集索引
E. customerType、customerDevice、 customerID上创建非聚集索引
F. customerType、customerDevice、 customerID上创建聚集索引

   答:D

        4.  设有一个职员表为Customers,其上有客户姓名（Name），客户ID（ID）等列，表 执行如下语句：
          CREATE CLUSTERED INDEX idx ON Customers(Name)
      得到以下错误　
　      Cannot create more than one clustered index
          原因是什么，为什么为出错?

  答:说明表上已经有了聚集索引，只能在同一张表上创建一个聚集索引。因为聚集索引会决定表的物理排列，由于只可能有一种排列方法，所以只能创建一个聚集索引。

       5.  设有一个职员表为Customers,其上有客户姓（Last Name），名字（First Name）客户ID（ID）等列，先在LastName 和FirstName上创建一个称为idxNames的非聚集的复合索引，然后在客户ID上创建唯一的聚集索引uidxID，说明在创建聚集索引时非聚集索引会有什么变化

  答: 创建聚集索引时非聚集索引将被重建，因为创建聚集索引将改变表中行的物理位置，而且在有聚集索引的表上的非聚集索引的B树的叶级存放的所有的鍵值和其对应的聚集索引的关键字，而之前的非聚集索引的B树的叶级存放的是所有的键值和其相对应的行ID。因此要进行非聚集索引的重建。

        6.   如果顾客表没有索引，SQL Server如何为客户Eva Corets查找行？
  答:SQL Server必须执行表扫描，读取表中的每一行来查找符合要求的行。

        7.   一个表可以创建多少个聚集索引？
  答: 一个。聚集索引定义数据页的物理存储并且表中的数据只能存储在一个位置。

8.   在表已经有聚集索引时，非聚集索引如何识别父行？在表没有聚集索引时，非聚集索引又如何识别数据行？
     答:在聚集索引存在时，非聚集索引为每一个被索引的行存储聚集索引。在没有聚 集索引时，非聚集索引存储文件ID、页码和数据行的RID。

9.    不包括索引的字段的扩展会导致页拆分，页拆分将把行移动到新的页中。这种移动会对表中的非聚集索引产生什么样的影响？

 答:这对非聚集索引没有影响。如果存储有聚集索引，聚集的值不会改变。非聚集索引将继续指向行，因为聚集索引关键字没有改变。如果没有聚集索引，在原记录的位置会留下一个转发指针指向新的记录。在任一情况下，非聚集索引不需要改变。

10.   考虑在表中的company name、last name和first name列上创建一个组合聚集索引。在创建索引时，什么是应该考虑的重点，为什么要考虑？还有更好的解决方法么？
答:尽可能保持聚集索引键尽量的小。大的聚集索引键会在所有的非聚集键上产生较大的影响。聚集索引越大，其效率越低。键值增加时，该值需要占有页上更多的空间，这样的页就只能容纳少量的键值，导致聚集索引树（B树）变得更大。聚集索引越大（它就会有更多的非叶级），那么需要遍历索引树的I/O周期越长。
 同样的，唯一的组合键最好作为非聚集索引或多索引来定义。
 可以考虑更好的解决方法是在customerID列（如果存在）或last name列上创建索引。如果customerID列并不存在，应该考虑使用标识属性或添加包括有通过在行中抽取数据的不同部分而衍生出来的键值的新列。

 

第14章  创建并维护索引

1. 假设你负责一家公司的数据库管理。用户向你抱怨查询据库Sales中的products表（表上建有idxProID索引）的速度太慢，你经过测试，发现可能是由于统计信息过时导致的。为了使将来不再发生这种问题，你要用哪个语句保证整个数据库的统计自动更新。
       A  DBCC SHOW_STATISTICS (products, idxProID)
       B   UPDATE STATISTICS ‘products’
       C   sp_autostats  ‘products ‘，ON
       D  ALTER　DATABASE　Sales SET AUTO_CREATE_ STATISTICS ON

答:D

2. 已知数据库Sales中的products表上建有idxProID索引，你想知道这个索引是个聚集索引还是一个非聚集索引，可用以下哪个语句？（不定项选择）
       A  sp_helpindex products  idxProID
       B  sp_help idxProID
       C  sp_help products
       D  USE Sales
              GO
  SELECT indid
                   FROM dbo. sysindexes
  WHERE name = ‘idxProID’

答:C

3. 在你管理的数据库中有一张名为products的表，在监测products表的磁盘I/O的时候，你怀疑表的索引存在很多的碎片。已知products表在主键上有一个叫作idxProid的索引，另外还有nid1、nid2两个非聚集索引。你想使用耗费最小资源的办法重建products表上的索引，
       应使用以下哪种方法。
       A 
DBCC DBREINDEX(products)
       B 
 ALTER TABLE products DROP CONSTRAINT  idxProid
       ALTER TABLE ADD CONSTRAINT primary key idxProid(...)
       C 

 CREATE INDEX idxProid ON products(...) WITH DROP_EXISTING
       CREATE INDEX nid1 ON products(...) WITH DROP_EXISTING
       CREATE INDEX nid2 ON products(...) WITH DROP_EXISTING
       D
       DROP INDEX products.idxProid
       DROP INDEX products.nid1
       DROP INDEX products.nid2
       CREATE INDEX idxProid ON products(...)
       CREATE INDEX nid1 ON products(...)
       CREATE INDEX nid2 ON products(...)

答:A

4. 下面的一条SQL语句是用来创建一个索引的，试解释其作用。
　     CREATE UNIQUE CLUSTERED INDEX index1
              ON table1(column1,column4 DESC)
       WITH  PAD_INDEX, FILLFACTO = 60,  DROP EXISTING

答:该语句将在名为table1表上的column1和column4上建立一个组合的唯一聚集索引。另外column4上指定 DESC表示在该行上的排序次序为降序。另外在WITH中指定了FILLFACTO = 60这将使索引的页级只有60%被填满，还指定了PAD_INDEX这将使索引的非页级也只有60%被填满。最后WITH中还指定了DROP_EXISTING，这里如果原表中存在名为index1的索引，则它的特性将会上述语句所更改，使用这个选项的优点是我们不用删除一已存在的索引再重建它。
 
 　
5. table1(存在于db1数据库上)上存在有一个index1索引，执行下列SQL语句
　   SELECT id, indid, reserved, used, origfillfactor, name
       FROM db1.dbo.sysindexes
       WHERE name = ‘index1’
       返回  
              
Id indid reserved used origfillfactor name
209452452 1 20 20 60 index1
(1 row(s) affected)
试说明SQL语句和返回的结果.

答: SQL语句在db1的系统索引表sysindexes查找名字为’index1’行的相关信息，也就是从系统索引表中找出索引index1的相关信息。 返回值中,id表示的是index1的ID值，indid为1表示该行是一个聚集索引的信息，reserved表示系统为索引分配的页面数，used表示该索引用的页面总数，origfillfactor表示索引创建时指定的FILLFACTO的值，默认为0，但在index1的创建中已指定了60，最后一列name表示索引名。

 6.  你是负责管理大型客户数据库的数据库管理员。最近，当提交客户定单时，定单处理部门发现系统反映时间变慢。你的经验告诉你在Orders和Order Details表中的索引是正确的。是什么原因导致执行变慢了呢？

答:索引统计可能没有被自动地维护，因此，随着数据的修改它将越来越过时。FILLFACTOR选项需要被重新应用到为新的定单(行)分配的表和索引空间上，而新的定单是要插入到Orders和Order Details表中的。

7.  SQL Server自动创建和更新统计信息有什么好处？

答:让查询优化器自动创建和更新统计表来可以减少管理负担并增加查询性能。

8. 你负责维护销售部门接受客户定单的数据库。销售数据库执行性能差。你的经理让你在两天内改善性能。解决这个问题的最恰当工具是什么？

答:用索引优化向导。第一天，创建一个工作负荷文件用于记录一整天的用户活动。在第二天，对工作负荷文件运行索引优化向导，查看索引分析并应用索引优化向导建议的索引。

 

第15章 实现视图

1. 你负责维护一个电信公司的数据库，不久前你在数据库上创建了一个视图vwOrders,并且在创建视图时使用了WITH SCHEMABINDING选项。现在你要修改这个vwOrders视图，增加一个WITH CHECK OPTION的选项。要求不能改动原有的选项，你要怎么做呢？（双项选择）
       A　删除vwOrders，并用WITH SCHEMABINDING和WITH CHECK OPTION选   
              项重建视图
       B　删除vwOrders，并用WITH CHECK OPTION选项重建视图
       C　改变视图，并用WITH SCHEMABINDING和WITH CHECK OPTION选项
       D　改变视图，并用WITH CHECK OPTION选项
      
答: AC

2. 你是公司的数据库管理员，有一天你要删除数据库中的一个视图 vwOrders（创建时没有使用WITH ENCRIPTION）时，系统提示存在其它视图依赖此视图，从而删除失败，你要怎么知道是哪个视图依赖这个视图呢？（不定项选择）
       A　通过查询系统表syscomments来得到视图的定义，从而得到依赖信息
       B　使用sp_helptext ‘vwOrders’来得到视图的定义，从而得到依赖信息
       C　使用 sp_depends ‘vwOrders’，来得到依赖信息
       D 查询INFORMATION_SCHEMA.VIEW_TABLE_USAGE视图来得到依赖信息

答:C

3. 用下述语句创建视图
      CREATE VIEW  ProductsView
      AS  
      SELECT ProductID, Price, Company
      FROM Supplier
          INNER JOIN Products
          ON Suppliers.ID = Products.SupplierID
      ORDER BY ProductID
      出现错误，为什么，如何修改。

答: 因为在视图的创建中如果包含了ORDER　BY子句，则要使用TOP语句才能生成视图。设我们要选出全部符合条件的记录，语句可改为如下：
CREATE VIEW  ProductsView
AS
SELECT TOP 100 PERCENT ProductID, Price, Company
FROM Supplier
     INNER JOIN Products
     ON Suppliers.ID = Products.SupplierID
ORDER BY ProductID

4. 在SQL Server上的Northwind数据库上创建这样一个叫作vwCustomerOrders的视图，视图中使用SELECT语句在以Orders表中的订单ID、Customers数据表中的公司名称（CompanyName）的和联系名称(ContactName),通过客户ID联接起来，并授于sales帐户在视图上的查询权限，写出创建视图，和授予权限的SQL语句，并说明sales帐户要具有在Orders和Customers表中的查询权限吗？

答:语句如下：
USE Northwind
GO
CREATE VIEW vwCustomerOders
AS
SELECT o.OrderID, c.CompanyName, c.ContactName
  FROM Orders o JOIN Customers c
  ON  o.CustomerID = c.CustomerID
GO
GRANT SELECT ON vwCustomerOders TO sales
另外，sale帐户不用具有Orders和Customers表中的查询权限，也可以对视图进行查询。

5. 视图的优点是什么？

答: 通过使用视图用户可以把注意力放在需要的数据上，也可以使用户对数据的操作变得简单。对用户来说，数据库和查询的复杂性被隐藏了，这样可以让用户看到更友好的名字。通过只允许用户访问视图中数据的这种方法，视图提供了一种安全机制。在视图上创建索引和通过视图分割数据可以优化性能。

6. 假设已经实现了联接Customer、Orders和Order Details表的查询，它列出了顾客订单的详细情况，例如物品的数量和要求的交货日期。在顾客改变现有的订单时，雇员需要更新Orders表和Order Details表。在不具有访问基表权限的情况下如何完成该任务？

 答: 在查询上创建一个名为OrderDetailsView的视图。在视图上授权RequiredDate和Quantity列的更新权限。这样确保雇员只在Orders和Order Details表更新这些列。

7．在视图定义中使用WITH CHECK OPTION有什么益处？

       答: 该选项强制视图上所有数据修改语句都要符合定义视图的SELECT中定义的准则。

8．在使用视图时应该考虑什么？

答:视图中引用的对象在创建视图时进行验证。为了使分配给视图的权限得到维护，可以修改视图。删除或修改基础表时会影响视图，如果视图的所有者不是dbo用户，用户的名字必须作为视图名字的一部分来指定。同一所有者必须拥有视图相关的所有对象以避免破坏所有权链。复杂性的隐藏会产生难以确定原因的性能问题。

第16章 实现存储过程

1. 创建一个名为FindCustomer存储过程，可以用它来找出SQL　SERVER中的northwind数据库的Customer表中，CustomerID为指定值（输入参数）的记录的ContactName字段的名称，然后调用这个存储过程，找出CustomerID为’thecr’的ContactName字段值，写出创建存储过程的SQL语句和调用的命令，以下选项哪一个是正确的。
 A
创建语句：
USE northwind
GO
CREATE PROCEDURE dbo. FindCustomer
$CustomerID char(5)
LIKE
SELECT contactName
From Customers WHERE CustomerID=$CustomerID
调用语句
EXEC northwind.dbo. FindCustomer
$CustomerID= ‘thecr’

B
创建语句：
USE northwind
GO
CREATE PROCEDURE dbo. FindCustomer
@CustomerID char(5)
AS
SELECT contactName
From Customers WHERE @CustomerID=CustomerID
调用语句
EXEC northwind.dbo. FindCustomer
@CustomerID= ‘thecr’
     
      C
创建语句：
USE northwind
GO
CREATE PROCEDURE dbo. FindCustomer
@CustomerID char(5)
AS
SELECT contactName
From Customers WHERE CustomerID=@CustomerID
调用语句
EXEC northwind.dbo. FindCustomer
CustomerID= ‘thecr’
  
  D
创建语句：
USE northwind
GO
CREATE PROCEDURE dbo. FindCustomer
@CustomerID char(5)
LIKE
SELECT contactName
From Customers WHERE CustomerID=@CustomerID
调用语句
EXEC northwind.dbo. FindCustomer
@CustomerID= ‘thecr’

答: B

2. 你在northwind数据库中创建了一个名为overdueOrders的储存过程，而且没被加密。那么以下哪些方法可以查看存储过程的内容。(不定项选择)
  A  EXEC sp_helptext 'overdueOrders'
B  EXEC sp_help overdueOrders
C  EXEC sp_stored_procedures 'overdueOrders'
D  EXEC sp_depends 'overdueOrders'
E   查询syscomments系统表
F   查询sysobjects系统表

答: AE

3. 创建一个名为FindCustomer1的存储过程，可以用它来找出SQL SERVER中的northwind数据库的Customer表中，CustomerID为指定值（输入参数）的记录的ContactName字段的名称，另外指定一个输出参数LineNum做为输出参数，还有必须在存储过程中判断CustomerID不能为空串，是的话要打印出出错信息，并返回错误值-1,如果查询成功在输出变量LineNum中保留选出的行数，然后返回值0。写出相应的SQL语句.

答:
USE NorthWind
GO
CREATE PROC FindCustomer3
@LineNum int OUTPUT,
@CustomerID char (5)
AS
IF LEN(@CustomerID)=0
  BEGIN
    PRINT 'You must supply a valid CustomerID'
    RETURN -1
  END
SELECT  contactName  
From Customers WHERE CustomerID=@CustomerID 
SET @LineNum = @@ROWCOUNT
RETURN 0

4. 首先自定义一个错误号为50512的用户自定义错误，错误的严重级别为10,错误的文本消息为’Can’t find the customer ID. ’，另外消息中还要加上表名和输入的CustomerID,  并且当发生消息时将消息写入 Microsoft? Windows NT? 应用程序日志中。然后创建一个名为ExistCustomerID的存储过程，以用它来找出SQL SERVER中的northwind数据库的Customer表中，指定的CustomerID是否存在，如果存在返回0，如果不存在返回错误号50512，并将消息写入 Microsoft? Windows NT? 应用程序日志中。写出定义错误消息和创建存储过程的语句。

答: 定义自定义错误消息
EXEC sp_addmessage
@msgnum = 50512,
@severity = 10,
@msgtext =  'Can’t find the customer ID:%s at table %s.',
@with_log ='true'

创建存储过程的语句如下
USE NorthWind
GO
CREATE PROC ExistCustomerID
@CustomerID char (5)
AS
SELECT  CustomerID  
From Customers WHERE CustomerID=@CustomerID 
IF @@ROWCOUNT=0
BEGIN
  RAISEERROR(50512, 10, 1, @ CustomerID, @DBNAME)
     RETURN
END
   RETURN  0

5. 已经创建了一个从数据库中删除客户的存储过程。在删除事务完成时，希望有一个自定义的错误信息写入Windows 2000应用程序日志。如何执行该任务？

答: 通过在sp_addmessage存储过程中指定@with_log参数创建一个自定义的错误信息。删除事务提交后，在存储过程中调用RAISERROR语句来生成自定义的错误信息。

6. 希望工资管理部门的用户可以在payroll数据库中插入、更新和删除数据。然而，不希望他们有访问基表的权限。那么除了创建一个视图以外，还能如何实现该目标？

答: 创建实现单一任务的存储过程。在存储过程中给工资管理部门的用户授予EXECUTE的权限。

7. 在数据库中必须修改一个存储过程，而有几个用户已被授予了执行该存储过程的权限。执行哪个语句来完成修改而又不影响现有的权限？

答: ALTER PROC。如果执行DROP PROC和CREATE PROC语句来实现想要的修改，必须再次授予用户EXECUTE权限。

第17章 实现用户定义函数

1. 你是某大型商场的数据库开发人员，要实现对商品的销售情况的复杂统计。这个统计每次根据用户提供的一个商品代号，访问一些表中的数据进行统计，最后返回一个值。你要在SELECT、UPDATE和DELETE语句中使用这个计算的结果。哪种实现方法最有效？
A.   内嵌表值用户定义函数
         B.    存储过程
         C.    视图
         D.   标量用户定义函数

        答:  D

2. 什么时候使用内嵌表值函数来代替视图比较好？

        答: 当视图不能静态确定，需要参数的时候。

3. 在学生信息管理系统中，学生信息表包含列“学号”、列“姓名”；课程信息表包含列“课程代号”、“课程名称”；学生成绩表包含列“学号”、列“课程代号”、列“成绩”，已用约束保证成绩的范围为0~100分。编写内嵌表值函数，每次输入一个学号，返回学生学习的课程的名称和分数。

答:
CREATE FUNCTION fn_RetrieveOneStudent
       (@学号 varchar(8))
RETURNS TABLE
AS
RETURN (
        SELECT 课程名称,成绩
        FROM 学生信息表 AS a
           INNER JOIN 学生成绩表 AS b ON a.学号=b.学号
           INNER JOIN 课程信息表 AS c ON b.课程代号=c.课程代号
        WHERE a.学号=@学号
       )

4. 接上题，编写用户定义函数，要求：每次输入一个学号，计算该学生的所有课程的平均分，如果是85~100分，返回“优”；如果是75~84分，返回“良”；如果是65~74分，返回“中”；如果是0~64分，返回“差”。

答:
CREATE FUNCTION fn_EvaluateOneStudent
               (@学号 char(8))
RETURNS varchar(10)
AS
BEGIN
DECLARE @平均分 integer, @等级 varchar(10)
      SELECT  @平均分=AVG(成绩)FROM 学生成绩表 WHERE 学号=@学号 
      IF @平均分 BETWEEN 85 AND 100
         SET @等级='优'
      ELSE
         IF @平均分 BETWEEN 75 AND 84
              SET @等级='良'
         ELSE IF @平均分 BETWEEN 65 AND 74
                   SET @等级='中'
              ELSE IF @平均分 BETWEEN 0 AND 64
                       SET @等级='差'
   RETURN @等级
END

5． 描述用户定义函数的三种类型的。

答:标量函数类似于内置函数；
    多语句表值函数类似于存储过程；
    内嵌表值函数类似于视图。

6．什么样的内置函数不允许出现在用户定义函数的函数体内？

答:不能使用非确定性函数，比如GETDATE()。

     7．什么类型的用户定义函数需要指明输出列的名称和数据类型？
答:标量和多语句用户定义函数要求完整的列描述，包括列名和数据类型定义。内嵌表值函数使用输出列的列名和隐含的数据类型。

 

第18章 实现触发器

1.  AFTER触发器和INSTEAD OF触发器有什么区别？

答: AFTER触发器在插入、删除或者更新语句执行后才激活，而且不能在视图或临时表上创建AFTER触发器。INSTEAD OF在插入、删除或者更新语句执行前激活，而且可以用在视图或临时表上。

        2.  请写出SQL语句，禁止表Employees中所有的触发器。

答:  ALTER TABLE Employees DISABLE TRIGGER ALL

3.  你负责开发了一个酒店管理系统。在设计系统时考虑到每年的住宿客人很多，所以在客人结账后就把该客人的所有信息从数据库中删除，不然的话几年后数据量会很大，数据库的访问就会变得很慢，而且编程也比较麻烦，要区分哪些是现有客人的信息和哪些是已经结帐的客人的信息。系统运行一段时间后，酒店管理人员要求保存已经结帐的客人的信息，但是你不想改动已经编写好的应用程序。如何才能比较轻松地完成酒店管理人员的要求。

答: 对每个用于保存客人信息的表，创建一个相同或相似的表（表的名称要不同），用于记录已经结账的客人的信息。然后在用于记录未结帐客人的信息的表上创建DELETE触发器。当客人结帐后在删除客人的信息时，这些DELETE触发器被激活，可在这时把客人的信息插入到新创建的对应的表上。

      4.  你在开发某公司的管理信息系统，其中要跟踪经理的购买情况。每个经理在预算表中都有一个记录。预算表包含列“经理代号”、列“预算总金额”、列“现有预算金额”；购买表包含列“购买代号”、列“购买金额”，列“经理代号”。每次购买都要与“现有预算金额”作比较，当该次购买的“购买金额”小于“现有预算金额”时，才允许插入到购买表中（一次购买只插入一个记录），同时自动更改预算表的“现有预算金额”。请在购买表上编写一个触发器，完成该任务。

答:
CREATE TRIGGER Purchase_Insert ON 购买表
FOR INSERT
AS
BEGIN
   IF (SELECT count(*) FROM Inserted) <> 1
      BEGIN
        RAISERROR(‘一次购买只允许插入一个记录！’,16, 1)
        ROLLBACK TRANSACTION
        RETURN
      END
   IF ( SELECT a.现有预算金额-b.购买金额
        FROM 预算表 AS a INNER JOIN
             Inserted AS b ON a.经理代号=b.经理代号)<0
      BEGIN
         RAISERROR(‘现有预算金额不足支付购买金额 ！’,16, 1)
         ROLLBACK TRANSACTION
         RETURN 
      END
   UPDATE 预算表
   SET 现有预算金额 = a.现有预算金额 – b.购买金额
   FROM 预算表 AS a INNER JOIN
             Inserted AS b ON a.经理代号=b.经理代号
END

        5.  如果存货清单管理器在INSERT语句中不提供 Products.ProductID 列的值，那么在列 的定义中必须包含什么特征？

答:列值允许空值，如果其不允许为空值，则它必须包含一个缺省值。

      6.  如果在Products 表中的ProductID列上存在一个主键约束，触发器是否能工作？为什么？

答:触发器不能工作。在修改（插入）数据前，已经进行了约束检查。一个主键约束不能为空值，因此INSERT插入语句将失败。

        7.  要使一个触发器进行工作，必须要做的是什么？

答:使用一个缺省值，它放入一个临时ProductID号作为占位符，然后让触发器分配正确的值。临时ProductID号必须是ProductID号可能范围之外的一个值。例如，9999999是一个好的选择，因为存货清单中的产品数不能多于9999999个。
另一种方法是删除主键约束。需要创建另外一个触发器来替换它，保持和检查ProductID 列的引用完整性。在ProductID 列上还必须创建一个惟一索引。

第19章 多服务器编程

        1.　添加一个Access的链接服务器，名为ACCESS_DB, Access的数据库文件位置在系统C:/DB目录下的tv.mdb，写出相应的命令（OLE DB的提供者使用Microsoft.Jet.OLEDB.4.0）。选择正确答案。
A
EXEC sp_addlinkedserver
server=’ ACCESS_DB’,
srvproduct = ‘Access’,
provider=’ Microsoft.Jet.OLEDB.4.0’,
datasrc=’C:/DB/tv.mdb’
B
EXEC sp_addlinkedserver
@server=’ ACCESS_DB’,
@srvproduct = ‘Access’,
@provider=’ Microsoft.Jet.OLEDB.4.0’,
@database=’C:/DB/tv.mdb’
C
EXEC sp_addlinkedserver
server=’ ACCESS_DB’,
srvproduct = ‘Access’,
provider=’ Microsoft.Jet.OLEDB.4.0’,
database=’C:/DB/tv.mdb’
D
EXEC sp_addlinkedserver
@server=’ ACCESS_DB’,
@srvproduct = ‘Access’,
@provider=’ Microsoft.Jet.OLEDB.4.0’,
@datasrc=’C:/DB/tv.mdb’

答:  D

        2.   将本地的所有用户连接到一个叫做ORACLE_DB的链接服务器时，都映射为一个叫做oracle_user的帐号，密码是1234,从选项中找出正确的答案。
A
EXEC sp_addlogin
@rmtsrvname = ‘ORACLE_DB’,
@useself = ‘true’,
@rmtuser=’ oracle_user’,
@rmtpassword = ‘1234’
B
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘ORACLE_DB’,
@useself = ‘true’,
@rmtuser=’ oracle_user’,
@rmtpassword = ‘1234’
C
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘ORACLE_DB’,
@useself = ‘false’,
@rmtuser=’ oracle_user’,
@rmtpassword = ‘1234’
D
EXEC sp_addlogin
@rmtsrvname = ‘ORACLE_DB’,
@useself = ‘false’,
@rmtuser=’ oracle_user’,
@rmtpassword = ‘1234’

答:  C

        3. 从本地 SQL Server Northwind 数据库的 customers 表中，以及存储在相同计算机上 Access Northwind 数据库的 orders 表中选择CustomerID相同的所有列,假设Access Northwind 数据库位于c:/Access/northwind.mdb,使用帐户admin,密码1234来访问Acess数据，另外使用‘Microsoft.Jet.OLEDB.4.0’做为OLE DB提供者，写出相应命令。

答:在本地 SQL Server Northwind 数据库中执行
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
   OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
   ‘c:/Access/northwind.mdb’;’admin’;’1234’, Orders)
   AS o
   ON c.CustomerID = o.CustomerID
GO

      4. 执行以下一个分布事务，将本地pubs数据库上的authors表中au_id 为 '409-56-7008，的au_lname列设为值'McDonald',同时执行名为remote的链接服务器上的相应数据库中的changeauth_lname存储过程（所有者为dbo）,来执行相同的更改，changeauth_lname存储过程带两个参数:第一个是要修改列的au_id的值，第二个参数是要更改的au_lname的值.写出相应命令。

答:
USE pubs
GO
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
UPDATE authors
   SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'
EXEC remote.pubs.dbo.changeauth_lname '409-56-7008','McDonald'
COMMIT TRAN
GO

      5.   你拥有一家经营邮购业务的企业，它有两个仓库，一个仓库位于美国，另一个位于亚洲。每个仓库都有SQL Server，其中存储了库存数据库的本地副本。这两台名为USsales和Asiasales的服务器通过广域网(WAN)相互连接。你的企业要求这两个数据库必须始终保存最新数据并相互同步。
a) 应使用哪种数据分布方法将一台服务器上的更改传播到另一台服务器？

答:由于严格的一致性要求，应该使用分布式事务处理来更新两个数据库。不要使用复制，因为这两个数据库必须始终保持同步。

b) 你的办公室在美国。你每天早晨都生成一个报表，用来显示在每个仓库中销售量最高的十项商品的库存量。你会如何产生这个报表？

答:你应该在USsales计算机上注册Asiasales服务器作为链接服务器，然后在USsales服务器上编写并执行查询，该查询使用具有四部分的全修饰名来访问链接服务器的信息。

c) 由于经常在仓库之间转移库存商品，所以必须同时更新两地的数据库，以保证库存数量总是最新的。你会如何做到这一点？

答:可以编写分布式查询或者存储过程来在每个数据库上增加或减少存货总量。然后编写查询，在这个查询的BEGIN DISTRIBUTED TRANSACTION和COMMIT TRANSACTION块中装入分布式查询，或者调用存储过程。使用分布式事务处理确保数据库的同步性。

d) 服务器Asiasales还拥有一个包含销售摘要信息的Microsoft Access数据库。有时候，你想访问该摘要数据来产生一个报表，该报表包含USsales库存数据库中的数据。如何在报表中将来自两个数据源的数据组合起来？

答:可以编写使用OPENROWSET函数访问Access数据库中数据的查询。要产生报表，则要将Access数据库中的表和本地SQL Server数据库中的表联接起来。

 

第20章 优化查询性能

1.   找出下列子句中，为SARG的子句。
A  ABS(num)<4
B  name  LIKE ‘%Sprite’
C  name = ‘jone%’ OR  customerID > 20000
D  name = ‘jone%’  AND  customerID > 20000

答:D

2.   你是一家销售公司的数据库管理员，最近有些员工向你抱怨进行某些查询的时候要花上几分钟，甚至十几分钟的时间。你经过检查，发现是有些员工用使用一些错误的查询命令，在彻底解决问题之前，你想先禁止员工进行这样的查询，要把所有连接的查询时间限在一定时间之内，超出时间的查询都要让它返回失败，下列哪个命令是可以达到这个目的的呢？
A  SET QUERY_GOVERNOR_COST_LIMIT
B  DBCC SHOW_STATISTICS
C  SET STATISTICS IO
D  sp_configure

答:  D

3.    假设有一张电话号码表，在表上的电话号码列上有聚集索引，在其姓氏列上有非聚集索引。现在已知聚集索引有三层，包括页层，非聚集索引也有三层，并且非聚集索引页上，每页有500条记录。现在对表上查询查找一个范围的姓氏内的号码信息，总共有1600条记录，试分析总逻辑I/O的数量。

答: 首先在非聚集索引中找出相应范围的值需要:
3（非聚集索引层数）　＋　4（1600行/ (500行/页)）=7次I/O
另外，由于表中含有聚集索引，所以非聚集索引叶级存放的是聚集索引，因此对于  每一条记录，还要在聚集索引中找出相应的记录，这需要：
　 1600 * 每行要进行的3次聚集索引的读取　＝　4800次I/O
　 所以总共要4800+7=4807次逻辑的I/O读取.

4.    在SQL Server中的Northwind数据库中的order details表和Products表中进行了某个查询，得到如下的图形执行计划。并且已知Products表中的ProductID为其主键，而order details中的ProductID也为其主键。
　
 
A
Select *
    From [order details ] o, Products p
 Where o.ProductID = p.ProductId
B
  Select o.ProductID
    From [order details ] o, Products p
    Where o.ProductID = p.ProductId
C
Select *
      From [order details ] o, Products p
Where o.ProductID <> p.ProductId
D
  Select o.ProductID
    From [order details ] o, Products p
     Where o.ProductID <> p.ProductId

答:   A

5.    一个金融分析员执行了一个运行时间很长的查询，降低了交易输入人员的反应时间。你要求金融分析员限制活动，但是，金融分析员不能说明哪些查询比其他的查询消耗更多的资源。如何才能减少对交易查询人员的影响呢？
    
答:利用查询控制器，将金融分析员的查询限制在30秒内。

 6.   利用SQL Profiler，鉴别出五种性能最差的查询。如何确定导致查询性能低下的原因？每一种方法有什么优点？
答:图形执行计划是识别查询性能低下原因的主要工具之一。图形执行计划允许查看执行查询的过程。你能够查看计划中的每一步以及执行的顺序。你也能查看到成本估计和索引或统计信息丢失的警告。还可以使用STATISTICS IO和STATISTICS TIME查看查询的附加信息，包括表扫描的次数和SQL Server处理查询所需的总的I/O数量。STATISTICS TIME语句让我们知道处理查询每个阶段所花费的时间，包括编译时间和CPU的处理时间。

    7. 你已经决定通过在表上多加一个索引来覆盖几个查询。有一个覆盖查询的索引，它能提高查询性能，并且这种性能的提高超过使用附加索引的成本。为了覆盖索引，要满足什么要求？
答:查询中所有指定的列都包含在索引中。至少存在一个非聚集索引。此外，可以使用组合索引和聚集索引来覆盖。

    8. 在检测索引的时候，注意到Client表的聚集索引在Last Name列上。你知道，通常情况下，会通过姓氏单独地查询客户。你也知道为了汇报，要通过Client Representative ID列来频繁地组合客户。那么应该在Client Representative ID列上创建非聚集索引吗？
答:否。首先应该删除Last Name列上的聚集索引，并在Client Representative ID列上创建新的聚集索引。创建新的索引能大幅度提高汇报的速度。然后，应该在Last Name列上创建非聚集索引。当通过Last Name查找单一客户时，创建非聚集索引可以得到相同的性能。

    9. 七月份，你曾在一个查询中使用优化程序提示来提高性能。三个月后，你发现查询执行的性能非常低。是什么原因呢？
答:数据可能以某种方式改变了，它使得你所指定的优化程序提示不再高效地处理查询。用户传递给查询的参数值特征也可能发生改变，引起优化程序提示不能高效地执行。

第21章 分析查询

1.  如果执行带有联接运算的查询，并且联接的列已排序，那么SQL　Server用什么联接策略效率最好？
A　哈希联接
B　合并联接
C　交叉联接
D　嵌套迭代

答:B

2.  在SQL　Server中的Northwind数据库中，进行如下的查询：
Select *
 From Orders
 Where  OrderDate BETWEEN '1996-07-19' AND '1996-07-25'
得到如下的图形查询的执行计划，那么以下情况哪些可能是正确的。（不定项选择）
 
A　在OrderDate列上存在聚集索引。
B　在OrderDate列上存在非聚集索引。
C　存在一个OrderDate、RequireDate上的复合聚集索引
D　存在一个OrderDate、RequireDate上的复合非聚集索引
E　存在一个RequireDate、OrderDate上的复合聚集索引
F　存在一个RequireDate、OrderDate上的复合非聚集索引

答:  B D

3.  使用联接提示，强制使用哈希联接来联接Northwind数据库中的Customers和Orders表，找出其中客户ID号(两个表中都是customerid列)相同的公司名（companyname列，在表Customers中）和订单号（orderid列，在Orders表中）。
A
USE Northwind
SELECT c.companyname, o.orderid
FROM Customers c INNER  JOIN Orders o
     ON c.customerid = o.customerid
B
USE Northwind
SELECT c.companyname, o.orderid
FROM Customers c INNER  JOIN  HASH Orders o
     ON c.customerid = o.customerid
C
USE Northwind
SELECT c.companyname, o.orderid
FROM Customers c  INNER HASH  JOIN Orders o
     ON c.customerid = o.customerid
D
USE Northwind
SELECT c.companyname, o.orderid
FROM Customers c  INNER JOIN Orders o HASH
     ON c.customerid = o.customerid
E
USE Northwind
SELECT c.companyname, o.orderid
FROM Customers c  INNER JOIN Orders o
     ON c.customerid = o.customerid HASH

答:C

 

4. 在SQL Server中的Northwind数据库进行以下查询
select *
from orders o, customers c
where o.customerId = c.customerId
得到的图型化，查询计划如下：

 
试说明使用了什么联接，和使用这种联接的原因。

 

答:  合并联接。
如果两个输入都是排序的，那使用合并联接的效率是最好的。从图中可以看出，customers表中的customerId是主键，所以表是按customerId排序的，另外orders表上不是按customerId排序，因此先进行了一次排序操作，然后再进行合并联接。虽然增加排序操作会增加成本，但查询优化器评估后，确定这种联接方式的原因还是优于其它的，所以就选择这种方式.

    5.  如果你正在为某个应用编写查询。不能肯定在使用AND运算符的WHERE子句中使用   多个约束的益处。在查询中使用多个AND运算符的益处是什么呢？

答: 使用的AND运算符越多，对查询的限制就越多。使用更多的AND运算符，潜在地允许查询使用了许多索引，或者提供了索引的更好选择。

    6.  下面的查询执行性能不好。尽管WHERE 子句引用的一些列上存在索引，查询优化器仍然在当前执行表扫描。是什么因素使得下述查询的性能不好呢？
SELECT * FROM member
 WHERE lastname = ‘GOHAN’
 OR expr_dt < ‘12/31/1999’
 OR region_no = 7

   
 答:当在查询中使用OR 运算符时，使用OR 运算符的每一列上拥有索引或者说是有用的索引，是很有必要的。如果仅仅一列没有索引或者说是没有有用的索引，那么查询优化器就进行表扫描。

7.  你的查询的性能还算可以行，但是你想确定是否能够改善它的性能。当时，查询优化器在执行哈希联接运算。为了改善性能可以做些什么呢？

答:哈希联接不一定不好。为了改善性能，验证联接的列是拥有索引或者说是拥有有用的索引。还可以验证查询包含WHERE 子句，验证搜索 条件限制搜索并且验证有用的索引存在于WHERE 子句所引用的列上。

第22章 管理事务和锁

1.  你是一家贸易公司的数据库管理员，当你把使用访问数据库应用程序的用户从30人增加到100人时，你发现数据库的响应速度很明显地慢了下来经过你仔细检查，发现查询和索引已进行了完全的优化，而且应用程序在使用人数小于50个并发用户时工作得很好，那么在保证每次读到的数据都是最新的情况下，以下什么办法有可能解决这个问题。
A   更新整个数据库的统计信息
B   使用sp_configure来限制每个查询的时间
C   使用乐观的并发控制而不是悲观并发控制
D   使用查询提示来指定使用正确的索引
E   在查询语句中使用NOLOCK锁提示

答:  C

2.  你是一家大型商务公司的数据库管理员。在你管理的数据库中有一个叫作Sales的表，在这个表上有频繁的更新、读取和删除操作。并且进行数据库查询的时候经常会返回几千条记录。你发现，如果在SELECT语句发出后马上进行UPDATE或是DELETE操作，往往会导致操作失败，怎么做能解决这个问题。
A　　在SELECT语句中，设置死锁的优先级为低
B　　在发出SELECT语句的事务中使用SERIALIZABLE隔离级
C　　设置LOCK_TIMEOUT减少每个操作在阻塞资源上等待的时间
D　　在SELECT语句中使用READUNCOMMITTED锁提示
E　　在UPDATE和DELETE中使用READCOMMITTED锁提示　

答:D

　　

3.  系统变量@@ TRANCOUNT可以返回返回当前连接的活动事务数。试说明在下列SQL语句的执行过程中，@@ TRANCOUNT的变化情况。
SELECT　‘START TEST’
BEGIN TRAN
 DELETE FROM table01
BEGIN TRAN
   INSERT INTO table02
COMMIT
UPDATE table03
COMMIT

答:  变化如下 
语句                                                            @@ TRANCOUNT
SELECT　‘START TEST’                                        0
BEGIN TRAN                                                           1
DELETE FROM table01                                           1
BEGIN TRAN                                                            2
INSERT INTO table02                                              2
COMMIT                                                                   1
UPDATE table03                                                      1
COMMIT                                                                   0

   4.   你拥有table1上的共享锁，你想将对表上的全部数据进行更新，却发现进程被阻塞，过了一小段时间，你又发现进程止了阻塞，更新顺利完成，试说明其可能的原因。

  答:开始不止一个进程拥有共享锁，而进程要更新数据需要排它锁，因此进程先申请意向排它锁，这时由于其它进程还拥有共享锁，两种锁不兼容，因此进程阻塞。隔了一段时间，其它进程在table1的共享锁都释放了，就余下你的进程拥有共享锁，进程就可以成功将锁升级到意向排它锁，然后升级到排它锁，从而成功地更新了数据。

    5.   你正在为公司开发新的订单录入系统。期望该系统能够高效运行，因为有450名操作员每天24小时地从顾客那里取得订单。操作员是否应该将在一个电话呼叫期间顾客订购的所有项目在单个事务中进行处理？
        答: 最好的方法可能是将订购的每一项产品作为独立的事务来对待。

    6.  需要每月更新一次订单录入系统的products表。Products表包含数百万条项目。预计每月的更新至少影响表中65%的行。可以编写单个的、复杂的UPDATE语句来执行更新，这通常需要花费至少30分钟时间执行该语句。这是执行更新的最好方法吗？

答:不是最好的方法。如果可能，最好将语句分成一组较小的事务，使与其它用户的锁定冲突降到最低。

    7.   你正在接听用户的电话。他们说订单录入系统的反应时间周期性地增加到超过了20秒。而你已经许诺了系统的反应时间为3秒。你怀疑系统存在锁定冲突。如何确定问题的根源？
答:使用sp_lock 存储的过程或者使用 SQL Server 企业管理器的当前活动窗口来识别该问题。然后修改所涉及的事务。