List the comparison operators and logical operators that are used in a WHERE clause

Operator

Meaning

=

Equal to

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

<>

Not equal to (can also use != or ^=)

BETWEEN ... AND ...

Between two values (inclusive)

IN (set)

Match any value in a list

LIKE

Match a character pattern '%' - zero or many; '_' - one character

IS NULL

is a null value

AND

returns TRUE if both conditions are true

OR

returns TRUE if either condition is true

NOT

returns TRUE if the condition is false

-- must specify the lower limit firstSELECTlast_name,salaryFROMhr.employeesWHEREsalaryBETWEEN4000AND5000;-- can also use on character valueSELECTlast_name,salaryFROMhr.employeesWHERElast_nameBETWEEN'Abel'AND'Bull'ORDERBYlast_name;SELECTlast_name,salaryFROMhr.employeesWHEREsalaryin(4000,6000,8000);-- last name start with 'A' and 2 characters at leastSELECTlast_name,salaryFROMhr.employeesWHERElast_namelike'A_%';-- hire date at year 1999SELECTlast_name,salary,hire_dateFROMhr.employeesWHEREhire_datelike'%99';-- employee doesn't report to any manager SELECTlast_name,salaryFROMhr.employeesWHEREmanager_idisnull;-- use AND, OR, NOT operatorsSELECTlast_name,job_id,salaryFROMhr.employeesWHERE(job_idlike'AD%'ORjob_idlike'IT%')ANDsalary>5000ANDNOTlast_name='King';-- use ESCAPE identifier SELECTlast_name,job_idFROMhr.employeesWHEREjob_idlike'A_\_P%'ESCAPE'\';