16 SQL commands

SQL Language

SQL is defined, developed, and controlled by international bodies. Oracle Corporation does not have to conform to the SQL standard but chooses to do so. The language itself can be thought as being very simple (there are only 16 commands), but in practice SQL coding can be phenomenally complicated. That is why a whole book is needed to cover the bare fundamentals.

SQL Standards

Structured Query Language (SQL) was first invented by an IBM research group in the ’70s, but in fact Oracle Corporation (then trading as Relational Software, Inc.) claims to have beaten IBM to market by a few weeks with the first commercial implementation: Oracle 2, released in 1979. Since then the language has evolved enormously and is no longer driven by any one organization. SQL is now an international standard. It is managed by committees from ISO and ANSI. ISO is the Organisation Internationale de Normalisation, based in Geneva; ANSI is the American National Standards Institute, based in Washington, DC. The two bodies cooperate, and their SQL standards are identical.

SQL Commands

These are the 16 SQL commands, separated into commonly used groups:

The Data Manipulation Language (DML) commands:

SELECT
INSERT
UPDATE
DELETE
MERGE

The Data Definition Language (DDL) commands:

SELECT
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

The Data Control Language (DCL) commands:

SELECT
GRANT
REVOKE

The Transaction Control Language (TCL) commands:

SELECT
COMMIT
ROLLBACK
SAVEPOINT

According to all the docs, SELECT is a DML statement. In practice, no one includes it when they refer to DML, they talk about it as though it were a language in its own right (it almost is) and use DML to mean only the commands that change data.

A Set-oriented Language

Most 3GLs are procedural languages. Programmers working in procedural languages specify what to do with data, one row at a time. Programmers working in a setoriented language say what they want to do to a group (a “set”) of rows and let the database work out how to do it to however many rows are in the set. Procedural languages are usually less efficient than set-oriented languages at managing data, as regards both development and execution. A procedural routine for looping through a group of rows and updating them one by one will involve many lines of code, where SQL might do the whole operation with one command: programmers’ productivity increases. During program execution, procedural code gives the database no options; it must run the code as it has been written. With SQL, the programmer states what he or she wants to do but not how to do it: the database has the freedom to work out how best to carry out the operation. This will usually give better results.