Difference between revisions of "AdventureWorks"

From SQLZOO
Jump to: navigation, search
 
Line 16: Line 16:
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT CompanyName
 
SELECT CompanyName
   FROM CustomerAW
+
   FROM Customer
 
  WHERE FirstName='James'
 
  WHERE FirstName='James'
 
   AND MiddleName='D.'
 
   AND MiddleName='D.'
Line 36: Line 36:
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT CompanyName,AddressType,AddressLine1
 
SELECT CompanyName,AddressType,AddressLine1
   FROM CustomerAW JOIN CustomerAddress
+
   FROM Customer JOIN CustomerAddress
     ON (CustomerAW.CustomerID=CustomerAddress.CustomerID)
+
     ON (Customer.CustomerID=CustomerAddress.CustomerID)
 
                   JOIN Address
 
                   JOIN Address
 
     ON (CustomerAddress.AddressID=Address.AddressID)
 
     ON (CustomerAddress.AddressID=Address.AddressID)
Line 45: Line 45:
 
<source lang='sql' class='ans'>
 
<source lang='sql' class='ans'>
 
SELECT CompanyName,AddressType,AddressLine1
 
SELECT CompanyName,AddressType,AddressLine1
   FROM CustomerAW JOIN CustomerAddress
+
   FROM Customer JOIN CustomerAddress
     ON (CustomerAW.CustomerID=CustomerAddress.CustomerID)
+
     ON (Customer.CustomerID=CustomerAddress.CustomerID)
 
                   JOIN Address
 
                   JOIN Address
 
     ON (CustomerAddress.AddressID=Address.AddressID)
 
     ON (CustomerAddress.AddressID=Address.AddressID)

Latest revision as of 17:37, 2 February 2017

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)

AdventureWorks.png

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 CustomerAW
 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 NATURAL JOIN SalesOrderDetail 
                        NATURAL JOIN ProductAW
WHERE CustomerID=635
SELECT OrderQty,Name,ListPrice
  FROM SalesOrderHeader NATURAL JOIN SalesOrderDetail 
                        NATURAL JOIN ProductAW
WHERE CustomerID=635