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
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
DataWars: Practice Data Science/Analysis with +100 Real Life Projects