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