WebDevelopersNotes logo

home-icon Home / MySQL / SQL primer – Logical Operators

SQL primer – Logical Operators

SQL primer - 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)

Sponsored Links

Your comments
Star icon IMPORTANT Have a question / problem? Click here to ask an expert.

How a Computer Engineering student feels, When the PROGRAM Compiles

Sponsored Links

Tips

How do I view a deleted web page?
Is there a way to view a deleted web page - one that is no longer available? Yes there is and the solution is quite simple. [more...]

Fact

The story behind the origin of the name of Linux is interesting. The creator of the operating system, Linus Torvalds, had originally named it "Freax" - a combination of "free", "freak" and "x". He had also considered "Linux" but thought it was too egoistical. So, during development, Torvalds had stored all files in a directory named Freax. Anyway, the files and the directory (with the name Freax) were maintained on an FTP server of FUNET. Ari Lemmke, who was one of the the administrators of the server, didn't like the name 'Freax' and changed it to 'Linux' without asking Torvalds, who later agreed to keep the new name.The first version of Linux was released on 25th August 1991. By the way, Ari Lemmke had played a role in the early days of the World Wide Web. He had been the the supervisor of the four Finnish students who developed Erwise, the first graphical web browser, at the Helsinki University of Technology. FYI, the term 'Linux' is trademarked in the US by Linus Torvalds to prevent others from making money off it. [more...]

We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy