To anyone who is familiar with AdventureWorks, do these questions make sense?

Question

1. Show the company name for 'James D.Kramer'.

2. Show all addresses listed for 'Modular Cycle Systems'

3. Show order quantity, the name and the list price of the order made by 'CustomerID 635'

Code snippets:

--use [AdventureWorks2016CTP3]

--CAN'T get the required company with this name.
select 
    [BusinessEntityID], [FirstName], [MiddleName], [LastName] 
from 
    [Person].[Person]
where  
    [FirstName] = 'James' and 
    [MiddleName] = 'D.' and
    [LastName] = 'Kramer'



--Addresses for Modular Cycle Systems
select
    [Name], PeA.[AddressLine1] as [Address]
from 
    [Sales].[Store] as SaS
inner join 
    [Person].[BusinessEntityAddress] as PeBEA on SaS.BusinessEntityID = PeBEA.BusinessEntityID
inner join 
    [Person].[Address] as PeA on PeBEA.[AddressID] = PeA.[AddressID]
where 
    [Name] = 'Modular Cycle Systems'


--Details of CustomerID 635 - Apparently INCORRECT accorrdin to my Superior.
select 
    SaC.[CustomerID], SaSOD.[OrderQty], SaS.[Name], SaSOD.[UnitPrice] as ListPrice
from
    [Sales].[Customer] as SaC
join 
    sales.Store as SaS on SaC.StoreID = SaS.BusinessEntityID
join 
    [Sales].[SalesOrderHeader] as SaSOH on SaC.[CustomerID] = SaSOH.[CustomerID]
join 
    [Sales].[SalesOrderDetail] as SaSOD on SaSOH.SalesOrderID = SaSOD.SalesOrderID
where 
    SaC.[CustomerID] = '635'

The parts which work:

Select 
    SaC.[CustomerID], [StoreID], SaS.[Name]
from
    [Sales].[Customer] as SaC
join
    [Sales].[Store] as SaS on SaS.BusinessEntityID = SaC.StoreID
where 
    SaC.[CustomerID] = '635'


select 
    SaSOH.[SalesOrderID], SaSOH.[CustomerID], 
    SaSOD.[OrderQty], SaSOD.[UnitPrice], SaSOD.[UnitPriceDiscount] 
from
    [Sales].[SalesOrderHeader] as SaSOH
join 
    [Sales].[SalesOrderDetail] as SaSOD on SaSOH.[SalesOrderID] = SaSOD.[SalesOrderID]

--WORKS! without customer constraint though. --Meaning different transaction by the same customer/sales agent?

Select SaC.[CustomerID], [StoreID], SaS.[Name], 
    SaSOD.[OrderQty], SaSOD.[UnitPrice]
from [Sales].[Customer] as SaC
    Join [Sales].[Store] as SaS
        on SaS.BusinessEntityID = SaC.StoreID
    join [Sales].[SalesOrderHeader] as SaSOH
        on SaSOH.[CustomerID] = SaC.[CustomerID]
    join [Sales].[SalesOrderDetail] as SaSOD
        on SaSOH.[SalesOrderID] = SaSOD.[SalesOrderID]
where SaC.[StoreID] = '1960'
--and SaC.[CustomerID] = '635'
order by SaC.StoreID

Answer 1

For the first question:

SELECT * FROM SALES.vStoreWithContacts
where FirstName = 'James'
and MiddleName = 'D.'
and LastName = 'Kramer'

---

BusinessEntityID    Name                            FirstName   MiddleName  LastName
1264                Refined Department Stores       Mr. James   D.          Kramer

In Person. Person table, James's BusinessentityID is 1263, which is different from 1264 in StoreContactTable. That is why you got nothing. But he is the owner of 'Refined Department Stores' if you look at the SALES.vStoreWithContacts separately.

For the second question: your query works fine.

For the third question:

The customer 645 did not place any order

select * from Sales.SalesOrderHeader
where CustomerID = '635'

It returned nothing.