做完这些课后习题~你的数据库就能上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数据库，创建满足如下要求的触发器：
sql统计每个系的学生人数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）在
发表评论