SQL-IF and CASE

This time around am going to take a look into the 'if' and 'case' used in SQL... as you can see in the code given below you have a very good feature from SQL. Making decisions inside a query can come in very handy as show below in the given 'if' statements included in this query.

DECLARE @num int
SET @num = 4

DECLARE @salesPersonId int

if @num = 3
BEGIN
 set @salesPersonId = 
 (
  SELECT top 1 SalesPersonID
  FROM Sales.SalesPerson
 )
 SELECT 'The number is 3' [Output]
END
ELSE IF @num = 4
BEGIN
 set @salesPersonId = 4
 SELECT 'The number is 4' [Output]
END
ELSE
BEGIN
 set @salesPersonId = -1
 SELECT 'The number is not valid' [Output]
END


IF @salesPersonId = -1
BEGIN
 SELECT 'No Sales Person selected' SalesPersonId
END
ELSE
BEGIN
 SELECT @salesPersonId SalesPersonId
END

Below this paragraph is a code snippet with a 'case' used to set the 'EmailPromotion' to a more user friendly look, it is actually given as an 'int', by taking in the EmailPromotion and setting it to a string that better ids the kind of promotion that the person is asking for.

SELECT FirstName, LastName,
(
 CASE EmailPromotion
  WHEN 1 THEN 'EmailPromo'
  WHEN 2 THEN 'TextPromo'
  ELSE 'NadaPromo'
 END
) Promo
FROM Person.Contact c

More to Come, CA.

The "UNION" of SQL

"The UNION operator is used to combine the result-set of two or more SELECT statements." Given to us by: http://www.w3schools.com/sql/sql_union.asp.

What this does it take the first "SELECT" statement, and tack on the second "SELECT" statement to the end of it.

SELECT SalesPersonID
FROM Sales.SalesPerson
UNION
SELECT ContactID
FROM HumanResources.Employee

Taking a look at this code snippet we take all of the "SalesPersonID" from "Sales.SalesPerson" and all of the "ContactID" from "HumanResources.Employee" and combine them together, even if there are duplicate records.

There is a "UNION ALL" that can be used in the place of just plain "UNION" that will actually give all the records as single records, giving no duplicated records.

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.