1 / 6

SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS a, Prod AS p

a:. SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS a, Prod AS p WHERE c.city = a.city AND a.city = p.city. b:. SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS a, Prod AS p WHERE c.city <> a.city OR a.city <> p.city. c:. SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS a, Prod AS p

verna
Télécharger la présentation

SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS a, Prod AS p

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. a: SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS a, Prod AS p WHERE c.city = a.city AND a.city = p.city b: SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS a, Prod AS p WHERE c.city <> a.city OR a.city <> p.city c: SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS a, Prod AS p WHERE c.city <> a.city AND a.city <> p.city AND c.city <> p.city d: SELECT DISTINCT a.city FROM Ag AS a, Ord AS o WHERE o.aid = a.aid AND o.cid = ‘c0002’ e: SELECT DISTINCT p.pname FROM Pro AS p, Or AS o, Ag AS a, Cu as c WHERE o.aid = a.aid AND o.cid = c.cid AND o.pid = p.pid AND c.city = ‘Dallas’ AND a.city = ‘Tokyo’ f: SELECT DISTINCT o.pid FROM Ord AS o WHERE o.aid IN (SELECT n.aid FROM Ord AS n, Cust AS c WHERE o.cid = c.cid AND c.city = ‘Kyoto’) g: SELECT a.aid, b.aid FROM Ag AS a, Ag AS b WHERE b.city = a.city

  2. h: SELECT c.cid, FROM Cust AS c WHERE NOT EXISTS (SELECT * FROM Ord AS o WHERE o.cid=c.cid AND a.aid = ‘a03’) i: SELECT c.cid, FROM Cust AS c WHERE c.discnt = (SELECT MAX(d.discnt) FROM Cust AS d) j: SELECT c.cid FROM Cust AS c WHERE NOT EXISTS (SELECT * FROM Prod AS p WHERE NOT EXISTS (SELECT * FROM Ord AS o WHERE o.cid=c.cid AND o.pid=p.pid)) k: SELECT DISTINCT o.pid FROM Ord AS o WHERE o.aid=‘a03’ AND NOT EXISTS (SELECT * FROM Ord AS p WHERE p.pid=o.pid AND p.aid=‘a06’ l: SELECT p.pid, p.pname FROM Prod AS p WHERE p.city IN (SELECT a.city FROM Ag a, Ord o WHERE a.aid=o.aid AND o.pid=p.pid)

  3. m: SELECT a.aid, a.aname FROM Ag AS a WHERE a.aname LIKE ‘N%” AND a.aid NOT IN (SELECT o.aid FROM Ord AS o, Prod AS p WHERE p.pid=o.pid AND p.city=‘Newark’ n: SELECT DISTINCT o.cid FROM Ord AS o, Ord AS n WHERE o.cid= n.cid AND o.pid=‘p01’ AND n.pid=‘p07’ o: SELECT DISTINCT a.aname FROM Ag AS a WHERE NOT EXISTS (SELECT * FROM FROM Ord AS o WHERE o.cid =‘c002’) AND o.pid NOT IN (SELECT n.pid FROM Ord AS n WHERE n.aid=a.aid))

  4. p: SELECT DISTINCT a.aname FROM Ag AS a WHERE NOT EXISTS (SELECT * FROM Ord AS o) AND o.pid NOT IN (SELECT n.pid FROM Ord AS n WHERE n.aid=a.aid)) r: SELECT p.pid FROM Prod AS p WHERE NOT EXISTS (SELECT * FROM Ord AS o WHERE o.aid=‘a03’ AND o.cid NOT IN (SELECT n.cid FROM Ord AS n WHERE n.pid=p.pid)) s: SELECT DISTINCT o.aid FROM Ord AS o, Cust AS c WHERE o.cid=c.cid AND o.dollars > 500 AND c.city=‘Kyoto’ t: SELECT DISTINCT a.aname, c.cname FROM Ag AS a, Ord AS o, Cust AS c WHERE a.aid=o.aid AND o.cid=c.cid

  5. Sales company DB • Ord (onum, anum, cname,amt,date) • Cust (cname,city) • Item (anum,name,price) Queries: Give the item names with at least 100 ordered in 2002 Give the item numbers and names for which the money spent on orders in 2002 was less than E.10000 For customers in Eindhoven for which the money spent on items ordered in May 2003 is less than E.100, give the customer name and the money they thus spent

  6. Huiswerk: a: Geef klantnaam en totaalbedrag per klant besteed in Mei 2003 b: Geef klantnaam en aantal verschillende artikelen van die klant gekocht in Mei 2003 c: Geef klantnaam van klanten die in Mei 2003 in totaal meer dan 50 artikelen gekocht hebben en dit aantal

More Related