数据库题目

做完这些课后习题~你的数据库就能上90了
第2章 数据库上机练习
1．分别用图形化方法和CREATE DATABASE语句创建符合如下条件的数据库：

数据库的名字为：students

数据文件的逻辑文件名为：students_dat，物理文件名Students.mdf：存放在D:\Test目录下（若D:中无此子目录，可先建立此目录，然后再创建数据库。）；

文件的初始大小为：5MB；

增长方式为自动增长，每次增加1MB。

日志文件的逻辑文件名字为：students_log，理文件名students.ldf，也存放在D:\Test目录下；

日志文件的初始大小为：2MB，；

日志文件的增长方式为自动增长，每次增加10%

2. 分别用图形化方法和CREATE DATABASE语句创建符合如下条件的数据库，此数据库包含两个数据文件和两个事务日志文件：

数据库的名字为：财务数据库

数据文件1的逻辑文件名为：财务数据1，物理文件名为：财务数据1.mdf，存放在“D:\财务数据”目录下（若D:中无此子目录，可先建立此目录，然后再创建数据库。）；

文件的初始大小为：3MB；

增长方式为自动增长，每次增加1MB。

数据文件2的逻辑文件名为：财务数据2，物理文件名为：财务数据2.ndf，存放在与主数据文件相同的目录下；

文件的初始大小为：3MB；

增长方式为自动增长，每次增加10％。

日志文件为：

日志文件1的逻辑文件名为：财务日志1，物理文件名为：财务日志1_log.ldf，存放在D:\财务日志目录下；

初始大小为：1MB，；

增长方式为自动增长，每次增加10%

日志文件2的逻辑文件名为：财务日志2，物理文件名为：财务日志2_log.ldf，存放在D:\财务日志目录下；

初始大小为：2MB；

不自动增长。

3. 在SQL Server Management Studio中查看在第1、2题所建的数据库的选项。

4. 删除新建立的“财务数据库”，观察该数据库包含的文件是否一起被删除了。

5. 分别用图形化方法和T-SQL语句对第1题所建的“students”数据库空间进行如下扩展：增加一个新的数据文件，文件的逻辑名为“students_dat2”，物理文件名students2.ndf：存放在D:\Test目录下,文件的初始大小为：2MB,不自动增长。

6. 将第4题新添加的“students_dat2”文件的初始大小改为6MB。

7. 分别用图形化方法和T-SQL语句对扩展后的“students”数据库进行如下缩小：

（1） 将“students”数据库缩小到使数据库中的空白空间为60%

（2） 将数据文件“students_dat”的初始大小缩小为3MB；

8. 分别用图形化方法和T-SQL语句实现如下分离和附加数据库的操作：

首先分离新建立的students数据库，然后将此数据库所包含的全部文件（包

括数据文件和日
志文件）移动到你的计算机的D:\students_db文件夹下（首先建立好该文件夹），然后再将该数据库附加回你的计算机的数据库管理系统中。

1 将pubs数据库和northwind数据库附加到你的计算机上。
第3章 基本表上机练习
利用SSMS工具，在第2章建立的students数据库中完成如下操作：

1． 分别创建满足如下条件的三张表（注：“说明”信息不作为创建表的内容）：

教师表

列名 说明 数据类型 约束

Tno 教师号 普通编码定长字符串，长度为7 主键

Tname 姓名 普通编码定长字符串，长度为10 非空


Tsex 性别 普通编码定长字符串，长度为2 取值为“男”、“女”

Birthday 出生日期 小日期时间型 允许空

Dept 所在部门 普通编码定长字符串，长度为20 允许空

Sid 身份证号 普通编码定长字符串，长度为18 取值不重

课程表

列名 说明 数据类型 约束

Cno 课程号 普通编码定长字符串，长度为1 主键


Cname 课程名 普通编码定长字符串，长度为20 非空
Credit 学分 小整型 大于0


Property 课程性质 字符串，长度为10 默认值为“必修”






