ORACLE SQL SYNTAX BRIEFLY

This syntax representation covers only the select statement of Oracle SQL. Even that is not complete.

Select statement

SELECT [ALL | DISTINCT] select-list
   FROM table-reference-list
   [WHERE search-condition]
   [GROUP BY column-name [, column-name]... ]
   [HAVING search-condition]
   [[UNION | UNION ALL |INTERSECT | MINUS] 
   select-statement]...
   [ORDER BY {unsigned integer | column-name}
   [ASC|DESC]]

Query-specification

query-specification ::= 
   SELECT [DISTINCT | ALL] select-list table-expression
select-list ::= 
   * | select-sublist [ {, select-sublist} ... ]
select-sublist ::= 
   derived-column |
   [table-name | table-identifier].*
derived-column ::= 
   expression [ [AS] column-alias] ]
table-expression ::= 
   FROM table-reference-list
   [WHERE search-condition]
   [GROUP BY column-name-list
   [ [UNION | UNION ALL | INTERSECT | MINUS] 
     query-specification]
   [HAVING search-condition]

Table-reference

table-reference-list ::= 
    table-reference [ , table-reference … ]
table-reference ::= 
    table-name [[AS] correlation-name] |
    derived-table [[AS] correlation-name [( derived-column-list )]]
table-name ::= 
    table-identifier | schema-name.table-identifier
derived-table ::= 
    subquery
derived-column-list ::= 
    column-name-list
column-name-list ::= 
    column-identifier [ { , column-identifier} …]

Search-condition

search-condition ::= 
    search-item | search-item { AND | OR } search-item
search-item ::= 
    [NOT] { search-test | (search-condition) }
search-test ::= 
    comparison-test | between-test | like-test | 
    null-test | set-test | quantified-test | existence-test
comparison-test ::= 
    single_test | list_test
single_test ::=
    expression  { = | <> | != | < | <= | > | >= } { expression | subquery }
list_test ::= 
    expression_list {= | <> | != } subquery
expression_list ::=
    ( expression  [, expression  ... ] ) 
between-test ::= 
    column-identifier [NOT] BETWEEN expression  AND expression 
like-test ::= 
    column-identifier [NOT] LIKE value [ESCAPE value]
null-test ::= 
    column-identifier IS [NOT] NULL
set-test ::= 
    expression  [NOT] IN ( { value [,value]... | subquery } )
quantified-test ::= 
    qsingle_test | qlist_test 
qsingle_test ::=
    expression  { = | <> | != | < | <= | > | >= } [ALL | ANY | SOME] subquery
qlist_test ::=
    expression_list { = | <> | != } [ALL | ANY | SOME]  { subquery | exp_list_seq }
exp_list_seq ::= 
    ( expression_list [ , expression_list ... ] )
existence-test ::= 
    EXISTS subquery
subquery ::= (query-specification)

Expression

expression ::= 
   expression-item | expression-item { + | - | * | / | || } expression-item
expression-item ::= 
    [ + | - ] { value | column-identifier | function | case-expression
        | ( expression ) }
value ::= literal | USER | variable
function ::= 
    set-function | string-function | 
    numeric-function | datetime-function | system-function | 
    datatypeconversion-function
set-function ::= 
    COUNT (*) |
    { AVG | MAX | MIN | SUM | COUNT }( { ALL | DISTINCT } expression )
datatypeconversion-function ::= 
   {TO_NUMBER(expression, format) | TO_CHAR(expression, format) | 
    TO_DATE(expression,format) | ...}

   Date conversions (when settings are Finnish), only some examples
   lets assume adate ='11.2.2000:15.30.12'

      to_char(adate,'DD.MM.YYYY') = 11.2.2000
      to_char(adate,'DD-MON-YYYY') =11-FEB-2000
      to_char(adate,'DAY DD MONTH"TA" YY') = PERJANTAI 11 HELMIKUU TA 00
      to_char(adate,'HH24.MI.SS')= 15.30.12

    Same formats can be used with to_date -function.

    Date -literals  DATE 'YYYY-MM-DD',  for example DATE '2000-08-20'.


case-expression ::=  
   NVL(expression, expression) | DECODE(expression {, expression}…)

	NVL(a,b) = 
          if a is null then b else a;
      DECODE(a,e1,v1,e2,v2,...,X) = 
          case a of
            when a=e1 then v1;
            when a=e2 then v2;
            ...
            else X
          end case;

String Functions

Function>	Description
LOWER(str)	Converts string str to lowercase
LENGTH(str)	Returns the length of a string
INSTR (str1, str2)	Returns starting position of str2 within str1
REPLACE(str1, str2[, str3])	Replaces occurrences of str2 in str1 with str3, if str3 is missing or null then occurences of str2 are removed
SUBSTR(str, start [,length])	Derives substring from str beginning at start
UPPER(str)	Converts str to uppercase

Numeric Functions

Function>	Description
ABS(numeric)	Absolute value of numeric
CEIL(numeric)	Smallest integer greater than or equal to numeric
FLOOR(numeric)	Largest integer less than or equal to numeric
MOD(integer1, integer2)	Modulus of integer1 divided by integer2
ROUND(numeric [, integer1])	Numeric rounded to the accuracy of integer1 decimals. If integer1 is missing it is assumed to be zero.
SIGN(numeric)	Sign of numeric (-1 or 1)
TRUNC(numeric [, integer])	Numeric truncated to the accuracy of integer1 decimals

Date and Time Functions

Function	Description
SYSDATE	Returns the current date and time
ADD_MONTHS(date,integer1)	Returns the a integer1 months away
LAST_DAY(date)	Returns the last date of the month the parameter date belongs to
MONTHS_BETWEEN(date1,date2)	Returns the number of month between date1 and date2
TO_CHAR(date,format)	Returns informatation within a datetime specified by format

System Functions

Function>	Description
NVL(exp, value)	If exp is null, returns value; if not, returns exp
USER()	Returns the user authorization name