Home arrow MySQL arrow Page 3 - Speaking SQL (part 2)

Teacher's Pet - MySQL

After learning how to insert and edit data in a database, thesecond part of our SQL tutorial takes an in-depth look at the SELECTquery,and explains how to use joins, sub-queries and built-in functions tofocus in on the data you need.

TABLE OF CONTENTS:
  1. Speaking SQL (part 2)
  2. Christmas Presents
  3. Teacher's Pet
  4. Reading Backwards
  5. Count() Me In
  6. Like, You Know, Man...
  7. Joining Them Together
  8. Nest Egg
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 5
January 18, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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:

OperatorWhat 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
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: