Difference between revisions of "Congestion Charging"
From SQLZOO
| Line 8: | Line 8: | ||
<p>ER diagram for the Congestion charging database:</p> | <p>ER diagram for the Congestion charging database:</p> | ||
[[Image:/pics/er5.gif]] | [[Image:/pics/er5.gif]] | ||
| + | |||
| + | [[File:/pics/er5.gif]] | ||
<p>camera(id, perim)</p> | <p>camera(id, perim)</p> | ||
<p>keeper(id, name, address)</p> | <p>keeper(id, name, address)</p> | ||
Revision as of 10:55, 12 December 2012
Congestion charging database
Graduated questions
ER diagram for the Congestion charging database:
camera(id, perim)
keeper(id, name, address)
vehicle(id, keeper)
image(camera, whn, reg)
permit(reg, sDate, chargeType)
Sample query
List the vehicles for which 'Strenuous, Sam' is the registered keeper. The link between Keepers and Vehicles is via the foreign key specified in the CREATE TABLE vehicle statement. Note the line:
,FOREIGN KEY(keeper) REFERENCES keeper(id)
This will be the basis of our join condition.
SELECT vehicle.id FROM vehicle JOIN keeper ON vehicle.keeper = keeper.id WHERE keeper.name = 'Strenuous, Sam'
