--WHERE

--SIMPLE
SELECT EmployeeID, LastName, City
FROM dbo.Employees

SELECT EmployeeID, LastName, City
FROM dbo.Employees
WHERE City = 'LONDON' --CASE INSENSITIVE

SELECT EmployeeID, LastName, City
FROM dbo.Employees
WHERE lastname = firstname

SELECT EmployeeID, LastName, City
FROM dbo.Employees
WHERE EmployeeID= 1

SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE UnitPrice > 20

--BETWEEN- For range
--Numbers:
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE UnitPrice BETWEEN 20 AND 40

-- Dates in SQL Server: 'YYYY-MM-DD', 'MM-DD-YYYY', 'YYYYMMDD'

--Dates:
SELECT EmployeeID, LastName, BirthDate
FROM dbo.Employees
WHERE BirthDate BETWEEN '1960-01-01' AND '1970-01-01'

SELECT EmployeeID, LastName, BirthDate
FROM dbo.Employees
WHERE BirthDate BETWEEN '19600101' AND '01-28-1966'

-- Invalid: (DD-MM-YYYY, MMDDYYYY, etc...)
SELECT EmployeeID, LastName, BirthDate
FROM dbo.Employees
WHERE BirthDate BETWEEN '01011960' AND '28-01-1966'

--Characters:

SELECT EmployeeID, LastName, BirthDate
FROM dbo.Employees
WHERE lastname BETWEEN 'c' AND 'q'

--IN
SELECT EmployeeID, LastName, City
FROM dbo.Employees
WHERE City IN ('LONDON', 'Tacoma')

SELECT ProductID, ProductName, CategoryID
FROM dbo.Products
WHERE CategoryID IN (1, 2, 7)

--LIKE
SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName LIKE 'K%'

SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName LIKE '%C%'

SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName LIKE '%a%a%'

SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName LIKE '_a%'

SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName LIKE '_[aei]%'

SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName LIKE '_[a-i]%'

SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName LIKE '_[a-z]%'

--Any single character not within the specified range ([^a-i])
--(^) = Not within the specified range

SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName LIKE '_[^a-i]%'

SELECT EmployeeID, LastName, Address
FROM dbo.Employees
WHERE Address LIKE '%AVE%'

-- NULL

-- Does not work - Any comparison to NULL evaluates to NULL...
SELECT CustomerID, ContactTitle, Region
FROM Customers
WHERE Region = NULL

SELECT CustomerID, ContactTitle, Region
FROM Customers
WHERE Region IS NULL

SELECT CustomerID, ContactTitle, Region
FROM Customers
WHERE Region IS NOT NULL

-- Calculations with NULL also evaluate to NULL
SELECT 50 + NULL

-- NOT

-- NOT BETWEEN
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE UnitPrice NOT BETWEEN 20 AND 40

-- NOT IN
SELECT ProductID, ProductName, CategoryID
FROM dbo.Products
WHERE CategoryID NOT IN (1, 2, 7)

-- NOT LIKE
SELECT EmployeeID, LastName
FROM dbo.Employees
WHERE LastName NOT LIKE 'K%'

-- Reverse any condition:
SELECT ProductID, ProductName, CategoryID
FROM dbo.Products
WHERE NOT CategoryID > 5

--AND / OR

--AND
SELECT ProductID, ProductName, UnitPrice, CategoryID
FROM dbo.Products
WHERE UnitPrice > 20 AND CategoryID IN (1, 2, 7)

-- OR

SELECT ProductID, ProductName, UnitPrice, CategoryID
FROM dbo.Products
WHERE (UnitPrice > 20 OR CategoryID IN (1, 2, 7))


--ORDER BY
-- Column name
SELECT EmployeeID "ID", LastName "Name", BirthDate
FROM dbo.Employees
ORDER BY LastName

-- Alias
SELECT EmployeeID ID, LastName "Name", BirthDate
FROM dbo.Employees
ORDER BY ID

-- Position of column in the select list
SELECT EmployeeID "ID", LastName "Name", BirthDate
FROM dbo.Employees
ORDER BY 2

SELECT EmployeeID "ID", LastName "Name", BirthDate
FROM dbo.Employees
ORDER BY ID DESC

--TOP N 
SELECT EmployeeID, LastName
FROM dbo.Employees

SELECT TOP 5 EmployeeID, LastName
FROM dbo.Employees

SELECT EmployeeID, LastName
FROM dbo.Employees
ORDER BY EmployeeID

SELECT TOP 5 EmployeeID, LastName
FROM dbo.Employees
ORDER BY EmployeeID

-- 3 Most expensive products
SELECT TOP 3 ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice DESC

-- Sorting according to a few columns (Primary, secondary etc.)
SELECT CustomerId, EmployeeID, OrderDate
FROM Orders
ORDER BY CustomerID, Employeeid

-- Does not have to be in the SELECT list
SELECT EmployeeID, OrderDate
FROM Orders
ORDER BY CustomerID

-- Specify ASC | DESC for each column separately

SELECT CustomerId, EmployeeID, OrderDate
FROM Orders
ORDER BY CustomerID DESC, Employeeid ASC

-- Just the second column will be in DESC order
SELECT CustomerId, EmployeeID, OrderDate
FROM Orders
ORDER BY CustomerID ASC, Employeeid DESC