Logical Operators

In this section of the SQL primer we look at how to select data based on certain conditions presented through MySQL logical operators.

SQL conditions can also contain Boolean (logical) operators. They are

• AND
• OR
• NOT

Their usage is quite simple. Here is a SELECT statement that lists the names of employees who draw more than $70000 but less than $90000.

SELECT f_name, l_name from employee_data
where salary > 70000 AND salary < 90000;

+--------+------------+
| f_name | l_name     |
+--------+------------+
| Mary   | Anchor     |
| Fred   | Kruger     |
| John   | MacFarland |
| Edward | Sakamuro   |
| Paul   | Simon      |
| Arthur | Hoopla     |
| Joseph | Irvine     |
+--------+------------+
7 rows in set (0.00 sec)

Let’s display the last names of employees whose last names start with the alphabet S or A.

SELECT l_name from employee_data where
l_name like 'S%' OR l_name like 'A%';

+----------+
| l_name   |
+----------+
| Sharma   |
| Anchor   |
| Sakamuro |
| Simon    |
| Sehgal   |
| Simlai   |
| Ali      |
+----------+
7 rows in set (0.00 sec)

Okay here is a more complex example… listing the names and ages of employees whose last names begin with S or P and who are less than 30 years of age.

SELECT f_name, l_name , age from employee_data
where (l_name like 'S%' OR l_name like 'A%') AND
age < 30;

+--------+----------+------+
| f_name | l_name   | age  |
+--------+----------+------+
| Manish | Sharma   |   28 |
| Mary   | Anchor   |   26 |
| Edward | Sakamuro |   25 |
| Hal    | Simlai   |   27 |
+--------+----------+------+
4 rows in set (0.00 sec)

Note the usage of parenthesis in the statement above. The parenthesis are meant to separate the various logical conditions and remove any abiguity.

The NOT operator helps in listing all non programmers. (Programmers include Senior programmers, Multimedia Programmers and Programmers).

SELECT f_name, l_name, title from employee_data
where title NOT LIKE "%programmer%";

+---------+----------+----------------------------+
| f_name  | l_name   | title                      |
+---------+----------+----------------------------+
| Manish  | Sharma   | CEO                        |
| Anamika | Pandit   | Web Designer               |
| Mary    | Anchor   | Web Designer               |

| Kim     | Hunter   | Senior Web Designer        |
| Roger   | Lewis    | System Administrator       |
| Danny   | Gibson   | System Administrator       |
| Mike    | Harper   | Senior Marketing Executive |
| Monica  | Sehgal   | Marketing Executive        |
| Hal     | Simlai   | Marketing Executive        |
| Joseph  | Irvine   | Marketing Executive        |
| Shahida | Ali      | Customer Service Manager   |
| Peter   | Champion | Finance Manager            |
+---------+----------+----------------------------+
12 rows in set (0.00 sec)

A final example before we proceed to the assignments.

Displaying all employees with more than 3 years or service and more than 30 years of age.

select f_name, l_name from employee_data
where yos > 3 AND age > 30;

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | Hagan      |
| Ganesh | Pillai     |
| John   | MacFarland |
| Peter  | Champion   |
+--------+------------+
4 rows in set (0.00 sec)