中南大学数据库实验报告Word格式文档下载.docx
- 文档编号:16283064
- 上传时间:2022-11-22
- 格式:DOCX
- 页数:21
- 大小:507.51KB
中南大学数据库实验报告Word格式文档下载.docx
《中南大学数据库实验报告Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《中南大学数据库实验报告Word格式文档下载.docx(21页珍藏版)》请在冰豆网上搜索。
WHEREcState='
California'
orcState='
Illinoi'
;
2.显示定单号码、顾客ID,定单的总价值,并以定单的总价值的升序排列。
SELECTcOrderNo,cShopperId,mTotalCost
FromOrders
ORDERBYmTotalCostASC;
3.显示在orderDetail表中vMessage为空值的行。
SELECT*
FromorderDetail
WHEREvMessageISNULL;
4.显示玩具名字中有“Racer”字样的所有玩具的基本资料。
FromToys
WHEREvToyNameLIKE'
%Racer%'
5.列出表PickofMonth中的所有记录,并显示中文列标题。
SELECTcToyId玩具编号,siMonth月,iYear年,iTotalSold销售总量
FromPickofMonth;
6.根据2000年的玩具销售总数,显示“PickoftheMonth”玩具的前五名玩具的ID。
selecttop5cToyId,SUM(iTotalSold)astotal
fromPickOfMonth
whereiYear='
2000'
groupbycToyId
OrderBytotalDESC;
7.根据OrderDetail表,显示玩具总价值大于¥50的定单的号码和玩具总价值。
selectcOrderNo,mToyCost
fromOrderDetail
wheremToyCost>
'
50'
8.显示一份包含所有装运信息的报表,包括:
OrderNumber,ShipmentDate,ActualDeliveryDate,DaysinTransit.
selectcOrderNoOrder_Number,
dShipmentDateShipment_Date,
dActualDeliveryDate
Actual_Delivery_Date,
dActualDeliveryDate-dShipmentDateDays_in_Transit
fromShipment;
9.显示所有玩具的名称、商标和种类(ToyName,Brand,Category)。
selectvToyName,cBrandId,cCategoryId
fromToys;
10.以下列格式显示所有购物者的名字和他们的简称:
(Initials,vFirstName,vLastName),例如AngelaSmith的Initials为A.S。
selectleft(vFirstName,1)+'
.'
+left(vLastName,1)Initials,
vFirstName,vLastName
fromshopper;
11.显示所有玩具的平均价格,并舍入到整数。
selectRound(AVG(mToyRate),0)averagerate
12.显示所有购买者和收货人的名、姓、地址和所在城市,要求显示结果中的重复记录。
selectvFirstName,vLastName,vAddress,cCity
fromREcipient
union
13.显示没有包装的所有玩具的名称。
(要求用In子查询实现)
selectvToyName
fromToys
wherecToyIdNOTIN(
selectcToyId
fromOrderDetail,Wrapper
whereOrderDetail.cWrapperId=Wrapper.cWrapperId);
14.显示已收货定单的定单号码以及下定单的时间。
(要求用Exists子查询实现)
selectcOrderNo,dOrderDate
fromOrders
whereexists(
select*
fromshipment
whereOrders.cOrderNo=cOrderNo
anddactualDeliveryDateisnotNULL
);
15.显示一份基于Orderdetail的报表,包括cOrderNo,cToyId和mToyCost,记录以cOrderNo升序排列,并计算每一笔定单的玩具总价值。
(提示:
使用运算符COMPUTEBY)。
selectcOrderNo,cToyId,mToyCost
fromOrderdetail
orderbycOrderNo
computesum(mToyCost)bycOrderNo;
16.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。
createtablePremiumToys
(
cToyIdchar(6)check(cToyId
like('
[0-9][0-9][0-9][0-9][0-9][0-9]'
))primarykey,
vToyNamevarchar(20)notnull,
vToyDescriptionvarchar(250),
cCategoryIdchar(3)referencesCategory(cCategoryId),
mToyRatemoneynotnull,
cBrandIdchar(3)referencesToyBrand(cBrandId),
imPhotoimage,
siToyQohsmallintnotnull,
siLowerAgesmallintnotnull,
siUpperAgesmallintnotnull,
siToyWeightsmallint,
vToyImgPathvarchar(50)null
)
insertintoPremiumToys
select*fromToys
wheremToyRate>
20
select*
fromPremiumToys
17.给id为‘000001’玩具的价格增加$1。
updateToys
setmToyRate=mToyRate+1
wherecToyId='
000001'
selectcToyId,vToyName,mToyRate
修改前:
修改后:
18.删除“Largo”牌的所有玩具。
delete
wherecBrandId=(selectcBrandId
fromToyBrand
wherecBrandName='
Largo'
)
wherecBrandId=(selectcBrandId
有外键约束存在,无法删除。
实验2:
存储过程与触发器
1.编写一段程序,将每种玩具的价格提高¥0.5,直到玩具的平均价格接近$24.5为止。
此外,任何玩具的最大价格不应超过$53。
createprocedureinprove_price
as
declare@averagemoney/*money是数据类型*/
select@average=AVG(mToyRate)fromToys
begin
while@average<
24.5
updateToys
setmToyRate=mToyRate+0.5
wheremToyRate<
53
select@average=AVG(mToyRate)fromToys
end
go
execinprove_price
select*fromToys
2.创建一个称为prcCharges的存储过程,它返回某个定单号的装运费用和包装费用。
createprocedureprcCharges
@cOrderNochar(6),
@mShippingChargesmoneyoutput,
@mGiftWrapChargesmoneyoutput
select@mShippingCharges=mShippingCharges,
@mGiftWrapCharges=mGiftWrapCharges
fromOrders
wherecOrderNo=@cOrderNo
declare@mShippingChargesmoney,
@mGiftWrapChargesmoney
execprcCharges'
000002'
@mShippingChargesoutput,
@mGiftWrapChargesoutput
print'
订单的装运费用为:
+convert(char(10),@mShippingCharges)
订单的包装费用为:
+convert(char(10),@mGiftWrapCharges)
3.创建一个称为prcHandlingCharges的过程,它接收定单号并显示经营费用。
PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。
提示:
经营费用=装运费+礼品包装费
createprocedureprcHandlingCharges
@cOrderNochar(6),
@RunningExpensesmoneyoutput
execprcCharges@cOrderNo,
@mShippingChargesoutput,
set@RunningExpenses=@mShippingCharges+@mGiftWrapCharges
declare@RunningExpensesmoney
execprcHandlingCharges'
@RunningExpensesoutput
000002号的经营费用为:
+convert(char(10),@RunningExpenses)
4.在OrderDetail上定义一个触发器,当向OrderDetail表中新增一条记录时,自动修改Toys表中玩具的库存数量(siToyQoh)。
createtriggercpponOrderDetailforinsert
as
declare@siQtysmallint
declare@cToyIdchar(6)
select@cToyId=inserted.cToyId,@siQty=inserted.siQty
fromInserted
setsiToyQoh=siToyQoh-@siQty
wherecToyId=@cToyId
return
5.在OrderDetail上定义一个触发器,如果购物者改变了定单的数量,玩具的成本也自动地改变。
Toycost=Quantity*ToyRate)
createtriggerCheckToyCostonOrderDetailforupdate
ifupdate(siQty)
begin
updateOrderDetail
setmToyCost=siQty*mToyRate
fromOrderDetail,Toys
end
selectcOrderNo,cToyId,siQty,mToyCost
wherecOrderNo='
setsiQty=siQty+10
--测试用例:
--对定单的数量修改前的OrderDetail:
--对定单的数量修改后的OrderDetail:
实验3:
视图、事务与游标
1.定义一个视图,包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。
createviewppt
(vFirstName,vLastName,cState,vToyName,mToyRate,siQty)
as
selectvFirstName,vLastName,cState,vToyName,mToyRate,siQty
fromOrderDetail,Recipient,Toys
WhereOrderDetail.cOrderNo=Recipient.cOrderNo
andOrderDetail.cToyId=Toys.cToyId
2.基于
(1)中定义的视图,查询显示所有California州的购买者的姓名和他们所订购玩具的名称及数量。
selectvFirstName,vLastName,vToyName,siQty
fromppt
wherecState='
3.名为prcGenOrder的存储过程产生存在于数据库中的定单号:
CREATEPROCEDUREprcGenOrder
@OrderNochar(6)OUTPUT
SELECT@OrderNo=Max(cOrderNo)FROMOrders
SELECT@OrderNo=
CASE
WHEN@OrderNo>
=0and@OrderNo<
9Then
‘00000’+Convert(char,@OrderNo+1)
=9and@OrderNo<
99Then
‘0000’+Convert(char,@OrderNo+1)
=99and@OrderNo<
999Then
‘000’+Convert(char,@OrderNo+1)
=999and@OrderNo<
9999Then
‘00’+Convert(char,@OrderNo+1)
=9999and@OrderNo<
99999Then
‘0’+Convert(char,@OrderNo+1)
=99999ThenConvert(char,@OrderNo+1)
END
RETURN
当购物者确认定单时,应该出现下面的步骤:
(1)用上面的过程产生定单号。
(2)定单号,当前日期,购物车ID,和购物者ID应该加到Orders表中。
(3)定单号,玩具ID和数量应加到OrderDetail表中。
(4)在OrderDetail表中更新玩具成本。
Toycost=Quantity*ToyRate).
将上述步骤定义为一个事务。
编写一个过程以购物车ID和购物者ID为参数,实现这个事务。
begintransactionOrder_Comfirmation
declare@cCartIdchar(6)
declare@ShopperIdchar(6)
declare@OrderNochar(6)
declare@mToyRatemoney
set@cCartId='
000009'
set@ShopperId='
000007'
execprcGenOrder@OrderNooutput
set@cToyId='
000008'
set@siQty=2
select@mToyRate=mToyRatefromToyswherecToyId=@cToyId
insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId)
values(@OrderNo,getdate(),@cCartId,@ShopperId)
insertintoOrderDetail(cOrderNo,cToyId,siQty)
values(@OrderNo,@cToyId,@siQty)
setmToyCost=siQty*(selectmToyRate
fromToys
wherecToyId=@cToyId)
wherecOrderNo=@OrderNo
commit
测试:
select*fromOrders
select*fromOrderDetail
4.编写一个程序显示每天的定单状态。
如果当天的定单值总合大于170,则显示“Highsales”,否则显示”Lowsales”。
报告中要求列出日期、定单状态和定单总价值。
(要求用游标实现)
declaresales_statusscroll
cursorfor
selectdistinctdOrderDate,sum(mTotalCost)Date_Total
fromOrders
groupbydOrderDate
Opensales_status
declare@dOrderdatedatetime
declare@Date_Totalmoney
dOrderdateDate_Total'
fetchfirstfromsales_statusinto@dOrderdate,@Date_Total
printconvert(char(10),@dOrderdate)+'
'
+convert(char(10),@Date_Total)
if@Date_Total>
170print'
Highsales'
elseprint'
Lowsales'
while@@fetch_status=0
fetchnextfromsales_statusinto@dOrderdate,@Date_Total
printconvert(char(10),@dOrderdate)+'
if@Date_Total>
170and@Date_Totalisnotnullprint'
elseprint'
deallocatesales_status
数据库表关系图
参考书籍
1.数据库——原理、编程与性能(第二版)PatrickQ'
Neil,Elì
zabethO'
Neil
机械工业出版社
2.《数据库系统概论》(第四版)王珊,萨师煊高等教育出版社
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 中南 大学 数据库 实验 报告