数据库SQL实验文档格式.docx

数据库SQL实验文档格式.docx

《数据库SQL实验文档格式.docx》由会员分享，可在线阅读，更多相关《数据库SQL实验文档格式.docx（6页珍藏版）》请在冰豆网上搜索。

--4． 对Recipient表和Country表中的cCountryId属性定义一个用户自定义数据类型，

并将该属性的类型定义为这个自定义数据类型。

--5． 把价格在$20以上的所有玩具的材料拷贝到称为PremiumToys的新表中。

ifexists（select*fromsysobjectswhere[name]='

PremiumToys'

）droptablePremiumToys

go

select*intoPremiumToysfromToyswheremToyRate>

20

select*fromPremiumToys

--6． 对表Toys实施下面数据完整性规则：

（）玩具的现有数量应在到之间；

（）玩具适宜

的最低年龄缺省为。

altertableToysaddconstraintck_toyQohcheck（siToyQoh>

0andsiToyQoh<

200）

createdefaultsiLowerAgeas1

--7． 不修改已创建的Toys表，利用规则实现以下数据完整性：

（）玩具的价格应大于；

（）

玩具的重量应缺省为。

createruleToyRate_ruleas（@mToyRate>

0）

createdefaultsiToyWeightas1

--8． 给id为„‟玩具的价格增加$1。

updateToyssetmToyRate=mToyRate+1wherecToyId='

000001'

select*fromToys --玩具（单价在这）

select*fromOrders --订单（总价在这包含运费包装费）

select*fromOrderDetail --订单细节（玩具总价）

select* fromshopper --购物者

select* fromCategory --玩具种类

select* fromCountry --国家

select*fromPickOfMonth --玩具生产日期及销售量

select* fromRecipient --收货人

select* fromShipment --货运详情

select* fromShippingMode --货运类型

select* fromShippingRate --货运收费详情

select* fromShoppingCart --货运订单

select* fromToyBrand --玩具商标

select* fromWrapper --玩具包装

--实验二：

查询数据库

-

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

selectvFirstName,vLastName,vEmailIdfromshopperwherecState='

California'

orcCity='

Illinoi'

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

selectcOrderNo,cShopperId,mShippingChargesfromOrdersorderbymShippingCharges

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

select*fromOrderDetailwherevMessageisNULL--不能用vMessage=null

--4. 显示玩具名字中有“Racer”字样的所有玩具的材料。

select*fromToyswherevToyNamelike'

%Racer%'

--5. 根据年的玩具销售总数，显示“PickoftheMonth”玩具的前五名玩具的ID。

selecttop5cToyIdfromPickOfMonthwhereiYear=2000orderbyiTotalSoldDESC

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

selectcOrderNo,mToyCostfromOrderDetailwheremToyCost>

50

--7. 显示一份包含所有装运信息的报表，包括：

OrderNumber,ShipmentDate,

--ActualDeliveryDate,DaysinTransit.

--（提示：

DaysinTransit=ActualDeliveryDate–ShipmentDate）ifexists（select*fromsysobjectswhere[name]='

shippingment'

）droptableshippingment

selectcOrderNoas'

OrderNumber'

dShipmentDateas'

ShipmentDate'

dActualDeliveryDateas'

ActualDeliveryDate'

convert（int,dActualDeliveryDate-dShipmentDate）as'

DaysinTransit'

intoshippingmentfromshipmentselect*fromshippingment

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

selectvToyNameas'

ToyName'

cBrandNameas'

Brand'

cCategoryas'

Category'

fromToys,ToyBrand,Category

whereToys.cCategoryId=Category.cCategoryId

andToys.cBrandId=ToyBrand.cBrandId

--9. 显示玩具的名称和所有玩具的购物车ID。

如果玩具不在购物车中，则显示NULL值。

selectvToyName,cCartIdfromShoppingCart

rightouterjoinToysonShoppingCart.cToyId=Toys.cToyId

--10.以下列格式显示所有购物者的名字和他们的简称：

（Initials,vFirstName,vLastName）,例如AngelaSmith的Initials为A.S。

select（left（vFirstName,1）+'

.'

+left（vLastName,1））asInitials,vFirstName,vLastNamefromShopper

--11.显示所有玩具的平均价格，并舍入到整数。

selectcToyId,vToyName,round（avg（mToyRate）,1）as'

avgprice'

fromToysgroupbycToyId,vToyName

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

selectvfirstname,vlastname,vaddress,ccityfromshopper

union

selectvfirstname,vlastname,vaddress,ccityfromrecipient

--13.显示没有包装的所有玩具的名称。

（要求用子查询实现）selectvToyNamefromToyswherecToyId

in（selectcToyIdfromOrderDetailwherecGiftWrap='

N'

）

--14.显示已发货定单的定单号码以及下定单的时间。

（要求用子查询实现）

selectcOrderNo,dOrderDatefromOrderswherecCartIdin（selectcCartIdfromShoppingCart）

--实验三：

视图与触发器

--1. 定义一个视图，包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。

createviewshopper_vw

as

selectvfirstname,vlastname,cState,vToyName,mToyCost,siQtyfromShopper,Toys,Orders,OrderDetail

whereShopper.cShopperId=Orders.cShopperIdandorders.cOrderNo=OrderDetail.cOrderNoandOrderDetail.cToyId=Toys.cToyId

select*fromshopper_vw

--2. 基于（）中定义的视图，查询显示所有California州的购买者的姓名和他们所订购

玩具的名称及数量。

selectvfirstname,vlastname,vToyName,siQtyfromshopper_vwwherecState='

--3. 视图定义如下：

-- CREATEVIEWvwOrderWrapper

-- AS

-- SELECTcOrderNo,cToyId,siQty,vDescription,mWrapperRate

-- FROMOrderDetailJOINWrapper

-- ONOrderDetail.cWrapperId=Wrapper.cWrapperId

--以下更新命令，在更新siQty和mWrapperRate属性使用了以下更新命令时出现错误：

-- UPDATEvwOrderWrapper

-- SETsiQty=2,mWrapperRate=mWrapperRate+1

-- FROMvwOrderWrapper

-- WHEREcOrderNo=„‟

--修改更新命令，以更新基表中的值。

--4. 在OrderDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自

动地改变。

--（提示：

Toycost=Quantity*ToyRate）createtriggerOrderDetail_trgonOrderDetail

forupdateas

declare@siQtyint

declare@mToyCostmoney

declare@ToyRatemoney

select@ToyRate=mToyRatefromToysselect@siQty=siQtyfrominsertedset@mToyCost=@siQty*@ToyRateupdateOrderDetailsetmToycost=@mToyCostgo