| ## picoSQL - supported SQL language | Corso Italia, 178 - 56125 Pisa Telefono/fax 050 46380 e-mail picosoft@picosoft.it

Supported syntax

    Instructions
    ------------

    alter-table-statement ::= alter-table-statement-add
          | alter-table-statement-drop
          | alter-table-statement-rename

    alter-table-statement-add ::=
          ALTER TABLE base-table-name ADD [COLUMN] column-identifier data-type

    alter-table-statement-drop ::=
          ALTER TABLE base-table-name DROP [COLUMN] column-identifier

    alter-table-statement-rename ::=
          ALTER TABLE base-table-name RENAME base-table-name

    create-index-statement ::=
          CREATE [UNIQUE] INDEX index-name ON base-table-name
          (column-identifier [ASC|DESC][,column-identifier [ASC|DESC]]...)

    create-table-statement ::=
          CREATE TABLE base-table-name
          (column-identifier data-type [PRIMARY KEY]
             [,column-identifier data-type [PRIMARY KEY]]...)
          [PRIMARY KEY (column-identifier[,column-identifier]...)]

    create-view-statement ::=
          CREATE VIEW base-view-name [(column-identifier[,column-identifier...])]
               AS select-for-view

    delete-statement-searched ::=
          DELETE FROM table-name [WHERE search-condition]

    drop-index-statement ::=
          DROP INDEX base-index-name

    drop-table-statement ::=
          DROP TABLE base-table-name

    drop-view-statement ::=
          DROP VIEW base-view-name

    insert-statement ::=
          INSERT INTO table-name [( column-identifier [, column-identifier]...)]
          VALUES (insert-value[, insert-value]... )

    replace-statement ::=
          REPLACE INTO table-name [( column-identifier [, column-identifier]...)]
          VALUES (insert-value[, insert-value]... )

    select-statement ::=
          SELECT [ALL | DISTINCT] select-list
          FROM table-reference-list
          [WHERE search-condition]
          [GROUP BY column-name [,column-name]...]
          [HAVING search-condition]
          [UNION select-statement]
          [order-by-clause]
          [LIMIT unsigned-integer]
          [OFFSET unsigned-integer]

    select-for-update-statement ::=
          SELECT [ALL | DISTINCT] select-list
          FROM base-table-name
          [WHERE search-condition]
          FOR UPDATE

    statement ::= create-index-statement
          | alter-table-statement
          | create-table-statement
          | create-view-statement
          | delete-statement-searched
          | drop-index-statement
          | drop-table-statement
          | drop-view-statement
          | insert-statement
          | replace-statement
          | select-statement
          | select-for-update-statement
          | update-statement-searched

    update-statement-searched
          UPDATE table-name
          SET column-identifier = {expression | NULL }
               [, column-identifier = {expression | NULL}]...
          [WHERE search-condition]

    Elements
    --------
    I seguenti elementi sono usati nelle istruzioni precedenti

    all-function :: {AVG|MAX|MIN|SUM}(expression)

    base-index-identifier ::= user-defined-name

    base-index-name ::= base-index-identifier

    base-table-identifier ::= user-defined-name

    base-table-name ::= base-table-identifier

    base-view-identifier ::= user-defined-name

    base-view-name ::= base-view-identifier

    between-predicate ::= expression [NOT] BETWEEN expression AND expression

    boolean-factor ::= [NOT] boolean-primary

    boolean-primary ::= comparison-predicate | ( search-condition )

    boolean-term ::= boolean-factor [AND boolean-term]

    character-string-literal ::= '{character}'

    column-identifier ::= user-defined-name

    column-name ::= [table-name.]column-identifier

    comparison-operator ::= < | > | <= | >= | = | <>

    comparison-predicate ::= expression comparison-operator expression

    data-type ::= CHAR | VARCHAR | NUMERIC | SMALLINT | INTEGER | INT | SERIAL |
                  REAL | FLOAT | DATE | TIME | TIMESTAMP | BLOB | CLOB

    digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

    distinct-function :: {AVG|COUNT|MAX|MIN|SUM}(DISTINCT column-name)

    dynamic-parameter ::= ?

    exists-predicate ::= EXISTS (sub-query)

    expression ::= term | expression {+|} term

    factor ::= [+|]primary

    inner-join ::= table-name INNER JOIN ON {table-name|join} ON search-condition

    in-predicate ::= expression [NOT] IN expression [, expression]

    insert-value ::=
          dynamic-parameter
          | literal
          | NULL

    join ::= outer-join | inner-join

    letter ::= lower-case-letter | upper-case-letter

    like-predicate ::= column-name [NOT] LIKE pattern-value

    literal ::= character-string-literal

    lower-case-letter ::= a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z

    null-predicate ::= IS [NOT] NULL

    order-by-clause ::=  ORDER BY sort-specification [, sort-specification]...

    outer-join ::= table-name {LEFT|RIGHT} OUTER JOIN ON {table-name|join} ON search-condition

    predicate ::= between-predicate | comparison-predicate | exists-predicate | in-predicate | like-predicate | null-predicate

    primary ::= column-name
          | dynamic-parameter
          | literal
          | set-function-reference
          | scalar-function
          | ( expression )

    scalar-func ::= ABS(expression) | ACOS(expression) | ASIN(expression) |
                    ATAN(expression) | CEILING(expression) | COS(expression) |
                    EXP(expression) | FLOOR(expression) |  LOG(expression) |
                    LOG10(expression) | SIGN(expression) | SIN(expression) |
                    SQRT(expression) | TAN(expression) | COT (expression) |
                    LOWER(expression) | UPPER(expression) | LCASE(expression)|
                    UCASE(expression) | RTRIM(expression) | case(expression) |
                    CURRENT_TIMESTAMP(expression) |
                    ROUND(expression,expression)|TRUNCATE(expression,expression)|
                    CHAR(expression) | SPACE(expression) | ASCII(expression) |
                    LENGTH(expression) | CONCAT(expression,expression) |
                    SUBSTRING(expression,expression)|
                    LOCATE(expression,expression,expression) |
                    YEAR(expression) | MONTH(expression) | DAYOFMONTH (expression) |
                    HOUR(expression) | MINUTE(expression) | SECOND (expression) |
                    MILLISECOND (expression)

    search-condition ::= boolean-term [OR search-condition]

    select-for-view ::=
          SELECT [ALL | DISTINCT] select-list
          FROM table-reference-list-for-view
          [WHERE search-condition]

    select-list ::= * | select-sublist [, select-sublist]...

    select-sublist ::= expression

    set-function-reference ::= COUNT(*)|distinct-function|all-function

    sort-specification ::= {unsigned-integer | column-name} [ASC | DESC]

    sub-query ::=
          SELECT [ALL | DISTINCT] select-list
          FROM table-reference-list
          [WHERE search-condition]
          [GROUP BY column-name [,column-name]...]
          [HAVING search-condition]

    table-identifier ::= user-defined-name

    table-name ::= table-identifier

    table-reference ::= table-name

    table-reference-list ::= table-reference [,table-reference] | join

    table-reference-list-for-view ::= table-reference | join

    term ::= factor | term {*|/} factor

    unsigned-integer ::= {digit}

    upper-case-letter ::= A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z

    user-defined-name ::= letter[digit | letter | _]...