iSelfSchooling - Oracle Syntax Statements

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.

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)]

WHERE search_conditions
A 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_list
The GROUP BY clause partitions the result set into groups.
The group_by_list may be one or more columns or expressions and may optionally include the CUBE / ROLLUP keywords for creating crosstab results.

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 spacify NOWAIT)

FOR UPDATE [OF [ [schema.]{table|view}.] column] [NOWAIT]

Writing a SELECT statement

The clauses (SELECT ... FROM ... WHERE ... HAVING ... ORDER BY ... ) must be in this order.

SELECT command { UNION | UNION ALL | INTERSECT | MINUS } SELECT command ]

INSERT Syntax Statement

INSERT [hint] INTO [schema.] table [@dblink] [t_alias] (column, column,...)

VALUES (expression)

INSERT [hint] INTO [schema.] table

[[SUB]PARTITION (ptn_name)] [t_alias] (column, column,...)

VALUES (expression)

INSERT [hint] INTO subquery

WITH [READ ONLY | CHECK OPTION [CONSTRAINT constraint] ]

[t_alias] (column, column,...)

VALUES (expression)

VALUES(expression) can be expanded to

VALUES ([expr, expr...] [subquery])

[RETURNING expr, expr... INTO host_variable|plsql_variable]

UPDATE Syntax Statement

UPDATE [hint] [schema.]table [@dblink] [alias]

[ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ]

SET col_expr(s)

[WHERE condition]

[ RETURNING (expr,...) INTO (data_item,...) ]

UPDATE [hint] [schema.]table [[SUB]PARTITION partition] [alias]

[ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ]

SET col_expr(s)

[WHERE condition]

[ RETURNING (expr,...) INTO (data_item,...) ]

UPDATE [hint] [schema.]view [@dblink] [alias]

[ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ]

SET col_expr(s) [WHERE condition]

UPDATE [hint] [schema.]snapshot [@dblink] [alias]

[ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ]

SET col_expr(s) [WHERE condition]

col_expr:

column = expression

column = (subquery)

(column, column,...) = (subquery)

VALUE (table_alias) = expression

VALUE (table_alias) = (subquery)

To update multiple columns, separate col_expr with commas. The terms "snapshot" and "materialized view" are synonymous.