Difference between revisions of "Congestion Charging"

Jump to navigation Jump to search
(2 intermediate revisions by the same user not shown)
Line 7: Line 7:
<p>ER diagram for the Congestion charging database:</p>
<p>ER diagram for the Congestion charging database:</p>
<p>camera(id, perim)</p>
<p>camera(<b>id</b>, perim)</p>
<p>keeper(id, name, address)</p>
<p>keeper(<b>id</b>, name, address)</p>
<p>vehicle(id, keeper)</p>
<p>vehicle(<b>id</b>, <i>keeper</i>)</p>
<p>image(camera, whn, reg)</p>
<p>image(<b><i>camera</i>, whn</b>, <i>reg</i>)</p>
<p>permit(reg, sDate, chargeType)</p>
<p>permit(<b><i>reg</i>, sDate</b>, chargeType)</p>
<h2>Sample query</h2>
<h2>Sample query</h2>

Latest revision as of 21:31, 30 August 2016

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'