----------  Select  ----------

This command retrieves information from one or more tables.  There  are  three
main variations:

1) Regular Selects.

Syntax:

a)   select [|bypass     |] [ |distinct| ] [ |*                          | ]
             |bypass_lock|    |all     |     |SELECT_ITEM {, SELECT_ITEM}|

       from TABLE_EXPRESSION {, TABLE_EXPRESSION}

               [|list  |] [dump] [|into| FILE]
                |report|          |onto|

               [ WHERE_CLAUSE ]

               [ group by ATTR {, ATTR} ]

               [ HAVING_CLAUSE ]

               [ SORT_CLAUSE ];

b)   QUERY_EXPRESSION [|list  |] [dump] [|into| FILE] [SORT_CLAUSE];
                       |report|          |onto|

QUERY_EXPRESSION contains one or more SELECT_COMMANDs/EXPLICIT_TABLE combined
by set operator. Empress supports UNION, UNION ALL, EXCEPT, EXCEPT ALL,
INTERSECT and INTERSECT ALL operations, which are based on the well-known
union, difference, and intersection operation of set theory. The two tables that
represent the direct operands of union, difference, or intersection must be of
the same degree and same data type.

The QUERY_EXPRESSION may be:

    | SELECT_COMMAND | { | union     | [all]  | SELECT_COMMAND | }
    | EXPLICIT_TABLE |   | except    |        | EXPLICIT_TABLE |
                         | intersect |

or

   '(' QUERY_EXPRESSION ')'

The SELECT_COMMAND is:

    select [|bypass     |] [ |distinct| ] [ |*                          | ]
            |bypass_lock|    |all     |     |SELECT_ITEM {, SELECT_ITEM}|

        from TABLE_EXPRESSION {, TABLE_EXPRESSION }

               [ WHERE_CLAUSE ]

               [ group by ATTR {, ATTR} ]

               [ HAVING_CLAUSE ]

The EXPLICIT_TABLE is:

    table TABLE

It equals syntactically to:

    (select * from TABLE)


SELECT_ITEM is one of:

a)   count [ |(*)              | ] [PRINT_CLAUSE]
             |([distinct] EXPR)|

     The "count" function prints out the number of records retrieved.  "*"  is
     used  to  count  all  retrieved records.  If "distinct" is used, all non-
     duplicate values are retrieved and counted.

b)   FUNCTION ([distinct] ATTR)  [PRINT_CLAUSE]

     where FUNCTION is one of: avg, max, min, sum

     The FUNCTION is applied to the values of the specified attribute and  the
     result  printed.  The functions "sum" and "avg" apply only to numeric at-
     tributes.  If "distinct" is specified, duplicate values are excluded.

c)   EXPR [PRINT_CLAUSE]

d)   SCALAR_SUBQUERY [PRINT_CLAUSE]

e)   TABLE.* [PRINT_CLAUSE]

f)   ALIAS.* [PRINT_CLAUSE]


A PRINT_CLAUSE is:

    print HEADER [width INTEGER] [|left     |] [wrapmargin INTEGER]
                                  |right    |
                                  |center   |
                                  |centre   |
                                  |leftright|

A TABLE_EXPRESSION is one of:

    a) TABLE [ [ | alias | ] ALIAS ]
                 | as    |

    b) ( QUERY_EXPRESSION ) ALIAS

    c) TABLE_EXPRESSION  cross  join   TABLE_EXPRESSION

    d) TABLE_EXPRESSION natural | inner         | join TABLE_EXPRESSION
                                | left  [outer] |
                                | right [outer] |
                                | full  [outer] |

    e) TABLE_EXPRESSION  | inner         | join TABLE_EXPRESSION
                         | left  [outer] |
                         | right [outer] |
                         | full  [outer] |

             [ | on WHERE_CLAUSE_CONDITION         | ]
               | using ( COLUMN_ID {, COLUMN_ID} ) |


A SORT_CLAUSE is:

    |sort | [|by|] |COLUMN_ID | [|ascending |] {, |COLUMN_ID | [|ascending |]}
    |order|  |on|  |COLUMN_NUM|  |asc       |     |OLUMN_NUM |  |asc       |
                                 |descending|                   |descending|
                                 |desc      |                   |desc      |

The keywords "asc" and "desc" are synonyms for "ascending"  and  "descending",
respectively.  The default order is ascending.

The COLUMN_ID or COLUMN_NUM of the SORT_CLAUSE of a set operation refers to
corresponding selected columns of the first SELECT_COMMAND operand of the set
operation.

The SELECT retrieves the contents of the TABLEs, and if "list" or  "dump"  are
not  specified,  prints  the selected items in columns.  The names of selected
attributes are printed out above each  column  of  values.   Alternate  column
headings may be printed by specifying a character string for HEADER.

"TABLE [alias] ALIAS" may be used to replace one or  more  of  the  tables
from which data is selected.  Any other instances of that TABLE in the command
may be replaced by the ALIAS.

The "dump" keyword prints the attribute values for each record (or combination
of  records)  on one line, separated by a special separator character (usually
CTRL-V).  The "list" keyword prints the attribute  values  on  separate  lines
preceded  by the attribute name (or user specified HEADER, see above); a blank
line separates records.  Specifying both "list" and  "dump"  prints  attribute
values  on  separate lines with no headings.  The "report" option is used with
"dump" to preface the "dump" output with information about the table  and  at-
tributes.

The output of a "select" command may be diverted from the terminal into a  new
file  or  appended to an existing file by specifying "into" or "onto", respec-
tively, and a filename.

A WHERE clause retrieves only those  records  satisfying  its  conditions.   A
"group  by"  clause forms groups of records having the same values for the at-
tributes named in the clause.  It causes an implicit sort on those attributes.
A  HAVING clause restricts output to those groups satisfying the conditions in
the clause.  A HAVING clause can only be used when every selected attribute is
either  grouped, a function, or a constant.  A SORT clause sorts the output by
the attributes named in it.  If groups are formed in the  command  sorting  is
done within the groups.

The keyword "unique" is a synonym for "distinct".  The keyword may appear only
once in the command.  If any "unique" or "distinct" attributes are requested a
sort on those attributes is done.  This sort  follows  the  implicit  sort  on
groups and precedes sorting requested in a SORT clause.

2)  Selects into Tables.

Syntax:

   QUERY_EXPRESSION [ SORT_CLAUSE ]

        insert [into] TABLE [| (*)                         |];
                             | set *                       |
                             | set COLUMN_ID {, COLUMN_ID} |
                             | (COLUMN_ID {, COLUMN_ID})   |


The "insert into TABLE" may also be placed before the "select", "table", or
QUERY_EXPRESSION. (e.g.  "insert into  TABLE  select  from  TABLE...".)  If
TABLE does not already exist, it is created with attributes of the same names 
and data types.  If the  "group  by" clause is used, it must produce only rows
as output.

3) Nested Selects.

In a WHERE clause the right side  of  an  expression  may  be  replaced  by  a
SUBQUERY containing a nested select.

A SUBQUERY is:

[| any |] (QUERY_EXPRESSION)
 | all |

For further information, enter:

        help expr;
        help having_clause;
        help insert;
        help scalar_subquery
        help sort;
        help where_clause;