授课表

列名
说明
数据类型
约束

Tno
教师号
普通编码定长字符串，长度为7
主键，引用教师表的外键

Cno
课程名
普通编码定长字符串，长度为10
主键，引用课程表的外键

Hours
授课时数
整数
大于0





2． 修改表结构：

（1） 在授课表中添加一个授课类别列：列名为：Type，类型为char(4)。

（2） 将授课表的Type列的类型改为char(8)。

（3） 删除课程表中的Property列。



3．分别用SSMS和T-SQL语句创建一用户自定义数据类型，类型名为：salary，类型为：定点小数，整数部分5位，小数部分2位。



4．用T-SQL语句在students数据库中创建如下表：

（1）销售表，结构为：

商品号 普通编码定长字符型，长度为10，

销售时间 小日期时间型，非空，

销售价格 整型，非空，

销售数量 小整型，非空，

销售总价 整型，等于本次销售价格*销售数量。

其中（商品号，销售时间）为主键

（2）

① 订购表，结构为：

货单号 整型，标识列，初值为1，自动增长，每次增加1，主键

订购时间 小日期时间，非空，

顾客号 普通编码定长字符型，长度为10



② 订购明细表，结构为：

货单号 整型，外键，引用订购表的“货单号”，

商品号

普通编码定长字符型，长度为10，

订购数量 整型，

订购价格 整型。

（货单号，商品号）为主键。



第4章 高级查询
在students数据库中执行“建表及数据插入语句.sql”文件中的语句，创建Student、Course和SC表和数据。针对该三张表数据执行下列操作：

1．查询在第4学期开设课程中与第1学期开设的课程学分相同的课程，列出课程名和学分。

2．查询“李勇”和“王大力”所选的相同的课程，列出课程名、开课学期和学分。

3．查询“李勇”选了但“王大力”没有选的课程，列出课程名、开课学期和学分。

4．查询至少同时选了“C001”和“C002”这两门课程的学生的学号和所选的课程号。

5．查询每个学期学分最高的2门课程的课程名和学分。

6．查询每个系年龄最小的两个学生的姓名、所在系和年龄。

7．查询每个系选课门数最多的两个学生的姓名和所在系。

第5章 索引
使用Northwind数据库，完成下列操作。

1． 写出查看Orders表上的已建索引的语句。

2． 删除“OrderDate”索引。

3． 在Orders表上为ShipCountry列建立一个非聚簇索引，索引名为：ShipCountry，叶级索引页的充满度为60％。

4． 在Orders表上为ShipCountry和ShipCity列上建立一个组合的非聚簇索引，索引名为：Country_City，中间级和叶级索引页的充满度均为80％。

5． 查询公司名为“Around the Horn”和“Ernst Handel”（Customers表中的CompanyName列）的公司名、订购的产品的产品名(Products表中的ProductName)、订购单价（[Order Details]表中的UnitPrice）、数量（[Order Details]表中的Quantity）和订购日期（Orders表中的OrderDate）。

（1）查看该语句执行时间及执行计划。

（2）删除这几张表的全部主键和外键，再次执行上述查询，再次查看执行时间和执行计划。

（3）如果经常执行以公司名和订购日期列为条件的查询，则应该建立怎样的索引来提高查询效率。试在建立完这些索引后再次执行该查询语句，查看执行时间及执行计划。

第6章 常用系统函数
针对students数据库中Student、Course和SC表，完成下列操作：

1． 计算从2000年到当前日期的天数、月份数及年数。

2． 求“You are a student”字符串中从11开始，长度为7的子串。

3． 分别计算“You are students”和“我们是学生”字符串中字符的个数。

4． 分别得到字符串“I am a teacher and you are students” 中左边14个和右边16个字符组成的字符串。

5． 查询全体男生的平均年龄，要求将结果保留到小数点后2位。

6． 在选课的学生中统计每个学生的平均选课门数，将结

果保存到小数点1位。

