=
<>
>
<
>=
<=
BETWEEN AND
[NOT] IN
IS [NOT] NULL
EXISTS
LIKE
In this section will cover....
BETWEEN Operator:
- To locate rows for which a numeric column is within a range of values.
Select the name and the employee's manager number for all employees whose job codes are in the 430000 range.
SELECT first_name
,last_name
,manager_employee_number
FROM employee
WHERE job_code BETWEEN 430000 AND 439999;
first_name last_name manager_employee_number
---------- ---------- ------------------------
Loretta Ryan 801
Armando Villegas 1005
- To locate rows for which a character column is within a range of values.
SELECT last_name
FROM employee
WHERE last_name BETWEEN 'r' AND 's';
last_name
------------
Ryan
IN Operator:
Use the IN operator as shorthand when multiple values are to be tested.
Ex:Select the name and department for all employees in either department 401 or 403.
SELECT first_name
,last_name
,department_number
FROM employee
WHERE department_number IN (401, 403);
first_name last_name department_number
----------- ----------- -----------------------
Darlene Johnson 401
Loretta Ryan 403
Armando Villegas 403
James Trader 401
NOT in Operator:
Use the NOT IN operator to locate rows for which a column does not match any of a set of values
SELECT first_name
,last_name
,department_number
FROM employee
WHERE department_number NOT IN (401, 403) ;
first_name last_name department_number
----------- ----------- ----------------------
Carol Kanieski 301
John Stein 301
LIKE operator:
The LIKE operator searches for patterns matching character data strings.
Here are some examples using the LIKE operator:
String pattern example: Meaning:
LIKE 'JO%' begins with 'JO'
LIKE '%JO%' contains 'JO' anywhere
LIKE '__HN' contains 'HN' in 3rd and 4th position
LIKE '%H_' contains 'H' in next to last position
Below are the examples
Display the full name of employees whose last name contains the letter "r" followed by the letter "a".
SELECT first_name
,last_name
FROM employee
WHERE last_name LIKE '%ra%';
first_name last_name
------------------------------ ---------------
James Trader
Peter Rabbit
I.B. Trainer
Robert Crane
Larry Ratzlaff
Note: LIKE Operator -- Case-Sensitive Comparison
Display the full name of employees whose last name contains "Ra". This is a case-sensitive test.
SELECT first_name
,last_name
FROM employee
WHERE last_name (CASESPECIFIC) LIKE '%Ra%';
first_name last_name
------------------------------ --------------------
Peter Rabbit
Larry Ratzlaff
The default comparison for Teradata mode is not case-specific.
Use of the Teradata extension (CASESPECIFIC) forces a case-specific comparison.
LIKE Operator -- Using Quantifiers
There are three such quantifiers:
ANY — any single condition must be met (OR logic)
SOME — same as ANY
ALL — all conditions must be met (AND logic)
Display the full name of all employees with both "E" and "S" in their last name.
SELECT first_name
,last_name
FROM employee
WHERE last_name LIKE ALL ('%E%', '%S%');
first_name last_name
------------------------------ -------------------
John Stein
Carol Kanieski
Arnando Villegas
Display the full name of all employees with either an "E" or "S" in their last name.
SELECT first_name
,last_name
FROM employee
WHERE last_name LIKE ANY ('%E%', '%S%');
first_name last_name
------------------------------ --------------------
John Stein
Carol Kanieski
Arnando Villegas
Darlene Johnson
James Trader
