Table of Contents
This chapter describes the basic concepts of SQL and SQL statements needed to perform database tasks.
Structured Query Language(SQL) is a non-procedural language that controls all database tasks and is used to perform queries.
Because SQL is a non-procedural language, database users can describe a desired task result using SQL without considering how the task is performed. Each system decides how best to execute a given SQL statement. Every database system has its own policies and optimization processes for saving data, using memory, reading data, and so on.
SQL statements describe general database tasks. The following database tasks can be described using SQL:
Schema object creation, updating, and removal
Database querying
Data insertion, updating, and deletion
Database management, including transaction management and session management
The SQL standard was co established by the American National Standards Institute (ANSI) and the International Organization for Standards/International Electrotechnical Commission (ISO/IEC) in 1992 and 1999 as version 2 and version 3, respectively. The SQL standard published in 1992 is called SQL2 or SQL-92 and is a language for relational databases. The SQL standard published in 1999 is called SQL3 or SQL-99 and is an object relational database language enhanced from SQL2 by adding object-oriented concepts. In SQL-2003, functions related to XML, OLAT, and object-relational database and the MERGE statement were added.
The SQL standard is divided into several levels by implementation range.
SQL-92 is divided into three levels:
Entry level
Includes over 80% of SQL-92's contents.
Intermediate level
Includes the entry level contents as well as additional content.
Full level
Includes all of SQL-92's content.
The SQL standard is so large that no commercial database system supports all of it. Tibero supports almost all of the entry level and some parts of the intermediate level.
The SQL language can be included in another program language. Embedded SQL (ESQL) and persistent stored module (PSM) are program language interfaces. Tibero provides the tbESQL and tbPSM interfaces for supporting ESQL and PSM respectively.
tbPSM is a program interface stored and executed on a Tibero server. tbPSM programs are executed only on the server side so that communication with clients is minimized, which improves execution performance. However, more sophisticated error handling is needed because client side users cannot monitor the execution process.
tbESQL, a separate interface, is also supported. tbESQL has advantages of both program languages and SQL. In general, program languages can perform complicated and detailed tasks, and SQL can express tasks related to databases with simple statements.
For more information about tbESQL, refer to Tibero tbESQL/C Guide.
For more information about tbPSM, refer to Tibero tbPSM Guide.
SQL statements defined in the SQL standard can be divided into three types:
Data Definition Language
Data Manipulation Language
Data Control Language
This document describes SQL commands included in the data control language, such as COMMIT and ROLLBACK, as part of the transaction and session management language.
Data definition language (DDL) is used to configure a database structure by defining relationships between data elements. In general, it is used to create, update, and remove schema objects. The CREATE, ALTER, and DROP commands exist for most schema objects while only one or two commands exist for some other objects.
In addition, the data definition language includes commands for optimizing table objects and for granting, revoking, and auditing privileges and roles.
The data definition language provided in Tibero supports the following commands:
| Classification | Command | Description |
|---|---|---|
| Database | CREATE DATABASE | Creates a database. |
| ALTER DATABASE | Modifies a database. | |
| Table | CREATE TABLE | Creates a table. |
| ALTER TABLE | Modifies a table. | |
| DROP TABLE | Removes a table. | |
| Tablespace | CREATE TABLESPACE | Creates a tablespace. |
| ALTER TABLESPACE | Modifies a tablespace. | |
| DROP TABLESPACE | Removes a tablespace. | |
| Index | CREATE INDEX | Creates an index. |
| ALTER INDEX | Modifies an index. | |
| DROP INDEX | Removes an index. | |
| View | CREATE VIEW | Creates a view. |
| ALTER VIEW | Modifies a view. | |
| DROP VIEW | Removes a view. | |
| Synonym | CREATE SYNONYM | Creates a synonym. |
| DROP SYNONYM | Removes a synonym. | |
| User | CREATE USER | Creates a user. |
| ALTER USER | Modifies a user name. | |
| DROP USER | Removes a user. | |
| Function | CREATE FUNCTION | Creates a function. |
| ALTER FUNCTION | Modifies a function. | |
| DROP FUNCTION | Removes a function. | |
| Procedure | CREATE PROCEDURE | Creates a procedure. |
| ALTER PROCEDURE | Modifies a procedure. | |
| DROP PROCEDURE | Removes a procedure. | |
| Type | CREATE TYPE | Creates a type. |
| ALTER TYPE | Modifies a type. | |
| DROP TYPE | Removes a type. | |
| Privilege | GRANT | Grants a privilege to a user. |
| REVOKE | Revokes a privilege from a user. | |
| Role | CREATE ROLE | Creates a role. |
| ALTER ROLE | Modifies a role. | |
| DROP ROLE | Removes a role. | |
| Object | RENAME | Renames a schema object such as a table, view, synonym, or sequence. |
| Audit | AUDIT | Audits privilege use. |
| NOAUDIT | Releases a privilege audit. |
For more information, refer to “Chapter 7. Data Definition Language”.
Data manipulation language (DML) is used to search, insert, update, and delete data stored in a database.
The data manipulation language provided in Tibero supports the following commands:
| Command | Description |
|---|---|
| SELECT | Searches data. |
| INSERT | Inserts data. |
| UPDATE | Updates data. |
| DELETE | Deletes data. |
For more information about DML, refer to “Chapter 8. Data Manipulation Language”.
Transaction management language is used to manage transactions. It sets properties of transactions and also completes and saves transactions.
Session management language is used to set properties of sessions.
The transaction and session management languages provided in Tibero support the following commands:
| Command | Description |
|---|---|
| COMMIT | Completes and saves a transaction. |
| ROLLBACK | Rolls back a transaction to its original state. |
| SAVEPOINT | Sets a savepoint. |
| SET TRANSACTION | Sets an attribute of a transaction. |
| ALTER SESSION | Changes a session. |
| SET ROLE | Activates or inactivates roles allocated to a user. |
For more information, refer to “Chapter 9. Transaction and Session Management Language”.