實(shí)驗(yàn)7SQL練習(xí)

1、實(shí)驗(yàn)7 SQL練習(xí)一、實(shí)驗(yàn)?zāi)康模壕毩?xí)SQL數(shù)據(jù)更新語(yǔ)句。二、實(shí)驗(yàn)內(nèi)容：1、 視圖2、 SQL數(shù)據(jù)更新語(yǔ)言練習(xí)三、表結(jié)構(gòu)描述3.1 Suppliers(供貨廠商)代碼描述數(shù)據(jù)類型長(zhǎng)度約束條件SupplierID供貨廠商編號(hào)INT4主碼CompanyName廠名VARCHAR40ContactName聯(lián)系人名VARCHAR30ContactTitle聯(lián)系人職位VARCHAR30Address地址VARCHAR60City城市名VARCHAR15Region地區(qū)VARCHAR15PostalCode郵政編碼VARCHAR10Country國(guó)家VARCHAR15Phone電話VARCHAR24Fax傳

2、真VARCHAR24HomePage主頁(yè)VARCHAR163.2 Region（地區(qū)）代碼描述數(shù)據(jù)類型長(zhǎng)度約束條件RegionID地區(qū)編號(hào)INT4主碼RegionDescription地區(qū)描述VARCHAR503.3 Products（產(chǎn)品）代碼描述數(shù)據(jù)類型長(zhǎng)度約束條件ProductID產(chǎn)品編號(hào)INT4主碼ProductName品名VARCHAR40SupplierID供貨廠商編號(hào)INT4CategoryID所屬種類號(hào)INT4QuantityPerUnit單位數(shù)量VARCHAR20UnitPrice單價(jià)FLOAT8UnitsInStock庫(kù)存INT2UnitsOnOrder定貨數(shù)INT2Reo

3、rderLevel修訂量INT2Discontinued是否進(jìn)行BIT13.4 Orders（定單）代碼描述數(shù)據(jù)類型長(zhǎng)度約束條件OrderID定單編號(hào)INT4主碼CustomerID顧客編號(hào)VARCHAR5EmployeeID職員編號(hào)INT4OrderDate定貨日期DATETIME8RequiredDate交貨日期DATETIME8ShippedDate載運(yùn)日期DATETIME8ShipVia經(jīng)由數(shù)INT4Freight運(yùn)費(fèi)FLOAT8ShipName船名VARCHAR40ShipAddress地址VARCHAR60ShipCity城市VARCHAR15ShipRegion地區(qū)VARCHAR

4、15PostalCode郵政編碼VARCHAR10ShipCountry國(guó)籍VARCHAR153.5 Order Details（定單詳細(xì)信息）代碼描述數(shù)據(jù)類型長(zhǎng)度約束條件OrderID定單編號(hào)INT4主碼ProductID產(chǎn)品編號(hào)INT4主碼UnitPrice單價(jià)FLOAT8Quantity數(shù)量INT2Discount折扣FLOAT43.6 Employees（職工）代碼描述數(shù)據(jù)類型長(zhǎng)度約束條件EmployeeID職工編號(hào)INT4主碼LastName姓VARCHAR20FirstName名VARCHAR10Title頭銜VARCHAR30TitleOfCourtesy性別VARCHAR25B

5、irthDate生日DATETIME8HireDate受聘日期DATETIME8Address地址VARCHAR60City城市VARCHAR15Region地區(qū)VARCHAR15PostalCode郵政編碼VARCHAR10Country國(guó)籍VARCHAR15HomePhone住宅電話VARCHAR24Extension分機(jī)號(hào)VARCHAR4Photo照片IMAGE16Notes備注VARCHAR16ReportsTo直接上級(jí)號(hào)INT4Photopath職工照片路徑VARCHAR2553.7 Customers（顧客）代碼描述數(shù)據(jù)類型長(zhǎng)度約束條件CustomerID顧客編號(hào)VARCHAR5主

