AdventureWorks 2012 LT Schema

• Step 1 download the scripts for this database from here
• Step 2 unzip files to local directory  C:\Samples\AdventureWorks\AdventureWorks 2012 LT Script
• Step 3 run instawltdb.sql from the SQLCMD mode (via the query menu in SSMS)

Show the CompanyName for James D. 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'


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

Show FirstName and CompanyName for Companies that contain 'bike' and 'bicycle', list these in two seperate groups
 
with cte_1 as
(
SELECT
      [FirstName]
     ,[CompanyName]
  FROM [AdventureWorksLT2012].[SalesLT].[Customer]
  where companyname like '%bike%'
  union all
  SELECT
      [FirstName]
     ,[CompanyName]
  FROM [AdventureWorksLT2012].[SalesLT].[Customer]
  where companyname like '%bicycle%'
  )
 select *
 from cte_1

Further questions can be found here