Teacher's Pet - MySQL

You can use relational and Boolean operators to modify your SQL query further - this comes in very handy if your table contains a large amount of numeric data, as illustrated below:

# grades table
+-------+------+---------+------------+
| name  | math | physics | literature |
+-------+------+---------+------------+
| john  |   68 |      37 |         45 |
| jim   |   96 |      89 |         92 |
| bill  |   65 |      12 |         57 |
| harry |   69 |      25 |         82 |
+-------+------+---------+------------+

The six relational operators available to you in SQL are as follows:

Operator	What It Means
=	is equal to
!=	is not equal to
>	is greater than
<	is less than
>=	is greater than/equal to
<=	is less than/equal to

You can also use the Boolean operators AND, OR and NOT to create more complex queries.
Now, looking at the table above, if you wanted a list of all students who scored over 90 in their math paper, you could formulate a query which looked like this:

mysql> SELECT * FROM grades WHERE math > 90;
+------+------+---------+------------+
| name | math | physics | literature |
+------+------+---------+------------+
| jim  |   96 |      89 |         92 |
+------+------+---------+------------+
1 row in set (0.00 sec)

Suppose you wanted to identify the smartest kid in class (you know this guy - he always sits in the front row, answers every question perfectly, and usually has wires on his teeth) so that you could beat him up during break.

mysql> SELECT name FROM grades WHERE math > 85 AND physics > 85 AND
literature > 85;
+------+
| name |
+------+
| jim  |
+------+
1 row in set (0.00 sec)

What if you needed to identify the ones who flunked at least one paper?

mysql> SELECT * FROM grades WHERE math <= 25 OR physics <= 25 OR literature
<= 25;
+-------+------+---------+------------+
| name  | math | physics | literature |
+-------+------+---------+------------+
| bill  |   65 |      12 |         57 |
| harry |   69 |      25 |         82 |
+-------+------+---------+------------+
2 rows in set (0.00 sec)

And finally, you can also perform basic mathematical operations within your query - the next example demonstrates how the three grades can be added together to create a total grade.

mysql> SELECT name, math+physics+literature FROM grades;
+-------+-------------------------+
| name  | math+physics+literature |
+-------+-------------------------+
| john  |                     150 |
| jim   |                     277 |
| bill  |                     134 |
| harry |                     176 |
+-------+-------------------------+
4 rows in set (0.05 sec)

Obviously, such an operation should only be attempted on fields of the same type.

This article copyright Melonfire 2001. All rights reserved.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus