SQL keywords

SQL keywords fall into a number of groups.

First there are the standard Data Manipulation Language elements. DML is the subset of the language used to query a database, add, update and delete data.

SELECT is the most commonly used DML command and allows the user to specify a query as a description of the desired result set. The query does not specify how the results should be located - turning a query into a form which can be executed is the job of the database system, more specifically of the Query Optimiser.

INSERT is used to add a row (formally a tuple) to an existing table.

UPDATE is used to change the data values in an existing table row.

DELETE permits existing rows to be removed from a table.

Two other keywords could be said to fall into DML:

COMMIT causes all data changes since the last COMMIT or ROLLBACK to be made permanent.

ROLLBACK causes all data changes since the last COMMIT or ROLLBACK to be discarded, so that the state of the data is "rolled back" to the way it was prior to those changes being requested.

COMMIT and ROLLBACK interact with areas such as transaction control and locking. Strictly, both terminate any open transaction and release any locks held on data.

The second group of keywords is the Data Definition Language. DDL allows the user to define new tables and associated elements. Most commercial SQL databases have proprietary extensions in their DDL, which allow control over proprietary and nonstandard, but usually operationally vital, elements of the specific system.

The most basic items of DDL are the CREATE and DROP commands.

CREATE causes an object (a table, for example) to be created within the database.

DROP causes an existing object within the database to be deleted, usually irretrievably.

Some database systems also have an ALTER command, which permits the user to modify an existing object in various ways - for example, adding a column to an existing table.

The third group of SQL keywords is the Data Control Language. DCL handles the authorisation aspects of data and permits the user to control who has access to see or manipulate data within the database.

Its two main keywords are

GRANT - authorises a user to perform an operation, and

REVOKE - removes or restricts the capability of a user to perform an operation.