SQL Statements
This section provides an overview of how the Structured Query Language (SQL) is implemented in Exasol. It includes syntax, usage, and examples of SQL statements used in Exasol.
Syntax diagrams
The syntax for each statement is presented as a railroad diagram that uses the following conventions:
- Terminals (elements that must be entered literally in the SQL statement) are represented by rounded boxes with no fill.
- Non-terminals (placeholders for values or references to other syntax diagrams) are represented by rectangular boxes with a grey fill.
Definition of database (DDL)
The structure of the database is defined using Data Definition Language (DDL). DDL statements can be used to create, modify, and remove schemas and all schema objects such as tables, views, functions, and scripts.
You can use the following statements to create or modify schemas:
- CREATE SCHEMA
- DROP SCHEMA
- ALTER SCHEMA
- CREATE TABLE
- SELECT INTO
- DROP TABLE
- ALTER TABLE(column)
- ALTER TABLE(distribution/partitioning)
- ALTER TABLE(constraints)
- CREATE VIEW
- DROP VIEW
- CREATE FUNCTION
- DROP FUNCTION
- CREATE SCRIPT
- DROP SCRIPT
- RENAME
- COMMENT
Manipulation of database (DML)
Using Data Manipulation Language (DML), you can change the content of tables.
You can use the following statements to change the content of a table:
Access control using SQL (DCL)
The SQL statements of the Data Control Language (DCL) are used to control the database access rights. Through the management of users and roles as well as the granting of privileges, you can determine who is permitted to perform what actions in the database.
For an introduction to the basic concepts of rights management, and further details such as a list of all privileges, a summary of the access rights for SQL statements, as well as the system tables relevant to rights management, refer to the Database Users and Roles, and Privileges sections.
Additionally, within the SQL reference for each SQL statement, the prerequisites regarding which privileges are necessary for the respective statement are specified.
You can use the following statements for access control:
- CREATE USER
- ALTER USER
- DROP USER
- CREATE ROLE
- DROP ROLE
- CREATE CONNECTION
- ALTER CONNECTION
- DROP CONNECTION
- GRANT
- REVOKE
Query language (DQL)
Contents from the database can be queried and analyzed with the Data Query Language (DQL).
You can use the following statements to query in the database:
Verification of data quality
You can use the following data-quality statements to analyze and ensure the quality of the data in the database:
- Verification of the Primary Key Property (PRIMARY KEY)
- Verification of the Uniqueness (UNIQUE)
- Verification of the Foreign Key Property (FOREIGN KEY)
Other statements
The following list shows the SQL statements that cannot be categorized in any of the above sections: