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
Customer(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)
Product(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
