Difference between revisions of "Congestion Charging"

From SQLZOO
Jump to: navigation, search
(Created page with "<h1>Congestion charging database</h1> <p>Graduated questions</p> <ul> <li>[[Congestion Easy |Easy questions: 1..5</li> <li>[[Congestion Medium |Medium questions: 6..10</li> ...")
 
Line 2: Line 2:
 
<p>Graduated questions</p>
 
<p>Graduated questions</p>
 
<ul>
 
<ul>
  <li>[[Congestion Easy |Easy questions: 1..5</li>
+
  <li>[[Congestion Easy |Easy questions: 1..5]]</li>
  <li>[[Congestion Medium |Medium questions: 6..10</li>
+
  <li>[[Congestion Medium |Medium questions: 6..10]]</li>
  <li>[[Congestion Hard |Hard questions: 11..15</li>
+
  <li>[[Congestion Hard |Hard questions: 11..15]]</li>
 
</ul>
 
</ul>
 
<p>ER diagram for the Congestion charging database:</p>
 
<p>ER diagram for the Congestion charging database:</p>

Revision as of 12:49, 19 July 2012

Congestion charging database

Graduated questions

ER diagram for the Congestion charging database:

File:/pics/er5.gif

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'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense