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

1
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
a
b
SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS
a, Prod AS p WHERE c.city ltgt a.city OR a.city ltgt
p.city
c
SELECT c.cid, a.aid, p.pid FROM Cust AS c, Ag AS
a, Prod AS p WHERE c.city ltgt a.city AND a.city ltgt
p.city AND c.city ltgt 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
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)
f
SELECT a.aid, b.aid FROM Ag AS a, Ag AS b WHERE
b.city a.city
g
2
SELECT c.cid, FROM Cust AS c WHERE NOT EXISTS
(SELECT FROM Ord AS o WHERE o.cidc.cid AND
a.aid a03)
h
SELECT c.cid, FROM Cust AS c WHERE c.discnt
(SELECT MAX(d.discnt) FROM Cust AS d)
i
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.cidc.cid AND
o.pidp.pid))
SELECT DISTINCT o.pid FROM Ord AS o WHERE
o.aida03 AND NOT EXISTS (SELECT FROM Ord
AS p WHERE p.pido.pid AND p.aida06
k
SELECT p.pid, p.pname FROM Prod AS p WHERE p.city
IN (SELECT a.city FROM Ag a, Ord o WHERE
a.aido.aid AND o.pidp.pid)
l
3
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.pido.pid AND
p.cityNewark
m
SELECT DISTINCT o.cid FROM Ord AS o, Ord AS
n WHERE o.cid n.cid AND o.pidp01 AND
n.pidp07
n
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.aida.aid))
4
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.aida.aid))
p
SELECT p.pid FROM Prod AS p WHERE NOT EXISTS
(SELECT FROM Ord AS o WHERE o.aida03 AND
o.cid NOT IN (SELECT n.cid FROM Ord AS n
WHERE n.pidp.pid))
r
SELECT DISTINCT o.aid FROM Ord AS o, Cust AS
c WHERE o.cidc.cid AND o.dollars gt 500 AND
c.cityKyoto
s
SELECT DISTINCT a.aname, c.cname FROM Ag AS a,
Ord AS o, Cust AS c WHERE a.aido.aid AND
o.cidc.cid
t
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