考研复试数据库课件

1、1. 分别用关系代数、元组演算、SQL语句完成CAP数据库的查询。CAP数据库有四 个关系(表)：数据库系统基础Customers(cid, cname, city,t), 客户定义表，描述了客户的唯一标识cid，客户名称cname，客户所在的城市city，以及该产品时所可能给予的折扣tAgents(aid, aname, city, percent),商定义表，描述了商的唯一标识参考aid,商名称aname,商所在的城市city，以及该商销售产品时所可能给予的佣金/提成percent(以百分比形式表达)Products(, pname, city,ty, price), 库存商品信息表, 描

2、述了商品标识, 商品名称pname, 有该商品库存的城市city, 在该城市有该商品的数量商品的批发价pricety,每Orders(ordno, month, cid, aid, qty, dollars), 订单信息表，订单唯一标识ordno, 订单发生的月份month，商品的客户cid ，所订购的商品，经由代理商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) 取出曾经收到Kyoto的顾客订单的商所销售的所有的商品的值。注意，本题和要求取出所有曾经被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值，注意：用关系代数提供的运算来(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生产的

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)对，要求对应的顾客曾经在对应的定购过商品商处(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) 为每个有订货的商列出他所订购的每样产品的值以及所有通过该商订购该产品的顾客们所定购的总量 仅用SQL语句SELECT aid, FROM OrdersGROUP BY aid, sum(qty)8(30) 求出没有为任何住在Duluth的顾客订购任何在Dallas生产的产品的商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的顾客都订购了的产品的值 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