Difference between revisions of "AdventureWorks"

From SQLZOO
Jump to: navigation, search
 
Line 61: Line 61:
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT OrderQty,Name,ListPrice
 
SELECT OrderQty,Name,ListPrice
   FROM SalesOrderHeader NATURAL JOIN SalesOrderDetail  
+
   FROM SalesOrderHeader JOIN SalesOrderDetail
                         NATURAL JOIN Product
+
          ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
 +
                         JOIN Product
 +
          ON SalesOrderDetail.ProductID=Product.ProductID
 
WHERE CustomerID=635
 
WHERE CustomerID=635
 
</source>
 
</source>
Line 68: Line 70:
 
<source lang='sql' class='ans'>
 
<source lang='sql' class='ans'>
 
SELECT OrderQty,Name,ListPrice
 
SELECT OrderQty,Name,ListPrice
   FROM SalesOrderHeader NATURAL JOIN SalesOrderDetail  
+
   FROM SalesOrderHeader JOIN SalesOrderDetail
                         NATURAL JOIN Product
+
          ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
 +
                         JOIN Product
 +
          ON SalesOrderDetail.ProductID=Product.ProductID
 
WHERE CustomerID=635
 
WHERE CustomerID=635
 
</source>
 
</source>
 
</div>
 
</div>
 
{{AdventureWorks Assessment}}
 
{{AdventureWorks Assessment}}

Latest revision as of 15:38, 9 February 2018

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

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