Difference between revisions of "Southwind"
From SQLZOO
(Created page with "Graduated questions * Easy questions: 1..5 * Medium questions: 6..10 * Hard questions: 11..15") |
|||
| (One intermediate revision by one user not shown) | |||
| Line 1: | Line 1: | ||
| − | Graduated questions | + | <h1>Southwind database</h1> |
| + | <p>Graduated question</p> | ||
| + | <ul> | ||
| + | <li>[[Southwind easy |Easy questions 1..5]]</li> | ||
| + | <li>[[Southwind medium |Medium questions 6..10]]</li> | ||
| + | <li>[[Southwind hard |Hard questions 11..15]]</li> | ||
| + | </ul> | ||
| − | + | [[southwind.mdb]] | |
| − | + | ||
| − | * | + | ER diagram for southwind databse: |
| + | [[Image]] | ||
| + | <p>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)</p> | ||
| + | |||
| + | <div class='qu'> | ||
| + | The table '''tpurcd''' gives details of all products ordered. This table gives details of all purchase orders. | ||
| + | <source lang='sql' class='def'> | ||
| + | SELECT * | ||
| + | FROM tcust | ||
| + | JOIN tpurcd ON (tcust.code=tpurcd.cust) | ||
| + | JOIN tprod ON (tpurcd.prod=tprod.code) | ||
| + | </source> | ||
| + | </div> | ||
| + | |||
| + | <div class='qu'> | ||
| + | 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). | ||
| + | <source lang='sql' class='def'> | ||
| + | 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 | ||
| + | </source> | ||
| + | </div> | ||
Latest revision as of 14:40, 19 July 2012
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
