SQL Manual

The de-facto language for database applications is SQL. It evolved through several phases of standardization to the version currently known as SQL:2016. The SQL standard provides an ideal language framework, in terms of standardization committee viewpoints. It is, however, hardly met by any of the existing (commercial) implementations. This is largely due to software legacy, backward compatibility requirements from their client base, and introduction of new features. See for instance the wikipedia on-line article on SQL standards and an overview of SQL features of main-stream systems.

In 2002 the first version of the SQL front-end for MonetDB emerged. This late development made it possible to immediately start from the SQL'99 definition. As soon as the SQL'03 specifications became available, its content was taken as the frame of reference. The SQL development strategy is driven by immediate needs of the user base, so that less-frequently used features end up low on the development stack. Features beyond the standard are marked as non-standard SQL in their description.

The architecture is based on a compiler, which translates SQL statements into MAL (MonetDB Assembly Language) statements. In this process common optimization heuristics, specific to the relational algebra are performed. There are bindings for SQL with programming languages: C (ODBC, Mapi, MonetDB/e), Java (JDBC), Python, PHP, Ruby on Rails to integrate seamlessly in the existing application developments environments.

The remainder of this manual presents a synopsis of the SQL language features. It provides a quick intro on the scope of the current implementation. Proficiency in elementary use of SQL is assumed. If you are new to this world then pick up any of the introduction books and study it carefully, e.g. J. Melton and A.R. Simon, SQL:1999 Understanding Relational Language Components, ISBN 1558604561.

The language features recognized are presented in simplified BNF notation. All reserved words are depicted in capitals. Optional constructs are enclosed by squary brackets ('[',']') with alternative choices separated by a bar ('|'), at most one can be choosen. A set of options is enclosed by curly braces '{','}' and one must be choosen. Repetition of language structures is depicted by list structures, e.g. A ',' ...  denotes a sequence of at least one A separated by commas.

Identifiers are optional qualified with a semantic tag, e.g. authid means an identifier for an authorised user.

Schema Commands

CREATE SCHEMACOMMENT ON SCHEMAALTER SCHEMADROP SCHEMA
SET SCHEMA

Data Definition

CREATE SEQUENCECOMMENT ON SEQUENCEALTER SEQUENCEDROP SEQUENCE
CREATE TABLECOMMENT ON TABLECOMMENT ON COLUMNDROP TABLE
CREATE TABLE ASALTER TABLE ADD COLUMNALTER TABLE ALTER COLUMNALTER TABLE DROP COLUMN
CREATE TEMPORARY TABLEALTER TABLE ADD CONSTRAINTALTER TABLE DROP CONSTRAINT
ALTER TABLE RENAME TOALTER TABLE RENAME COLUMNALTER TABLE SET SCHEMA
ALTER TABLE SET INSERT ONLYALTER TABLE SET READ ONLYALTER TABLE SET READ WRITE
CREATE MERGE TABLEALTER TABLE ADD TABLEALTER TABLE SET TABLEALTER TABLE DROP TABLE
CREATE REMOTE TABLE
CREATE REPLICA TABLE
CREATE UNLOGGED TABLE
CREATE TABLE FROM LOADER
CREATE INDEXCOMMENT ON INDEXDROP INDEX
CREATE IMPRINTS INDEX
CREATE ORDERED INDEX
CREATE TRIGGERDROP TRIGGER
CREATE VIEWCOMMENT ON VIEWDROP VIEW
CREATE FUNCTIONCOMMENT ON FUNCTIONDROP FUNCTIONDROP ALL FUNCTION
CREATE FUNCTION EXTERNALCREATE FUNCTION LANGUAGE
CREATE PROCEDURECOMMENT ON PROCEDUREDROP PROCEDUREDROP ALL PROCEDURE
CREATE PROCEDURE EXTERNAL
CREATE AGGREGRATECOMMENT ON AGGREGRATEDROP AGGREGRATEDROP ALL AGGREGRATE
CREATE AGGREGRATE EXTERNAL
CREATE FILTERCOMMENT ON FILTERDROP FILTERDROP ALL FILTER
CREATE LOADERCOMMENT ON LOADERDROP LOADERDROP ALL LOADER
CREATE WINDOW EXTERNALCOMMENT ON WINDOWDROP WINDOWDROP ALL WINDOW
CREATE TYPE EXTERNADROP TYPE
DECLARE

Data Manipulation

SELECTSELECT INTOWITH SELECT
INSERT VALUESINSERT SELECT
UPDATEMERGE
DELETETRUNCATE
PREPAREEXECUTEDEALLOCATE
CALL
SETSET TIME ZONESET CURRENT_TIMEZONE

Data Import/Export

COPY INTO FROMCOPY INTO FROM STDINCOPY BINARY INTO FROMCOPY LOADER INTO FROM
COPY INTOCOPY INTO STDOUT

Authorization & Data Access Control

CREATE USERALTER USERDROP USER
SET USERSET CURRENT_USERSET SESSION_USER
GRANT PRIVILEGESREVOKE PRIVILEGES
CREATE ROLESET ROLESET SESSION AUTHORIZATIONDROP ROLE
GRANT ROLEREVOKE ROLE

Transaction Management

START TRANSACTIONCOMMITROLLBACK
SET TRANSACTIONSET LOCAL TRANSACTION
SAVEPOINTRELEASE SAVEPOINTROLLBACK TO SAVEPOINT

Development Commands

EXPLAINPLANTRACE
ANALYZESET OPTIMIZER

Pseudo Columns

CURRENT_SCHEMACURRENT_USERCURRENT_DATECURRENT_TIME
CURRENT_TIMESTAMPCURRENT_TIMEZONELOCALTIMELOCALTIMESTAMP
LOCALTIMESTAMPNEXT VALUE FOR