HOWTO - SQL92 Syntax

SQL92 Syntax and common or <Oracle> additions (not = '!')

Types:

	CHAR(n) | CHARACTER(n)
	VARCHAR(n) | CHARACTER VARYING(n) | <VARCHAR2(n)>
	INTEGER | INT | SMALLINT
	DECIMAL(p,s) | DEC(p,s) | NUMERIC(p,s)
	FLOAT(p) | REAL | DOUBLE PRECISION
	DATE | TIME
	INTERVAL year-month | INTERVAL day
	BOOLEAN | BLOB

Conditionals:
< > <= >= <> = AND OR NOT IS [NOT] NULL [NOT] LIKE [NOT] IN ( [,...] ) [NOT] BETWEEN x AND y [conditional] ANY ( [,...] ) [conditional] ALL ( [,...] )

Functions:

	AVG | MAX | MIN | SUM | COUNT
	GREATEST|LEAST(x,y,...)
	<{ROUND|TRUNC<!ATE>}({x,places|date,format})>
	POSITION( s1 IN s2)
	EXTRACT( datetime FROM datetime_value)
	CHAR_LENGTH( s1 ) <LENGTH( s1 )>
	SUBSTRING(string FROM start [FOR length])|<SUBSTR(string,start,length)>
	<INSTR(str,substr,start,mnth)>
	{<INITCAP>|UPPER|LOWER}(string)
	TRIM({BOTH|LEADING|TRAILING} char FROM string)|<{L|R}TRIM(str,chrset)>
	{TRANSLATE|CONVERT}( char USING value) |<TRANSLATE(str,from,to)>
	<{L|R}PAD(str,to_len,str2)>
	<DECODE(expr,search1,result1,...[,default])>
	<NVL(expr,replace)>

<Date Format - ROUND\|TRUNC\|TO_CHAR\|TO_DATE(value,fmt)>:
SYYYY\|YYYY\|YEAR\|SYEAR\|YY\|IYYY\|RR\|RRRR MONTH\|MON\|MM\|RM DDD\|DD\|J DAY\|DY\|D HH\|HH12\|HH24 MI SS\|SSSSS IW AM\|PM BC Q WW(year week) W (month week)

Table Constraints:
[CONSTRAINT cname] {{UNIQUE\|PRIMARY KEY}(col,...)\| CHECK(condition)\|FOREIGN KEY (col,...) REFERENCES table(col,...)}

Column Constraints:
[CONSTRAINT cname] {[NOT] NULL\|UNIQUE\|PRIMARY KEY\| REFERENCES table(col,...) ON DELETE CASCADE\|CHECK(condition)}

Command: ALTER TABLE

Description: Modifies table properties

	ALTER TABLE table [ * ]
	    ADD [<!COLUMN>] column type
	ALTER TABLE table [ * ]
	    DROP [ COLUMN ] column
	ALTER TABLE table [ * ]
	    MODIFY [<!COLUMN>] column { <!SET> DEFAULT value | DROP DEFAULT }
	ALTER TABLE table [ * ]
	    MODIFY [<!COLUMN>] column column_constraint
	ALTER TABLE table [ * ]
	    RENAME [<!COLUMN>] column TO newcolumn
	ALTER TABLE table
	    RENAME TO newtable
	ALTER TABLE table
	    ADD table_constraint
	ALTER TABLE table
	    {ENABLE|DISABLE} {NO}VALIDATE CONSTRAINT constraint

Command: `ALTER USER`
Description: Set a user password
ALTER USER username IDENTIFIED BY passwd

Command: CREATE TABLE

Description: Creates a new table

	CREATE <![TEMPORARY|TEMP]> TABLE table (
	    column type
	    [ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ]
	    [column_constraint_clause | PRIMARY KEY } [ ... ] ]
	    [, ... ]
	    [, PRIMARY KEY ( column [, ...] ) ]
	    [, CHECK ( condition ) ]
	    [, table constraint ]
	    )
	<CREATE TABLE table AS select query>

Command: (CREATE <OR REPLACE> TRIGGER) (not SQL92)

