SQL Expressions

The definition of an expression is simple: An expression returns a value. Expression types are very broad, covering different data types such as String, Numeric, and Boolean. In fact, pretty much anything following a clause (SELECT or FROM, for example) is an expression. In the following example amount is an expression that returns the value contained in the amount column.

SELECT amount FROM checks;

In the following statement NAME, ADDRESS, PHONE and ADDRESSBOOK are expressions:

SELECT NAME, ADDRESS, PHONE

FROM ADDRESSBOOK;

Now, examine the following expression:

WHERE NAME = 'BROWN'

It contains a condition, NAME = 'BROWN', which is an example of a Boolean expression. NAME = 'BROWN' will be either TRUE or FALSE, depending on the condition =.

Conditions

If you ever want to find a particular item or group of items in your database, you need one or more conditions. Conditions are contained in the WHERE clause. In the preceding example, the condition is

NAME = 'BROWN'

To find everyone in your organization who worked more than 100 hours last month, your condition would be

NUMBEROFHOURS > 100

Conditions enable you to make selective queries. In their most common form, conditions comprise a variable, a constant, and a comparison operator. In the first example the variable is NAME, the constant is 'BROWN', and the comparison operator is =. In the second example the variable is NUMBEROFHOURS, the constant is 100, and the comparison operator is >. You need to know about two more elements before you can write conditional queries: the WHERE clause and operators.

The WHERE Clause

The syntax of the WHERE clause is

SYNTAX:

WHERE <SEARCH CONDITION>

SELECT, FROM, and WHERE are the three most frequently used clauses in SQL. WHERE simply causes your queries to be more selective. Without the WHERE clause, the most useful thing you could do with a query is display all records in the selected table(s). For example:

INPUT:

SQL> SELECT * FROM BIKES;

lists all rows of data in the table BIKES.

OUTPUT:

NAME           FRAMESIZE COMPOSITION  MILESRIDDEN TYPE

-------------- --------- ------------ ----------- -------

TREK 2300           22.5 CARBON FIBER        3500 RACING

BURLEY                22 STEEL               2000 TANDEM

GIANT                 19 STEEL               1500 COMMUTER

FUJI                  20 STEEL                500 TOURING

SPECIALIZED           16 STEEL                100 MOUNTAIN

CANNONDALE          22.5 ALUMINUM            3000 RACING

6 rows selected.

If you wanted a particular bike, you could type

INPUT/OUTPUT:

SQL> SELECT *

     FROM BIKES

     WHERE NAME = 'BURLEY';

which would yield only one record:

NAME           FRAMESIZE COMPOSITION    MILESRIDDEN TYPE

-------------- --------- -------------- ----------- -------

BURLEY                22 STEEL                 2000 TANDEM

ANALYSIS:

This simple example shows how you can place a condition on the data that you want to retrieve.