In this section of the MySQL tutorial we'll look at the format of a SELECT statement we met in the last session in detail. We will learn how to use the select statement using the WHERE clause.
SELECT column_names from table_name [WHERE …conditions];
Now, we know that the conditions are optional (we've seen several examples in the last session… and you would have encountered them in the assignments too).
The SELECT statement without conditions lists all the data in the specified columns. The strength of RDBMS lies in letting you retrieve data based on certain specified conditions.
In this session we'll look at the SQL Comparision Operators.
The = and != comparision operators for MySQL Select
SELECT f_name, l_name from employee_data where f_name = 'John';
+——–+————+
| f_name | l_name |
+——–+————+
| John | Hagan |
| John | MacFarland |
+——–+————+
2 rows in set (0.00 sec)
This displays the first and last names of all employees whose first names are John. Note that the word John in the condition is surrounded by single quotes. You can also use double quotes. The quotes are important since MySQL will throw an error if they are missing. Also, MySQL comparisions are case insensitive; which means "john", "John" or even "JoHn" would work!
SELECT f_name,l_name from employee_data where title="Programmer";
+——–+————+
| f_name | l_name |
+——–+————+
| Fred | Kruger |
| John | MacFarland |
| Edward | Sakamuro |
| Alok | Nanda |
+——–+————+
4 rows in set (0.00 sec)
Selects the first and last names of all employees who are programmers.
SELECT f_name, l_name from employee_data where age = 32;
+———+——–+
| f_name | l_name |
+———+——–+
| John | Hagan |
| Ganesh | Pillai |
| Alok | Nanda |
| Arthur | Hoopla |
| Kim | Hunter |
| Shahida | Ali |
+———+——–+
6 rows in set (0.00 sec)
This lists the first and last names of all empoyees 32 years of age. Remember that the column type of age was int, hence it's not necessary to surround 32 with quotes. This is a subtle difference between text and integer column types.
The != means 'not equal to' and is the opposite of the equality operator.
The greater than and lesser than operators
Okay, let's retrieve the first names of all employees who are older than 32.
SELECT f_name, l_name from employee_data where age > 32;
+——–+————+
| f_name | l_name |
+——–+————+
| John | MacFarland |
| Hassan | Rajabi |
| Paul | Simon |
| Roger | Lewis |
| Danny | Gibson |
| Mike | Harper |
| Peter | Champion |
+——–+————+
7 rows in set (0.00 sec)
How about employees who draw more than $120000 as salary…
SELECT f_name, l_name from employee_data where salary > 120000;
+——–+——–+
| f_name | l_name |
+——–+——–+
| Manish | Sharma |
+——–+——–+
1 row in set (0.00 sec)
Now, let's list all employees who have had less than 3 years of service in the company.
SELECT f_name, l_name from employee_data where yos < 3;
+——–+———-+
| f_name | l_name |
+——–+———-+
| Mary | Anchor |
| Edward | Sakamuro |
| Paul | Simon |
| Arthur | Hoopla |
| Kim | Hunter |
| Roger | Lewis |
| Danny | Gibson |
| Mike | Harper |
| Hal | Simlai |
| Joseph | Irvine |
+——–+———-+
10 rows in set (0.00 sec)
The <= and >= operators for selecting MySQL data
Used primarily with integer data, the less than equal (<=) and greater than equal (>=)operators provide additional functionality.
select f_name, l_name, age, salary
from employee_data where age >= 33;
+——–+————+——+——–+
| f_name | l_name | age | salary |
+——–+————+——+——–+
| John | MacFarland | 34 | 80000 |
| Hassan | Rajabi | 33 | 90000 |
| Paul | Simon | 43 | 85000 |
| Roger | Lewis | 35 | 100000 |
| Danny | Gibson | 34 | 90000 |
| Mike | Harper | 36 | 120000 |
| Peter | Champion | 36 | 120000 |
+——–+————+——+——–+
7 rows in set (0.00 sec)
Selects the names, ages and salaries of employees who are more than or equal to 33 years of age..
select f_name, l_name from employee_data where yos <= 2;
+——–+———-+
| f_name | l_name |
+——–+———-+
| Mary | Anchor |
| Edward | Sakamuro |
| Paul | Simon |
| Arthur | Hoopla |
| Kim | Hunter |
| Roger | Lewis |
| Danny | Gibson |
| Mike | Harper |
| Hal | Simlai |
| Joseph | Irvine |
+——–+———-+
10 rows in set (0.00 sec)
Displays employee names who have less than or equal to 2 years of service in the company.
Assignments:
1.Write the SELECT statement to extract the ids of employees who are more than 30 years of age.
2.Write the SELECT statement to extract the first and last names of all web designers.
3.What will the following SELECT statement display:
SELECT * from employee_data where salary <=100000;
4.How will you display the salaries and perks for employees who have more than $16000 as perks?
5.List all employee names (last name followed by first name) who hold the title of Marketing Executive.
Possible Answers:
1.select emp_id from employee_data where age > 30;
+——–+
| emp_id |
+——–+
| 2 |
| 3 |
| 6 |
| 7 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 20 |
| 21 |
+——–+
14 rows in set (0.00 sec)
2.select f_name, l_name from employee_data where title='web designer';
+———+——–+
| f_name | l_name |
+———+——–+
| Anamika | Pandit |
| Mary | Anchor |
+———+——–+
2 rows in set (0.00 sec)
3.This displays all the information on employees who draw less than or equal to $100000 as salary.
SELECT * from employee_data where salary <=100000;
+——–+———+————+————————–+——+——+——–+——-+——————–+
| emp_id | f_name | l_name | title | age | yos | salary | perks | email |
+——–+———+————+————————–+——+——+——–+——-+——————–+
| 4 | Anamika | Pandit | Web Designer | 27 | 3 | 90000 | 15000 | ana@bignet.com |
| 5 | Mary | Anchor | Web Designer | 26 | 2 | 85000 | 15000 | mary@bignet.com |
| 6 | Fred | Kruger | Programmer | 31 | 3 | 75000 | 15000 | fk@bignet.com |
| 7 | John | MacFarland | Programmer | 34 | 4 | 80000 | 16000 | john@bignet.com |
| 8 | Edward | Sakamuro | Programmer | 25 | 2 | 75000 | 14000 | eddie@bignet.com |
| 9 | Alok | Nanda | Programmer | 32 | 3 | 70000 | 10000 | alok@bignet.com |
| 10 | Hassan | Rajabi | Multimedia Programmer | 33 | 3 | 90000 | 15000 | hasan@bignet.com |
| 11 | Paul | Simon | Multimedia Programmer | 43 | 2 | 85000 | 12000 | ps@bignet.com |
| 12 | Arthur | Hoopla | Multimedia Programmer | 32 | 1 | 75000 | 15000 | arthur@bignet.com |
| 14 | Roger | Lewis | System Administrator | 35 | 2 | 100000 | 13000 | roger@bignet.com |
| 15 | Danny | Gibson | System Administrator | 34 | 1 | 90000 | 12000 | danny@bignet.com |
| 17 | Monica | Sehgal | Marketing Executive | 30 | 3 | 90000 | 25000 | monica@bignet.com |
| 18 | Hal | Simlai | Marketing Executive | 27 | 2 | 70000 | 18000 | hal@bignet.com |
| 19 | Joseph | Irvine | Marketing Executive | 27 | 2 | 72000 | 18000 | joseph@bignet.com |
| 20 | Shahida | Ali | Customer Service Manager | 32 | 3 | 70000 | 9000 | shahida@bignet.com |
+——–+———+————+————————–+——+——+——–+——-+——————–+
15 rows in set (0.00 sec)
4.select salary, perks from employee_data where perks >= 16000;
+——–+——-+
| salary | perks |
+——–+——-+
| 200000 | 50000 |
| 120000 | 25000 |
| 110000 | 20000 |
| 80000 | 16000 |
| 110000 | 20000 |
| 120000 | 28000 |
| 90000 | 25000 |
| 70000 | 18000 |
| 72000 | 18000 |
| 120000 | 25000 |
+——–+——-+
10 rows in set (0.01 sec)
5.select l_name, f_name from employee_data where title = 'Marketing Executive';
+——–+——–+
| l_name | f_name |
+——–+——–+
| Sehgal | Monica |
| Simlai | Hal |
| Irvine | Joseph |
+——–+——–+
3 rows in set (0.00 sec)