1、实验一 数据查询实验目的1 掌握 SQL 的单表查询操作2 掌握 SQL 的连接查询操作3 掌握 SQL 的嵌套查询操作4 掌握 SQL 的集合查询操作实验内容1. 创建实验所需要的数据库2. .启动 SQL Server 查询分析器，并使用 PracticeDB1 数据库 (如果没有该数据库，返回 1进行安装)。3. 显示所有玩具(Toys 表)、购物者(Shopper 表)和收件人(Recipient 表) 的所有材料，并详细浏览、熟悉这些数据库。4. 显示所有购物者(Shopper 表) 的名、姓和 E_mailID5. 显示订单(Orders 表)的定单号、购物方式、礼品包装费和所有定
2、单的总成本6. 显示所有玩具(Toys 表)的名和价格7. 显示所有玩具(Toys 表)的名和年龄群(最低和最高) 。8. 按以下格式显示所有玩具的名字、现存数量和重量9. 按以下格式显示所有收件人的名字、地址和邮政编码10. 显示所有住在 California 州的购物者的名和姓。11. 显示总成本超过$75 的订单的所有材料12. 显示价格低于$20 的所有玩具名13. 显示玩具名为“Tin Drum”的玩具的描述14. 显示属于“Largo”牌的玩具材料(Largo 的商标 ID 为 004)15. 显示用万事达信用卡购买玩具的顾客(万事达信用卡为Master Card)16. 显示所有
3、类别 ID 为002 的玩具材料17. 显示日期为 2001 年 5 月 22 号的订单材料18. 显示顾客 ID 为000035 的顾客的名和 emailID。19. 按以下格式显示所有定单的报告(Handling Charges = mShippingCharges + mGiftWrapCharges)20. 显示价格在$15 到$20 之间的所有玩具的列表21. 显示属于California和Illinois州的顾客的名、姓和 emailID22. 按以下格式显示 2001 年 5 月 20 日且总消费高于$75 的订单select OrderNumber=corderno,Order
4、 Date=dorderdate,Shopper Id=cshopperid,Total Cost=mtotalcostfrom orderswhere dexpdeldate=2001 年月日and mtotalcost75实验二 数据查询（检索结果集）-1.显示那些没有附带任何消息(vmessage is NULL) 的订单材料 (orderdetail 表)-2.以下面格式显示所有玩具的名称和价格，保证最贵的玩具总显示在列表的前面-3.显示价格低于$20 的玩具的名称和低年龄限制，以价格的升序排列。-4.显示定单号码、商店 ID 和定单的总价值，以定单的总价值的升序排列。-5.显示在全球
5、玩具销售中玩具的总数。-6.显示玩具的最大、最小和平均价格-7.显示所有定单合起来的总价格。-8.根据 2000 年的玩具销售总数，显示 PickOfMonth 表玩具前 5 名的玩具 ID。-9.显示玩具名字中有“Racer”字样的玩具的所有材料-10.显示以“”开头的商店的所有材料-11.以下列格式显示所有玩具的订单号码、玩具 ID(OrderDetail 表)和玩具的包装说明(Wrapper 表)-12.为显示各定单的总定单值所写的以下代码将输出什么：SELECT cOrderNo, SUM(mToyCost)FROM OrderDetailGROUP BY cOrderNo-13.你可
6、以定购一个以上的玩具。显示报表，它包含玩具的总价值大于$50 的订单的订单号码和玩具的总价值-14.下面包含 COMPUTE BY 子句的两段代码都产生了错误。产生错误的可能原因是什么，修改之。 SELECT cToyId,mToyCost FROM OrderDetailORDER BY cToyId COMPUTE SUM(mToyCost) BY cToyIdSELECT cCartId,mTotalCost FROM OrdersORDER BY cCartIdCOMPUTE AVG(mTotalCost) BY cCartId实验三 数据查询（函数与连接）-1.显示所有玩具(Toys
7、 表)的名称(vToyName)、描述(vToyDescription)和价格(mToyRate )。然而，只需显示描述的前 40 个字符。-2.以下列格式显示一份包含所有装运信息(Shipment 表) 的报表：Order Number Shipment Date Actual Delivery Date Days in Transit-3.以下列格式显示(Shipment 表) 订货号 cOrderNo 为 000009 的报表：Order Number Days in Transit-4.以下列格式显示所有定单：Order Number Shopper Id Day of Order W
8、eek Day-5.显示所有玩具的名称(Toys 表)和种类的名称(Category 表)-6.以下列格式显示所有玩具的名称(Toys 表)、商标(ToyBrand 表) 和种类(Category 表)Toy Name Brand Category-7.以下列格式显示所有玩具的订货号、玩具 ID(OrderDetail 表)和玩具使用的礼品包装说明(Wrapper 表)-8.使用外连接显示所有玩具的名称(Toys 表)和所对应的购物车 ID(ShoppingCart 表)。如果玩具不在购物车当中，则显示 NULL 值。-9.以下列格式显示所有购物者(shopper 表)的名、姓和他们的简称 (
9、名、姓的首字母)select Initials=left(vFirstName,1)+.+left(vLastName,1)+.,vFirstName,vlastName from shopperOrder Number Toy ID Wrapper Description-10.以下列格式显示定单号码、定单日期和每个定单所在的季节(orders 表)cOrderNo dOrderDate Quarterselect cOrderNo,dOrderDate,Quarter = datepart(quarter,dOrderDate)FROM orders-11.显示所有玩具(toys 表)的平
10、均价格，并把它舍入到整数-12.使用外连接显示所有 cShopperId 及其相应定单的接收者标号 cOrderNo，如果无接收者，则显示 NULL 值。/*ch05_独立实践及答案.sql*/实验四 数据查询（使用子查询）1.把 Toys 表中价格在$20 以上的所有玩具的材料拷贝到称为 PremiumToys 的新表中。2.以下面格式显示购买者和收货人的名、姓、地址和所在的城市。First Name Last Name Address City3.显示属于Stuffed Toys类型的玩具名称（玩具名称在 Toys 表中，类型玩具在 CATEGORY表中）4.下面代码用于把来自来自加州的所
11、有购买者的材料拷贝到称为 CaliforniaShopper 的新表中。当代码被执行时，产生了错误。确定错误并改正之。SELECT * FROM Shopper INTO CaliforniaShopperWHERE cState=California5.把位于 Woodbridge、San Jose 和 Las Vegas 城市生活的所有购买者（shopper 表）的名和姓显示出来。6.显示没有包装(orderdetail 表的 cWrapperId 为 NULL)的所有玩具的名称(toys 表的vToyName)。7.显示已发货定单(shipment 表的 cDeliveryStatus
12、为S)的定单号(orders 表的 cOrderNo)。实验五 数据库的建立和维护实验目的1 掌握 SQL 的数据定义功能包括：定义表、删除表和修改表掌握 SQL 的数据更新功能，包括：插入数据、修改数据和删除数据。2 掌握在 SQL Server 企业管理器中定义表、删除表、修改表、以及插入数据、修改数据和删除数据的操作。实验内容1.创建用于存储收货人材料的 recipient 表。下面是要存储到表中的收货人材料-答案：-创建表USE PracticeDB2GOcreate table Recipient(cOrderNochar(6) not null,vFirstName varchar
13、(20)not null,vLastName varchar(20) not null, vAddress varchar(20) null,cCity char(15) null,cState char(15) null,cCountryId char(3) null,cZipCodechar(10) null,cPhone char(15) null)INSERT RecipientVALUES (000035 ,Shirley, Nelson, 56700 Chain Boulevard Apartment #899, Austin, Texas, 001, 78727, 409-238
14、7, ) 2.Recipient 表和 Country 表对于 cCountryID 属性没有同样的数据类型。下面是两个表的示例结构：重新创建 Recipient 表和 Country 表，使这两个表在 cCountryID 属性上有同样的数据类型。-答案：-创建用户定义的数据类型sp_addtype typCountryID,char(3)GOcreate table Recipient(cOrderNo char(6) not null,vFirstName varchar(20)not null,vLastName varchar(20) not null,vAddress varcha
15、r(20) null,cCity char(15) null,cState char(15) null,cCountryId typCountryID null,cZipCode char(10)null,cPhone char(15) null)create table Country(cCountryId typCountryID,cCountry char(25)3.删除 Recipient 表和 Country 表4.考虑下面的表结构：就以下问题参考这些表结构：a.创建 Category 表。在创建表时实施下面的数据完整性规则：cCategoryID 属性应作为主健cCategory
16、属性应为唯一，但不是主健vDescription 的描述可存储 NULL 值-答案：-创建约束drop table Categorycreate table Category(cCategoryId char(3) constraint pkCategoryId primary key,cCategory char(20) constraint unqCategory unique,vDescription varchar(100) null)b.创建 ToyBrand 表。在创建表时实施下面的数据完整性规则：cBrandID 属性应作为主健cBrandName 属性应为唯一，但不是主健-答案
17、：-创建约束DROP TABLE ToyBrandcreate table ToyBrand(cBrandId char(3) constraint pkBrandId primary key,cBrandName char(20) constraint unqBrandName unique)c.创建 Toys 表。在创建表时实施下面的数据完整性规则：cToysID 属性应作为主健玩具的 siToyQoh 现有数量应在 0 到 200 之间imPhoto 和 vToyImgPath 属性不可取 NULL 值vToyName 和 vToyDescription 属性不可取 NULL 值玩具的最
18、低年龄 siLowerAge 缺省为 1cCategoryId 属性的值应出现在 Category 表中-答案：-创建约束DROP TABLE Toyscreate table Toys(cToyId char(6) constraint pkToyid primary key clustered,vToyName varchar(20) not null,vToyDescription varchar(250)not null,cCategoryId char(3) references Category(cCategoryId) ,mToyRate money not null,cBran
19、dId char(3),imPhoto image null,siToyQoh smallint constraint chkToyQoh check(siToyQoh between 0 and 200),siLowerAge smallint default 1,siUpperAge smallint ,siToyWeight smallint,vToyImgPath varchar(50) null)d.修改 Toys 表。实施下面的数据完整性规则：cBrandID 属性的值应出现在 ToyBrand 表中玩具的最大年龄 siUpperAge 缺省为 1-答案：-修改约束alter table toysadd constraint fkBrandId FOREIGN KEY(cBrandId) references ToyBrand(cBrandId)alter table toysadd constraint defUpperAge DEFAULT 1 FOR siUpperAgesp_help toys5.下面的代码用于把 CaliforniaShopper 表抹去Delete TABLE CaliforniaShopper这代码产生了错误并终止，确定错误并修改之-答案：-删除表DROP TABLE CaliforniaShopper
