Advanced Relational Database */ Subqueries */

use Adventureworks

--Select first name, last name, and email of employees who are sales people
select pc.FirstName, pc.LastName, pc.EmailAddress
from Person.Contact pc
 inner join HumanResources.Employee hre on pc.ContactID = hre.ContactID
 where hre.EmployeeID 
 in 
  (
  select ssp.SalesPersonID
  from Sales.SalesPerson ssp
  )

--Get emails of all sales people who have made a sale greater than $150k 
select pc.EmailAddress
from Person.Contact pc
 inner join HumanResources.Employee hre 
 on pc.ContactID = hre.ContactID
 inner join Sales.SalesPerson ssp
 on hre.EmployeeID = ssp.SalesPersonID
 where ssp.SalesPersonID 
 in 
  (
   select soh.SalesPersonID
   from Sales.SalesOrderHeader soh
   where soh.TotalDue > 150000
  )

select pp.ProductID, pp.Name, (pp.ListPrice - pp.StandardCost) Markup
from Production.Product pp
where pp.ProductID
not in
 (
  select sod.ProductID
  from Sales.SalesOrderDetail sod
 )
order by Markup desc

--The CEO wants to reward the top sales person for the previous year.  
--Write a query that will find the top sales person based on sales last year (saleslastyear).  
--Given that sales person, get their first and last name, how many years they have been around, 
--how many customers they have, and how many sales they have.


Declare @Top int
set @Top = 
(
select TOP 1 ssp.SalesPersonID
from Sales.SalesPerson ssp
order by ssp.SalesLastYear desc
)
Declare @Customer int
set @Customer =
(
select COUNT(distinct soh.CustomerID)
from Sales.SalesOrderHeader soh
where soh.SalesPersonID = @Top
)
Declare @Sales int
set @Sales =
(
select COUNT(distinct soh.SalesOrderID)
from Sales.SalesOrderHeader soh
where soh.SalesPersonID = @Top
)
select pc.FirstName, pc.LastName, ssp.SalesLastYear, DATEDIFF(YEAR, hre.HireDate, GETDATE()) YearsWorked, @Customer Customers, @Sales TotalSales
from Person.Contact pc
 inner join HumanResources.Employee hre 
 on pc.ContactID = hre.ContactID
 inner join Sales.SalesPerson ssp
 on hre.EmployeeID = ssp.SalesPersonID
 where ssp.SalesPersonID 
 in 
  (
   select @Top
   from Sales.SalesOrderHeader soh
  )
order by ssp.SalesLastYear desc