Difference between revisions of "AdventureWorks"

From SQLZOO
Jump to: navigation, search
(Created page with "Graduated questions * Easy questions: 1..5 * Medium questions: 6..10 * [[AdventureWorks hard questions|H...")
 
 
(3 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
Graduated questions
 
Graduated questions
 +
{{AdventureWorks Assessment}}
  
−
* [[AdventureWorks easy questions|Easy questions: 1..5]]
+
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]
−
* [[AdventureWorks medium questions|Medium questions: 6..10]]
+
−
* [[AdventureWorks hard questions|Hard questions: 11..15]]
+
−
* [[AdventureWorks resit questions|Resit questions: 1..5]]
+
  
−
This data is based on Microsoft's AdventureWorksLT database. Access version: AdventureWorksLT.mdb
+
 
 +
{{AdventureWorks tables}}
 +
 
 +
==Sample queries==
 +
 
 +
<div class='qu'>
 +
<p class='imper'>Show the CompanyName for James D. Kramer</p>
 +
 
 +
<source lang='sql' class='def'>
 +
SELECT CompanyName
 +
  FROM CustomerAW
 +
WHERE FirstName='James'
 +
  AND MiddleName='D.'
 +
  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>
 +
</div>
 +
{{AdventureWorks Assessment}}

Latest revision as of 19:56, 9 August 2012

Graduated questions

AdventureWorks
Easy questions: 1..5
Medium questions: 6..10
Hard questions: 11..15
Resit questions: 1..5

This data is based on Microsoft's AdventureWorksLT database. Access version: AdventureWorksLT.mdb


CustomerAW(CustomerID, FirstName, MiddleName, LastName, CompanyName, EmailAddress)

CustomerAddress(CustomerID, AddressID, AddressType)

Address(AddressID, AddressLine1, AddressLine2, City, StateProvince, CountyRegion, PostalCode)

SalesOrderHeader(SalesOrderID, RevisionNumber, OrderDate, CustomerID, BillToAddressID, ShipToAddressID, ShipMethod, SubTotal, TaxAmt, Freight)

SalesOrderDetail(SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount)

ProductAW(ProductID, Name, Color, ListPrice, Size, Weight, ProductModelID, ProductCategoryID)

ProductModel(ProductModelID, Name)

ProductCategory(ProductCategoryID, ParentProductCategoryID Name)

ProductModelProductDescription(ProductModelID, ProductDescriptionID, Culture)

ProductDescription(ProductDescriptionID, Description)

[edit] Sample queries

Show the CompanyName for James D. Kramer

SELECT CompanyName
  FROM CustomerAW
 WHERE FirstName='James'
   AND MiddleName='D.'
   AND LastName='Kramer'
SELECT CompanyName
  FROM CustomerAW
 WHERE FirstName='James'
   AND MiddleName='D.'
   AND LastName='Kramer'

Show all the addresses listed for 'Modular Cycle Systems'

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

Show OrdeQty, the Name and the ListPrice of the order made by CustomerID 635

SELECT OrderQty,Name,ListPrice
  FROM SalesOrderHeader NATURAL JOIN SalesOrderDetail 
                        NATURAL JOIN ProductAW
WHERE CustomerID=635
SELECT OrderQty,Name,ListPrice
  FROM SalesOrderHeader NATURAL JOIN SalesOrderDetail 
                        NATURAL JOIN ProductAW
WHERE CustomerID=635
AdventureWorks
Easy questions: 1..5
Medium questions: 6..10
Hard questions: 11..15
Resit questions: 1..5
Retrieved from "http://sqlzoo.net/w/index.php?title=AdventureWorks&oldid=3383"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense