实验任务书

实验任务书

地点：计算机楼四楼实验室

时间安排：

第14周星期六（12月3日）上午（四节）

第15周星期六（12月10日）上午（四节）

实验报告要求：

1.列出所有的SQL语句和源代码；

2.程序要求有适当的注释；

3.对数据完整性约束实施、实验三、实验四和实验五要求给出相应的测试用例。

实验内容：

实验一：创建表、更新表和实施数据完整性

1．运行给定的SQL Script，建立数据库GlobalToyz。

2．创建所有表的关系图。

3．列出所有表中出现的约束（包括Primary key, Foreign key, check constraint, default, unique）4．对Recipient表和Country表中的cCountryId属性定义一个用户自定义数据类型，并将该属性的类型定义为这个自定义数据类型。（sp_addType CIDNum,’char (3)’,’not null’）5．把价格在$20以上的所有玩具的材料拷贝到称为PremiumToys的新表中。（select* into PremiumToys from toys where mToyRate>20）

6．对表Toys实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。(alter table toys

add constraint siToyQoh check(siToyQoh>=0 and siToyQoh<=200), default 1 for siLowerAge)

不修改已创建的Toys表，利用规则实现以下数据完整性：（1）玩具的价格应大于0；（2）玩具的重量应缺省为1。(alter table toys

add constraint mToyRate check(mToyRate>0),

default 1 for siToyWeight

7．)

8．给id为‘000001’玩具的价格增加$1。(update toys set mToyRate=mToyRate+1 where cToyId=000001)

实验二：查询数据库

1.显示属于California和Illinoi州的顾客的名、姓和emailID。（select

vFirstName,vLastName,vEmailId from shopper where cState in ('California','Illinoi')）

2.显示定单号码、商店ID，定单的总价值，并以定单的总价值的升序排列。

（select cOrderNo,cShopperId,mTotalCost from Orders order by mTotalCost asc

）

3.显示在orderDetail表中vMessage为空值的行。

（select*from OrderDetail where vMessage is null

）

4.显示玩具名字中有“Racer”字样的所有玩具的材料。（select*from toys where

vToyName like'%Racer%'）

5.根据2000年的玩具销售总数，显示“Pick of the Month”玩具的前五名玩具的ID。（select top 5 *from pickofmonth where iYear=2000 order by iTotalSold desc）

6.根据OrderDetail表，显示玩具总价值大于￥50的定单的号码和玩具总价值。

（select cOrderNo,mToyCost from OrderDetail where mToyCost>50）

7.显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery

Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date – Shipment Date) （select

cOrderNo,dShipmentDate,dActualDeliveryDate,(dActualDeliveryDate-dShip mentDate)as DaysinTransit

from Shipment

）

8.显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。

（select toys.vToyName,ToyBrand.cBrandName,ategory

from toys inner join ToyBrand on toys.cBrandId=ToyBrand.cBrandId inner join Category

on ategoryId=ategoryId

）

9.显示玩具的名称和所有玩具的购物车ID。如果玩具不在购物车中，则显示NULL值。（select toys.vToyName,artId

from toys left join ShoppingCart on toys.cToyId=ShoppingCart.cToyId）10.以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）,

例如Angela Smith的Initials为A.S。

（select(LEFT(vFirstName,1)+'.'+LEFT(vLastName,1))

as Initials, vFirstName,vLastName from shopper）

11.显示所有玩具的平均价格，并舍入到整数。（select cast(avg(mToyRate)as int)

from toys）

12.显示所有购买者和收货人的名、姓、地址和所在城市。

（select vFirstName,vLastName,cCity from shopper

select vFirstName,vLastName,cCity from Recipient）

13.显示没有包装的所有玩具的名称。（要求用子查询实现）

（select vToyName from toys where cToyId not in(select cToyId from OrderDetail where cWrapperId is null)

）

14.显示已发货定单的定单号码以及下定单的时间。（要求用子查询实现）

（select cOrderNo,dOrderDate from Orders where cOrderNo in(select cOrderNo from Orders where cOrderProcessed='Y')）

实验三：视图与触发器