数据库一答案

1. 会mysql的同学考证基本上没问题，题目60%以上是增删改查
2. 如果记不住就用笔写下来，记个模子，框子，然后往里面填写参数
3. 操作的截图名字和.sql文件的名字一定不能错，存放在相对应的位置 
4. 不用纠结截面不相同，80%以上的分都在sql上
5. 希望大家经过训练后顺利通过考试

重要提示：

1、本题所用到的样张和素材文件在“C:\GATDoc\”文件夹下。

2、将本题产生的所有答案文件保存在“C:\GATAnswer\”文件夹下。

3、请在答完本题后，对答题文档进行保存，保存后请关闭所有文档以及应用程序。最后请点击“完成”按钮返回选题的界面。

4、当前的答案文档只能在当前试题下进行编辑，如在其它试题下编辑当前试题的答案文档，编辑将无效。

5、如没有按照要求进行保存，可能会导致所答内容丢失，后果由考生自行承担！位置

第一题（SQL Sever基础）

【操作要求】

依次打开SQL Server Management Studio界面、查询窗口、SQL Server配置管理器窗口和Microsoft Help查看器窗口，分别按照题目要求进行操作，并把操作结果保存到“C:\GATAnswer\”文件夹中。

本题目要求实现的功能：连接数据库服务器，进入SQL Server Management Studio界面；在查询窗口中做简单的日期和时间查询并返回正确结果；使用SQL Server配置管理器控制SQL Server各服务的运行状态；使用Microsoft Help查看器搜索关键字找出需要的内容。

1. 连接和断开数据库服务器：执行“SQL Server Management Studio”菜单命令，打开“连接到服务器”对话框，选择“Windows身份验证”或“SQL Server身份验证”选项进行身份验证。在与数据库服务器连接成功后，进入SQL Server Management Studio界面，在该界面可进行断开数据库服务器连接的操作，请将该操作界面通过屏幕截图（按Print Screen键）并以文件名X1-01A.png保存到“C:\GATAnswer\”文件夹中。

1. 打开查询窗口：在SQL Server Management Studio界面，新建查询窗口，用内置的日期和时间函数返回输入日期的年份，输入日期为“2012年5月18日”（注意日期的书写格式），将查询结果以文件名X1-01B.sql保存到“C:\GATAnswer\”文件夹中。

SELECT YEAR(REPLACE(REPLACE(REPLACE('2012年5月18日','年','-'),'月','-'),'日',''))

1. 使用SQL Server配置管理器：执行“配置工具”子菜单中的“SQL Server配置管理器”命令，打开SQL Server配置管理器窗口，在服务项上停止SQL Server服务，操作结果如【样图1-01A】所示。操作成功后把操作界面通过屏幕截图（按Print Screen键）并以文件名X1-01C.png保存到“C:\GATAnswer\”文件夹中后，再启动SQL Server服务。

4．使用SQL Server联机丛书：执行“文档和社区”子菜单中的“SQL Server文档”命令，在启动SQL Server Help Library代理的同时打开Microsoft Help查看器窗口，用Microsoft Help查看器右上角的搜索功能搜索出如【样图1-01B】所示的内容。将搜索后显示的内容通过屏幕截图（按Print Screen键）并以文件名X1-01D.png保存到“C:\GATAnswer\”文件夹中。

 

【样图1-01A】

【样图1-01B】

第二题（数据库和表的建立与维护）

【操作要求】

在SQL Server Management Studio界面建立数据库、选择数据库、在数据库中建立数据表、修改表结构、删除数据表、删除数据库，并按要求把操作结果（扩展名为.sql 的文件）保存在“C:\GATAnswer\”文件夹中。

本题目要求实现的功能：使用T-SQL语句创建数据库，建立数据表并添加相关约束，修改表结构：添加列、修改列和删除列，删除数据表和删除数据库。