7． 查询每个系的平均学生人数，将结果保存到
小数点后1位。

8． 查询每个姓氏的学生人数。

9． 查询姓氏人数最多的前2个姓及其人数。

10． 查询名字为3个字的学生中第2字是“小”的学生人数。

针对pubs数据库完成下列操作：

11． 对Pubs数据库的titles表，查询全部在1991年6月出版的图书的书名（title）和出版日期（pubdate）。

12． 对Pubs数据库的titles表，查询1991年出版的图书的总数量。

13． 对Pubs数据库的titles表，查询1991年每个月出版的图书的总数量。

第7章 触发器
针对pubs数据库，创建满足如下要求的触发器：

1.对pubs数据库的jobs表，分别编写一个前触发和后触发型的触发器，保证min_lvl列的值小于max_lvl列的值。
2.对pubs数据库的jobs表和employee表，定义一个后触发型触发器，保证employee表中的job_lvl的值在jobs表的相应工作的min_lvl和max_lvl值范围内。
3.对pubs数据库，建立满足如下要求的后触发型触发器，如果被删除的商店（stores表）的图书销售总量（sales表中的qty的和值）大于等于100本，则不能删除这样的商店；如果被删除的商店的图书销售总量小于100本，则将这个商店以及这个商店的销售记录一起删除。（注：如果要测试这个触发器的作用，则应先删除sales表中的引用stores表的外码约束，然后再进行测试）。
4.针对sales表和titles表，每当在sales表中插入一行数据时（有销售记录），自动用新插入的qty值计算titles表中的ytd_sales列的值。（计算方法为：ytd_qty =ytd_qty+qty。注：只重新计算所售图书的ytd_sales的值）


针对students数据库中的Student、Course和SC表，创建满足下列要求的触发器。

4． 限制每个学生每个学期的选课总门数不能超过6门。

5． 每当在SC表插入数据时，自动计算该学生的不及格课程总门数，如果该值超过4门，则显示提示信息：“该学生不及格门数已经超过4门，应多加注意。”

第8章 存储过程
1． 创建计算1+2+3+… 一直加到指定值的存储过程，要求：计算的终止值由输入参数决定，计算结果由输出参数返回给调用者。

2． 对Pubs数据库的authors表，创建查询指定邮政编码（Zip）的作者的姓名（au_lname和au_fname）、电话号码（phone）和地址（address）的存储过程。

3． 对Pubs数据库的titles表，创建查询指定类型（type）的图书的平均价格的存储过程，将平均价格作为输出参数返回，写出执行此存储过程的代码，并分别指定一些不同的输入参数值。

4． 对Pubs数据库的titles表，创建查询指定类型的图书中价格（price）在指定

范围内的图书的书名（title）、价格（price）、出版日期（pubdate）和出版商（publishers表中的pub_name列）。

5．
对Pubs数据库的titles表,创建删除价格低于指定价格（price）的图书信息的存储过程。

6． 对Pubs数据库的titles表，创建将指定类型的图书的价格增加指定的百分比的存储过程。默认类型为“business”，默认的百分比为10%。

7． 对Pubs数据库的titles表，创建查询指定书号（title_id）的图书的书名和价格的存储过程，如果指定的书号存在，则执行查询并返回代码值0；如果指定的书号不存在，则返回代码值1；如果指定书号的价格为空，则返回代码值2。编写执行此存储过程的代码，并相应的处理存储过程返回的代码值，显示对应的错误类型。

8． 对Northwind数据库的Products表，创建完成如下操作的存储过程。如果此产品的库存量（UnitsInStock）超过100，则单价（UnitPrice）降低5%，如果库存在50～100之间，则单价降低3%，对库存低于50的则不降价。

第9章 游标
1． 对pubs数据库的authors表，用游标实现查询所在州（state）为“CA”的作者的姓名（au_fname 加 au_lname）、电话（phone）和地址（address），并用print语句显示出全部的信息。

