------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- October 1, 2009 -----------------------------------------------------
-- WHERE COMMAND -------------------------------------------------------
------------------------------------------------------------------------

-- WHERE
SELECT CustFirstName, CustLastName, CustStreetAddress, CustCity, CustState, CustZipCode
FROM Customers
WHERE CustLastName = 'Patterson'
ORDER BY CustFirstName;

SELECT ProductName, RetailPrice
FROM Products
WHERE RetailPrice >= 250;

SELECT OrderNumber, CustomerID,
CONVERT(CHAR(12),OrderDate,107) AS [Order Date],
CONVERT(CHAR(12),ShipDate,107)  AS [Ship Date]
FROM Orders
WHERE DATEDIFF(dd, OrderDate, ShipDate)>3;

-- BETWEEN & AND
SELECT *
FROM Orders
WHERE ShipDate BETWEEN '02/1/2008' AND '02/29/2008'
ORDER BY ShipDate;

SELECT CustFirstName, CustLastName
FROM Customers
WHERE CustLastName BETWEEN 'A' AND 'M'
ORDER BY CustLastName;

SELECT ProductName, RetailPrice
FROM Products
WHERE RetailPrice BETWEEN 100 AND 199
ORDER BY RetailPrice;

-- IN
SELECT CustFirstName + ' ' + CustLastName, CustState
FROM Customers
WHERE CustState IN('WA','OR','CA');

SELECT ProductName, CategoryID
FROM Products
WHERE CategoryID IN(1,3,4);

SELECT OrderNumber, DATEDIFF(dd, OrderDate, ShipDate), CustomerID, EmployeeID
FROM Orders
WHERE EmployeeID IN(701,703,708);
