SQL Language Syntax Summary

The syntax for the SQL statements that are implemented in RDM SQL is given below. Note that those items in red have not yet been implemented. Refer to "A Language for Describing a Language" for a description of how to read the syntax specification. C-style comments are explanatory and not part of the syntax.

SQL Syntax

RDM_SQL:
          ddl_stmts | dml_stmt

ddl_stmts:
          [create_schema]
     |    {RDM_DDL_stmt}...
     |    [persist_database]
     |    [commit_stmt]

create_schema:
          CREATE {SCHEMA | DATABASE} {database_name | db-uri}

persist_db_stmt:
          PERSIST DATABASE {database_name | db-uri}

dml_stmt:
          db_stmt | select_stmt | mod_stmt | trans_stmt | upd_stats_stmt | set_stmt
     |    create_procedure | create_function | drop_routine
     |    declare_simple_var
     |    asgt_stmt

db_stmt:
          open_db_stmt | close_db_stmt | init_db_stmt

mod_stmt:
          insert_stmt | update_stmt | delete_stmt | import_stmt | export_stmt

trans_stmt:
          start_stmt
     |    savepoint_stmt
     |    release_stmt
     |    commit_stmt
     |    precommit_stmt
     |    rollback_stmt

open_db_stmt:
          {OPEN DATABASE | USE} {database_name | db-uri} [open_mode]
     |    {OPEN DATABASE | USE} database_name AS UNION OF db-uri[, db-uri]...

open_mode:
          open_mode_spec
     |    IN open_mode_spec [MODE]

open_mode_spec:
          SHARE | EXCLUSIVE | READ ONLY

close_db_stmt:
          CLOSE DATABASE database_name

init_db_stmt:
          INITIALIZE [DATABASE] database_name

select_stmt:
          SELECT [FIRST | ALL | DISTINCT] select_item [, select_item]...
               FROM table_ref [, table_ref]...
               [WHERE conditional_expr]
               [grouping | sorting | grouping sorting]
               [LIMIT (num {ROWS | MINS | SECS | MSECS})]
               [FOR UPDATE [OF column_name [, column_name]...]]

grouping:
          GROUP BY sort_col[, sort_col]... [HAVING conditional_expr]

sorting:
          ORDER BY sort_col [ASC | DESC][, sort_col [ASC | DESC]]...

sort_col:
          num | column_var

select_item:
          named_expr
     |    [table_name.]*

named_expr:
          [HIDE] {column_var |  value_expr} [[AS] alias_name]

table_ref:
          table_spec | table_join

table_spec:
          [database_name.]table_name [[AS] correlation_name]
     |    (table_join)

table_join:
          natural_join | qualified_join | cross_join

natural_join:
          table_ref NATURAL [INNER | LEFT [OUTER]] JOIN table_spec

qualified_join:
          table_ref [INNER | LEFT [OUTER]] JOIN table_spec
               [USING (column_name [, column_name]...) | ON conditional_expr]

cross_join:
          table_ref CROSS JOIN table_spec

value_expr:
          arith_expr | dt_expr | string_expr

arith_expr:                                              /* involving only numeric operands and operations */
          operand [{+ | - | * | /} operand]…

dt_expr:                                                 /* involving only date/time/timestamp operands and operations */
          operand [{+ | -} operand]…

string_expr:                                            /* involving only string operands and operations */
          operand [ || operand]…

operand:
          constant
     |    param_ref
     |    variable_ref
     |    function_ref
     |    sub_query
     |    ( value_expr )

param_ref:
          ? | : param_name

variable_ref:
          global_var | routine_var | column_var

global_var:
          [@] var_name

routine_var:
          [routine_name. | block_name.]var_name

column_var:
          [{table_name | correlation_name}.]column_name

function_ref:
          aggregate_fcn | scalar_fcn

aggregate_fcn:
          calc_fcn_name ( [DISTINCT] arith_expr ) 
     |    COUNT{* | column_var })
     |    {EVERY | ANY | SOME} ( conditional_expr )
     |    {MIN | MAX | INVAR} (value_expr)
     |    agg_udf_name( [DISTINCT] value_expr)

