--------------------------------
-- Basic WHERE
--------------------------------

SELECT productName , unitPrice
FROM products
WHERE unitPrice > 20 

SELECT productName , unitPrice
FROM products
WHERE productName = 'CHAI'

SELECT lastName , hireDate
FROM employees
WHERE hireDate > '1994-01-01' 

-------------------------------
-- IN
-------------------------------

SELECT productName ,unitPrice
FROM products
WHERE unitPrice IN (55, 52, 67)

SELECT productName ,unitPrice
FROM products
WHERE unitPrice NOT IN (55, 52, 67)

-------------------------------
-- BETWEEN
-------------------------------

SELECT productName ,unitPrice
FROM products
WHERE unitPrice BETWEEN 40 AND 61 

SELECT productName ,unitPrice
FROM products
WHERE unitPrice BETWEEN 61 AND 40 

SELECT productName ,unitPrice
FROM products
WHERE unitPrice BETWEEN 40  AND  51.35

------------------------------
-- LIKE
------------------------------

SELECT CompanyName , phone
FROM customers 

SELECT CompanyName , phone
FROM customers
WHERE phone LIKE '03%' 

SELECT CompanyName , phone
FROM customers
WHERE phone LIKE '%9' 

SELECT CompanyName , phone
FROM customers
WHERE phone LIKE '%03%' 

SELECT CompanyName , phone
FROM customers
WHERE phone LIKE '%2_'

SELECT CompanyName , phone
FROM customers
WHERE phone LIKE '03%-%2_' 

SELECT CompanyName , phone
FROM customers
WHERE phone LIKE '03!%-%2_' ESCAPE '!' 

SELECT CompanyName , phone
FROM customers
WHERE phone NOT LIKE '03%-%2_' 

------------------------------
-- IS NULL / IS NOT NULL
------------------------------

SELECT productName , unitPrice , unitPrice + 1
FROM products 

SELECT productName , unitPrice
FROM products
WHERE unitPrice IS NULL 

SELECT productName , unitPrice
FROM products
WHERE unitPrice IS NOT NULL 

------------------------------------
-- Using more than one condition
------------------------------------

SELECT productName , unitPrice , categoryID
FROM products
WHERE unitPrice BETWEEN 0 AND 60
AND
	 categoryID IN (3,2,4)

SELECT productName , unitPrice , categoryID
FROM products
WHERE unitPrice BETWEEN 0 AND 60
OR
	 categoryID IN (3,2,4)

SELECT productName , unitPrice , categoryID
FROM products
WHERE unitPrice BETWEEN 0 AND 60
AND
	 ( productName LIKE '%a%' OR productName LIKE '%e%' )

-------------------------------

SELECT productName , categoryID , unitPrice
FROM products
ORDER BY categoryID , unitPrice DESC 

SELECT TOP 5 productName , unitPrice
FROM products
ORDER BY unitPrice DESC