2． 对pubs数据库的authors表、titleauthor表和titles表，定义产生如下形式的报表的游标：首先列出一个作者名，然后列出此作者所写的全部图书的书名、类型和价格，然后再列出第二个作者名，再在此作者名下列出此作者所写的全部图书的书名、类型和价格；依此类推，直到列出全部的作者。

3． 利用9.3例6所示的job表的结构和原始数据，用游标实现对此表数据的如下修改：将工作级别相同的工作只保留工作编号较小的一项工作，同时，将这些工作的工作描述拼接为一个工作描述，中间用逗号分隔。修改后的数据示意如下：

Jobid J_desc lvl


J01 软件开发，软件测试 10


J02 硬件开发，硬件测试 12

J04 硬件维护 8


4． 对Northwind数据库，用游标实现查询每个国家（Customers表的Country列）的客户中订购数量（[Order Details]表中的Quantity列）最多的前2个客户的客户ID（Customers表的CustomerID）、客户公司名（Customers表的CompanyName）、订购的数量和所在的国家。（说明：Customers表和[Order Details]表要通过Orders表连接）。

5． 对Northwind数据库的products表，用游标实现查询每种重订购级别（ReorderLevel列）中，单价（UnitPrice）最便宜的3种产品的游标，列出产品ID（ProductID）、产品名（ProductName）、定购级别和单

价，并将最终的查询结果按ReorderLevel和UnitPrice的升序显示。

第10 章 用户定义的函数
利用pubs数据库，创建满足下述要求的用户自定义函数：

1.创
建计算圆的面积的标量函数。输入参数为圆的半径，类型为整型，返回值为浮点型数。并写出利用此函数计算半径为4的圆面积的SQL语句。
2.利用sales表和stores表，建立查询指定州的书店的图书销售总量的标量函数。
3.利用authors表，titleauthor表和sales表，建立查询指定作者（作者编号）所写的图书的销售总量的标量函数。
4.利用sales表、stores表和titles表，建立查询一次销售数量在指定范围的书店名称、书店所在城市、销售的书名以及销售数量的内嵌表值函数。
5.利用authors表、titleauthor表、titles表和sales表，建立查询指定作者（作者编号）的作者名、所写的图书的书号和书名、这些图书的一次销售数量和此作者的全部图书的销售总量的内嵌表值函数。（提示：可利用（3）建立的函数）
6.利用authors表、titles表、titleauthor表、stores表和sales表建立查询指定书店编号的书店名称、销售的图书的作者姓名、书名和销售数量的多语句表值函数。
7.用SSMS工具建立SQL Server认证的登录帐户：log1,log2,log3
8.用log1登录，能否访问Students数据库？为什么？
9.将log1、log、log3映射为Students数据库的用户。
10.授予log1、log、log3具有对Student、Course和SC三张表的查询权限。
11.在查询分析器中，分别用log1、log2、log3登录，对上述三张表执行查询。
12.授予log1具有对Student表的插入、删除权限。
13.用log2登录，在Student表中插入一行数据，会成功吗？
14.在log1登录中，在Student表插入一行数据，会成功吗？
15.在Students数据库中建立用户角色：ROLE1，并将log1、log2添加到此角色中。授予ROLE1角色具有SC表的全部操作权限。
16.在log2的登录中，在SC表中插入一行数据，会成功吗？
第11章 安全管理
11. 如果希望log1具有创建数据库的权限，应将它加到哪个系统角色中？

12. 如果希望log3具有系统管理员的权限，应将它加到哪个系统角色中？

13. 如果希望log2在Students数据库中具有建表权，应将它加到哪个系统角色中？

1.如果希望log2具有Students数据库中的全部数据的查询权，比较好的实现方法是什么？
第12章 备份与恢复
1．按如下顺序完成下列操作：

（1）创建永久备份设备：backup1, backup2，两个备份设备均存放在默认路径下。

（2）将Students数据库完全备份到backup1上；