calc_fcn_name:
          SUM | AVG | VAR_SAMP | VAR_POP | STDDEV_SAMP | STDDEV_POP

scalar_fcn:
          IF (conditional_expr, value_expr1, value_expr2)
     |    numeric_function | datetime_function | string_function | misc_function
     |    scalar_udf_name (value_expr1[, value_expr2]...)

numeric_function:
          abs(arith_expr)
     |    acos(arith_expr)
     |    asin(arith_expr)
     |    atan(arith_expr)
     |    atan2(arith_expr1, arith_expr2)
     |    {ceil | ceiling}(arith_expr)
     |    cos(arith_expr)
     |    cot(arith_expr)
     |    degrees(arith_expr)
     |    exp(arith_expr)
     |    floor(arith_expr)
     |    {ln | log}(arith_expr)
     |    mod(arith_expr1, arith_expr2)
     |    pi()
     |    power(arith_expr1, arith_expr2)
     |    rand(num)
     |    round(arith_expr)
     |    sign(arith_expr)
     |    sin(arith_expr)
     |    sqrt(arith_expr)
     |    tan(arith_expr)

datetime_function:
          age(dt_expr)
     |    {curdate | current_date}()
     |    {curtime | current_time}()
     |    {curts | current_timestamp}()
     |    dayofmonth(dt_expr)
     |    dayofweek(dt_expr)
     |    dayofyear(dt_expr)
     |    hour(dt_expr)
     |    minute(dt_expr)
     |    month(dt_expr)
     |    quarter(dt_expr)
     |    second(dt_expr)
     |    trunctime (dt_expr, tt_spec)
     |    week(dt_expr)
     |    year(dt_expr)

string_function:
          ASCII(string_expr)
     |    CHAR(num)
     |    {CHAR_LENGTH | CHARACTER_LENGTH}(string_expr)
     |    CONCAT(left_string_expr, right_string_expr)
     |    CONVERT(value_expr, {convert_type | { , width_num, convert_format})
     |    insstr(target_string_expr, offset_num, count_num, replacement_string_expr)
     |    lcase(string_expr)
     |    left(string_expr, count_num)
     |    length(string_expr)
     |    locate(match_string_expr, search_string_expr, startpos_num)
     |    ltrim(string_expr)
     |    octet_length(string_expr)
     |    position(match_string_expr, search_string_expr)
     |    repeat(string_expr, count_num)
     |    replace(string_expr, string_expr, string_expr)
     |    right(string_expr, count_num)
     |    rtrim(string_expr)
     |    substring(string_expr, startpos_num, length_num)
     |    trim(num, string_expr, string_expr)
     |    ucase(string_expr)
     |    unicode(string_expr)

misc_function:
          DATABASE()
     |    COALESCE(value_expr1, value_expr2, value_exprN)
     |    ROWNUMBER()
     |    IFNULL(value_expr1, value_expr2)
     |    TYPEOF(value_expr)

tt_spec:
          "1S" | "5S" | "10S" | "15S" | "20S" | "30S" | "1M"  | "5M" 
     |    "10M" | "15M" | "20M" | "30M" | "1H" | "3H" | "4H" 
     |    "6H" | "8H" | "12H"

convert_type:
          CHAR | TINYINT | SMALLINT | INTEGER | BIGINT | REAL | DATE | TIME | TIMESTAMP

convert_format:
          numeric_format | datetime_format

numeric_format:
          "[<< | >> | ><]['text' | $][- | (][#,]#[.#[#]...][e | E]['text' | $ | %]"

datetime_format:
          "[<< | >> | ><]['text' | spchar | date_code | time_code]..."

date_code:
          m | mm | mmm | mon | mmmm | month
     |    d | dd | ddd | dddd | day
     |    yy | yyyy

time_code:
          h | hh | m | mm | s | ss | .f[f]... | [a/p | am/pm | A/P | AM/PM]

conditional_expr:
          relational_expr [{ AND | OR} relational_expr]...

relational_expr:
          value_expr [NOT] rel_oper value_expr
     |    value_expr [NOT] BETWEEN constant AND constant
     |    value_expr [NOT] IN (constant [, constant]...) 
     |    column_ref  IS [[NOT]] 
     |    string_expr [NOT] NULL LIKE "string"
     |    [NOT] relational_expr
     |    ( conditional_expr )
     |    value_expr [NOT] rel_oper [{ANY | SOME} | ALL] sub_query
     |    value_expr [NOT]IN sub_query
     |    [NOT] EXISTS sub_query

rel_oper:
          =  |  ==                /* equal */
     |    <                         /* less than */
     |    >                         /* greater than */
     |    <=                       /* less than or equal */
     |    >=                       /* greater than or equal */
     |    <>  |  !=  |  /=       /* not equal */

sub_query:
          ( sub_select )

sub_select:
          SELECT {* | named_expr} FROM table_ref [, table_ref]...
               [WHERE conditional_expr] [grouping]

insert_stmt:
          INSERT INTO [database_name.]table_name [( column_name [, column_name]... )] data_source

data_source:
          VALUES insert_value_list
     |    [FROM] select_stmt

insert_value_list:
          [value_expr] [, [value_expr]]...
     |    ( [value_expr] [, [value_expr]]... )

update_stmt:
          UPDATE [database_name.]table_name [[AS] correlation_name] 
                 SET column_name = value_expr [, column_name = value_expr]... 
                 [WHERE {conditional_expr | CURRENT OF cursor_name}]

delete_stmt:
          DELETE [database_name.]table_name [[AS] correlation_name] 
               [WHERE {conditional_expr | CURRENT OF cursor_name}]

start_stmt:
          START TRANSACTION [transid_name] [transact_item [, transact_item]...]

transact_item:
          {READ {ONLY | WRITE} | UPDATE} [ON table_list]
     |    isolation level iso_level

table_list:
          database_name.]table_name [, [database_name.]table_name]...

