Guides/SQL

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

• JDD
• ODBC
• https://en.wikipedia.org/wiki/SQL_syntax
• https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

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