1. 建立数据库：在SQL Server Management Studio界面，新建查询窗口，用Transact-SQL语句新建一个名为question1的数据库，将主要数据库文件和日志文件分别命名为question1_data.mdf和question1_log.ldf，初始大小都设成5MB，最大大小都设成10MB，增长率分别设为10%和15%，并将这两个文件临时保存到“C:\GATAnswer\”文件夹中；在查询窗口写入Transact-SQL语句。操作成功后在数据库上单击鼠标右键刷新，操作结果如【样图2-01A】所示，保存该查询，以文件名X2-01-1.sql保存到“C:\GATAnswer\”文件夹中。

CREATE DATABASE question1

ON PRIMARY(

NAME='question1_data.mdf',

FILENAME='C:\GATAnswer\question1_data.mdf',

SIZE=5MB,

FILEGROWTH=10%,

MAXSIZE=10MB

)

LOG ON (

NAME='question1_log.ldf',

FILENAME='C:\GATAnswer\question1_log.ldf',

SIZE=5MB,

FILEGROWTH=15%,

MAXSIZE=10MB

)

2．建立数据表和添加约束：在SQL Server Management Studio界面，新建查询窗口，用Transact-SQL语句在question1数据库中创建数据表city和publishers，两个表的结构如表1和表2所示。

表1  城市表：city

列名	描述	数据类型	备注
CityCode	城市代码	Char(4)	主键
CityName	城市名称	Varchar(80)	非空

表2  出版社表：publishers

列名	描述	数据类型	备注
PubCode	出版社代码	Char(4)	主键
PubName	出版社名称	Char(50)	非空
Phone	联系电话	Char(15)
Address	地址	Varchar(100)
CityCode	城市代码	Char(4)	外键（city）

在查询窗口写入Transact-SQL语句，操作成功后在表上单击鼠标右键刷新，操作结果如【样图2-01B】所示。保存该查询，以文件名X2-01-2.sql保存到“C:\GATAnswer\”文件夹中。

CREATE TABLE city(

    CityCode CHAR(4) NOT NULL,

    CityName VARCHAR(80) NOT NULL,

    PRIMARY KEY (CityCode)

)

CREATE TABLE publishers(

    PubCode CHAR(4) NOT NULL,

    PubName CHAR(50) NOT NULL,

    Phone CHAR(15),

    Address VARCHAR(100),

    CityCode CHAR(4),

    PRIMARY KEY (PubCode),

    FOREIGN KEY (CityCode) REFERENCES city(CityCode)

)

1. 修改表添加列：在SQL Server Management Studio界面，新建查询窗口，在查询窗口写入Transact-SQL语句：修改表publishers，增加列comment，字符类型为变长，长度是100个字节。操作成功后在表上单击鼠标右键刷新，操作结果如【样图2-01C】所示，保存该查询，以文件名X2-01-3.sql保存到“C:\GATAnswer\”文件夹中。

ALTER TABLE publishers ADD comment VARCHAR(100)

1. 修改表修改列：在SQL Server Management Studio界面，新建查询窗口，在查询窗口写入Transact-SQL语句：修改表publishers，修改列comment，列的长度修改为120个字节。操作成功后在表上单击鼠标右键刷新，保存该查询，以文件名X2-01-4.sql保存到“C:\GATAnswer\”文件夹中。

ALTER TABLE publishers ALTER COLUMN comment VARCHAR(120)

1. 修改表删除列：在SQL Server Management Studio界面，新建查询窗口，在查询窗口写入Transact-SQL语句：修改表publishers，删除列comment。操作成功后在表上单击鼠标右键刷新，保存该查询，以文件名X2-01-5.sql保存到“C:\GATAnswer\”文件夹中。

alter table publishers  drop column comment

1. 删除数据表：在SQL Server Management Studio界面，新建查询窗口，在查询窗口写入Transact-SQL语句：删除表publishers和city。操作成功后在表上单击鼠标右键刷新，保存该查询，以文件名X2-01-6.sql保存到“C:\GATAnswer\”文件夹中。

DROP TABLE publishers

DROP TABLE city

1. 删除数据库：在SQL Server Management Studio界面，新建查询窗口，在查询窗口写入Transact-SQL语句：选择master数据库，删除前面创建的数据库question1。操作成功后在数据库上单击鼠标右键刷新，保存该查询，以文件名X2-01-7.sql保存到“C:\GATAnswer\”文件夹中。

