参考答案数据库技术实验指导

数据库技术(SQL Server 2005)实验指导 / / 学年学年 第第 学期学期 姓姓 名：名：_ 学学 号：号：_ 班班 级：级：_ 指导教师：指导教师：_ 计计算机科学与工程学院算机科学与工程学院 2010 2 目录目录 目录2 实验一 SQl Server 2005 常用工具使用3 实验二 数据的简单查询6 实验三 数据的复杂查询12 实验四 管理数据库和表16 实验五 操纵表中数据22 实验六 SQL Server 程序设计25 实验七 存储过程和自定义函数27 实验八 视图和索引29 实验九 触发器32 实验十 数据库设计35 3 实验一实验一 SQl Server 2005 常用工具使用常用工具使用 一、实验目的一、实验目的 1 . 了解 SQl Server 2005 包含的主要组件及其功能； 2 了解 SQl Server 2005 的配置管理工具； 3 熟悉 SQl Server 管理工作室的界面及基本使用方法； 4 了解联机帮助文档的使用，熟悉示例数据库 adventureworks； 5. 掌握 select 语句实现简单查询。 二、实验预习二、实验预习 1 SQL Server 各组件的主要功能是什么？ 2、如何安装 SQL Server 2005？ 三、实验内容和要求三、实验内容和要求 1 通过“开始” “程序” Microsoft SQL Server2005 “配置工具”SQL Server Configuration Manager，打开“SQL Server 配置管理工具”窗口，选择 SQL Server 服务并且通过该窗口“启动”和“停止” SQL Server 服务。 2 通过“开始” “程序” Microsoft SQL Server2005 “SQL Server Management Studio”打开管理工作室窗口。 3 打开本地服务器的属性对话框，在常规中查看以下信息：产品名称、主机操作系统、 产品的版本、操作平台、主机内存等 4 利用管理工作室访问系统自带的示例数据库 adventureworks。 ( l )登录到管理工作室。 ( 2 )在管理工作室的树形目录中找到 adventureworks 数据库并展开，查看该数据库的所 4 有对象，如表、视图，存储过程、默认和规则等。 ( 3 )选择 adventureworks 数据库的“表”选项，在右窗格中将列出 adventureworks 数 据库的所有表（包括系统表和用户表） ，选择用户表 humanresources.employee，右击，弹 出快捷菜单，从中选择“打开表”菜单项，打开 humanresources.employee 表，查看其内 容并作出解释。 5 利用查询编辑器访问 adventureworks 数据库的表。 ( l )通过在管理工作室中，选择“新建查询”工具项，运行 SQL Server 查询编辑器。 ( 2 )在管理工作室窗口中，选择更改数据库下拉框的下拉按钮，选择要操作的 adventureworks 数据库。 ( 3 )在查询编辑器的编辑窗口中，输人以下代码： SELECT * FROM humanresources.employee ( 4 )选择查询编辑器的“查询” “分析”菜单项，或者单击常用工具栏上“分析查询” 按钮，查询编辑器将对输入的代码进行语法分析，并由消息窗格给出分析结果报告 ( 5 )选择查询编辑器的“查询” “执行”菜单项，或者单击常用工其栏上“执行查询” 按钮。SQL Server 将编译窗口中的代码并在结果窗格中显示查询结果，写出记录数。 6、通过在管理工作室中，选择“帮助” “目录”菜单项打开联机帮助文档，找到范例 数据库部分，了解范例数据库详细介绍及其数据字典。 7、利用 SELECT 语句完成以下简单查询。 （执行操作后，将实现功能的语句写在空白处） 1)显示所有顾客的详情。 SELECT * FROM Sales.Customer 2)简单查询：以下面的格式显示所有信用卡的 ID, type, number 和 expiry year： 信用卡 ID信用卡类型信用卡号过期年份 SELECT Credit Card ID = CreditCardID, Credit Card Type = CardType,Credit Card Number = CardNumber, Expiry Year = ExpYear from Sales.CreditCard 3)查询公司所有员工的详细信息。 5 SELECT * from humanresource.employee 4)以下面格式查询部门的相关信息。 SELECT DepartmentID as 部门编号,Name as 部门名称,GroupName as 部 门所属组名from humanresources.department 5)显示所有产品的名称，产品标识号，尺寸，重量，开始销售日期，停止销售日期。 SELECT Name,ProductNumber,Size,Weight,SellStartDate,SellEndDate from duct 6)显示所有采购运输的编号、发货商名称、最低的运费。 SELECT ShipMethodID,Name,ShipBase from Purchasing.ShipMethod 四、实验小结：四、实验小结： 五、评阅成绩五、评阅成绩 实验预习 20%实验过程 20%实验结果 30%实验报告 30%总成绩 部门编号部门名称部门所属组名 6 实验二实验二 数据的简单查询数据的简单查询 一、实验目的 1.掌握使用 SELECT 语句的 where 子句进行条件查询； 2.掌握使用 ORDER BY 从句来以特定的顺序显示数据的方法； 3.掌握使用 TOP、DISTINCT 的使用； 4.掌握 SQL Server 2005 常用系统函数的使用。 5.掌握各种集合函数（聚合函数）的使用； 6.掌握产生各种分类汇总结果的方法。 二、实验预习二、实验预习 二、实验预习 1、 查询语句 SELECT 的基本格式： 2、 SQL Server 2005 提供了哪些系统函数供使用？ 3、 SQL Server 中的集合函数及其各自的功能? 4、 GROUP BY, COMPUTE 子句的功能? 三、实验内容和要求：（执行操作后，将实现功能的语句写在空白处） (一、一、)带条件查询带条件查询 1、 显示所有区域 ID 为 4 的顾客 ID 和顾客帐号。 SELECT CustomerID, AccountNumber FROM Sales.Customer WHERE TerritoryID = 4 7 2、 显示超过 2,000 的销售订单的详情。 （一个订单的总花费） SELECT * FROM Sales.SalesOrderDetail WHERE LineTotal 2000.00 3、 显示产品名为Cable Lock的销售订单的详情。 （提示： Cable Lock 的产品 ID 是 843。 ） SELECT * FROM Sales.SalesOrderDetail WHERE ProductID=843 4、 显示所有在 2004 年 6 月 6 日下的订单。 SELECT * FROM Sales.SalesOrderHeader WHERE OrderDate = 06-06-2004 5、以下面的格式显示所有订单的报告。 Order IDOrder QuantityUnit PriceTotal Cost 提示: Total Cost = Order Quantity * Unit Price，不允许使用 LineTotal 列。 SELECT Order ID = SalesOrderID, Order Quantity = OrderQty, Unit Price = UnitPrice, Total Cost = OrderQty * UnitPrice FROM Sales.SalesOrderDetail 6、 显示所有价格在 2,000 到 2,100 之间的销售订单的列表。 （包括 2000 和 2100） SELECT * FROM Sales.SalesOrderDetail WHERE LineTotal BETWEEN 2000 AND 2100 7、 显示区域 ID 是 1 的地区的名称、国家地区代码和本年度的销售额。 SELECT Name, CountryRegionCode, SalesYTD FROM Sales.SalesTerritory WHERE TerritoryID = 1 8、 显示税收总额超过 10,000 的订单的详情。 SELECT * FROM Sales.SalesOrderHeader WHERE TaxAmt = 10000 9、 显示加拿大、法国和德国的销售地区详情。 SELECT * FROM Sales. SalesTerritory WHERE Name IN (Canada, France, Germany) 10、使用下面的格式，产生一个包括生活地区 ID 是 2 或 4 的地区的销售人员 ID 的报告。 SELECT Sales Person ID=SalesPersonID, Territory ID = TerritoryID FROM Sales.SalesTerritoryHistory WHERE TerritoryID = 2 OR TerritoryID = 4 11、显示在 2006 年失效的 Vista 信用卡的详情。 SELECT * FROM Sales.CreditCard WHERE CardType = Vista AND ExpYear = 2006 12、显示所有在 2004 年 7 月 12 日交货的订单的详情。 SELECT * FROM Sales.SalesOrderHeader WHERE ShipDate 2004-07-12 Sales Person IDTerritory ID 8 13、以下面的格式，显示在 2001 年 7 月 1 日下的客户付费额超过 10,000 的订单。 SELECT Order Number = SalesOrderID, Order Date = OrderDate, Status, Total Cost = TotalDue FROM Sales.SalesOrderHeader WHERE OrderDate = 07-01-2001 AND TotalDue 10000 14、显示顾客在线下的订单的详情。 SELECT * FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 0 15、查找产品名以 e 结束，倒数第二个字符为 c 或者 d 的所有产品的详细信息。 SELECT * FROM Production.Product WHERE Name like %c,de 16、显示所有中间名非空的人的详细信息。 SELECT * FROM Person.Contact WHERE MiddleName Is Not NULL 17、以下面的格式，显示所有销售订单的销售 ID 和总应到款。要求按订单价格从高到低排 序。 SELECT Order ID = SalesOrderID, Total Due = TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC 18、按本年度销售额降序显示销售人员 ID，区域 ID 和本年度销售额。 SELECT SalesPersonID,TerritoryID,SalesQuota FROM Sales.SalesPerson order by SalesQuota desc 19、显示订单总额小于 2,000 的销售订单订单 ID 和总税收。数据应该以订单总额升序显示。 SELECT SalesOrderID, TaxAmt FROM Sales.SalesOrderHeader WHERE TotalDue 1000 ORDER BY TotalDue ASC 21、显示销售构架中货币名字中含有Dollar的所有货币的详情。 SELECT * FROM Sales.Currency WHERE Name like %Dollar% 22、显示所有名字以N开始的销售地区的详细信息。 SELECT * FROM Sales.SalesTerriory WHERE Name like N% 23、显示 SalesPersonID, TerritoryID 和指定了指定了销售计划额的销售人员的销售计划额。数 据应该以下面的格式显示。 Order NumberOrder DateStatusTotal Cost Order IDTotal Due 9 SELECT Sales Person ID = SalesPersonID, TerritoryID= TerritoryID, Sales Quota = SalesQuota FROM Sales.SalesPerson WHERE SalesQuota IS NOT NULL 24、显示用于购买产品的信用卡的不同类型。 （类型重复的不显示） SELECT DISTINCT CardType FROM Sales.CreditCard （二）系统函数的使用（二）系统函数的使用 25、以下面的格式显示所有的订单。 （期中 Week Day 为星期） Order NumberTotal DueDay of OrderWeek Day SELECT Order Number = SalesOrderID, Total Due = TotalDue, Day of Order = DATEPART(dd, OrderDate), Week Day = DATEPART(dw, OrderDate) FROM Sales.SalesOrderHeader 26、 显示 2001 年订单总额最高的 5 个订单的订单 ID。 SELECT TOP 5 SalesOrderID FROM Sales.SalesOrderHeader WHERE Datepart(yyyy,OrderDate) = 2001 ORDER BY TotalDue DESC 27、显示所有商店的顾客 ID，商店名和销售人员的 ID，期中商店名只显示前 15 个字符。 SELECT CustomerID, Name = LEFT(Name, 15), SalesPersonID FROM Sales.Store 28、显示来自 SalesOrderDetai 表的 SalesOrderID, OrderQty、UnitPrice 和自定义编号， 自定义编号的要求为表中一个相同的单价需要用一个值标识，且所有编号是连续的。 SELECT SalesOrderID, OrderQty,UnitPrice, DENSE_RANK() OVER(ORDER BY UnitPrice ASC)AS RANK FROM Sales.SalesOrderDetail 29、显示来自员工表的员工 EmployeeID 、Year(受聘年份)、Month(受聘月份)。 SELECT EmployeeID, Month = DATEPART (mm, HireDate), Year = DATEPART (yy, HireDate) FROM HumanResources.Employee 30、显示颜色不为空的所有产品的产品代号，产品名称和颜色，其中产品名称用大写显示， 颜色用小写显示。 SELECT ProductNumber UPPER(Name) Lower(Color) FROM Production.Product Where Color Is Not NULL 31、假设销售订单的有效天数为 20 天，输出订单号，订单创建的日期，以及订单的有效时 间截止日期。 SELECT SalesOrderNumber,OrderDate,有效时间截止日期DateAdd(dd,20,orderdate) FROM Sales.SalesOrderHeader Sales Person IDTerritory ID Sales Quota 10 32、显示采购订单的订单号，发货日期，发货日期到当前日期的相隔天数，年数。 SELECT PurchaseOrderID,ShipDate,相隔天数DateDiff(dd,shipdate,getdate(), 相隔年数DateDiff(yy,shipdate,getdate() FROM Purchasing.PurchaseOrderHeader 33、显示对产品重量有描述的产品的产品代号，产品名称和产品重量，其中重量四舍五入 到保留一位小数。 SELECT ProductNumber,Name round(Weight,1) FROM Production.Product Where Weight Is Not NULL 34、以下面格式显示所有联系人的名、姓和他们的简称。 InitialFirstnamelastname A.S.AngelaSmith SELECT Initial=left(Firstname,1)+.+left(Lastname)+.,FirstName,LastName FROM Person.Contact 35、以下面的格式显示订单号码、订单日期和每个订单所在的季节 SalesOrderIDOrderDateQuarter 436592001-7-1 0:00:003 SELECT SalesOrderID,OrderDate,QuarterDatepart(qq,orderdate) FROM Sales.SalesOrderHeader 36、查询销售订单号与订单日期，订单日期以 2001.01.08 的格式显示 SELECT SalesOrderID,OrderDateConvert(char(10),orderdate,102) FROM Sales.SalesOrderHeader 37、下面 SQL 语句将输出什么？ 1)、select substring(I am a fresher,3,10) am a fresh 2)、select upper(John Smith),lower(John Smith) JOHN SMITH john smith 3)、select replicate(good!,3) good!good!good! 4)、select 2,3,power(2,3) 2 3 8 5)、select round(668.678,2) 668.680 （三）分组与统计 38、从 Sales.SalesOrderHeader 表中显示所有销售订单的总值。 SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader 39、下面写出的代码能否输出每个订单的总订单值?如不能请写出正确的语句。 SELECT SalesOrderID,ProductID,SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY SalesOrderID SELECT SalesOrderID,ProductID,SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY SalesOrderID,ProductID 11 40、在 Sales.SalesOrderDetail 表中，一个订单可以包含多个产品。显示产品 ID 和单个产品 总价超过 10000 的总价。 SELECT ProductID, Sum(LineTotal) as Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING Sum(LineTotal) 10000 41、下面包含 COMPUTE BY 从句的 SQL 查询是否有错误？如有错请修改。 SELECT ProductID, LineTotal AS Total FROM Sales.SalesOrderDetail COMPUTE SUM(LineTotal) BY ProductID SELECT ProductID, LineTotal AS Total FROM Sales.SalesOrderDetail Order by Productid,Linetotal COMPUTE SUM(LineTotal) BY ProductID 42、显示每天的订单款总和，并按该总和降序排列结果集。 SELECT OrderDate, SUM(TotalDue) FROM Sales.SalesOrderHeader GROUP BY OrderDate order by SUM(TotalDue) DESC 43、在销售架构中，显示 774 号产品和 777 号产品的明细和总单价及总数，最后计算这两 种产品的总单价和总数。 SELECT ProductID, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE ProductID IN (777, 774) ORDER BY ProductID, UnitPrice, LineTotal COMPUTE SUM(UnitPrice), SUM(LineTotal) BY ProductID COMPUTE Sum(UnitPrice), Sum(LineTotal) 44、在 Sales.SalesOrderDetail 中，查询订单总值大于 5,000 的订单，显示销售订单 ID、订 单总值 LineTotal 的最大值、订单总值的最小值。 SELECT SalesOrderID, MIN(LineTotal) as Minimum , MAX(LineTotal) as Maximum FROM Sales.SalesOrderDetail WHERE LineTotal 5000 GROUP BY SalesOrderID 45、在 Sales.SalesOrderDetail 中，以下面的格式显示销售订单 ID、基于销售订单的商品总 值 LineTotal 的平均值，要求该平均值是大于$5,000 的才被显示。 SELECT SalesOrderID, Average Value = Avg(LineTotal) FROM Sales.SalesOrderDetail GROUP BY SalesOrderID HAVING AVG(LineTotal) 5000 46、在 Sales.SalesOrderDetail 表中，显示总销售量大于 1000000 以及产品平均定购数量小 于 3 的产品的产品 ID、总销售额和产品平均定购数量。 ProductIDTotalAverage SELECT ProductID, sum(linetotal),AVG(OrderQty) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING Sum(LineTotal) 1000000 and Avg(OrderQty) (SELECT Avg(TotalDue) FROM Sales.SalesOrderHeader) 13、写一个查询显示订单 ID，订单详情 ID 和订单产品总值，要求单产品总值大于订单 ID 为 43662 的订单产品总值中的最大值。 SELECT SalesOrderID, SalesOrderDetailID, LineTotal FROM Sales.SalesOrderDetail WHERE LineTotal ALL (SELECT LineTotal FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43662) 14、写出一个查询，显示订单 ID 和在 2007 年过期的信用卡的 ID。 SELECT SalesOrderID, CreditCardID FROM Sales.SalesOrderHeader WHERE CreditCardID IN (SELECT CreditCardID FROM Sales.CreditCard WHERE ExpYear = 2007) 15、写出一