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
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
Statement |
Min |
Core |
Ext |
alter-table-statement ::= ALTER TABLE base-table-name |
X |
||
create-index-statement ::= CREATE [UNIQUE] INDEX index-name |
X |
||
create-table-statement ::= CREATE TABLE base-table-name-1 |
X |
||
create-view-statement ::= CREATE VIEW viewed-table-name |
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 |
X |
||
drop-view-statement ::= DROP VIEW viewed-table-name |
X |
||
grant-statement ::= GRANT {ALL | grant-privilege [, grant-privilege]... } |
X |
||
insert-statement ::= INSERT INTO table-name [( column-identifier [, column-identifier]...)] |
X |
||
insert-statement ::= INSERT INTO table-name [( column-identifier [, column-identifier]... )] |
X |
||
ODBC-procedure-extension ::= ODBC-std-esc-initiator [?=]call procedure ODBC-std-esc-terminator |
X | ||
revoke-statement ::= REVOKE {ALL | revoke-privilege [, revoke-privilege]... } |
X |
||
select-statement ::= SELECT [ALL | DISTINCT] select-list |
X |
||
select-statement ::= SELECT [ALL | DISTINCT] select-list |
X |
||
select-for-update-statement ::= SELECT [ALL | DISTINCT] select-list |
X (v1) |
X (v2) | |
statement ::= create-table-statement | delete-statement-searched | drop-table-statement |
X |
||
statement ::= alter-table-statement | create-index-statement |
X |
||
statement ::= alter-table-statement | create-index-statement |
X | ||
statement-list ::= statement | statement;statement-list |
X | ||
update-statement-positioned ::= UPDATE table-name |
X (v1) |
X (v2) | |
update-statement-searched UPDATE table-name |
X |
Elements Used in SQL Statements
Element |
Min |
Core |
Ext |
approximate-numeric-literal ::= mantissaEexponent mantissa ::= exact-numeric-literal |
X |
||
approximate-numeric-type ::= FLOAT |
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) |
X | ||
character ::= {any character in the implementor's character set} |
X |
||
character-string-literal :: = '{character}...' |
X |
||
character-string-type ::= |
X |
||
character-string-type ::= |
X |
||
character-string-type ::= |
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 ::= |
X |
||
comparison-predicate ::= expression comparison-operator |
X |
||
correlation-name ::= user-defined-name |
X |
||
cursor-name ::= user-defined-name |
X |
||
data-type ::= character-string-type |
X |
||
data-type ::= |
X |
||
data-type ::= |
X | ||
date-literal ::= 'date-value' |
X | ||
date-separator ::= - |
X | ||
date-type ::= DATE |
X | ||
date-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 ] |
X |
||
exact-numeric-type ::= DECIMAL(precision,scale) |
X | ||
exact-numeric-type ::= DECIMAL(precision,scale) |
X |
||
exists-predicate ::= EXISTS ( sub-query ) |
X |
||
expression ::= term | expression {+|-} term | dynamic-parameter primary ::= column-name | dynamic-parameter |
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)} |
X |