iso_level:
          READ UNCOMMITTED
    |     READ COMMITED
    |     REPEATABLE READ
    |     SERIALIZABLE

savepoint_stmt:
          SAVEPOINT savepoint_id

release_stmt:
          RELEASE SAVEPOINT savepoint_id

rollback_stmt:
          ROLLBACK [WORK] [[TO SAVEPOINT] savepoint_id]

precommit_stmt:
          PRECOMMIT [WORK]

commit_stmt:
          COMMIT [WORK]

set_stmt:
          set_global
     |    set_transaction 
     |    set_default_open_mode
     |    set_storage_media
     |    set_option 
     |    set_stats

set_global:
          SET @var_name = value_expr
     |    SET (@var_name [, @var_name]... ) = ( value_expr [, value_expr]... )

set_transaction:
          SET TRANSACTION READ {ONLY | WRITE} [ISOLATION LEVEL iso_level]
     |    SET TRANSACTION AUTOCOMMIT [TO | =] {ON | OFF}

set_default_open_mode:
          SET [DEFAULT] OPEN MODE [TO | =] open_mode_spec

set_storage_media:
          SET STORAGE  [TO | =]
               {ONDISK | INMEMORY_VOLATILE | INMEMORY_KEEP | INMEMORY_PERSIST}

set_option:
          SET DATE FORMAT [TO | =] 
               {"YYYY-MM-DD" | "MM-DD-YYYY" | "DD-MM-YYYY"
              |  "YYYY/MM/DD" | "MM/DD/YYYY" | "DD/MM/YYYY"}
     |    SET TIME FORMAT [TO | =] "HH[:MM[:SS[:F[F[F[F]]]]]]"
     |    SET WILD ALL [TO | =] {"%" | "*"}
     |    SET WILD ONE [TO | =] {"_" | "." | "?"}
     |    SET TIMEOUT [TO | =] integer
     |    SET {DISTINCT | GROUP BY | ORDER BY} CACHE SIZE [TO | =] integer
     |    SET SORT COST FACTOR [TO | =] decimal
     |    SET IGNORE IMPORT ERROR [TO | =] {ON | OFF}

