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)
Assignments:
1.List the first and last names of all employees who draw less than or equal to $90000 and are not Programmes, Senior programmers or Multimedia programmers.
2.What is displayed by the following statement?
SELECT l_name, f_name from employee_data
where title NOT LIKE '%marketing%'
AND age < 30;
3.List all ids and names of all employees between 32 and 40 years of age.
4.Select names of all employees who are 32 years of age and are not programmers.
Possible Answers:
1.mysql> select f_name, l_name from employee_data
-> where salary <= 90000
-> AND title NOT LIKE '%programmers%';
+———+————+
| f_name | l_name |
+———+————+
| Anamika | Pandit |
| Mary | Anchor |
| Fred | Kruger |
| John | MacFarland |
| Edward | Sakamuro |
| Alok | Nanda |
| Hassan | Rajabi |
| Paul | Simon |
| Arthur | Hoopla |
| Danny | Gibson |
| Monica | Sehgal |
| Hal | Simlai |
| Joseph | Irvine |
| Shahida | Ali |
+———+————+
14 rows in set (0.00 sec)
2.The command displays names (last name followed by first name) of employees who are not in the marketing division and are less than 30 years of age.
mysql> SELECT l_name, f_name from employee_data
-> where title NOT LIKE '%marketing%'
-> AND age < 30;
+———-+———+
| l_name | f_name |
+———-+———+
| Sharma | Manish |
| Pandit | Anamika |
| Anchor | Mary |
| Sakamuro | Edward |
+———-+———+
4 rows in set (0.00 sec)
3.mysql> select emp_id, f_name, l_name from
-> employee_data where age > 32
-> and age < 40;
+——–+——–+————+
| emp_id | f_name | l_name |
+——–+——–+————+
| 7 | John | MacFarland |
| 10 | Hassan | Rajabi |
| 14 | Roger | Lewis |
| 15 | Danny | Gibson |
| 16 | Mike | Harper |
| 21 | Peter | Champion |
+——–+——–+————+
6 rows in set (0.00 sec)
4.mysql> select f_name, l_name from employee_data
-> where age = 32 AND
-> title NOT LIKE '%programmer%';
+———+——–+
| f_name | l_name |
+———+——–+
| Kim | Hunter |
| Shahida | Ali |
+———+——–+
6 rows in set (0.01 sec)