SQL Subqueries Practice 1

1. Select firstname, lastname, and email of employees who are sales people.

use adventureworks
go

select FirstName, LastName, EmailAddress
from person.Contact p
 inner join HumanResources.Employee HRE
 on p.ContactID = HRE.ContactID
where hre.EmployeeID in
 (select distinct SalesPersonID from Sales.SalesPerson)

2. Get emails of all sales people who have made a sale greater than $150K.

select emailaddress
from person.Contact p
 inner join HumanResources.Employee HRE
  on p.ContactID = HRE.ContactID
 inner join Sales.SalesPerson SP
  on HRE.EmployeeID = SP.SalesPersonID
where SP.SalesPersonID in
 (select SalesPersonID from Sales.SalesOrderHeader sh
where sh.TotalDue > 150000)