實驗7SQL練習

1、實驗7 SQL練習一、實驗目的：練習SQL數據更新語句。二、實驗內容：1、 視圖2、 SQL數據更新語言練習三、表結構描述3.1 Suppliers(供貨廠商)代碼描述數據類型長度約束條件SupplierID供貨廠商編號INT4主碼CompanyName廠名VARCHAR40ContactName聯系人名VARCHAR30ContactTitle聯系人職位VARCHAR30Address地址VARCHAR60City城市名VARCHAR15Region地區(qū)VARCHAR15PostalCode郵政編碼VARCHAR10Country國家VARCHAR15Phone電話VARCHAR24Fax傳

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

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

4、15PostalCode郵政編碼VARCHAR10ShipCountry國籍VARCHAR153.5 Order Details（定單詳細信息）代碼描述數據類型長度約束條件OrderID定單編號INT4主碼ProductID產品編號INT4主碼UnitPrice單價FLOAT8Quantity數量INT2Discount折扣FLOAT43.6 Employees（職工）代碼描述數據類型長度約束條件EmployeeID職工編號INT4主碼LastName姓VARCHAR20FirstName名VARCHAR10Title頭銜VARCHAR30TitleOfCourtesy性別VARCHAR25B

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

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

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

8、erCount，顯示顧客編號，顧客所下訂單的產品總金額(金額=單價×數量×折扣)，該視圖包含兩個字段：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) 通過視圖V_SupplyCount，查出供應產品的品種數最少的

9、供應商編號和供應商名稱。select SupplierID,CompanyNamefrom Supplierswhere SupplierID in(select SupplierIDfrom V_SupplyCountwhere ProductCount=(select min(ProductCount)from V_SupplyCount) (4) 通過視圖V_OrderCount，查出所下訂單的產品總金額最多的顧客編號和地址。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表中，添加一個新的地區(qū)：地區(qū)編號為5，地區(qū)描述為Centralinsertinto Regionvalues (5,Central)(6) 將Suppliers表中的所有國家為法國的供應商信息內容添加到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)簽訂的訂單運費降低10%update Ordersset Freight=Freight*0.9where EmployeeID=(select EmployeeID from Employees where LastName='Fuller')(8) 將所有美國顧客購買的訂單單價調高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) 將訂貨數量最多的產品的單價上調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) 刪除訂單個數最少的職員的信息delete from Employeeswhere EmployeeID in (select EmployeeID from Orders group by Employee