Difference between pages "AdventureWorks" and "Congestion Charging"

From SQLZOO
(Difference between pages)
Jump to: navigation, search
 
 
Line 1: Line 1:
Graduated questions
+
<h1>Congestion charging database</h1>
{{AdventureWorks Assessment}}
+
<p>Graduated questions</p>
 
+
<ul>
This data is based on [http://www.codeplex.com/MSFTDBProdSamples/Wiki/View.aspx?title=%20AdventureWorksLTDiagram&referringTitle=AWSchemaDiag Microsoft's AdventureWorksLT] database. Access version: [http://sqlzoo.net/~andrew/big/AdventureWorksLT.mdb AdventureWorksLT.mdb]
+
<li>[[Congestion Easy |Easy questions: 1..5]]</li>
 
+
<li>[[Congestion Medium |Medium questions: 6..10]]</li>
 
+
<li>[[Congestion Hard |Hard questions: 11..15]]</li>
{{AdventureWorks tables}}
+
</ul>
 
+
<p>ER diagram for the Congestion charging database:</p>
==Sample queries==
+
[[Image:CongestionChargeER.gif]]
 +
<p>camera(id, perim)</p>
 +
<p>keeper(id, name, address)</p>
 +
<p>vehicle(id, keeper)</p>
 +
<p>image(camera, whn, reg)</p>
 +
<p>permit(reg, sDate, chargeType)</p>
 +
<h2>Sample query</h2>
  
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>Show the CompanyName for James D. Kramer</p>
+
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.
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT CompanyName
+
SELECT vehicle.id
  FROM CustomerAW
+
   FROM vehicle JOIN keeper
WHERE FirstName='James'
+
        ON vehicle.keeper = keeper.id
  AND MiddleName='D.'
+
  WHERE keeper.name = 'Strenuous, Sam'  
  AND LastName='Kramer'
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT CompanyName
 
  FROM CustomerAW
 
WHERE FirstName='James'
 
  AND MiddleName='D.'
 
  AND LastName='Kramer'
 
</source>
 
</div>
 
 
 
<div class='qu'>
 
<p class='imper'>Show all the addresses listed for 'Modular Cycle Systems'</p>
 
 
 
<source lang='sql' class='def'>
 
SELECT CompanyName,AddressType,AddressLine1
 
   FROM CustomerAW JOIN CustomerAddress
 
    ON (CustomerAW.CustomerID=CustomerAddress.CustomerID)
 
                  JOIN Address
 
    ON (CustomerAddress.AddressID=Address.AddressID)
 
WHERE CompanyName='Modular Cycle Systems'
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT CompanyName,AddressType,AddressLine1
 
  FROM CustomerAW JOIN CustomerAddress
 
    ON (CustomerAW.CustomerID=CustomerAddress.CustomerID)
 
                  JOIN Address
 
    ON (CustomerAddress.AddressID=Address.AddressID)
 
WHERE CompanyName='Modular Cycle Systems'
 
</source>
 
</div>
 
 
 
<div class='qu'>
 
<p class='imper'>Show OrdeQty, the Name and the ListPrice of the order made by
 
CustomerID 635</p>
 
 
 
<source lang='sql' class='def'>
 
SELECT OrderQty,Name,ListPrice
 
  FROM SalesOrderHeader NATURAL JOIN SalesOrderDetail
 
                        NATURAL JOIN ProductAW
 
WHERE CustomerID=635
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT OrderQty,Name,ListPrice
 
  FROM SalesOrderHeader NATURAL JOIN SalesOrderDetail
 
                        NATURAL JOIN ProductAW
 
WHERE CustomerID=635
 
 
</source>
 
</source>
 
</div>
 
</div>
{{AdventureWorks Assessment}}
 

Revision as of 09:32, 5 May 2013

Congestion charging database

Graduated questions

ER diagram for the Congestion charging database:

CongestionChargeER.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'