drop database  question1

【样图2-01A】

【样图2-01B】                【样图2-01C】

第三题（表的数据操作与简单查询）

【操作要求】

在SQL Server Management Studio界面，确认已经存在名为examDB的数据库，并按照下面对examDB数据库的介绍检查该数据库是否安装完整。如已经安装完整，新建查询窗口，选择数据库，对数据表完成增、删、改和简单的查询操作，并按要求把操作结果（扩展名为.sql的文件）保存在“C:\GATAnswer\”文件夹中。

examDB数据库介绍（examDB数据库两个文件位于“C:\GATDoc\”文件夹中的dbfile目录中。）：这是一个针对出版物管理的小型数据库。主要是针对某城市出版社的出版物进行管理，该数据库包含4个表，分别是城市表、出版物类别表、出版社表以及出版物表，表名分别为city、category、publishers、titles，各表的字段名、字段的数据类型和描述如表1～表4所示。

表1  城市表：city

列名	描述	数据类型	备注
CityCode	城市代码	Char(4)	主键
CityName	城市名称	Varchar(80)	非空

表2  类别表：category

列名	描述	数据类型	备注
CategoryID	类别ID	int	主键，标识列
CategoryName	类别名称	Varchar(50)	非空

表3  出版社表：publishers

列名	描述	数据类型	备注
PubCode	出版社代码	Char(4)	主键
PubName	出版社名称	Char(50)	非空
Phone	联系电话	Char(15)
Address	地址	Varchar(100)
CityCode	城市代码	Char(4)	外键（city）

表4  出版物表：titles

列名	描述	数据类型	备注
TitleCode	出版物代码	Char(6)	主键
TitleName	出版物名称	Varchar (80)	非空
Author	作者	Char(8)	非空
CategoryID	类别ID	Int	外键（category）
PubCode	出版社代码	Char(4)	外键（publisher）
Price	价格	Money
PubDate	出版日期	Datetime

本题目要求实现的功能：使用Transact-SQL语句添加记录、修改记录、删除记录以及简单的数据查询。

验证和选择examDB数据库：在SQL Server Management Studio界面，对照上面对examDB数据库的介绍，检查本机是否存在examDB数据库，该数据库是否安装正确且完整。如已正确，新建查询并选择该数据库，确认以下的操作是针对该数据库。

1. 添加记录：新建查询窗口，输入Transact-SQL语句，在publishers表中添加一条新记录，其PubCode为“ZLTC”、 PubName为“蓝天出版社”、Phone为“020-83546627”、Address为“越秀区果戈里大街12号”、CityCode为“AGZ”，运行结果如【样图3-01A】所示。操作成功后保存该查询，以文件名X3-01-1.sql保存到“C:\GATAnswer\”文件夹中。

INSERT INTO publishers values('ZLTC','蓝天出版社','020-83546627','越秀区果戈里大街12号','AGZ')

1. 更新记录：新建查询窗口，输入Transact-SQL语句，将titles表中TitleCode为“T012”（出版物《MYSQL数据库研究》）的单价改为36，运行结果如【样图3-01B】所示。操作成功后保存该查询，以文件名X3-01-2.sql保存到“C:\GATAnswer\”文件夹中。

UPDATE titles set price=36 where TitleCode = 'T012'

1. 删除记录：新建查询窗口，输入Transact-SQL语句，删除上面在publishers表添加的PubCode为“ZLTC”的记录。操作成功后保存该查询，以文件名X3-01-3.sql保存到“C:\GATAnswer\”文件夹中。

DELETE FROM publishers WHERE PubCode='ZLTC'

1. 显示表所有列并排序——查询并用ORDER BY子句对数据进行排序：新建查询窗口，输入Transact-SQL语句，实现查询examDB数据库中titles表所有列的记录，查询结果要根据价格由高到低排序。操作成功后保存该查询，以文件名X3-01-4.sql保存到“C:\GATAnswer\”文件夹中。

SELECT * FROM titles  ORDER BY Price DESC

