Difference between revisions of "AdventureWorks"
| Line 6: | Line 6: | ||
* [[AdventureWorks resit questions|Resit questions: 1..5]] | * [[AdventureWorks resit questions|Resit questions: 1..5]] | ||
| − | This data is based on Microsoft's AdventureWorksLT database. Access version: AdventureWorksLT.mdb | + | 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 tables}} | {{AdventureWorks tables}} | ||
Revision as of 13:30, 19 July 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)
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
