http://sqlzoo.net/w/index.php?title=Old_Congestion&feed=atom&action=history
Old Congestion - Revision history
2013-06-20T03:19:37Z
Revision history for this page on the wiki
MediaWiki 1.20.4
http://sqlzoo.net/w/index.php?title=Old_Congestion&diff=1942&oldid=prev
Connor: Created page with "<h1>Congestion charging database</h1> <p>Graduated questions</p> <ul> <li>Easy questions: 1..5</li> <li>Medium questions: 6..10..."
2012-07-19T14:47:49Z
<p>Created page with "<h1>Congestion charging database</h1> <p>Graduated questions</p> <ul> <li><a href="/wiki/Congestion_Easy2" title="Congestion Easy2">Easy questions: 1..5</a></li> <li><a href="/wiki/Congestion_Medium2" title="Congestion Medium2">Medium questions: 6..10</a>..."</p>
<p><b>New page</b></p><div><h1>Congestion charging database</h1><br />
<p>Graduated questions</p><br />
<ul><br />
<li>[[Congestion Easy2 |Easy questions: 1..5]]</li><br />
<li>[[Congestion Medium2 |Medium questions: 6..10]]</li><br />
<li>[[Congestion Hard2 |Hard questions: 11..15]]</li><br />
</ul><br />
<p>ER diagram for the Congestion charging database:</p><br />
[[Image:/pics/er5.gif]]<br />
<p>camera(id, perim)</p><br />
<p>keeper(id, name, address)</p><br />
<p>vehicle(id, keeper)</p><br />
<p>image(camera, whn, reg)</p><br />
<p>permit(reg, sDate, chargeType)</p><br />
<h2>Sample query</h2><br />
<br />
<div class='qu'><br />
List the vehicles for which 'Strenuous, Sam' is the registered keeper.<br />
The link between Keepers and Vehicles is via the foreign key specified in the CREATE TABLE vehicle statement. Note the line:<br />
,FOREIGN KEY(keeper) REFERENCES keeper(id)<br />
This will be the basis of our join condition.<br />
<source lang='sql' class='def'><br />
SELECT vehicle.id<br />
FROM vehicle JOIN keeper<br />
ON vehicle.keeper = keeper.id<br />
WHERE keeper.name = 'Strenuous, Sam' <br />
</source><br />
</div></div>
Connor