set_stats:
          SET {STATISTICS | STATS} 
          [ HISTOSIZE [=] num ]
          [ SAMPLING [=] (pct_num, minrows_num, maxrows_num) ]
          [ AUTORUN [=] (pct_num, minrows_num) ]
          [ THREADS [=] num]

create_procedure:
          CREATE PROCEDURE [database_name.]proc_name [(proc_arg_decl[, proc_arg_decl]...)]
          [LANGUAGE SQL] 
          [[NOT] DETERMINISTIC] 
          [CONTAINS SQL | {MODIFIES | READS} SQL DATA] 
          sqlproc_stmt

proc_arg_decl:
          [IN | OUT | INOUT] arg_name var_type

var_type:
          non_numeric_vartype | numeric_vartype

non_numeric_vartype:
          {CHAR | VARCHAR} [(length_num)]
     |    {BINARY | VARBINARY} (length_num)
     |    DATE | TIME | TIMESTAMP
     |    UUID
     |    ROWID

numeric_vartype:
          REAL | FLOAT | DOUBLE
     |    BOOLEAN | TINYINT | SMALLINT | INTEGER | BIGINT
     |    DECIMAL [(precision_num [, scale_num)]

create_function:
          CREATE FUNCTION [database_name.]func_name ( [fcn_arg_decl [, fcn_arg_decl]...] )
          RETURNS var_type
          [LANGUAGE SQL]
          [[NOT] DETERMINISTIC]
          [CONTAINS SQL | {MODIFIES | READS} SQL DATA]
          sqlproc_stmt

fcn_arg_decl:
          arg_name var_type

var_type:
          non_numeric_vartype | numeric_vartype

non_numeric_vartype:
          {CHAR | VARCHAR} [(length_num)]
     |    {BINARY | VARBINARY} (length_num)
     |    DATE | TIME | TIMESTAMP
     |    UUID
     |    ROWID

numeric_vartype:
          REAL | FLOAT | DOUBLE
     |    BOOLEAN | TINYINT | SMALLINT | INTEGER | BIGINT
     |    DECIMAL [(precision_num [, scale_num)]

create_function:
          CREATE FUNCTION [database_name.]func_name ( [fcn_arg_decl [, fcn_arg_decl]...] )
          RETURNS var_type
          [LANGUAGE SQL]
          [[NOT] DETERMINISTIC]
          [CONTAINS SQL | {MODIFIES | READS} SQL DATA]
          sqlproc_stmt

fcn_arg_decl:
          arg_name var_type

drop_routine:
          drop_proc | drop_func

drop_proc:
          DROP PROCEDURE proc_name

drop_func:
          DROP FUNCTION func_name

compound_stmt:
          [ label_name:] BEGIN [[[NOT]] ATOMIC | TRANSACTION]
               declarations
               statement_list
          END [ label_name]

declarations:
          [{declare_variable | declare_conditions} ;]...
     |    [declare_cursor ;]...
     |    [declare_handler ;]...

declare_variable:
          DECLARE var_name[, var_name]... var_type [DEFAULT constant]

declare_condition:
          DECLARE cond_name CONDITION [FOR SQLSTATE [VALUE] "string"]
     |    DECLARE cond_name CONDITION [FOR SQLCODE [VALUE] code_name]

declare_cursor:
          DECLARE cursor_name CURSOR [{WITH | WITHOUT} RETURN] FOR select_stmt

declare_handler:
          DECLARE {CONTINUE | EXIT | UNDO} HANDLER FOR condition_value_list sqlproc_stmt

condition_value_list:
          condition_value[, condition_value]...

condition_value:
          specific_condition | SQLEXCEPTION | SQLWARNING | NOT FOUND

specific_condition:
          cond_name
     |    FOR SQLSTATE [VALUE] "string"
     |    FOR SQLCODE [VALUE] {code_name | code_num}

statement_list:
          sqlproc_stmt ; [sqlproc_stmt ;]...

sqlproc_stmt:
          compound_stmt
     |    select_stmt
     |    update_stmt
     |    delete_stmt
     |    insert_stmt
     |    trans_stmt
     |    call_stmt
     |    asgt_stmt
     |    prepare_stmt
     |    execute_stmt
     |    deallocate_stmt
     |    open_stmt
     |    fetch_stmt
     |    getdiags_stmt
     |    control_stmt

call_stmt:
          CALL [database_name.]proc_name [(proc_arg[, proc_arg]...)]

proc_arg:
          value_expr | var_name

asgt_stmt:
          SET var_ref = value_expr 
     |    SET ( var_ref [, var_ref]... ) = ( value_expr [, value_expr]... )

var_ref:
          [@]var_name

control_stmt:
          if_stmt
     |    case_stmt
     |    loop_stmt
     |    while_stmt
     |    repeat_stmt
     |    for_stmt
     |    iterate_stmt
     |    leave_stmt
     |    signal_stmt
     |    resignal_stmt
     |    return_stmt

if_stmt:
          IF conditional_expr THEN statement_list
          [ELSEIF conditional_expr THEN statement_list]...
          [ELSE statement_list]
          END  IF

case_stmt:
          CASE value_expr
               {WHEN = value_expr  THEN statement_list}...
               [ELSE {OKAY | statement_list}]
          END CASE
     |    CASE
               {WHEN conditional_expr  THEN statement_list}...
               [ELSE {OKAY | statement_list}]
          END CASE

loop_stmt:
          [ label_name:] LOOP statement_list END  LOOP [ label_name]

while_stmt:
          [ label_name:] WHILE conditional_expr
               statement_list
          END WHILE [ label_name]

repeat_stmt:
          [ label_name:] REPEAT
               statement_list
          UNTIL conditional_expr END REPEAT [ label_name]

for_stmt:
          [ label_name:] FOR [for_loop_var_name AS] [cursor_name CURSOR FOR]
               select_stmt
               DO
                      statement_list
               END FOR [ label_name]

iterate_stmt:
          ITERATE  label_name

leave_stmt:
          LEAVE  label_name

getdiags_stmt:
          GET [CURRENT | STACKED] DIAGNOSTICS stmt_info[, stmt_info]...
     |    GET [CURRENT | STACKED] DIAGNOSTICS {EXCEPTION | CONDITION} cond_info[, cond_info}...

stmt_info:
          var_name = stmt_item

stmt_item:
          COMMAND_FUNCTION | COMMAND_FUNCTION_CODE
     |    DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE
     |    MORE | NUMBER | ROW_COUNT | TRANSACTION_ACTIVE

cond_info:
          var_name = cond_item

cond_item:
          RETURNED_SQLSTATE | RETURNED_SQLCODE | MESSAGE_LENGTH | MESSAGE_TEXT
     |    SQLCODE_NAME | CONDITION_IDENTIFIER | CONDITION_NUMBER
     |    CURSOR_NAME | ROUTINE_NAME | SOURCE_FILE
     |    STATEMENT_NUMBER | LINE_NUMBER | COLUMN_NUMBER

signal_stmt:
          SIGNAL specific_condition [SET MESSAGE_TEXT = "string"]

resignal_stmt:
          RESIGNAL specific_condition [SET MESSAGE_TEXT = "string"]

return_stmt:
          RETURN {value_expr | NULL}

prepare_stmt:
          PREPARE stmt_name FROM {var_name | string_expr}

execute_stmt:
          EXECUTE stmt_name [INTO var_name[, var_name]...]

deallocate_stmt:
          DEALLOCATE PREPARE stmt_name

open_stmt:
          OPEN cursor_name

fetch_stmt:
          FETCH [NEXT] [FROM] cursor_name INTO var_name[, var_name]...

close_stmt:
          CLOSE cursor_name