Description: Creates a new trigger

	CREATE TRIGGER name { BEFORE | AFTER |INSTEAD OF}
	    {DELETE| INSERT | UPDATE [OF (col,...)] [OR ...] }
	    ON {table|view} FOR EACH { ROW | STATEMENT }
	    [ WHEN (condition) ]
	    [<!EXECUTE PROCEDURE func ( arguments )>| pl/sql block]

Command: `CREATE SEQUENCE`	(not SQL92)
Description: Creates a new sequence number generator
CREATE SEQUENCE seqname [ INCREMENT BY increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache \| <WITH> NOCACHE ] [ CYCLE ] (use seqname.CURRVAL & seqname.NEXTVAL)

Command: CREATE INDEX (not SQL92)

Description: Constructs a secondary index

	CREATE [UNIQUE|<BITMAP>] INDEX index_name ON table
	    [<!USING acc_name>] ( column [ASC|DESC][,...]) [NOSORT|REVERSE]
	CREATE [ UNIQUE ] INDEX index_name ON table
	    [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )

Command: `(CREATE <OR REPLACE> VIEW)`
Description: Constructs a virtual table
CREATE VIEW view AS select query ALTER VIEW view COMPILE

Command: `CREATE SYNONYM`	(not SQL92)
Description: Create an alias for an object
CREATE SYNONYM synname FOR object

Command: `COMMENT`	(Oracle)
Description: Comment on objects and view in `USER_{TAB\|COL}_COMMENTS`
COMMENT ON TABLE table IS 'string' COMMENT ON COLUMN table.col IS 'string'

Command: `TRUNCATE TABLE`	(Oracle)
Description: Remove all table rows
TRUNCATE TABLE table

Command: `RENAME TABLE`	(Oracle)
Description: Rename the object
RENAME table TO newtable

Command: `DROP`
Description: Removes existing objects from database
DROP TABLE name [,...] <CASCADE CONSTRAINTS> DROP VIEW name DROP SEQUENCE name [,...] ...

Command: `INSERT`
Description: Inserts new rows into a table
INSERT INTO table [ ( column [, ...] ) ] { VALUES ( expression [, ...] ) \| SELECT query }

Command: `UPDATE`
Description: Replaces values of columns in a table
UPDATE table SET col = expression [,...] [ FROM fromlist ] [ WHERE condition ]

Command: `DELETE`
Description: Removes rows from a table
DELETE FROM table [ WHERE condition ]

Command: SELECT query

Description: Retrieve rows from a table or view

	SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
	    expression [ <![AS]> name ] [,...]
	    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
	    [ FROM {table | (select query)} [ alias ] [,...] ]
	    [ {{LEFT | RIGHT} [OUTER] | NATURAL |[FULL] OUTER} JOIN table alias
		{ON condition | USING(col1,col2,...)} ]
	    [ WHERE {condition | EXISTS (correlated subquery)} ]
	    [ GROUP BY column [,...] ]
	    [ HAVING condition [,...] ]
	    [ { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS } select ]
	    [ ORDER BY {column | int} [ ASC | DESC | USING operator ] [,...] ]
	    [ FOR UPDATE [ OF class_name [,...] ] ]
	    LIMIT { count | ALL } [ { OFFSET | ,} start ]

Command: `DECLARE`	(Oracle)
Description: Defines a cursor for table access
DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ] CURSOR FOR query [ FOR { READ ONLY \| UPDATE [ OF column [,...] ] ]

Command: `FETCH`	(Oracle)
Description: Gets rows using a cursor
FETCH [ selector ] [ count ] { IN \| FROM } cursor FETCH [ RELATIVE ] [{ [ # \| ALL \| NEXT \| PRIOR ] }] FROM cursor

Command: `CLOSE`	(Oracle)
Description: Close a cursor
CLOSE cursor

Last Modified: 2002/09/21 18:10:42

Brought to you by: R.K. Owen,Ph.D.
This page is http://owen.sj.ca.us/rkowen/howto/sql92.html