（3）对Students数据库中的修课表中的数据进行一些修改，然后将学生数据库差异备份到backup2上；

（4）在

修课表中插入一行新记录，然后将Students数据库差异备份到backup2上；

（5）再在修课表中插入一行新的记录，然后对Students数据库进行日志备份到backup2上；

（6）在修课表中删除一行记录，
然后对Students数据库进行日志备份到backup2上；

（7）再在修课表中删除一行记录；

（8）对Students数据库进行恢复。先看一下图中所显示的恢复顺序，然后再进行恢复。恢复此数据库后，最后删除的修课记录恢复出来了吗？为什么？

2．创建永久备份设备：backup3, backup4，其中backup3建立在C：盘根目录下，backup4建立在D：盘根目录下。将这两个备份设备作为一个媒体集，将pubs数据库完全备份到这两个备份设备上。

3．将master数据库以附加的方式备份到backup1上，是否成功？若将master数据库以追加或重写的方式备份到backup3备份设备上，是否能成功？为什么？

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLSEREVER测试题(下) 2005年10月10日 softjSQLSEREVER测试题(下)

18 以下作业皆以*.sql文件存储于c:\homework目录中

实践1：创建和维护SQL Server数据库



（1） 使用下表提供的值，创建数据库ClassNorthwind



参 数 值


数据库名称 ClassNorthwind

数据库文件名 ClassNorthwind_Data

位置 C:\data

初始大小 25MB


25MB



文件组


Primary



文件增长


1MB



最大文件长度


100MB



事务日志文件名称


ClassNorthwind_Log



位置


C:\data



初始大小


15MB



文件增长


1MB



最大文件长度


30MB









（2） 使用sp_helpdb存储过程浏览有关数据库ClassNorthwind的信息

（3） 修改数据库属性

l 编写和执行将ClassNorthwind事务日志文件的最大文件长度增加到50MB的语句

l 编写和执行向数据库ClassNorthwind添加一个事务日志文件ClassNorthwind_LogApp，文件属性如下：

位置


C:\dataapp



初始大小


20MB



文件增长


1MB



最大文件长度


40MB













实践2：创建和修改SQL Server数据库的表



（1） 创建数据库ClassNorthwind中的雇员表Employees、雇员工资表wage和工资税表tax



雇员表Employees:



字 段 名 称
类 型
空 否

EmployeeID
Int，identity ,初值为1，增量为1
NOT NULL

Name
VarChar(10)
NOT NULL

Sex
Char(2)
NOT NULL

Birthdate
Date
NULL

Address
Varchar(50)
NULL

Phone
Char(13)
NULL

Remark
text










雇员工资
表wage:



字 段 名 称
类 型
空 否

EmployeeID
Int
NOT NULL

Name
VarChar(10)
NOT NULL

Wage
money
NOT NULL

Putdate
Date
NOT NULL







工资税表tax:



字 段 名 称
类 型
空 否

EmployeeID
Int
NOT NULL

Name
VarChar(10)
NOT NULL

Tax
money
NOT NULL

Paydate
Date
NOT NULL







（2） 向雇员表Employees中添加列Department varchar(20) NULL

（3） 修改雇员表Employees中的列Address varchar(60)

（4） 删除雇员表Employees中的列Remark

（5） 用存储过程sp_help查看三个表的信息

（6） 向雇员表Employees中插入十个雇员信息

（7） 向雇员工资表wage中插入十个雇员相应的工资发放情况信息

（8） 向工资税表tax中插入十个雇员相应的交税情况





实践3：创建SQL Server数据库表的视图和索引



（1） 在雇员表Employees中，创建一个name和phone列上的合成索引



（2） 使用存储过程sp_helpindex Employees列出表的索引



（3） 分别查询雇员表Employees、雇员工资表wage和工资税表tax中的行数据



（4） 创建一个视图，用于检索雇员工资、税款情况，包括下列字段：



