(Dr.R.K.) HOWTO - SQL92 Syntax

Go to Top Go to Bottom blank blank Go up one level R.K.'s Home Page Keyword Index Site Map Help Linux

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
© R.K. Owen, Ph.D. 2002 (expanded from PostGreSQL syntax)

Brought to you by: R.K. Owen,Ph.D.
This page is http://rkowen.owentrek.com/howto/sql92.html