1. 去掉重复数据行——不显示重复的数值：新建查询窗口，输入Transact-SQL语句，查询examDB数据库中publishers表的CityCode，并且相同的代码只显示一次，运行结果如【样图3-01C】所示。操作成功后保存该查询，以文件名X3-01-5.sql保存到“C:\GATAnswer\”文件夹中。

SELECT DISTINCT(CityCode) FROM publishers

1. 数据行筛选——选取部分列的特定记录：新建查询窗口，输入Transact-SQL语句，查询examDB数据库中CategoryID为“1”的titles表记录，选取的列名为TitleName、Author、Price并使用别名，运行结果如【样图3-01D】所示。操作成功后保存该查询，以文件名X3-01-6.sql保存到“C:\GATAnswer\”文件夹中。

SELECT TitleName 名称, Author 作者, Price 价格 FROM titles where CategoryID= '1'

【样图3-01A】

【样图3-01B】

【样图3-01C】

【样图3-01D】

第四题（一般数据查询与常用函数）

【操作要求】

在SQL Server Management Studio界面，新建查询，选择examDB的数据库，分别对以下8个问题进行操作，并按照要求把操作结果（扩展名为.sql的文件）分别保存在“C:\GATAnswer\”文件夹中。

本题目要求实现的功能：使用SELECT语句实现对数据的排序；在查询中使用BETWEEN、IN和LIKE操作符；在查询中使用运算符和常用的字符、数学和日期函数。

1. 选取介于某一范围的数据：新建查询窗口，输入查询语句，查询examDB数据库的titles表中出版日期在2009年至2010年的出版记录，并按出版日期升序排序，运行结果如【样图4-01A】所示。操作成功后保存该查询，以文件名X4-01-1.sql保存到“C:\GATAnswer\”文件夹中。

SELECT * FROM titles WHERE PubDate > '2008-12-31' and PubDate < '2010-01-01' ORDER BY PubDate ASC

1. 选取属于某一子集合的数据：新建查询窗口，输入查询语句，查询examDB数据库的publishers表中城市代码包含“AGZ”、“DSZ”、“LGY”的记录，运行结果如【样图4-01B】所示。操作成功后保存该查询，以文件名X4-01-2.sql保存到“C:\GATAnswer\”文件夹中。

SELECT * FROM publishers  WHERE CityCode IN('AGZ','DSZ', 'LGY')

1. 包含Like和通配符“%”的模糊查询：新建查询窗口，输入查询语句，查询examDB数据库的titles表中出版物名称包含“数据库”的出版记录。操作成功后保存该查询，以文件名X4-01-3.sql保存到“C:\GATAnswer\”文件夹中。

SELECT * FROM titles WHERE TitleName like '%数据库%'

1. 包含Like和通配符“_”的模糊查询：新建查询窗口，输入查询语句，查询examDB数据库的titles表中出版社代码第二位是“X”的出版物名称、作者和出版社代码，运行结果如【样图4-01C】所示。操作成功后保存该查询，以文件名X4-01-4.sql保存到“C:\GATAnswer\”文件夹中。

SELECT TitleName, Author,PubCode FROM titles WHERE PubCode like '_X%'

1. 在查询中使用算术运算符：新建查询窗口，输入查询语句，查询examDB数据库中titles表的价格超过35的出版物名称、价格和85折后的价格，运行结果如【样图4-01D】所示。操作成功后保存该查询，以文件名X4-01-5.sql保存到“C:\GATAnswer\”文件夹中。

SELECT TitleName 书名, Price 全价, Price*0.85 '85折' FROM titles WHERE Price>35

1. 使用常用的字符串函数：新建查询窗口，输入查询语句，查询examDB数据库中titles表的出版物名称长度大于7的TitleCode、TitleCode的后三位、后一位和出版物名称及名称、长度，并按长度降序排列，运行结果如【样图4-01E】所示。操作成功后保存该查询，以文件名X4-01-6.sql保存到“C:\GATAnswer\”文件夹中。

SELECT TitleCode,right(TitleCode,5) ID1, right(TitleCode,3) ID2,TitleName 名称, LEN(TitleName)长度 FROM titles where LEN(TitleName) > 7 ORDER BY LEN(TitleName) DESC