EmployeeID、Name、Sex、Wage、Putdate 、Tax、Paydate



（5） 创建一个视图，用于检索雇员工资高于1000元的雇员情况表



（6） 创建一个视图，用于检索雇员工资高于5000元的雇员情况表







实践4：查询SQL Server数据库表的信息



（1） 列出每个雇员的工资、税款情况一览表



（2） 列出工资超过1000元，而未交税的雇员



（3） 列出工资在1000元以下，不应该交税的雇员情况一览表



（4） 列出只领工资而未交税的雇员表



（5） 对以上四种情况各作一统计报告



（6） 删除三个表中的所有记录







实践5：实现SQL Server数据库的完整性

（1） 创建雇员表Employees的主关键字，列：EmployeeID

（2） 创建雇员工资表wage的外部关键字，列：EmployeeID，参考Employees的主键



（3） 创建工资税表tax的外部关键字，列：EmployeeID，参考Employees的主键



（4） 向雇员表Employees中添加关于以下列的DEFAULT约束：



name：unknown，sex：男，Address：Salt Lake，Phone：(000)00000000



（5） 向雇员表Employees中添加BirthDate列的CHECK约束：在BirthDate列中的值必

须早于今天的日期



（6） 定义雇员电话号码Phone的规则：(***)********，其中*是0~9之间的数字字符



（7） 执行存储过程sp_helpconstraint浏览上面创建的约束信息



（8） 向雇员表Employees
中插入十个雇员信息，并检查约束



（9） 向雇员工资表wage和工资税表tax中添加关于以下列的DEFAULT约束：



Putdate：昨天，Paydate：昨天







实践6：SQL Server数据库的高级查询



（1） 分别列出在雇员表Employees中而未领工资和未交税的男、女雇员情况



（2） 查询电话号码区号为(010)、工资大于3000的男雇员情况

（3） 查询交税最多的六名雇员情况

（4） 列出工资在前六名的，而交税并不在前六名的雇员情况





实践7：创建、执行、修改SQL Server数据库的存储过程



（1） 编写存储过程，向雇员表Employees、雇员工资表wage和工资税表tax中各插入两条记录



（2） 编写存储过程，列出工资最高的六名雇员情况



（3） 编写存储过程，查找雇员表Employees中Sex、Phone与指定值相匹配的雇员



（4） 编写存储过程，查找雇员表Employees中BirthDate在指定两个日期之间的雇员



（5） 编写存储过程，用于计算男、女雇员的平均工资，男、女雇员的人数，并输出



（6） 用存储过程sp_helptext查看上面的存储过程定义







实践8：创建和使用SQL Server数据库的触发器



（1） 创建触发器，从雇员表Employees中一次删除的记录数应不超过2条



（2） 创建触发器，当向雇员工资表wage中插入记录时，同时向工资税表tax中插入记录，工资税计算为：

Wage：1000以下，不交税；1000~5000：交税工资的1%



5000以上：交税工资的3%

（3） 创建触发器，当修改雇员工资表wage中的记录时，同时修改工资税表tax中的记录

（4） 创建触发器，当删除雇员表Employees中的记录时，同时删除雇员工资表wage和工资税表tax中的有关该雇员的所有记录

（5） 创建触发器，当修改工资税表tax中Tax列的值时，检查交税情况是否符合（2）的交税比例

（6） 测试上面创建的触发器





实践9：使用SQL Server数据库的游标



（1） 使用游标获取雇员表Employees中的记录



（2） 使用游标检查雇员工资税的交纳是否正确







实践10：使用SQL Server数据库的用户字定义函数



（1） 编写函数，按男、女雇员分类计算平均工资和合计工资



（2） 编写函数，查找雇员表Employees中Sex、Phone与指定值相匹配的雇

员



（3） 编写函数，查找雇员表Employees中BirthDate在指定两个日期之间的雇员



（4） 定义数据类型mytext，向雇员表Employees中添加列Remark，其类型为mytext