6、碼CompanyName公司名VARCHAR40ContactName聯(lián)系人名VARCHAR30ContactTitle聯(lián)系人頭銜VARCHAR30Address地址VARCHAR60City城市VARCHAR15Region地區(qū)VARCHAR15PostalCode郵政編碼VARCHAR10Country國(guó)籍VARCHAR15Phone電話VARCHAR24Fax傳真VARCHAR243.8 OldSuppliers（供應(yīng)廠商備份表）結(jié)構(gòu)與Suppliers表相同四、實(shí)驗(yàn)步驟1、 運(yùn)行SQL SERVER服務(wù)管理器，確認(rèn)數(shù)據(jù)庫(kù)服務(wù)器開(kāi)始運(yùn)行。2、 運(yùn)行企業(yè)管理器，以圖示方式點(diǎn)擊“附加數(shù)據(jù)庫(kù)”

7、，恢復(fù)db目錄下的數(shù)據(jù)庫(kù)文件3、 打開(kāi)查詢分析器，選擇剛才恢復(fù)的數(shù)據(jù)庫(kù)exampleDB，輸入SQL指令，獲得運(yùn)行結(jié)果。4、 完成以下SQL數(shù)據(jù)更新語(yǔ)句(1) 創(chuàng)建視圖V_SupplyCount,顯示供應(yīng)商編號(hào)，以及該供應(yīng)商供應(yīng)的產(chǎn)品的品種數(shù)(非CategoryID)，該視圖包含兩個(gè)字段：SupplierID、ProductCount。create view V_SupplyCount(SupplierID,ProductCount)asselect SupplierID,count(ProductID)from productsgroup by SupplierID(2) 創(chuàng)建視圖V_Ord

8、erCount，顯示顧客編號(hào)，顧客所下訂單的產(chǎn)品總金額(金額=單價(jià)×數(shù)量×折扣)，該視圖包含兩個(gè)字段：CustomerID，TotalFee。create view V_OrderCount(CustomerID, TotalFee)asselect CustomerID,sum(UnitPrice*Quantity*Discount)from Orders,OrderDetails where Orders.OrderID=OrderDetails.OrderID group by CustomerID(3) 通過(guò)視圖V_SupplyCount，查出供應(yīng)產(chǎn)品的品種數(shù)最少的

9、供應(yīng)商編號(hào)和供應(yīng)商名稱。select SupplierID,CompanyNamefrom Supplierswhere SupplierID in(select SupplierIDfrom V_SupplyCountwhere ProductCount=(select min(ProductCount)from V_SupplyCount) (4) 通過(guò)視圖V_OrderCount，查出所下訂單的產(chǎn)品總金額最多的顧客編號(hào)和地址。select CustomerID,Address from Customers where CustomerID in(select CustomerID fro

10、m V_OrderCount where TotalFee=(select max(TotalFee) from V_OrderCount)(5) 再Region表中，添加一個(gè)新的地區(qū)：地區(qū)編號(hào)為5，地區(qū)描述為Centralinsertinto Regionvalues (5,Central)(6) 將Suppliers表中的所有國(guó)家為法國(guó)的供應(yīng)商信息內(nèi)容添加到OldSuppliers中create table OldSuppliers( SupplierID int NOT NULL primary key, CompanyName varchar(50) , ContactName var

11、char(50) , ContactTitle varchar(50) , Address varchar(60) , City varchar(50) , Region varchar(50) , PostalCode varchar(50) , Country varchar(50) , Phone varchar(50) , Fax varchar(50) , HomePage varchar(50) )insertinto OldSuppliersselect * from Suppliers where Country='France'(7) 將所有由職員Fuller

12、(LastName)簽訂的訂單運(yùn)費(fèi)降低10%update Ordersset Freight=Freight*0.9where EmployeeID=(select EmployeeID from Employees where LastName='Fuller')(8) 將所有美國(guó)顧客購(gòu)買的訂單單價(jià)調(diào)高20%update Orderdetailsset UnitPrice=UnitPrice*1.2where OrderID in (select distinct Orderdetails.OrderID from Orderdetails,Orders,Customers

13、where Orderdetails.OrderID=Orders.OrderID and Orders.CustomerID=Customers.CustomerID and Customers.Country='USA')(9) 將訂貨數(shù)量最多的產(chǎn)品的單價(jià)上調(diào)5元update Productsset UnitPrice=UnitPrice+5where ProductID in (select ProductID from Orderdetails group by ProductID having sum(Quantity) >=ALL (select sum(Quantity) from Orderdetails group by ProductID)(10) 刪除訂單個(gè)數(shù)最少的職員的信息delete from Employeeswhere EmployeeID in (select EmployeeID from Orders group by Employee