AdventureWorks

Graduated questions

This data is based on Microsoft's AdventureWorks 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

schema:adventure

Show the CompanyName for James D. Kramer

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

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

Show all the addresses listed for 'Modular Cycle Systems'

SELECT CompanyName,AddressType,AddressLine1  FROM Customer JOIN CustomerAddress  ON (Customer.CustomerID=CustomerAddress.CustomerID)  JOIN Address  ON (CustomerAddress.AddressID=Address.AddressID)  WHERE CompanyName='Modular Cycle Systems'

SELECT CompanyName,AddressType,AddressLine1  FROM Customer JOIN CustomerAddress  ON (Customer.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 JOIN SalesOrderDetail  ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID  JOIN Product  ON SalesOrderDetail.ProductID=Product.ProductID WHERE CustomerID=635

SELECT OrderQty,Name,ListPrice  FROM SalesOrderHeader JOIN SalesOrderDetail  ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID  JOIN Product  ON SalesOrderDetail.ProductID=Product.ProductID WHERE CustomerID=635