Southwind
Southwind database
Graduated question
ER diagram for southwind databse: Image
tprod(code, dscr, pric) tcust (code, firm, addr) tpurc (cust, recv) tpurcd (cust, recv, prod, qnty) tship (cust,recv,addr,shpd) tshipd (cust, recv, shpd, prod, qnty) tsupl (code, addr) tdlvr (supl, recv) tdlvrd (supl, recv, prod, qnty) tretn (cust, recv, prod, qnty, expl) tstck (chck, prod, qnty)
The table tpurcd gives details of all products ordered. This table gives details of all purchase orders.
SELECT *
FROM tcust
JOIN tpurcd ON (tcust.code=tpurcd.cust)
JOIN tprod ON (tpurcd.prod=tprod.code)
Some of the tables are joined by a composite foreign key. For example to link shipment details to a shipment record we must join on (cust,shpd).
SELECT addr, tship.shpd, prod
FROM tship JOIN tshipd
ON(tshipd.cust=tship.cust AND
tshipd.shpd=tship.shpd)
WHERE tship.addr IS NOT NULL