Subquery Practice

 use AdventureWorks
  
 select PC.FirstName, PC.LastName, PC.EmailAddress, HE.Title
  
 from Person.Contact PC
  
 inner join HumanResources.Employee HE
  
 on PC.ContactID = HE.ContactID
  
 where HE.EmployeeID in
  
      (
  
      select SS.SalesPersonID
  
      from Sales.SalesPerson SS
  
      )
  
 select SP.salespersonID, PC.EmailAddress
  
 from Sales.SalesPerson SP
  
 inner join HumanResources.Employee HRE
  
 on SP.SalesPersonID = HRE.EmployeeID
  
 inner join Person.Contact PC
  
 on HRE.ContactID = PC.ContactID
  
 where SP.SalesPersonID in 
  
      (
  
      select SOH.SalesPersonID
  
      from Sales.SalesOrderHeader SOH
  
      where SOH.TotalDue > '150000'
  
      )
  
 select PP.productID, (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
  
 use AdventureWorks
  
 declare @top_sales_person int
  
 set @top_sales_person = 
  
      (
  
      select top 1 SP.SalesPersonID
  
      from Sales.SalesPerson SP
  
      order by SP.SalesLastYear desc
  
      )
  
 declare @customers int
  
 set @customers =
  
      (
  
      select COUNT (distinct customerid)
  
      from Sales.SalesOrderHeader SOH
  
      where SOH.SalesPersonID = @top_sales_person
  
      )
  
 declare @total_sales int
  
 set @total_sales =
  
      (select COUNT (distinct SOH.SalesOrderID)
  
      from Sales.SalesOrderHeader SOH
  
      where SOH.SalesPersonID = @top_sales_person
  
      )
  
 select PC.FirstName, PC.LastName, DATEDIFF ( year, HRE.HireDate, GETDATE() ) Tenure, @customers Customers 
  
 , @total_sales Total_Sales
  
 from Person.Contact PC
  
 inner join HumanResources.Employee HRE
  
 on PC.ContactID = HRE.ContactID
  
 inner join Sales.SalesPerson SP
  
 on HRE.EmployeeID = SP.SalesPersonID
  
 where SP.SalesPersonID = @top_sales_person