1. 使用常用的数学函数：新建查询窗口，输入查询语句，返回小于或等于以下两个数的整数：4.5和-7.6；返回大于或等于8.1的整数；返回2的5次幂。操作成功后保存该查询，以文件名X4-01-7.sql保存到“C:\GATAnswer\”文件夹中。

SELECT FLOOR(4.5)

SELECT FLOOR(-7.6)

SELECT CEILING(8.1)

SELECT POWER(2,5)

1. 使用常用的其他函数：新建查询窗口，输入查询语句，返回当前系统日期，返回两个日期“2008-01-01”和“2009-01-01”之间的月份数。操作成功后保存该查询，以文件名X4-01-8.sql保存到“C:\GATAnswer\”文件夹中。

SELECT CONVERT(varchar(100), GETDATE(), 23)

SELECT DATEDIFF(MM,'2008-01-01','2009-01-01')

【样图4-01A】

【样图4-01B】

【样图4-01C】

【样图4-01D】

【样图4-01E】

第五题（高级数据查询）

【操作要求】

在SQL Server Management Studio界面，新建查询，选择examDB的数据库，分别对以下6个问题进行操作，并按照要求把操作结果（扩展名为.sql的文件）分别保存在“C:\GATAnswer\”文件夹中。

本题目要求实现的功能：使用SELECT语句实现聚合函数查询；分组查询GROUP BY和HAVING的使用；多表联接查询；子查询；使用UNION运算符实现从两个表选择相同格式的数据。

1. 常用的聚合函数的使用：新建查询窗口，输入查询语句，查询examDB数据库的titles表中所有出版物的平均价格。操作成功后保存该查询，以文件名X5-01-1.sql保存到“C:\GATAnswer\”文件夹中。

SELECT AVG(Price) FROM titles

1. 在SELECT语句中使用GROUP BY：新建查询窗口，输入查询语句，实现在examDB数据库publishers表中统计各城市的出版社数量，运行结果如【样图5-01A】所示。操作成功后保存该查询，以文件名X5-01-2.sql保存到“C:\GATAnswer\”文件夹中。

SELECT  COUNT(PubName) 出版社数量, CityCode FROM publishers  GROUP BY CityCode

1. 在SELECT语句中使用HAVING（与GROUP BY一起使用）：新建查询窗口，输入查询语句，实现在examDB数据库publishers表中统计各城市出版社数量超过1的城市。操作成功后保存该查询，以文件名X5-01-3.sql保存到“C:\GATAnswer\”文件夹中。

SELECT  COUNT(PubName) 出版社数量, CityCode FROM publishers   GROUP BY CityCode HAVING COUNT(PubName) >1

1. 在一个SELECT语句中使用多个表（内联接）：新建查询窗口，输入查询语句，查询examDB数据库中每个城市（city表中的CityName）的出版社名称（publishers表中），运行结果如【样图5-01B】所示。操作成功后保存该查询，以文件名X5-01-4.sql保存到“C:\GATAnswer\”文件夹中。

SELECT a.CityName,b.PubName FROM city a, publishers b WHERE a.CityCode = b.CityCode

1. 在SELECT语句中使用子查询：新建查询窗口，输入查询语句，查询examDB数据库的publishers表中价格最高的出版物名称、作者和出版日期。操作成功后保存该查询，以文件名X5-01-5.sql保存到“C:\GATAnswer\”文件夹中。

select TitleName, Author, PubDate  from titles where price=(select max(titles.Price) from titles)

1. 使用UNION运算符的集合运算：新建查询窗口，输入查询语句，查询examDB数据库publishers表中CityCode为“AGZ”的出版社信息：包括PubCode、PubName、Phone、CityCode的记录集合，与CityCode为“HBJ”的出版社信息：包括PubCode、PubName、Phone、CityCode的记录集合，进行并集运算，运行结果如【样图5-01C】所示。操作成功后保存该查询，以文件名X5-01-6.sql保存到“C:\GATAnswer\”文件夹中。

SELECT PubCode, PubName, Phone, CityCode FROM publishers  WHERE CityCode = 'AGZ'

UNION

