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}}
 

Latest revision as of 10: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'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense