Guides/SQL

From J Wiki
Jump to navigation Jump to search

The SQL Language

SQL, or Structured Query Language, is a widely accepted protocol used for data access. It is an ANSI standard with SQL:2016 being the most recent specification.

The language is defined with various levels, listed in the appendix as minimum, core and extended. All SQL servers support at least the minimum level, some may also support core or extended levels, or extensions of their own. ODBC DBMS drivers are distributed with Help files that list the functionality of the driver, plus other useful information - when you install the drivers you should also print out the Help files for reference.

See also

SQL Reserved Keywords

The following words are reserved for use in ODBC function calls. These words do not constrain the minimum SQL grammar; however, to ensure compatibility with drivers that support the core SQL grammar, applications should avoid using any of these keywords. 
ABSOLUTE          DECLARE           INTEGER           REVOKE
ADA               DEFERRABLE        INTERSECT         RIGHT
ADD               DEFERRED          INTERVAL          ROLLBACK
ALL               DELETE            INTO              ROWS
ALLOCATE          DESC              IS                SCHEMA
ALTER             DESCRIBE          ISOLATION         SCROLL
AND               DESCRIPTOR        JOIN              SECOND
ANY               DIAGNOSTICS       KEY               SECTION
ARE               DICTIONARY        LANGUAGE          SELECT
AS                DISCONNECT        LAST              SEQUENCE
ASC               DISPLACEMENT      LEFT              SET
ASSERTION         DISTINCT          LEVEL             SIZE
AT                DOMAIN            LIKE              SMALLINT
AUTHORIZATION     DOUBLE            LOCAL             SOME
AVG               DROP              LOWER             SQL
BEGIN             ELSE              MATCH             SQLCA
BETWEEN           END               MAX               SQLCODE
BIT               END-EXEC          MIN               SQLERROR
BIT_LENGTH        ESCAPE            MINUTE            SQLSTATE
BY                EXCEPT            MODULE            SQLWARNING
CASCADE           EXCEPTION         MONTH             SUBSTRING
CASCADED          EXEC              MUMPS             SUM
CASE              EXECUTE           NAMES             SYSTEM
CAST              EXISTS            NATIONAL          TABLE
CATALOG           EXTERNAL          NCHAR             TEMPORARY
CHAR              EXTRACT           NEXT              THEN
CHAR_LENGTH       FALSE             NONE              TIME
CHARACTER         FETCH             NOT               TIMESTAMP
CHARACTER_LENGTH  FIRST             NULL              TIMEZONE_HOUR
CHECK             FLOAT             NULLIF            TIMEZONE_MINUTE
CLOSE             FOR               NUMERIC           TO
COALESCE          FOREIGN           OCTET_LENGTH      TRANSACTION
COBOL             FORTRAN           OF                TRANSLATE
COLLATE           FOUND             OFF               TRANSLATION
COLLATION         FROM              ON                TRUE
COLUMN            FULL              ONLY              UNION
COMMIT            GET               OPEN              UNIQUE
CONNECT           GLOBAL            OPTION            UNKNOWN
CONNECTION        GO                OR                UPDATE
CONSTRAINT        GOTO              ORDER             UPPER
CONSTRAINTS       GRANT             OUTER             USAGE
CONTINUE          GROUP             OUTPUT            USER
CONVERT           HAVING            OVERLAPS          USING
CORRESPONDING     HOUR              PARTIAL           VALUE
COUNT             IDENTITY          PASCAL            VALUES
CREATE            IGNORE            PLI               VARCHAR
CURRENT           IMMEDIATE         POSITION          VARYING
CURRENT_DATE      IN                PRECISION         VIEW
CURRENT_TIME      INCLUDE           PREPARE           WHEN
CURRENT_TIMESTAM  INDEX             PRESERVE          WHENEVER
CURSOR            INDICATOR         PRIMARY           WHERE
DATE              INITIALLY         PRIOR             WITH
DAY               INNER             PRIVILEGES        WORK
DEALLOCATE        INPUT             PROCEDURE         YEAR
DEC               INSENSITIVE       PUBLIC
DECIMAL           INSERT            RESTRICT

SQL Statements

The following SQL statements define the base ODBC SQL grammar.

Statement

Min

Core

Ext

alter-table-statement ::=

       ALTER TABLE base-table-name

       {ADD column-identifier data-type

       |ADD (column-identifier data-type [, column-identifier data-type]... )        }
 

X

 

create-index-statement ::=

      CREATE [UNIQUE] INDEX index-name

      ON base-table-name

      ( column-identifier [ASC | DESC]

      [, column-identifier [ASC | DESC] ]... )
 

X

 

create-table-statement ::=

      CREATE TABLE base-table-name-1

      (column-element [, column-element] ...)

      column-element ::= column-definition | table-constraint-definition

      column-definition ::=

              column-identifier data-type

               DEFAULT default-value

              [column-constraint-definition [,column-constraint-definition]...]

      column-constraint-definition ::=

              NOT NULL

              | UNIQUE | PRIMARY KEY )

              | REFERENCES base-table-name-2 referenced-columns

              | CHECK (search-condition)

             default-value ::= literal | NULL | USER

      table-constraint-definition ::=

              UNIQUE (column-identifier [, column-identifier] ...)

              | PRIMARY KEY (column-identifier

                     [, column-identifier] ...)

              | CHECK (search-condition)

              | FOREIGN KEY referencing-columns REFERENCES

                     base-table-name-2 referenced-columns

X

   

create-view-statement ::=

      CREATE VIEW viewed-table-name

     [( column-identifier [, column-identifier]... )]      AS

      query-specification
 

X

 

delete-statement-positioned ::=

      DELETE FROM table-name WHERE CURRENT OF cursor-name
 

X

X

(v2)X

delete-statement-searched ::=

      DELETE FROM table-name [WHERE search-condition]

X

   

drop-index-statement ::=

      DROP INDEX index-name
 

X

 

drop-table-statement ::=

      DROP TABLE base-table-name

              [ CASCADE | RESTRICT ]

X

   

drop-view-statement ::=

      DROP VIEW viewed-table-name

              [ CASCADE | RESTRICT ]
 

X

 

grant-statement ::=

      GRANT {ALL | grant-privilege [, grant-privilege]... }

      ON table-name

      TO {PUBLIC | user-name [, user-name]... }

      grant-privilege ::=

              DELETE

              | INSERT

              | SELECT

              | UPDATE [( column-identifier [, column-identifier]... )]

              | REFERENCES [( column-identifier

                     [, column-identifier]... )]
 

X

 

insert-statement ::=

      INSERT INTO table-name [( column-identifier [, column-identifier]...)]

      VALUES (insert-value[,insert-value]... )

X

   

