Difference between revisions of "AdventureWorks"
| Line 69: | Line 69: | ||
</source> | </source> | ||
</div> | </div> | ||
| − | {{ | + | {{AdventureWorks Assessment}} |
Revision as of 19: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)
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
