SQL (Structured Query Language) is a programming language, It is used for managing and manipulating relational databases using various command such as DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data control Language) and TCL (Transaction Control Language).
Let’s understand each of them one by one:
DDL (Data Definition Language): DDL commands are used to define and manage/modify the structure of a database schema. These commands allow you to create, modify, and delete database objects such as tables, views, indexes and constraints. Following are the DDL commands:
- CREATE: Creates a new database object, such as a table, views, indexes, or synonyms.
- ALTER: Modifies the structure of an existing database object.
- TRUNCATE: Delete all data from a table while keep the table structure intact.
- DROP: It deletes a database object, such as drop column, table or indexes.
- RENAME: It is used to rename an existing database object such as rename table, column, or indexes.
DML (Data Manipulation Language): DML commands are used to manipulate data within the database. These commands allow you to insert, update, retrieve, and delete data in the tables. Following are the DML commands:
- SELECT: It retrieves the data from one or more tables.
- INSERT: It inserts new data into a table.
- UPDATE: It modifies existing data in a table.
- DELETE: It deletes records from a database table.
DCL (Data Control Language): DCL commands are used to control and manage user access permissions and security within a database. These commands allow you to control grant or revoke privileges, security settings, and manage user roles. Following are the DCL commands:
- GRANT: The GRANT command is used to provide specific privileges or permissions to database users. It allows the database administrator to grant various levels of access to different users or user groups. For example, you can grant SELECT, INSERT, UPDATE, or DELETE privileges on specific tables to a user or role.
- REVOKE: The REVOKE command is used to revoke previously granted privileges from a user or role. It removes the specified privileges and restricts the user’s access accordingly. For example, you can revoke the INSERT privilege on a table from a user.
TCL (Transaction Control Language): TCL commands are used to manage transactions within a database. A transaction is a sequence of one or more tasks that are treated as a single unit of work. It is a way to ensure that a group of related tasks either succeeds as a whole or fails as a whole, maintaining the consistency and integrity of the data.
TCL commands allow you to control the transactional behavior of your database. Following are the TCL commands:
- COMMIT: Saves all changes made within a transaction and makes them permanent.
- ROLLBACK: Rollback all changes made within a transaction and restores the database to its previous state.
- SAVEPOINT: It is used to set a savepoint within a transaction, It allows you to roll back to that point if required.