Simple Conditions

-- equals, is the same as
WHERE quantity = 15                  # number
WHERE surname = 'Smith'              # text
WHERE date_of_birth = '2004-11-23'   # date
WHERE appointment = '09:45'          # time
WHERE bill_paid = 1                  # Boolean
 
-- is not equal to, is not the same as
WHERE quantity != 15
 
-- is less than
WHERE quantity < 15
 
-- is less than or equal to
WHERE quantity <= 15
 
-- is greater than
WHERE quantity > 15
 
-- is greater than or equal to
WHERE quantity >= 15

Complex Conditions

Each condition must be in full. You cannot write:

WHERE forename = 'John' OR 'Jon'

AND, OR, NOT

-- and - all conditions must be met
WHERE surname ='MacDonald'
    AND forename = 'John'
 
-- or - any of the conditions must be met
WHERE quantity = 10 
    OR quantity = 20
 
-- not - the condition must not be met
WHERE not (surname = "MacDonald")

Examples:

-- is between two numbers (inclusive)
WHERE quantity>=10 AND quantity <=15
 
-- is not between two numbers (inclusive) 
WHERE quantity < 10 OR quantity > 15 
  
-- is in a list of values
WHERE colour='black' OR colour='gold' OR colour='silver' OR colour='white'

Advanced Higher Operators

AH students should use BETWEEN and IN operators as required.
National 5 and Higher should use complex conditions as above to meet course requirements.

WHERE quantity BETWEEN 10 AND 15
WHERE colour IN ['black', 'gold', 'silver', 'white']