實驗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 from v_ordercou

10、nt 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 varchar(50) , c

11、ontacttitle 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(lastname)簽訂的訂單運費降低10%

12、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 where orderdetails.orderid=order

13、s.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 employe