Conditions and Data Analysis
 

List Arrangement

The lists of records we got above with the SELECT statement were presented in the same order they were created in the table. SQL allows you to arrange records in alphabetical order, in chronological order or in numeric incremental order.

After selecting a series of columns, you may want to list the records following an alphabetical order from one specific field. To get an alphabetical or an incremental order of records, you must let the database know what field would be used as reference. In SQL, this is done using the ORDER BY expression. The syntax used would be: 

SELECT What FROM WhatObject ORDER BY WhatField;

The column used as the basis must be recognized as part of the selected columns.

Practical LearningPractical Learning: Ordering Items

  1. To get a list of students in alphabetical order based on the LastName column, execute the following statement:
      
    SELECT LastName, FirstName, Gender, EmailAddress
FROM Students
ORDER BY LastName

  2. In the same way, you can get the list of girls followed by the list of boys by ordering the list in alphabetical order based on the Gender. As an example, execute the following statement:

    mysql> SELECT FirstName, LastName, Gender, EmailAddress
    -> FROM Students
    -> ORDER BY Gender;
+------------+--------------+--------+-----------------------+
| FirstName  | LastName     | Gender | EmailAddress          |
+------------+--------------+--------+-----------------------+
| Donnie     | Mart         | Female | martd@rosh.md.us      |
| Arlene     | Andriamirano | Female | Andriam@rosh.md.us    |
| Gabrielle  | Ledoux       | Female | ledouxg@rosh.md.us    |
| Koko       | Lobila       | Female | lobilak@rosh.md.us    |
| Arlette    | Duma         | Female | dumat@rosh.md.us      |
| Harriette  | Sans         | Female | sansh@rosh.md.us      |
| Bernadette | Howerson     | Female | howb@rosh.md.us       |
| Judith     | Steinberg    | Female | steinbergj@rosh.md.us |
| Ella       | Napolis      | Female | napolise@rosh.md.us   |
| Ann        | Miller       | Female | millern@rosh.md.us    |
| Millicent  | Broadskey    | Female | broadskeym@rosh.md.us |
| Victoria   | Milchen      | Female | milchenv@rosh.md.us   |
| Martine    | Quarles      | Female | quarlesm@rosh.md.us   |
| Julie      | Laurens      | Female | laurensj@rosh.md.us   |
| Martha     | Bastens      | Female | bastensm@rosh.md.us   |
| Paul       | Marlly       | Female | marllyp@rosh.md.us    |
| Mincy      | Franse       | Female | fransem@rosh.md.us    |
| Suzanna    | Verde        | Female | verdes@rosh.md.us     |
| Ruby       | DeGaram      | Female | degaramr@rosh.md.us   |
| Antoinette | Clarck       | Female | clarcka@rosh.md.us    |
| Catherine  | Chang        | Female | changc@rosh.md.us     |
| Suzie      | Hoak         | Female | hoaks@rosh.md.us      |
| Carole     | Chance       | Female | chancec@rosh.md.us    |
| Jeannette  | Hutchins     | Female | hutchinsj@rosh.md.us  |
| Sherryl    | Ashburn      | Female | ashburns@rosh.md.us   |
| Brenda     | Lobo         | Female | lobob@rosh.md.us      |
| Janet      | West         | Female | westj@rosh.md.us      |
| Martin     | Davis        | Male   | davism@rosh.md.us     |
| Arthur     | Junger       | Male   | jungera@rosh.md.us    |
| Koko       | Domba        | Male   | dombak@rosh.md.us     |
| Danilo     | Chico        | Male   | chicod@rosh.md.us     |
| Nehemiah   | Dean         | Male   | deann@rosh.md.us      |
| George     | Orion        | Male   | oriong@rosh.md.us     |
| Albert     | Linken       | Male   | linkena@rosh.md.us    |
| Ralph      | Hagers       | Male   | hagersr@rosh.md.us    |
| Arthur     | Milley       | Male   | milleya@rosh.md.us    |
| Charles    | Edelman      | Male   | edelmane@rosh.md.us   |
| Anselme    | Waters       | Male   | watersa@rosh.md.us    |
| Ismael     | Zara         | Male   | zarai@rosh.md.us      |
| Justin     | Vittas       | Male   | vittasj@rosh.md.us    |
| Paul       | Farms        | Male   | farmsp@rosh.md.us     |
| Lester     | Bell         | Male   | belll@rosh.md.us      |
| Christian  | Liss         | Male   | lissc@rosh.md.us      |
| Sebastien  | Porter       | Male   | porters@rosh.md.us    |
| Clint      | Fuller       | Male   | clintf@rosh.md.us     |
| Thomas     | Moore        | Male   | mooret@rosh.md.us     |
| Dean       | Chen         | Male   | chend@rosh.md.us      |
| Tim        | Amorros      | Male   | amorrost@rosh.md.us   |
| Mohamed    | Husseini     | Male   | husseinim@rosh.md.us  |
| Santos     | Pacheco      | Male   | pachecos@rosh.md.us   |
| Maurice    | Walken       | Male   | walkenm@rosh.md.us    |
| Charles    | Laurel       | Male   | laurelc@rosh.md.us    |
+------------+--------------+--------+-----------------------+
52 rows in set (0.01 sec)

mysql>

  3. To list all students arranged in alphabetical order by their last name, execute the following the statement:
      
    SELECT *
FROM Students
ORDER BY LastName

  4. By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order including the first and last names, execute the following statement:
      
    SELECT *
FROM Students
ORDER BY LastName ASC

  5. On the other hand, if you want to sort records in reverse order, you can use the DESC keywords instead. It produces the opposite result to the ASC effect. As an example, execute the following statement:

    SELECT LastName, FirstName, Gender, HomePhone, EmailAddress
FROM Students
ORDER BY LastName DESC

  6. Notice the result

Introduction to Conditions

Instead of displaying all data as we have done so far using the SELECT keyword, you can present a condition that the database would follow to isolate specific records. One of the keywords you can use to formulate conditions is WHERE. Its basic syntax is: 

SELECT What FROM WhatObject WHERE Expression;

The expressions used in conditions are built using algebraic, logical, and string operators. The Expression factor is called a criterion. Although a group of expressions, making it plural is called criteria, the word criteria is sometimes used for a singular expression also.

The expression is written using the formula: 

ColumnName=Value

The ColumnName factor must be an existing column of the table. It is followed by the assignment operator. The Value factor is the value that would set the condition. If the value is a word or a group of words (also called a string), you must include it in single-quotes. If it is a number, you can type its numeric value.

Practical LearningPractical Learning: Using the WHERE Condition

  1. To get a list of students who live in Maryland, execute the following statement:
    SELECT *
FROM Students
WHERE State='MD'

  2. To get a list of girls with their names and email addresses, you would execute the following statement:
      
    SELECT LastName, FirstName, Gender, EmailAddress
FROM Students
WHERE Gender='Female'
ORDER BY LastName

  3. Notice the result
  4. Type Exit and press Enter to end the lesson
 
Previous Copyright © 2007, Yevol Next