SELECT PubCode, PubName, Phone, CityCode FROM publishers  WHERE CityCode = 'HBJ'

【样图5-01A】

【样图5-01B】

【样图5-01C】

第六题（T-SQL编程、视图和索引）

【操作要求】

在SQL Server Management Studio界面，新建查询，选择examDB的数据库，分别对以下7个问题进行操作，并按照要求把操作结果（扩展名为.sql的文件）分别保存在“C:\GATAnswer\”文件夹中。

本题目要求实现的功能：使用DECLARE语句对局部变量进行定义；使用SET或SELECT语句对变量进行赋值；使用PRINT或SELECT语句查看局部变量和全局变量的值；使用PRINT或SELECT语句输出变量的值，使用CAST和CONVERT函数转换数据类型以便按要求的格式输出；创建简单视图和复杂视图；创建索引，删除视图和索引。

1. 变量的定义、赋值和查看：新建查询窗口，输入Transact-SQL语句：定义两个局部变量@vname和@vnum，数据类型分别为字符型（定长10）和整型，然后分别给两个变量赋值“张三”和“12”。操作成功后保存该查询，以文件名X6-01-1.sql保存到“C:\GATAnswer\”文件夹中。

DECLARE @vanme varchar(10),@vnum int

set @vanme='张三'

set @vnum=12

1. 使用PRINT语句输出局部变量和全局变量的值：新建查询窗口，输入Transact-SQL语句：输出上题中两个变量的值；因为局部变量的定义、赋值和输出需要在一个批处理中执行，所以要把上题的代码和本题的输出语句print一并执行；同时再输出全局变量@@Language（当前所用语言的名称）的值，运行结果如【样图6-01A】所示。操作成功后保存该查询，以文件名X6-01-2.sql保存到“C:\GATAnswer\”文件夹中。

USE examDB

GO

DECLARE @vanme varchar(10),@vnum int

set @vanme='张三'

set @vnum=12

print '局部变量：@vanme：' + @vanme + '  '+'@vnum:'+convert(varchar,@vnum)

print '全局变量：@@Language(当前系统所用语言的名称):'+ @@Language

1. 使用CAST转换函数：新建查询窗口，输入Transact-SQL语句：定义两个局部变量分别用于保存出版物代码的值“T002”和其对应的出版物的价格；出版物的价格需要从titles表中取出并赋值给局部变量；使用select输出语句输出出版物代码的值和其对应的使用CAST函数转换后以字符形式输出的价格，运行结果如【样图6-01B】所示。操作成功后保存该查询，以文件名X6-01-3.sql保存到“C:\GATAnswer\”文件夹中。

USE examDB

GO

DECLARE @vanme varchar(10),@vnum Float

set @vanme='T002'

set @vnum=(SELECT Price FROM titles where TitleCode = @vanme)

SELECT TitleCode, TitleName, Author,CategoryId,PubCode, CAST(Price AS varchar(10)) Price, PubDate FROM titles

1. 创建简单视图：新建查询窗口，输入Transact-SQL语句，实现在examDB数据库中titles表上创建名为view1的视图，查询2010年后出版的出版物的TitleName、Author、Price和PubDate。创建成功后，保存视图并用SELECT语句查看视图所包含的列，结果如【样图6-01C】所示。保存该查询，以文件名X6-01-4.sql保存到“C:\GATAnswer\”文件夹中。

CREATE VIEW view1 AS

SELECT TitleName, Author, Price, PubDate FROM titles WHERE PubDate > '2010-12-31'

SELECT * FROM view1

1. 创建复杂视图：新建查询窗口，输入Transact-SQL语句，实现在examDB数据库中创建名为view2的视图，视图实现按类别ID（Category表）和类别名称（Category表）分组统计各类出版物（TitleCode）的数量。创建成功后，保存视图并用SELECT语句查看视图所包含的数据，结果如【样图6-01D】所示。保存该查询，以文件名X6-01-5.sql保存到“C:\GATAnswer\”文件夹中。

USE examDB

GO

CREATE VIEW view2 AS

SELECT A.*, ISNULL(B.CountNum,0) 数量 FROM category A  LEFT JOIN

