Difference between revisions of "AdventureWorks"

From SQLZOO
Jump to: navigation, search
 
(2 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}}
 
{{AdventureWorks tables}}
Line 72: Line 69:
 
</source>
 
</source>
 
</div>
 
</div>
 +
{{AdventureWorks Assessment}}

Latest revision as of 20:56, 9 August 2012

Graduated questions

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

Variants
Actions
Reference
Toolbox
Google AdSense