Documentation > USER GUIDE > SQL Manual

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 SCHEMA	COMMENT ON SCHEMA	ALTER SCHEMA	DROP SCHEMA
SET SCHEMA

Data Definition


CREATE SEQUENCE	COMMENT ON SEQUENCE	ALTER SEQUENCE	DROP SEQUENCE

CREATE TABLE	COMMENT ON TABLE	COMMENT ON COLUMN	DROP TABLE
CREATE TABLE AS	ALTER TABLE ADD COLUMN	ALTER TABLE ALTER COLUMN	ALTER TABLE DROP COLUMN
CREATE TEMPORARY TABLE	ALTER TABLE ADD CONSTRAINT		ALTER TABLE DROP CONSTRAINT
	ALTER TABLE RENAME TO	ALTER TABLE RENAME COLUMN	ALTER TABLE SET SCHEMA
	ALTER TABLE SET INSERT ONLY	ALTER TABLE SET READ ONLY	ALTER TABLE SET READ WRITE

CREATE MERGE TABLE	ALTER TABLE ADD TABLE	ALTER TABLE SET TABLE	ALTER TABLE DROP TABLE
CREATE REMOTE TABLE
CREATE REPLICA TABLE
CREATE UNLOGGED TABLE
CREATE TABLE FROM LOADER

CREATE INDEX	COMMENT ON INDEX	DROP INDEX
CREATE IMPRINTS INDEX
CREATE ORDERED INDEX

CREATE TRIGGER	DROP TRIGGER

CREATE VIEW	COMMENT ON VIEW	DROP VIEW

CREATE FUNCTION	COMMENT ON FUNCTION	DROP FUNCTION	DROP ALL FUNCTION
CREATE FUNCTION EXTERNAL	CREATE FUNCTION LANGUAGE

CREATE PROCEDURE	COMMENT ON PROCEDURE	DROP PROCEDURE	DROP ALL PROCEDURE
CREATE PROCEDURE EXTERNAL

CREATE AGGREGRATE	COMMENT ON AGGREGRATE	DROP AGGREGRATE	DROP ALL AGGREGRATE
CREATE AGGREGRATE EXTERNAL

CREATE FILTER	COMMENT ON FILTER	DROP FILTER	DROP ALL FILTER

CREATE LOADER	COMMENT ON LOADER	DROP LOADER	DROP ALL LOADER

CREATE WINDOW EXTERNAL	COMMENT ON WINDOW	DROP WINDOW	DROP ALL WINDOW

CREATE TYPE EXTERNA	DROP TYPE
DECLARE

Data Manipulation


SELECT	SELECT INTO	WITH SELECT
INSERT VALUES	INSERT SELECT
UPDATE	MERGE
DELETE	TRUNCATE

PREPARE	EXECUTE	DEALLOCATE
CALL
SET	SET TIME ZONE	SET CURRENT_TIMEZONE

Data Import/Export


COPY INTO FROM	COPY INTO FROM STDIN	COPY BINARY INTO FROM	COPY LOADER INTO FROM
COPY INTO	COPY INTO STDOUT

Authorization & Data Access Control


CREATE USER	ALTER USER	DROP USER
SET USER	SET CURRENT_USER	SET SESSION_USER
GRANT PRIVILEGES	REVOKE PRIVILEGES
CREATE ROLE	SET ROLE	SET SESSION AUTHORIZATION	DROP ROLE
GRANT ROLE	REVOKE ROLE

Transaction Management


START TRANSACTION	COMMIT	ROLLBACK
SET TRANSACTION	SET LOCAL TRANSACTION
SAVEPOINT	RELEASE SAVEPOINT	ROLLBACK TO SAVEPOINT

Development Commands


EXPLAIN	PLAN	TRACE
ANALYZE	SET OPTIMIZER

Pseudo Columns


CURRENT_SCHEMA	CURRENT_USER	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_TIMEZONE	LOCALTIME	LOCALTIMESTAMP
LOCALTIMESTAMP	NEXT VALUE FOR