中南大学 大数据库实验盛津芳.docx
《中南大学 大数据库实验盛津芳.docx》由会员分享，可在线阅读，更多相关《中南大学 大数据库实验盛津芳.docx（12页珍藏版）》请在冰豆网上搜索。
中南大学大数据库实验盛津芳
数据库技术实验报告
实验一创建表、更新表和实施数据完整性
实验容
1．运行给定的SQLScript，建立数据库GlobalToyz。
2．了解表的结构，建立所有表的关系图。
3．利用系统预定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。
4．利用系统预定义的存储过程sp_helpconstraint查看所有表中出现的约束（包括Primarykey,Foreignkey,checkconstraint,default,unique）
5．对表Toys实施下面数据完整性规则：
（1）玩具的现有数量应在0到200之间；
（2）玩具适宜的最低年龄缺省为1。
6.列出表PickofMonth中的所有记录，并显示中文列标题。
作答
2.
3.执行命令execsp_helpdbGlobalToyz
4.依次执行命令：
execsp_helpconstraintCategory
execsp_helpconstraintCountry
execsp_helpconstraintOrderDetail
execsp_helpconstraintOrders
execsp_helpconstraintPickOfMonth
execsp_helpconstraintRecipient
execsp_helpconstraintShipment
execsp_helpconstraintShippingMode
execsp_helpconstraintShippingRate
execsp_helpconstraintShopper
execsp_helpconstraintShoppingCart
execsp_helpconstraintToyBrand
execsp_helpconstraintToys
execsp_helpconstraintWrapper
5.
（1）altertabletoys
addconstraintsiToyQoh_sizecheck（siToyQoh>0andsiToyQoh<200）
（2）altertabletoys
addconstraintnewsiLowerAge_mincheck（siLowerAge>=1）
6.执行命令：
selectcToyIdas'玩具id',siMonthas'出厂月份',iYearas'出厂年份',iTotalSoldas'出厂总数'
fromPickOfMonth
实验二查询、更新数据库
实验容
1.显示属于California和Illinoi州的顾客的名、姓和emailID。
2.显示定单、顾客ID，定单的总价值，并以定单的总价值的升序排列。
3.显示在orderDetail表中vMessage为空值的行。
4.显示玩具名字中有“Racer”字样的所有玩具的基本资料。
5.根据2000年的玩具销售总数，显示“PickoftheMonth”玩具的前五名玩具的ID。
6.根据OrderDetail表，显示玩具总价值大于￥50的定单的和玩具总价值。
7.显示一份包含所有装运信息的报表，包括：
OrderNumber,ShipmentDate,ActualDeliveryDate,DaysinTransit.（提示：
DaysinTransit=ActualDeliveryDate–ShipmentDate）
8.显示所有玩具的名称、商标和种类（ToyName,Brand,Category）。
9.以下列格式显示所有购物者的名字和他们的简称：
（Initials,vFirstName,vLastName）,例如AngelaSmith的Initials为A.S。
10.显示所有玩具的平均价格，并舍入到整数。
11.显示所有购买者和收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。
12.显示没有包装的所有玩具的名称。
（要求用子查询实现）
13.显示已收货定单的定单以及下定单的时间。
（要求用子查询实现）
14.显示一份基于Orderdetail的报表，包括cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每一笔定单的玩具总价值。
（提示：
使用运算符COMPUTEBY）。
15.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。
16.给id为‘000001’玩具的价格增加$1。
17.删除“Largo”牌的所有玩具。
作答
1.selectvfirstname,vlastname,vemailid
fromshopperwherecstate='California'orcstate='Illinoi'
2.selectcOrderNo,cShopperId,mTotalCost
fromOrders
orderbymTotalCost
3.select*
fromorderDetail
wherevmessageisnull
4.selectvToyDescription
fromToys
wherevToyNamelike'Racer'
5．selecttop5cToyId
fromPickOfMonth
whereiyear=2000
orderbyiTotalsolddesc
6.selectcOrderNo,mToyCost
fromOrderDetail
wheremToyCost>50
7.selectcOrderNo,dShipmentDate,dActualDeliveryDate,
dActualDeliveryDate-dShipmentDateasDaysinTransit
fromshipment
8.selectvToyName,cBrandName,cCategory
fromToys,category,toybrand
whereToys.cbrandid=ToyBrand.cbrandid
andCategory.cCategoryid=Toys.cCategoryid
9.selectvFirstName,vLastName,
substring（vFirstName,1,1）+'.'+substring（vLastName,1,1）asInitials
fromshopper
10.selectround（avg（mtoyrate）,0）asavgrate
fromtoys
11.selecta.vFirstName,a.vLastName,a.vAddress,a.cCity,
b.vFirstName,b.vLastName,b.vAddress,b.cCity
fromshoppera,Recipientb,orders
whereorders.cShopperId=a.cShopperId
andorders.cOrderNo=b.cOrderNo
12.selectvToyName
fromtoys
wherecToyIdin（selectcToyId
fromOrderDetail
wherecWrapperIdisnull）
13.selectcOrderNo,dOrderDate
fromorders
wherecOrderNoin（selectcOrderNo
fromshipment
wheredActualDeliveryDateisnotnull）
14.selectcOrderNo,cToyId,mToyCost
fromorderdetail
orderbycOrderNo
computesum（mToyCost）bycOrderNo
15.ifexists（select*fromsysobjectswhere[name]='PremiumToys'）
droptablePremiumToys
go
select*intoPremiumToysfromToys
wheremToyRate>20
select*fromPremiumToys
16.updateToyssetmToyRate=mToyRate+1wherecToyId='000001'
17.delete*
fromToys
whereBrandName=’Largo’
实验三：
视图与触发器
实验容
1.定义一个视图，包括购买者的、所在州和他们所订购玩具的名称、价格和数量。
2.基于
（1）中定义的视图，查询显示所有California州的购买者的和他们所订购玩具的名称及数量。
3.编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。
此外，任何玩具的最大价格不应超过$53。
4.创建一个称为prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。
5.创建一个称为prcHandlingCharges的过程，它接收定单号并显示经营费用。
PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。
提示：
经营费用=装运费+礼品包装费
6.在OrderDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。
（提示：
Toycost=Quantity*ToyRate）
作答
1.Createview
Shopperx（vFirstName,vLastName,cState,vToyName,mToyRate,siQty）
as
selecta.vFirstName,a.vLastName,a.cState,b.vToyName,b.mToyRate,c.siQty
fromShoppera,Toysb,OrderDetailc,Ordersd
where（a.cShopperId=d.cShopperIdandb.cToyId=c.cToyId）andc.cOrderNo=d.cOrderNo
2.selectvFirstName,vLastName,vToyName,siQty
fromShopperx
wherecState='California'
3.UseGlobalToyz
while（selectavg（mToyRate）fromtoys）<24.5
begin
if（selectmax（mToyRate）fromtoys）>53
break
else
updatetoys
setmToyRate=mToyRate+0.5
4.createprocprcCharges
cOrderNochar（6）
as
selectcOrderNo,mShippingCharges,mGiftWrapCharges
fromorders
wherecOrderNo=cOrderNo
5.createprocprcHandingCharges
OrderNochar（6）//定单号要长度为6的字符型数据
as
createtable#temp（OrderNochar（6）,mSChargesmoney,mGChargesmoney）
insertinto#tempexecprcChargesOrderNo//使用存储过程prcCharges
selectOrderNo,mSCharges+mGCharges
asHandingCharges//经营费用=装运费+礼品包装费
from#temp
6.createtriggerchange_toycost//新建触发器
onOrderDetail
forupdate
as
ifupdate（siQty）
begin
updateOrderDetail
setmToyCost=siQty*mToyRate
fromOrderDetail,toys
end
实验四：
事务与游标
实验容
1.名为prcGenOrder的存储过程产生存在于数据库中的定单号：
CREATEPROCEDUREprcGenOrder
OrderNochar（6）OUTPUT
as
SELECTOrderNo=Max（cOrderNo）FROMOrders
SELECTOrderNo=
CASE
WHENOrderNo>=0andOrderNo<9Then
‘00000’+Convert（char,OrderNo+1）
WHENOrderNo>=9andOrderNo<99Then
‘0000’+Convert（char,OrderNo+1）
WHENOrderNo>=99andOrderNo<999Then
‘000’+Convert（char,OrderNo+1）
WHENOrderNo>=999andOrderNo<9999Then
‘00’+Convert（char,OrderNo+1）
WHENOrderNo>=9999andOrderNo<99999Then
‘0’+Convert（char,OrderNo+1）
WHENOrderNo>=99999ThenConvert（char,OrderNo+1）
END
RETURN
当购物者确认定单时，应该出现下面的步骤：
（1）用上面的过程产生定单号。
（2）定单号，当前日期，购物车ID，和购物者ID应该加到Orders表中。
（3）定单号，玩具ID，和数量应加到OrderDetail表中。
（4）在OrderDetail表中更新玩具成本。
（提示：
Toycost=Quantity*ToyRate）.
将上述步骤定义为一个事务。
编写一个过程以购物车ID和购物者ID为参数，实现这个事务。
2.编写一个程序显示每天的定单状态。
如果当天的定单值总合大于170，则显示“Highsales”,否则显示”Lowsales”.报告中要求列出日期、定单状态和定单总价值。
作答
1.
createprocprcOrderCartIDchar（6）,ShopperIDchar（6）
as
begintransaction
declareOrderNochar（6）,OrderNo1char（6）,ToyIDchar（6）,Qtychar（6）
execprcGenOrderOrderNooutput
selectOrderNo1=isnull（CONVERT（nvarchar,OrderNo）,''）
createtable#temp1（OrderNochar（6）,CartIdchar（6）,ShopperIdchar（6）,OrderDatedatetime）
insertinto#temp1（OrderNo,CartId,ShopperId,OrderDate）
values（OrderNo1,CartID,ShopperID,getdate（））
ifrowcount=0
begin
rollbackTransaction
return
end
insertintoOrders（cOrderNo,cCartId,cShopperId,dOrderDate）
select*from#temp1
selectToyID=cToyId,Qty=siQtyfromShoppingCart
wherecCartId=CartID
ifrowcount=0
begin
rollbackTransaction
return
end
insertintoOrderDetail（cOrderNo,cToyId,siQty）
values（OrderNo1,ToyID,Qty）
updateOrderDetail
setmToyCost=siQty*mToyRate
fromOrderDetail,Toys
ifrowcount=0
begin
rollbackTransaction
return
end
committransaction
go
2.
declareTotalCostmoney//定义一个变量TotalCost保存当天定单总值
selectOrders.dOrderDate,Orders.cOrderProcessed,sum（mToyCost）
asTotalCost
fromOrders,OrderDetail
whereOrders.cOrderNo=OrderDetail.cOrderNoand
Orders.dOrderDate=getdate（）
groupbyOrders.dOrderDate,Orders.cOrderProcessed
selectTotalCost=sum（mTotalCost）
fromOrders,OrderDetail
whereOrders.cOrderNo=OrderDetail.cOrderNoand
Orders.dOrderDate=getdate（）//选出当天的所有定单
ifTotalCost>170
print'HighSales'
else
print'LowSales'
升级会员 