Speaking SQL (part 2) - Teacher's Pet (
Page 3 of 8 )
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.
