An Overview of SQL Commands: DDL, DML, DCL, TCL, DQL – Types, Syntax, and Illustrative Examples
Structured Query Language (SQL) is a programming language used for managing and manipulating relational databases. SQL commands are categorized into different types based on their functionality. These types include Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and Data Query Language (DQL). In this article, we will provide an overview of each type of SQL command, their syntax, and provide illustrative examples.
1. Data Definition Language (DDL):
DDL commands are used to define and manage the structure of the database. They allow users to create, modify, and delete database objects such as tables, indexes, views, and constraints. Some commonly used DDL commands include CREATE, ALTER, and DROP.
– CREATE: This command is used to create a new database object. For example, to create a table named “Customers” with columns “ID,” “Name,” and “Email,” the syntax would be:
CREATE TABLE Customers (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(100)
);
– ALTER: This command is used to modify the structure of an existing database object. For example, to add a new column “Phone” to the “Customers” table, the syntax would be:
ALTER TABLE Customers
ADD Phone VARCHAR(20);
– DROP: This command is used to delete an existing database object. For example, to delete the “Customers” table, the syntax would be:
DROP TABLE Customers;
2. Data Manipulation Language (DML):
DML commands are used to manipulate data within the database. They allow users to insert, update, delete, and retrieve data from tables. Some commonly used DML commands include INSERT, UPDATE, DELETE, and SELECT.
– INSERT: This command is used to insert new records into a table. For example, to insert a new customer with ID 1, name “John Doe,” and email “john@example.com” into the “Customers” table, the syntax would be:
INSERT INTO Customers (ID, Name, Email)
VALUES (1, ‘John Doe’, ‘john@example.com’);
– UPDATE: This command is used to modify existing records in a table. For example, to update the email of the customer with ID 1 to “john.doe@example.com” in the “Customers” table, the syntax would be:
UPDATE Customers
SET Email = ‘john.doe@example.com’
WHERE ID = 1;
– DELETE: This command is used to delete records from a table. For example, to delete the customer with ID 1 from the “Customers” table, the syntax would be:
DELETE FROM Customers
WHERE ID = 1;
– SELECT: This command is used to retrieve data from one or more tables. For example, to retrieve all customers from the “Customers” table, the syntax would be:
SELECT * FROM Customers;
3. Data Control Language (DCL):
DCL commands are used to control access and permissions within the database. They allow users to grant or revoke privileges to other users. Some commonly used DCL commands include GRANT and REVOKE.
– GRANT: This command is used to grant specific privileges to a user or a group of users. For example, to grant SELECT privilege on the “Customers” table to a user named “user1,” the syntax would be:
GRANT SELECT ON Customers TO user1;
– REVOKE: This command is used to revoke previously granted privileges from a user or a group of users. For example, to revoke the SELECT privilege on the “Customers” table from the user “user1,” the syntax would be:
REVOKE SELECT ON Customers FROM user1;
4. Transaction Control Language (TCL):
TCL commands are used to manage transactions within the database. They allow users to control the changes made to the database and ensure data integrity. Some commonly used TCL commands include COMMIT, ROLLBACK, and SAVEPOINT.
– COMMIT: This command is used to permanently save the changes made within a transaction. For example, to commit the changes made within a transaction, the syntax would be:
COMMIT;
– ROLLBACK: This command is used to undo the changes made within a transaction and restore the database to its previous state. For example, to rollback the changes made within a transaction, the syntax would be:
ROLLBACK;
– SAVEPOINT: This command is used to set a savepoint within a transaction. It allows users to rollback to a specific point within a transaction. For example, to set a savepoint named “sp1” within a transaction, the syntax would be:
SAVEPOINT sp1;
5. Data Query Language (DQL):
DQL commands are
- SEO Powered Content & PR Distribution. Get Amplified Today.
- PlatoData.Network Vertical Generative Ai. Empower Yourself. Access Here.
- PlatoAiStream. Web3 Intelligence. Knowledge Amplified. Access Here.
- PlatoESG. Carbon, CleanTech, Energy, Environment, Solar, Waste Management. Access Here.
- PlatoHealth. Biotech and Clinical Trials Intelligence. Access Here.
- Source: Plato Data Intelligence.
- Source Link: https://zephyrnet.com/sql-commands-ddl-dml-dcl-tcl-dql-types-syntax-and-examples/