Basic SELECT statement

The format and syntax shown here are very basic. SELECT statements can be more varied than the examples presented in this topic.

You can write SQL statements on one line or on many lines. For SQL statements in precompiled programs, the rules for the continuation of lines are the same as those of the host language (the language the program is written in). A SELECT statement can also be used by a cursor in a program. Finally, a SELECT statement can be prepared in a dynamic application.

Notes:

The SQL statements described in this section can be run on SQL tables and views, and database physical and logical files.
Character strings specified in an SQL statement (such as those used with WHERE or VALUES clauses) are case sensitive; that is, uppercase characters must be entered in uppercase and lowercase characters must be entered in lowercase.
```
WHERE ADMRDEPT='a00'     (does not return a result)
 
WHERE ADMRDEPT='A00'     (returns a valid department number)
```
Comparisons may not be case sensitive if a shared-weight sort sequence is being used where uppercase and lowercase characters are treated as the same character.

A SELECT statement can include the following:

The name of each column you want to include in the result.
The name of the table or view that contains the data.
A search condition to identify the rows that contain the information you want.
The name of each column used to group your data.
A search condition that uniquely identifies a group that contains the information you want.
The order of the results so a specific row among duplicates can be returned.

A SELECT statement looks like this:

   SELECT column names
     FROM table or view name
     WHERE search condition
     GROUP BY column names
     HAVING search condition
     ORDER BY column-name

The SELECT and FROM clauses must be specified. The other clauses are optional.

With the SELECT clause, you specify the name of each column you want to retrieve. For example:

   SELECT EMPNO, LASTNAME, WORKDEPT

You can specify that only one column be retrieved, or as many as 8000 columns. The value of each column you name is retrieved in the order specified in the SELECT clause.

If you want to retrieve all columns (in the same order as they appear in the table's definition), use an asterisk (*) instead of naming the columns:

   SELECT *

The FROM clause specifies the table that you want to select data from. You can select columns from more than one table. When issuing a SELECT, you must specify a FROM clause. Issue the following statement:

SELECT *
       FROM EMPLOYEE

The result is all of the columns and rows from table EMPLOYEE.

The SELECT list can also contain expressions, including constants, special registers, and scalar fullselects. An AS clause can be used to give the resulting column a name. For example, issue the following statement:

SELECT LASTNAME, SALARY * .05 AS RAISE
       FROM EMPLOYEE
       WHERE EMPNO = '200140'

The result of this statement is:

Table 1. Results for query
LASTNAME	RAISE
NATZ	1421