Some Awesome Sub-query Practice

Lets take a look at some SQL again, this is three problems that need to be figured out I will be using the database called AdventureWorks...

The first problem to figure out is this..."Select first name, lastname, and email of employees who are sales people"

SELECT c.FirstName, c.LastName, c.EmailAddress
FROM Person.Contact c
WHERE c.contactID IN
 (
 SELECT e.ContactID
 FROM HumanResources.Employee e
 WHERE e.EmployeeID IN
  (
   SELECT SalesPersonID
   FROM Sales.SalesPerson
  )
 ) 

First I actually found the "SalesPersonID" of all the records in the "Sales.SalesPerson" table, this gives me all the IDs in a single table that I can then use to get all the "SalesPersonID" records that are also the same as the "EmployeeID" in the "HumanResources.Employee" table. From the combination of both of these two tables, the "Sales.SalePerson" and "HumanResources.Employee", I get the "ContactID" from the "HumanResources.Employee" table, after it has been sorted through. That allows me to use the "Employee" table "ContactID" and get all "ContactID" records that are also in the "Person.Contact" table "ContactID" records, getting all the records that fall into this criteria I am able to get the "FirstName", "LastName", and "EmailAddress" of all records sorted out by the sub-queries.

The next problem is..."Get emails of all sales people who have made a sale greater than $150k"

SELECT sp.SalesPersonID, c.EmailAddress
FROM Person.Contact c, Sales.SalesPerson sp 
INNER JOIN HumanResources.Employee e
 ON sp.SalesPersonID = e.EmployeeID
WHERE sp.SalesPersonID IN
 (
  SELECT soh.SalesPersonID
  FROM Sales.SalesOrderHeader soh
  WHERE soh.TotalDue > 150000
 )
AND
 c.ContactID = e.ContactID

This query gets all the information from the "Contact" table, to get the "EmailAddress" of the employees. And by using an "INNER JOIN" to "join" the "SalesPerson" and the "Employee" tables to get all the "Employee" records that are only in the "SalesPerson" table. By using the "SalersPersonID" we are able then use a nested select statement to get only the "SalesPersonID" where that record has a "TotalDue" greater than 150,000. And to finally get the "EmailAddress" we only grab the records where the "Contact" "ContactID" is equal to the "Employee" "ContactID".

Another problem to figure out was..."Query all the products that have not sold and figure out the markup by subtracting the standardcost from the listprice. Order the query by markup descending."

SELECT p.Name, p.ProductNumber, (p.ListPrice - p.StandardCost) Markup
FROM Production.Product p
WHERE p.ProductID NOT IN
 (
  SELECT sod.ProductID
  FROM Sales.SalesOrderDetail sod
 )
ORDER BY Markup DESC

This query starts by taking the "Name", "ProductNumber", "ListPrice", and "StandardCost" of the records in the "Product" table. Since I wanted to get an answer that would list the "Marked up" price of all the product not sold, I pulled from all the products and then used a sub-query to get all product in the "SalesOrderDetail", this actually gives me a list of all the product sold. so using a "NOT IN" "WHERE" I am able to all the products "NOT SOLD". Nice huh...

More To Come, CA.