insert-statement ::=

  INSERT INTO table-name [( column-identifier [, column-identifier]... )]

      { query-specification | VALUES (insert-value [, insert-value]...}
 

X

 

ODBC-procedure-extension ::=

      ODBC-std-esc-initiator [?=]call procedure ODBC-std-esc-terminator

      | ODBC-ext-esc-initiator [?=]call procedure ODBC-ext-esc-terminator
   

X

revoke-statement ::=

      REVOKE {ALL | revoke-privilege [, revoke-privilege]... }

      ON table-name

      FROM {PUBLIC | user-name [, user-name]... }

      [ CASCADE | RESTRICT ]

       revoke-privilege ::=

              DELETE

              | INSERT

              | SELECT

              | UPDATE  | REFERENCES
 

X

 

select-statement ::=

      SELECT [ALL | DISTINCT] select-list

      FROM table-reference-list[

      [WHERE  search-condition]

      [order-by-clause]

X

   

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]
 

X

 

select-for-update-statement ::=

      SELECT [ALL | DISTINCT] select-list

      FROM table-reference-list

      [WHERE  search-condition]

      FOR UPDATE OF [column-name [, column-name]...]
 

X

(v1)

X

(v2)

statement ::= create-table-statement

      | delete-statement-searched     | drop-table-statement

      | insert-statement     | select-statement

      | update-statement-searched

X

   

statement ::= alter-table-statement

      | create-index-statement

      | create-table-statement

      | create-view-statement

      | delete-statement-positioned

      | delete-statement-searched

      | drop-index-statement

      | drop-table-statement

      | drop-view-statement

      | grant-statement

      | insert-statement

      | revoke-statement

      | select-statement

      | select-for-update-statement

      | update-statement-positioned

      | update-statement-searched
 

X

 

statement ::= alter-table-statement

      | create-index-statement

      | create-table-statement

      | create-view-statement

      | delete-statement-positioned

      | delete-statement-searched

      | drop-index-statement

      | drop-table-statement

      | drop-view-statement

      | grant-statement

      | insert-statement

      | ODBC-procedure-extension

      | revoke-statement

      | select-statement

      | select-for-update-statement

      | statement-list

      | update-statement-positioned

      | update-statement-searched
   

X

statement-list ::= statement | statement;statement-list

   

X

update-statement-positioned ::=

      UPDATE table-name

      SET column-identifier = {expression | NULL}

              [, column-identifier = {expression | NULL}]...

      WHERE CURRENT OF cursor-name
 

X

(v1)

X

(v2)

update-statement-searched

      UPDATE table-name

      SET column-identifier = {expression | NULL }

             [, column-identifier = {expression | NULL}]...

      [WHERE search-condition]

X

   

Elements Used in SQL Statements

The following elements are used in the SQL statements listed previously .

Element

Min

Core

Ext

approximate-numeric-literal ::= mantissaEexponent

      mantissa ::= exact-numeric-literal

      exponent ::= [+|-] unsigned-integer
 

X

 

approximate-numeric-type ::=

      FLOAT

      | DOUBLE PRECISION

      | REAL
 

X

 

argument-list ::= expression | expression, argument-list

X

   

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

X

   

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

X

   

base-table-name ::= [user-name.]base-table-identifier

 

X

 

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

 

X

 

binary-literal ::= {implementation defined}

   

X

binary-type ::= 

      BINARY (length)

      | VARBINARY (length)

      | LONG VARBINARY·
   

X

character ::= {any character in the implementor's character set}

X

   

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

X

   

character-string-type ::=
CHARACTER(length)
| CHAR(length)

X

   

character-string-type ::=
CHARACTER(length)
| CHAR(length)
| CHARACTER VARYING(length)
| VARCHAR(length)

 

X

 

character-string-type ::=
CHARACTER(length)
| CHAR(length)
| CHARACTER VARYING(length)
| VARCHAR(length)
| LONG VARCHAR

   

X

column-identifier ::= user-defined-name

X

   

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

X

   

column-name ::= [{table-name | correlation-name}.]column-identifier

 

X

 

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

X

   

comparison-predicate ::=
expression comparison-operator expression

X

   

comparison-predicate ::= expression comparison-operator
{expression | (sub-query)}

 

X

 

correlation-name ::= user-defined-name

 

X

 

cursor-name ::= user-defined-name

 

X

 

data-type ::= character-string-type

X

   

data-type ::=
character-string-type
| exact-numeric-type
| approximate-numeric-type

 

X

 

data-type ::=
character-string-type
| exact-numeric-type
| approximate-numeric-type
| binary-type
| date-type
| time-type
| timestamp-type

   

X

date-literal ::= 'date-value'

   

X

date-separator ::= -

   

X

date-type ::= DATE

   

X

date-value ::=
years-value date-separator months-value date-separator days-value

   

X

days-value ::= digit digit·

X

   

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

X

   

dynamic-parameter ::= ?

X

   

exact-numeric-literal ::=

        [+|-] { unsigned-integer [.unsigned-integer ]

        | unsigned-integer.

        | .unsigned-integer }
 

X

 

exact-numeric-type ::= DECIMAL(precision,scale)
| NUMERIC(precision,scale) | SMALLINT | INTEGER

   

X

exact-numeric-type ::= DECIMAL(precision,scale)
| NUMERIC(precision,scale) | BIT | SMALLINT | INTEGER
|BIGINT

 

X

 

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

 

X

 

expression ::= term | expression {+|-} term
term ::= factor | term {*|/} factor
factor ::= [+|-]primary
primary ::= column-name
| dynamic-parameter
| literal
(continued)
| ( expression )
primary ::= column-name

              | dynamic-parameter

              | literal

              | set-function-reference

              | USER

              | ( expression )

primary ::= column-name

              | dynamic-parameter

              | literal

              | ODBC-scalar-function-extension

              | set-function-reference

              | USER

              | ( expression )

X

X

X

hours-value ::= digit digit

   

X

index-identifier ::= user-defined-name

 

X

 

index-name ::= [index-qualifier.]index-identifier

 

X

 

Index-qualifier ::= user-defined-name

 

X

 

in-predicate ::=

      expression [NOT] IN {(value {, value}...) | (sub-query)}

      value ::= literal | USER  | dynamic-parameter
 

X

 
Retrieved from "https://code.jsoftware.com/mediawiki/index.php?title=Guides/SQL&oldid=40235"