(SELECT CategoryID, COUNT(TitleCode) CountNum FROM titles GROUP BY CategoryID)

 B ON  A.CategoryID = B.CategoryID

SELECT * FROM view2

1. 创建索引：新建查询窗口，输入Transact-SQL语句，在表titles的pubdate字段上建立名为newidx的索引，对象资源管理器展开如【样图6-01E】所示。操作成功后保存该查询，以文件名X6-01-6.sql保存到“C:\GATAnswer\”文件夹中。

create nonclustered index newidx

on titles(pubdate)

with(drop_existing=on)

1. 删除视图和索引：新建查询窗口，输入Transact-SQL语句，删除examDB数据库中前面所建的视图和索引。操作成功后保存该查询，以文件名X6-01-7.sql保存到“C:\GATAnswer\”文件夹中。

drop view view1

drop view view2

drop index newidx on titles

【样图6-01A】

【样图6-01B】

【样图6-01C】

【样图6-01D】

【样图6-01E】

第七题（存储过程和触发器）

【操作要求】

在SQL Server Management Studio界面，新建查询，选择examDB的数据库，分别对以下5个问题进行操作，并按照要求把操作结果（扩展名为.sql的文件）分别保存在“C:\GATAnswer\”文件夹中。

本题目要求实现的功能：用Transact-SQL语句实现如下操作：创建不带参数的存储过程；创建带输入参数的存储过程；创建带输出参数的存储过程；创建触发器；删除存储过程和触发器。

1. 创建不带参数的存储过程：新建查询窗口，输入Transact-SQL语句，实现在examDB数据库中新建名为sproc1的存储过程。该存储过程实现在city表中cityName为“北京”、在publishers表中查询该城市的pubName 和Address，即查询城市名是“北京”的出版社名称和出版社地址。成功创建后执行该存储过程，运行结果如【样图7-01A】所示。保存该查询，以文件名X7-01-1.sql保存到“C:\GATAnswer\”文件夹中。

if (exists (select * from sys.objects where name = 'sproc1'))

     drop proc sproc1

 go

 create proc sproc1

 as

 begin

     select A.PubName,A.Address from publishers A,city B where A.CityCode = B.CityCode and B.CityName = '北京'

 end

USE examDB

EXEC sproc1

GO

1. 创建带输入参数的存储过程：新建查询窗口，输入Transact-SQL语句，实现在examDB数据库中新建名为sproc2的存储过程。该存储过程包括一个输入参数，用来表示cityName。该存储过程实现在city表中给定不同的cityName，在publishers表查询该城市的pubName和Address。成功创建后执行该存储过程，运行结果如【样图7-01B】所示。保存该查询，以文件名X7-01-2.sql保存到“C:\GATAnswer\”文件夹中。

if (exists (select * from sys.objects where name = 'sproc2'))

     drop proc sproc2

 go

 create proc sproc2

     @cityname varchar(20)

 as

 begin

     select A.PubName,A.Address from publishers A,city B where A.CityCode = B.CityCode and B.CityName = @cityname

 end

USE examDB

EXEC sproc2 '北京'

GO

1. 创建带输出参数的存储过程：新建查询窗口，输入Transact-SQL语句，实现在examDB数据库中新建名为sproc3的存储过程。该存储过程包括一个输入参数和一个输出参数：输入参数表示TitleCode，输出参数表示Price。该存储过程实现在titles表中根据不同的出版物代码，求出其对应的Price。成功创建后执行该存储过程，运行结果如【样图7-01C】所示，保存该查询，以文件名X7-01-3.sql保存到“C:\GATAnswer\”文件夹中。

if (exists(select * from  sys.objects where name='sproc3'))

     drop proc sproc3

 go

 create proc sproc3

     @titlecode varchar(20),

     @price float output

 as

 begin

     select @titlecode=TitleCode,@price=Price from titles where TitleCode=@titlecode

     print @titlecode+'的价格是:'+ cast(@price as varchar(20))

 end

 go

use examDB

declare @p decimal(5,2)

exec sproc3  'T002', @p output

