考研復試數(shù)據(jù)庫課件

1、1. 分別用關系代數(shù)、元組演算、SQL語句完成CAP數(shù)據(jù)庫的查詢。CAP數(shù)據(jù)庫有四 個關系(表)：數(shù)據(jù)庫系統(tǒng)基礎Customers(cid, cname, city,t), 客戶定義表，描述了客戶的唯一標識cid，客戶名稱cname，客戶所在的城市city，以及該產(chǎn)品時所可能給予的折扣tAgents(aid, aname, city, percent),商定義表，描述了商的唯一標識參考aid,商名稱aname,商所在的城市city，以及該商銷售產(chǎn)品時所可能給予的傭金/提成percent(以百分比形式表達)Products(, pname, city,ty, price), 庫存商品信息表, 描

2、述了商品標識, 商品名稱pname, 有該商品庫存的城市city, 在該城市有該商品的數(shù)量商品的批發(fā)價pricety,每Orders(ordno, month, cid, aid, qty, dollars), 訂單信息表，訂單唯一標識ordno, 訂單發(fā)生的月份month，商品的客戶cid ，所訂購的商品，經(jīng)由代理商aid，訂單總額dollars.(2) 找出所有價格在$0.50和$1.00之間的商品名字，包括邊界價格pname(price=0.50 price=1000(orders)對 ppname | pProducts ( ppri = 0.5 ppri = 1000 SELECT

3、ordno,FROM Orders WHERE dollars=1000SELECT pname FROM Products WHERE pri=0.5 ANDpri=1.01(4) 找出所有三月份接受的訂單的(ordno, aname)對，使用一次連接(3) 找出訂單價格低于$500的(ordno, cname)對，使用一次連接 ordno, aname(month=mar(AgentsOrders)(CustomersOrders)ordno, cname dollars500 oordno, aaname | (oOrders omonth = mar) (aAgents oaid =

4、aaid) oordno, ccname | (oOrders odollars 500) (cCustomers ocid = ccid)SELECT O.ordno,ame FROM Orders O, Customers CWHERE O.cid = C.cid AND O.dollars 500SELECT O.ordno, A.aname FROM Orders O, Agents AWHERE O.aid = A.aid AND O.month = mar(6) 找出所有位于New York的價格少于$500商，并且要求這些商所接受的單個訂單(5) 找出所有三月份接受的訂單的(or

5、dno, cname, aname)三元組，使用兩次連接 ordno, cname, aname(month=mar(CustomersOrdersAgents) 對嗎aid, aname, city, percent(city=New York dollars500(AgentsOrders)Order)(Agents)ordno, cname, aname( month=mar(Customersaid, aname, a | aAgents acity=New York oOrders aaid = oaid odollars 500 oordno, ccname , aaname |

6、oOrders cCustomers aAgents ocid = ccid oaid = aaid omonth = mar SELECT A.aid, A.aname, A.city,rcent FROM Agents A, Orders OSELECT O.ordno,ame, A.aname FROM Orders O, Customers C, Agents AWHERE O.aid = A.aid AND O.dollars 500 AND A.city=New YorkWHERE O.aid = A.aid AND O.cid = C.cid AND O.month = mar2

7、(8) 找出所有顧客、不涉及訂單信息 cid, aid,商和商品都在同一個城市的三元組(cid, aid,)，本題(7) 找出所有三月份定購的(位于)Duluth的商品的名字pname(month=mar city=Duluth(ProductsOrders)(CustomersAgentsProducts) ccid, aaid, p | cCustomers aAgents pProducts ccity = acity ccity = pcity ppname | pProducts pcity=Duluth oOrders o = pomonth = mar SELECT P.pnam

8、e FROM Products P, Orders OSELECT C.cid, A.aid, P.FROM Customers C, Agents A, Products PWHERE C.city = A.city AND C.city = P.cityWHERE P.= O.AND O.month = mar AND P.city = Duluth(9) 找出所有顧客、商和商品不都在同一個城市(可能有兩個在同一城市)的 三元組(cid, aid,) cid, aid,(Customers x Agents x Products)- (CustomersAgentsProducts)cid

9、, aid, ccid, aaid, p | cCustomers aAgents pProducts (ccity = acity ccity = pcity acity = pcity) SELECT C.cid, A.aid, P.FROM Customers C, Agents A, Products PWHERE C.city A.city OR C.city P.city OR A.city P.city3(10) 找出所有顧客、商和商品兩兩不在同一個城市的三元組 (cid, aid,)cid, aid, (Customers.cityAgents.city Customers.c

10、ityProducts.city Products.cityAgents.city (Customers x Agents x Products) ccid, aaid, p | cCustomers aAgents pProducts (ccity acity ccity pcity acity pcity) SELECT C.cid, A.aid, P.FROM Customers C, Agents A, Products PWHERE C.city A.city AND C.city P.city AND A.city P.city(11) 找出接受顧客c002訂單的商所在的城市(12

11、) 取出至少被一個在Dallas的顧客通過位于Tokyo的商定購的商品的名字pname(aid,(cid (city=Dallas (Customers)Orders)( ( (Orders)Agents)(aid (city=Tokyo (Agents)Products)cityaidcid=c002 ppname | pProducts ( (cCustomers ccity=Dallas) (aAgentsacity=Tokyo) (oOrders ) )(oaid =aaid ocid = ccid o=p) acity | aAgents o Orders (oaid =aaid o

12、cid = c002) SELECT P.pname FROM Customers C, Agents A, Products P, Orders O WHERE O.cid=C.cid AND O.aid=A.aid AND O.=P.AND C.city=Dallas AND A.city=TokyoSELECT A.city FROM Agents A, Orders OWHERE O.cid=c002 AND O.aid = A.aid(13) 取出曾經(jīng)收到Kyoto的顧客訂單的商所銷售的所有的商品的值。注意，本題和要求取出所有曾經(jīng)被Kyoto的顧客定購的商品不同 , aid(Orde

13、rs) (aid(city=kyoto (CustomersOrders) (Orders(aid(city=kyoto (Customers)Orders) p | pProducts (cCustomers ccity=kyoto)(oOrders )(o=p ocid=ccid)對嗎SELECTFROM Orders WHERE aid IN(SELECT aid FROM Orders O, CustomersWHERE O.cid = C.cid AND C.city=Kyoto)4(14) 列出所有在同一個城市的商的aid對 a1.aid, a2.aid( a1.city=a2.c

14、ity a1.aida2.aid( a1 (Agents) x a2 (Agents) ) ) a1aid, a2aid | a1Agents a2Agents a1city = a2city a1aid a2aid)SELECT A1.aid, A2.aid FROM Agents A1, Agents A2WHERE A1.city = A2.city AND A1.aidA2.aid(16) 找出折扣率最大和最小的顧客的cid值，注意：用關系代數(shù)提供的運算來(15) 列出沒有通過商a03定購過商品的顧客的cid值.下面是求最大的表達式表示本題比較cid(Customers) - cid(

15、aid=a03 (Orders)t(c1 (Customers) xc2 (Customers) (Customers) - c1.cid (t ccid | cCustomers (c1Customers) (c1t ct)SELECT C.cid FROM Customers C WHERE C.cid NOT IN(SELECT O.cid FROM Orders O WHERE aid = a03)SELECT cid FROM CustomersWHEREt = all (SELECTt FROM Customers)(17) 找出定購了所有商品的顧客的cid值(18) 找出通過商a

16、03而不通過商a06定購的商品的值(Orders) (Orders) - (Products)(Orders) )cid,aid=a03aid=a06 o1.aid=a03 o2.aida06 ( o1 (Orders) x o2 (Orders) ) ) o1. (o1. =o2.對嗎 ccid cCustomers (pProducts)(oOrders)(o ocid=ccid) =po | oOrders oaid = a03 (o1Orders o1=o) (o1aid = a06)SELECT C.cid FROM Customers CWHERE NOT EXISTS(SELEC

17、T * FROM Products P WHERE NOT EXISTS(SELECT * FROM Orders OSELECT O1.FROM Orders O1 WHERE O1.aid = a03 AND NOT EXISTS(SELECT * FROM Orders O2WHERE O.= P.AND O.cid = C.cid) )WHERE O2.= O1.AND O2.aid = a06)5(19) 取出商品的pname和值，要求這些商品所在的城市和某個銷售過該商品(20) 取出名字是以N開頭的商的aid和aname值，并且這些沒有銷售過的商所在的城市相同任何Newark生產(chǎn)的

18、商品 aid, aname(aname=N aid aname=N aaname=N AND A.anameOAND NOT EXISTS (SELECT * FROM Orders O, Products PWHERE O.= P.AND O.aid = A.aid AND P.city = A.cityWHERE P.city=Newark AND O.=P.AND O.aid=A.aid)(21) 取出同時定購了商品p01和p07的顧客的cid值(22) 取出銷售過所有曾被顧客c002定購過的商品的商的名字(Orders) =p07( o1 (Orders) x o2 (Orders)a

19、name( Agents(aid,(cid=c002 (Orders)o1.cid(o1.cid=o2.cid o1. =p01 o2.aaname | aAgents (o1Orders o1cid=c002)(o2Orders) (o2aid = aaid o1 = o2)ocid | oOrders o= p01 (o1Orders) (o1cid = ocid o1 = p07)SELECT A.aname FROM Agents A WHERE Not Exists(SELECT * FROM Orders O1 WHERE O1.cid=c002 AND Not Exists (S

20、ELECT * FROM Orders O2WHERE O2. =O1.AND O2.aid=A.aid)SELECT O1.cid FROM Orders O1, Orders O2WHERE O1.cid = O2.cid AND O1.= p01 AND O2.= p076(24) 取出所有的三元組(cid, aid,)，要求對應的顧客，商和商品中至少有兩者是位于同一座城市。本題的要求與8) 9) 10)相同嗎？(23) 取出銷售過所有曾被某些顧客定購過的商品的商的名字cid, aid,(Customers x Agents x Products) -( Agents(Orders)(O

21、rders)(anameaid,Customers.cityAgents.city Customers.cityProducts.city Products.cityAgents.city (Customers x Agents x Products)cid, aid,aaname | aAgents (o1Orders)(o2Orders) (o2aid = aaid o1ccid, aaid, p |cCustomers aAgents pProducts) = o2)(ccityacity ccitypcity acitypcity)SELECT A.aname FROM Agents

22、A WHERE Not Exists (SELECT * FROM Orders O1 WHERE Not Exists(SELECT * FROM Orders O2WHERE O2. =O1.AND O2.aid=A.aid)SELECT C.cid, A.aid, P.FROM Customers C, Agents A, Products PWHERE NOT EXISTS (SELECT * FROM Customers C1, Agents A1, Products P1 WHERE C1.cityA1.city AND C1.cityP1.city AND A1.cityP1.c

23、ity AND C.cid=C1.cidAND A.aid=A1.aid AND P.=P1.)(25) 取出所有曾在商a03處定購商品的顧客定購過的商品的值 (Orders( cid(aid=03 (Orders)o1 | o1Orders (o2Orders o2aid =a03) (o2cid = o1cid)SELECT O1.FROM Orders O1, Orders O2WHERE O1.cid = O2.cid AND O2.aid = a037(26) 取出接受過Kyoto的顧客一筆總額超過$500的訂單的商的aid值aid(city=Kyoto dollars500 (Cu

24、stomerOrders)oaid | oOrders odollars 500 (cCustomers ccity =kyoto) (ccid = ocid)SELECT O.cid FROM Orders O, Customers CWHERE O.cid = C.cid AND C.city = Kyoto AND O.dollars500(27) 給出所有的(cname, aname)對，要求對應的顧客曾經(jīng)在對應的定購過商品商處(28) 取出只從一家商定購過商品的顧客的cid值cid (Orders) o1.cid(o1.cid=o2.cid o1.aido2.aid( o1 (Ord

25、ers) x o2 (Orders) ) )(Orders(Customers)(Agents)cname, anamecid, cnameaid, anametcname, aaname | cCustomers oOrders aAgents (ocid = ccid oaid=aaid)o1cid | o1Orders (o2Orders) (o1cid = o2cid oaidaaid)SELECTame, A.aname FROM Customers C, Agents A, Orders OWHERE O.cid = C.cid AND O.aid = A.aidSELECT O1

26、.cid FROM Orders O1 WHERE NOT EXISTS (SELECT * FROM Orders O2WHERE O2.cid = O1.cid AND O2.aid O1.aid)(29) 為每個有訂貨的商列出他所訂購的每樣產(chǎn)品的值以及所有通過該商訂購該產(chǎn)品的顧客們所定購的總量 僅用SQL語句SELECT aid, FROM OrdersGROUP BY aid, sum(qty)8(30) 求出沒有為任何住在Duluth的顧客訂購任何在Dallas生產(chǎn)的產(chǎn)品的商aid值aid(Agents)- aid(Customers.city Duluth Products.cit

27、y Dallas(CustomersProductsOrders) aaid aAgents (cCustomersccity=Duluth) (pProductspcity=Dallas) (o Orders)(oaid =aaid ocid = ccid o = p ) SELECT A.aid FROM Agents AWHERE Not Exists(SELECT * FROM Customers C, Products P, Orders OWHERE O.aid=a.aid AND O.cid=C.cid AND O. =P.AND C.city=Duluth AND P.city

28、=Dallas)(31) 求出為住在Duluth或Kyoto的所有顧客訂購了至少一樣公品的(31) 求出為住在Duluth或Kyoto的所有顧客訂購了至少一樣公品的商的aid值商的aid值(Orders) (Orders) aid(aid,aid(aid,(city=duluth(Customers) (city= kyoto(Customers)SELECT O1.aid FROM Orders O1 WHERE Not Exists(SELECT * FROM Customers C WHERE C.city=Duluth AND Not Exists (SELECT * FROM Ord

29、ers O2 WHERE O2.cid=C.cid AND O2.aid=O1.aid AND O2. =O1. )Orders)Orders) oaid (oOrders (cCustomersccity=Duluth)(o1Orders)(o1cid = ccid o1 (oOrders (cCustomersccity=Duluth)(o1Orders)(o1cid = ccid o1UNION = o)SELECT O1.aid FROM Orders O1 WHERE Not Exists(SELECT * FROM Customers C WHERE C.city=Kyoto AN

30、D Not Exists (SELECT * FROM Orders O2 WHERE O2.cid=C.cid AND O2.aid=O1.aid AND O2. =O1. ) = o) (32) 求出只通過商a03或a05訂貨的顧客的cid值(33) 求出被所有住在Dallas的顧客都訂購了的產(chǎn)品的值 cid(Orders) cid(city Dallas(Customers) p pProducts (cCustomers ccity=Dallas)(oOrders)(o =p ocid=ccid) (Orders) - (Orders)cid aid a03 aid a05cid ai

31、da03 aida05cid(aid a03 aid a05(Orders) O1.cid(O1.aida03 O2.aida05 O2.cid O1.cid( o2 ccid cCustomers (o1O(rOdredrsers)o1coid2 (O=crdceidrs) (o1aid=a03o1aid=a05) (o2Orders o2cid=ccid) (o2aida03o2aida05) SELECT O1.cid FROM Orders O1 WHERE O1.aid in (a03,a05) EXCEPTSELECT O2.cid FROM Orders O2 WHERE O2.aid not in (a03,a05)SELECT O1.FROM Orders O1 WHERE Not Exists(SELECT * FROM Customers C WHERE C.city=Dallas AND Not Exists (SELECT * FROM Orders O2WHERE O2. =O1.AND O2.cid=C.c