SQL Keywords: DML, DCL, DDL, TCL

In SQL operations are typically classified into a few distinct groups based on their purpose and functionality. Each serving different roles in database management and manipulation.

Data Manipulation Language (DML)

Data Manipulation Language (DML) is a subset of SQL used for adding (inserting), deleting, and modifying (updating) data in a database. DML is all about managing data within the tables. Here are the key DML commands:

1. SELECT: This is the most common DML command, used to query data from a database and retrieve it for use. It can be filtered, sorted, and grouped as needed.
2. INSERT: Used to add new rows of data to a table. You specify the table and then provide values for the columns either directly or from another query.
3. UPDATE: Used to modify existing data within a table. The UPDATE command can change data in one or more rows, and it is often used with a WHERE clause to specify which rows should be updated.
4. DELETE: This command removes rows from a table, and like UPDATE, it is typically used with a WHERE clause to specify which rows should be removed.

Data Control Language (DCL)

Data Control Language (DCL) is used to define access permissions and security levels for database users and objects. It involves commands that control access to data within the database and command execution. The primary commands under DCL include:

1. GRANT: This command allows specified users to perform specified tasks like reading, writing, and deleting data within the database. You can grant privileges on tables, procedures, and other database objects.
2. REVOKE: The opposite of GRANT, this command removes specified permissions from a user. It is used to enforce security for sensitive data and to ensure that only authorized users have certain abilities.

Data Definition Language (DDL)

Data Definition Language (DDL) involves commands that define the structure of the database itself. This includes creating, altering, and deleting database objects such as tables, indexes, and views. The primary DDL commands include:

1. CREATE: This command is used to create new database objects. For example, CREATE TABLE is used to create a new table, CREATE INDEX to create an index, and CREATE DATABASE to create a new database.
2. ALTER: Used to modify an existing database object without destroying it. For example, ALTER TABLE commonly adds, deletes, or modifies columns in a table, or changes its characteristics.
3. DROP: This command is used to delete database objects. DROP TABLE deletes a table, DROP INDEX removes an index, and DROP DATABASE deletes an entire database.
4. TRUNCATE: Used to delete all rows in a table but does not affect the table’s structure. This can be more efficient than a DELETE command as it bypasses several integrity enforcement mechanisms.
5. RENAME: Used to rename database objects. For example, it can change the name of a table from one to another.

Transaction Control Language (TCL)

Transaction Control Language (TCL) commands are used to manage transactions within the database. Transactions are important for maintaining data integrity, especially in environments where multiple users or applications might be modifying the data concurrently. The main TCL commands include:

1. COMMIT: This command is used to save all changes made during the current transaction.
2. ROLLBACK: This command restores the database to the last committed state.
3. SAVEPOINT: Allows you to set a savepoint within a transaction. You can rollback to these savepoints instead of rolling back the entire transaction.
4. SET TRANSACTION: Used to specify characteristics for the transaction that follows. For example, a transaction can be set to read only or read write.

Other Groups

Data Query Language (DQL)

• Data Query Language is often considered part of DML, primarily because its main component, the SELECT statement, is used for querying data, which is technically a form of data manipulation. DQL is crucial for retrieving information from a database without altering the data.

Session Control Statements

• Session Control commands are used to control the properties of a user session. These include commands like SET to change session settings or ALTER SESSION. These are often used to configure aspects of the database environment that affect the execution of SQL statements but do not necessarily fit neatly into DDL, DML, or DCL.

System Control Language

• System Control Language involves commands that affect the operation of the database system as a whole. These might include commands used to start up or shut down a database, set system-level parameters, or manage system resources. These commands are more relevant in administrative contexts and are not typically included in SQL standards but are part of system-specific extensions provided by database systems like Oracle SQL.