1. 创建触发器：新建查询窗口，输入Transact-SQL语句，实现在examDB数据库中建立名为newtrigger的触发器。触发器规定当titles表的PubDate列的数据发生变化时，显示“出版日期已更改，触发器起到作用。”文本。修改titles表的PubDate列数据以触发该触发器，运行结果如【样图7-01D】所示。操作成功后保存该查询，以文件名X7-01-4.sql保存到“C:\GATAnswer\”文件夹中。

if(OBJECT_ID('newtrigger') is not null)

drop trigger  newtrigger

go

create trigger newtrigger

 on titles

 for update

 as

 if update(PubDate)

 begin

 print '出版日期已更改，触发器起到作用'

end

update titles

set PubDate ='2022-05-07'

where TitleCode='T001'

go

1. 删除存储过程和触发器：新建查询窗口，输入Transact-SQL语句，删除上面题目中建立的存储过程和触发器（注意存储过程和触发器的名字）。操作成功后保存该查询，以文件名X7-01-5.sql保存到“C:\GATAnswer\”文件夹中。

drop procedure  sproc1,sproc2,sproc3

drop trigger newtrigger

【样图7-01A】

【样图7-01B】

【样图7-01C】

【样图7-01D】

第八题（用户权限管理和数据的导入导出）

【操作要求】

在SQL Server Management Studio界面，分别对以下6个问题进行操作。操作完成后应及时在相应对象节点上进行“刷新”操作，然后按照要求把操作结果（扩展名为.sql的文件）分别保存在“C:\GATAnswer\”文件夹中。

本题目要求实现的功能：创建数据库登录帐户；创建数据库用户；给数据库用户授权；将表数据导出至外部文件；将外部文件数据导入表；将一个数据库导出至另一个数据库。

1. 创建数据库登录帐户：新建查询窗口，输入Transact-SQL语句，使用系统存储过程sp_addLogin创建SQL Server身份的登录账户：登录名为Login1，密码为123456，运行结果如【样图8-01A】所示。操作成功后，保存该查询，以文件名X8-01-1.sql保存到“C:\GATAnswer\”文件夹中。

EXEC sp_addlogin 'Login1', '123456'

1. 创建数据库用户：新建查询窗口，选择数据库examDB，输入Transact-SQL语句，使用系统存储过程sp_grantdDBaccess在该数据库中创建数据库用户dbUser1（默认登录名为Login1，也可以不同），运行结果如【样图8-01B】所示。操作成功后，保存该查询，以文件名X8-01-2.sql保存到“C:\GATAnswer\”文件夹中。

exec sp_grantdbaccess 'Login1','dbUser1'

1. 给数据库用户授权：新建查询窗口，选择数据库examDB，输入Transact-SQL语句，授予数据库用户dbUser1对publishers表具有select和update对象权限。保存该查询，以文件名X8-01-3.sql保存到“C:\GATAnswer\”文件夹中。在“文件”菜单断开与“对象资源管理器的连接”，重新使用“SQL Server身份验证”登录，登录名为Login1，密码为123456。成功登录后选择examDB数据库，dbUser1用户可以对publishers表进行查询和修改操作，同时可查看dbUser1用户的对象权限，查看界面如【样图8-01C】所示。

use examDB

     go

     grant select,update on publishers to dbUser1

1. 将表数据导出至文本文件：用SQL Server中的导入导出工具将examDB数据库中的city表导出到名为cp的文本文件中，将列分隔符选择为制表符。操作完成界面如【样图8-01D】所示，并把该文本文件保存到“C:\GATAnswer\”文件夹中。

1. 将文本文件数据导入表：用SQL Server中的导入导出工具将“C:\GATDoc\”文件夹中的“2015SQL10\Y8-01\cityexp.txt”文件导入到examDB数据库新建的名为cityexp的数据表中，如【样图8-01E】所示。

6．将一个数据库导出至另一个数据库：用SQL Server中的导入导出工具将examDB数据库中的city、category、publishers和titles表导出到export1数据库中，如【样图8-01F】所示。

 

【样图8-01A】

【样图8-01B】

【样图8-01C】

【样图8-01D】

【样图8-01E】

【样图8-01F】