Selecting Information to Display

How to Create a Simple Query Using SELECT

We have already used the SELECT command in MySQL to list the contents of a table. For instance, if a user had already connected to the database "games and wanted to review the scores, the following would list all the contents of that table:

mysql> SELECT * FROM scores;

+---------+------+
| Name    | Num  |
+---------+------+
| Phyllis |  987 |
| Randy   | 1285 |
| Don     |  919 |
| Mark    |    0 |
| Mary    |  567 |
| Bob     |   23 |
| Pete    |  456 |
| Sally   |  333 |
+---------+------+
8 rows in set (0.00 sec)   

More Advanced Queries

1. To List By Numerical Order
   
   mysql> SELECT * FROM scores ORDER BY Num;

   +---------+------+
   | Name    | Num  |
   +---------+------+
   | Mark    |    0 |
   | Bob     |   23 |
   | Sally   |  333 |
   | Pete    |  456 |
   | Mary    |  567 |
   | Don     |  919 |
   | Phyllis |  987 |
   | Randy   | 1285 |
   +---------+------+
   8 rows in set (0.00 sec)
   

2. To List By Alphabetical Order
   
   mysql> SELECT * FROM scores ORDER BY Name;

   +---------+------+
   | Name    | Num  |
   +---------+------+
   | Bob     |   23 |
   | Don     |  919 |
   | Mark    |    0 |
   | Mary    |  567 |
   | Pete    |  456 |
   | Phyllis |  987 |
   | Randy   | 1285 |
   | Sally   |  333 |
   +---------+------+
   8 rows in set (0.01 sec)
   
   

3. List Only the Names of the Losers
   
   mysql> SELECT Name FROM scores WHERE (Num < 100);

   +------+
   | Name |
   +------+
   | Mark |
   | Bob  |
   +------+
   2 rows in set (0.00 sec) 
   

4. List the Winners by Order of High Score
   Note: This will be a long Query, and will be split on multiple lines:
   
   mysql> SELECT Num, Name FROM scores
         -> WHERE (Num > 900)
         -> ORDER BY Num DESC;

      
   +------+---------+
   | Num  | Name    |
   +------+---------+
   | 1285 | Randy   |
   |  987 | Phyllis |
   |  919 | Don     |
   +------+---------+
   3 rows in set (0.00 sec)