Hobby – Family – Technology – More!

mysql – SELECT with CONDITION

selecting data using conditions

We’ll look at the format of a SELECT statement . We will use the select statement using the WHERE clause by example….

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 strengthOkay, 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
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. 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 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.

Leave a Reply

Powered by WordPress | Designed by Elegant Themes