【文章內(nèi)容簡(jiǎn)介】 39。c0739。,39。00239。,20101211)insert into orders values(39。o1439。,39。c0739。,39。00539。,20101119)insert into orders values(39。o1539。,39。c0839。,39。00439。,20100929)insert into orders values(39。o1639。,39。c0839。,39。00639。,20100819)goinsert into orderDetails values(39。o0139。,39。f0300139。,18,)insert into orderDetails values(39。o0239。,39。f0200239。,51,)insert into orderDetails values(39。o0339。,39。f0100439。,25,)insert into orderDetails values(39。o0439。,39。f0200139。,20,)insert into orderDetails values(39。o0539。,39。f0100539。,56,)insert into orderDetails values(39。o0639。,39。f0400139。,23,)insert into orderDetails values(39。o0739。,39。f0100639。,11,)insert into orderDetails values(39。o0839。,39。f0500439。,90,)insert into orderDetails values(39。o0939。,39。f0100139。,34,)insert into orderDetails values(39。o1039。,39。f0500239。,45,)insert into orderDetails values(39。o1139。,39。f0300339。,41,)insert into orderDetails values(39。o1239。,39。f0500339。,53,)insert into orderDetails values(39。o1339。,39。f0100339。,21,)insert into orderDetails values(39。o1439。,39。f0500139。,54,)insert into orderDetails values(39。o1539。,39。f0100239。,61,)insert into orderDetails values(39。o1639。,39。f0300239。,74,)goinsert into storehouse values(39。s139。,39。中山路46號(hào)39。)goinsert into storetable values(39。s139。,39。f0100139。,400)insert into storetable values(39。s139。,39。f0100239。,400)insert into storetable values(39。s139。,39。f0100339。,400)insert into storetable values(39。s139。,39。f0100439。,400)insert into storetable values(39。s139。,39。f0100539。,400)insert into storetable values(39。s139。,39。f0100639。,400)insert into storetable values(39。s139。,39。f0200139。,400)insert into storetable values(39。s139。,39。f0200239。,400)insert into storetable values(39。s139。,39。f0300139。,400)insert into storetable values(39。s139。,39。f0300239。,400)insert into storetable values(39。s139。,39。f0300339。,400)insert into storetable values(39。s139。,39。f0400139。,400)insert into storetable values(39。s139。,39。f0500139。,400)insert into storetable values(39。s139。,39。f0500239。,400)insert into storetable values(39。s139。,39。f0500339。,400)insert into storetable values(39。s139。,39。f0500439。,400)goinsert into TotalSales values(39。00139。,)insert into TotalSales values(39。00239。,)insert into TotalSales values(39。00339。,)insert into TotalSales values(39。00439。,)insert into TotalSales values(39。00539。,)insert into TotalSales values(39。00639。,)go（2）創(chuàng)建外鍵約束 alter table orders add constraint fk_orders_Salers foreign key (saleID) references Salers (saleID),constraint fk_orders_Customers foreign key (customerID) references Customers (customerID) onupdate cascadegoalter table orderDetails add constraint fk_orderDetails_drink foreign key (drinkID) referencesdrink (drinkID) on update cascadego（3）輸出表中所有的信息查詢用戶信息select * from Usersgo查詢銷售員信息select * from Salersgo查詢客戶信息select * from Customersgo查詢飲料信息select * from drinkgo查詢訂單信息select * from ordersgo查詢訂購(gòu)表信息update orderDetails set totalprice=scount from orderDetails,(select top 100 percent orderID,price*quantity scount from drink,orderDetails where = order by orderID)C where =select * from orderDetailsgo查詢倉(cāng)庫(kù)信息select * from storehousego查詢庫(kù)存表信息select * from storetablego查詢銷售員總銷售額信息update TotalSales set totalprice=ccount from TotalSales,(select saleID,sum(price*quantity) ccountfrom orderDetails,drink,orders where = and = group by saleID)Cwhere =select * from TotalSales order by totalprice descgo（4）建立查詢統(tǒng)計(jì)視圖統(tǒng)計(jì)所有訂單的總金額create view sum1asselect sum(totalprice) 所有訂單總金額 from orderDetailsgo按照訂單編號(hào)的查詢統(tǒng)計(jì)總金額create view sum2asselect orderID,sum(totalprice) 按照訂單編號(hào)統(tǒng)計(jì)的總金額 from orderDetails group by orderIDgo按照銷售員的查詢統(tǒng)計(jì)總金額create view sum3asselect saleID,totalprice 按照銷售員的查詢統(tǒng)計(jì)總金額 from TotalSalesgo按照客戶的查詢統(tǒng)計(jì)總金額create view sum4asselect customerID,sum(totalprice) 按照客戶的查詢統(tǒng)計(jì)總金額 from orderDetails,orders where = group by customerIDgo按照產(chǎn)品的查詢統(tǒng)計(jì)總金額create view sum5asselect drinkID,sum(totalprice) 按照產(chǎn)品的查詢統(tǒng)計(jì)總金額 from orderDetails group by drinkIDgo按照產(chǎn)品分類的查詢統(tǒng)計(jì)總金額create view sum6asselect categoryID,sum(totalprice) 按照產(chǎn)品分類的查詢統(tǒng)計(jì)總金額 from orderDetails,drink where = group by categoryIDgo（5）建立存儲(chǔ)過(guò)程插入、刪除、修改數(shù)據(jù)向Salers表插入數(shù)據(jù)create proc insertSalers @saleID char(3),@salename char(8),@sex char(2),@birthday datetime,@hiredate datetime,@address char(60),@telephone char(13)asdelete from Salers where saleID=@saleID insert into Salers values(@saleID,@salename,@sex,@birthday,@hiredate,@address,@telephone)go向Customers表插入數(shù)據(jù)create proc insertCustomers@customerID char(3),@panyName char(60),@connectName char(8),@address char(40),@zipcode char(14),@telephone char(13)asdelete from Customers where customerID=@customerID insert into Customer values(@customerID,@panyName,@connectName,@address,@zipcode,@telephone)go向drink表插入數(shù)據(jù)create proc insertdrink@drinkID char(6),@drinkname varchar(40),@categor