Friday, 13 July 2012



Retrieve data from one or more tables, views, or snapshots.

   SELECT [hint][DISTINCT] select_list
   INTO {variable1, variable2... | record_name}
   FROM table_list
   [WHERE conditions]
   [GROUP BY group_by_list]
   [HAVING search_conditions]
   [ORDER BY order_list [ASC | DESC] ]
   [FOR UPDATE for_update_options]
A comma-separated list of table columns (or expressions) eg:
column1, column2, column3 
table.column1, table.column2
table.column1 Col_1_Alias, table.column2 Col_2_Alias
schema.table.column1 Col_1_Alias, schema.table.column2 Col_2_Alias
expr1, expr2
In the select_lists above, 'table' may be replaced with view or snapshot.
Using the * expression will return all columns.
If a Column_Alias is specified this will appear at the top of any column headings in the query output.
Supress duplicate rows - display only the unique values.
Duplicate rows have matching values across every column (or expression) in the select_list.

A list of previously defined variables - there must be one variable for each item SELECTed - the results of the select statement are stored in these variables.
When SELECTing data INTO variables in this way the query must return ONE row.
FROM table_list
Contains a list of the tables from which the result set data is retrieved.
[schema.]{table | view | snapshot}[@dblink] [t_alias]
When selecting from a table you can also specify Partition and/or Sample clauses e.g.
[schema.]table [PARTITION (partition)] [SAMPLE (sample_percent)]
If the SELECT statement involves more than one table, the FROM clause can also contain join specifications (SQL1992 standard).

WHERE search_conditionsA filter that defines the conditions each row in the source table(s) must meet to qualify for the SELECT. Only rows that meet the conditions will be included in the result set. The WHERE clause can also contain inner and outer join specifications (SQL1989 standard). e.g.
WHERE tableA.column = tableB.column
WHERE tableA.column = tableB.column(+)
WHERE tableA.column(+) = tableB.column

GROUP BY group_by_listThe GROUP BY clause partitions the result set into groups.
The rows in each group having a unique value in group_by_list.
The group_by_list may be one or more columns or expressions and may optionally include the CUBE / ROLLUP keywords for creating crosstab results.
(note the groups will be in random order unless you additionally specify ORDER BY)
For example, the Order_Items table contains:
oi_shipping   oi_value   oi_units
ldn           89.75      2
ny            12.99      1
ldn           55.15      4
edi           23.00      6
A GROUP BY oi_shipping will partition the result set into the three groups: ldn, ny, edi
Heirarchical Queries
Any query that does *not* include a GROUP BY clause may include a CONNECT BY heirarchy clause:
[START WITH condition] CONNECT BY condition

HAVING search_conditions
An additional filter - the HAVING clause acts as an additional filter to the grouped result rows - as opposed to the WHERE clause that applies to individual rows. The HAVING clause is most commonly used in conjunction with a GROUP BY clause.

ORDER BY order_list [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted ascending (1...9 a...z) or descending (9...1 z...a).
You can sort by any column even if that column is not actually in the main SELECT clause. If you do not include an ORDER BY clause then the order of the result set rows will be unpredictable (random or quasi random).

FOR UPDATE options - this locks the selected rows (Oracle will normally wait for a lock unless you specify NOWAIT)

Cursors are often used with a SELECT FROM ... FOR UPDATE [NOWAIT]
Specifying NOWAIT will exit with an error if the rows are already locked by another session.
Because the locks are not released until the end of the transaction you should not 'commit across fetches' from an explicit cursor if FOR UPDATE is used.
FOR UPDATE [OF [ [schema.]{table|view}.] column] [NOWAIT]

   CURSOR ... IS
      SELECT ..FROM...WHERE...
Writing a SELECT statement
The clauses (SELECT ... FROM ... WHERE ... HAVING ... ORDER BY ... ) must be in this order.
The position of commas and semicolons is not forgiving
Each expression must be unambiguous. In other words if the FROM clause includes 2 columns with the same name, then both column names must be prefixed with the tablename (or view name).
        customers.customer_id = order_items.customer_id
        AND order_items.oi_ship_date > '01-may-2001';
If the table and view names themselves must be qualified with the schema (scott.t_customers.customer_id,) then this can become rather verbose. This syntax can be greatly simplified by assigning a table_alias (sometimes also known as a range variable or correlation name).

The fully qualified table has to be specified only in the FROM clause. All other table or view references can then use the alias name. e.g.
        customers cst
        order_items ord
        cst.customer_id = ord.customer_id
        AND ord.oi_ship_date > '01-may-2001';
Ambiguous expressions can also be avoided through the use of an appropriate naming convention.
"Computers are useless. They only give you answers" - Pablo Picasso

No comments:

Post a Comment