The SELECT statement is the DML one providing a way for a record reading. If you want to read record from a table or a view, you must select criteria defining records you want to read. There are also additional specifications allowing you to define the sorting, null value representation, and etc. Determining the select criteria defines new virtual table, called view, and all records from this virtual table are read.
Syntax of the SELECT statement
SELECT [DISTINCT|ALL] *|{attr_name[ AS new_attr_name]} FROM {table_or_view[ alias]}
[ WHERE where_criteria]
[ GROUP BY {attributes}[ HAVING condition]]
[ ORDER BY {attrname|attrorder [ASC|DESC]}]
[ INTERSECT|UNION[ ALL] SELECT_statement]
Comments
| DISTINCT|ALL | Determines whether to read all records or to discard all duplicities, so each record is the unique one. If DISTINCT is not used, ALL is presumed and used. |
| * | Marks all attributes of all tables and views to be read. |
| attr_name | Name of attribute to be read. The attribute name may be preceded with a table/view name or its alias followed by a dot. There can be also a name of an aggregation function; see below for more details. |
| AS new_attr_name | Allows new name definition for the attr_name. |
| table_or_view | A list of used tables and views used as records' sources. |
| alias | Allows an abbreviation definition for a table/view within an entire statement scope. |
| WHERE where_criteria | Allows definition determining, which records will be selected. See the chapter WHERE for more details. |
| GROUP BY {attributes} | Allows a record grouping by columns. |
| HAVING condition | Allows a record group selection based on used condition. See the chapter WHERE for more details about condition defining. For the HAVING clause newly defined names of attributes can not be used. |
| ORDER BY {..} | Allows records to be sorted by their values. Attributes used for a sorting must represented as a comma separated list of their names or ordinal numbers. |
| INTERSECT | Allows making intersection between two selects. |
| UNION[ ALL] | Allows uniting of two selects. [ALL] gives option to discard duplicities. |
| SELECT_statement | The next SELECT statement for INTERSECT and UNION. |
These functions may not be used in the where clause!
Each of following functions, except COUNT and NVL, takes as an argument an expression computing a value from one or more attributes. This expression is evaluated for each record.
| COUNT() | Returns a count of all records. There can be used * and DISTINCT in the argument. |
| NVL(,) | For a specified attribute (the first argument) returns custom value (the second argument) in the case of the null one. |
| SUM() | Returns the sum of values generated by its argument. |
| AVG() | Returns the average value of values generated by its argument. |
| MIN() | Returns the minimum value of values generated by its argument. |
| MAX() | Returns the maximum value of values generated by its argument. |