-------------------------------- -- 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