Common Types of SQL
The most common types of SQL (Structured Query Language) are used for managing and manipulating relational databases. SQL commands are broadly categorized based on their functionality:
1. Data Definition Language (DDL)
Purpose:
DDL commands are used to define and manage database structures such as tables, indexes, and schemas.
Common Commands:
CREATE
- Creates a new table, database, index, or other objects.ALTER
- Modifies the structure of an existing database object (e.g., adding or modifying columns in a table).DROP
- Deletes an existing database object such as a table or index.TRUNCATE
- Removes all records from a table, but the table structure remains.
2. Data Manipulation Language (DML)
Purpose:
DML commands are used for inserting, updating, deleting, and retrieving data within the database.
Common Commands:
SELECT
- Retrieves data from one or more tables. It is the most commonly used SQL command.INSERT
- Adds new rows of data to a table.UPDATE
- Modifies existing data in a table.DELETE
- Removes existing data from a table.
3. Data Control Language (DCL)
Purpose:
DCL commands are used to control access to data in the database.
Common Commands:
GRANT
- Provides specific privileges to users or roles (e.g., the ability to query or modify tables).REVOKE
- Removes previously granted privileges from users or roles.
4. Transaction Control Language (TCL)
Purpose:
TCL commands are used to manage transactions in the database, ensuring the integrity of data.
Common Commands:
COMMIT
- Saves the current transaction's changes permanently in the database.ROLLBACK
- Undoes the changes made in the current transaction.SAVEPOINT
- Sets a point within a transaction to which you can later roll back.SET TRANSACTION
- Specifies characteristics for the transaction, such as isolation level.
5. Data Query Language (DQL)
Purpose:
DQL is primarily concerned with the retrieval of data from the database.
Common Commands:
SELECT
- While technically part of DML, SELECT is often considered its own category (DQL) due to its significance in querying data.
Additional SQL Variants and Extensions
Procedural SQL (PL/SQL, T-SQL, etc.):
SQL extensions that introduce procedural elements (e.g., loops, conditionals) to SQL. Common in Oracle (PL/SQL) and SQL Server (T-SQL).Analytical SQL:
Includes commands for complex data analysis, like WINDOW FUNCTIONS (e.g., ROW_NUMBER, RANK), GROUP BY CUBE, and ROLLUP.