SQL Commands | DDL, DQL, DML, DCL and TCL Commands

SQL,or standard query language is a standardized programming language used to manage and manipulate relational databases
It is. SQL is used to perform tasks like querying data, updating records, inserting new data and deleting existing data within a database. To initiate different operations the commands are classified in different types which are as follows:-

• DDL
• DML
• DQL
• DCL
• TCL

DDL(Data definition language)

DDL commands or Data definition language are a set of commands in SQL which are used to tables and structures in a database. Primary commands within DDL commands are CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME. Each of these commands serve a specific purpose.

CREATE

Purpose of create commands is to create new databases objects such as tables, indexes, views and schema. Create is the fundamental to creating a structure of a database. Following is an example.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

ALTER

The ALTER command is used to edit the structure of an existing database object. It allows you to add delete or modify columns, change datatype, RENAME object and perform other schema altering operations. Alter command is necessary for adopting the database schema to evolving requirements. Following is an example.

ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

DROP

Drop command in sql is used to delete existing database objects such as tables, views, indexes, schemas and databases. When an object is dropped it Is completely removed from the database including all the data. Following is an example.

DROP TABLE employees;

TRUNCATE

The TRUNCATE command in SQL is used to quickly remove all rows from a table, effectively resetting the table to its initial state. It is different from delete command as it deletes one row at a time. Following is an example.

TRUNCATE TABLE employees;

COMMENT

Comment command is used to add descriptive text to database objects such as tables, columns, views, indexes and constraints. This helps to document the schema, making g it more understandable to the developer. Following is an example.

COMMENT ON TABLE employees IS 'Table to store employee details including personal and job-related information';

RENAME

The RENAME command in SQL is used to change the name of existing database objects such as tables, columns, indexes, views, and schemas. Renaming improves the clarity of object, making it easier to understand and maintain. Following is an example.

ALTER TABLE employees RENAME TO staff;

DML(DATA MANIPULATION LANGUAGE)

DML commands are fundamental for interacting with the data in a relational database. They are used to manipulate or manage data within database objects like tables. These commands work on existing records of a table. Following are some basic DML commands.

INSERT

Insert command in sql is used to add new rows to a table. It can handle singular or multiple rows and can draw data from another table. Following is the syntax.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

UPDATE

Update command in sql is used to modify existing records in a table. It allows you to change the values of one or more columns for all rows that meet certain conditions.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE

Delete command is used to remove rows from table.it is a crucial command for data manipulation ,allowing you to manage and clean up your database by removing incorrect data.

DELETE FROM table_name
WHERE condition;

SELECT

the select command is used to fetch data from one or more tables in a database. It is the most commonly used SQL command and allows you to query and display data according to specific criteria.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

DQL(DATA QUERY LANGUAGE)

DQL commands are those which help to fetch data data from a database. In SQL DQL commands primarily encompass the SELECT statement, which is used extensively to query and retrieve data based on specific criteria.

SELECT

The select statement in SQL is the core DQL command used to retrieve data from one or more tables. By mastering these commands and understanding their various clauses and functions, you can efficiently query and analyze data to meet specific business requirement. following is the basic syntax.

SELECT column1, column2, ...
FROM table_name;

DCL(DATA CONTROL LANGUAGE)

DCL commands in SQL are used to grant or revoke permissions to users to perform certain action in the database. The two main DCL commands are GRANT and REVOKE. These commands are used to maintain data integrity and security within the database environment.

GRANT

Grant command is used to give certain privileges or permissions to the user. These privileges allow users to perform certain actions on the database objects like tables, views, procedures etc.

GRANT privileges
ON object
TO {user | role | PUBLIC}
[WITH GRANT OPTION];

REVOKE

Revoke command is used to revoke the granted privileges from a user. It removes the permissions restricting users from performing those actions on a database object. Following is the syntax.

REVOKE privileges
ON object
FROM {user | role | PUBLIC};

TCL(TRANSACTION CONTROL LANGUAGE)

TCL commands are used to manage transactions within a database.these commands play crucial role in ensuring the ACID( ATOMICITY, CONSISTENCY, ISOLATION, DURABILITY) properties of transactions in a database.

COMMIT

Commit is used to save all the changes made during the current transaction permanently. If one operation fails, none of the operations are committed to the database and if transaction is committed, the changes are permanent even if the system crashes.

COMMIT;

ROLLBACK

Rollback command is used to undo the changes made during the current transaction.it reverts the database to its state before the transaction. It is ensured that the data remain in a constant state if a transaction fails for any reason. By rolling back a transaction the changes made are not visible to other transactions ensuring isolation.

ROLLBACK ;

SAVEPOINT

Savepoint command sets a point within a transaction to which you can later roll back to, allowing partial rollback of the transaction. Multiple savepoints can be set and the transaction can be rolled back to any savepoint. it is usefull for complex transactions where partial rollback is needed.

SAVEPOINT savepoint_name;

RELEASE SAVEPOINT

Release savepoint command deletes a savepoint, invalidating the ability to rollback a transaction. It frees up resources associated with the savepoint, as maintaining multiple savepoints can consume system resources.

RELEASE SAVEPOINT savepoint_name;

SET TRANSACTION

Set transaction command sets the characteristics of the current transaction such as its isolation level. Different isolation levels control the visibility of changes made by one transaction to other concurrent transactions. Common isolation levels include:

• READ UNCOMMITTED: Allows dirty reads; changes made by one transaction can be seen by others before the transaction is committed
• READ COMMITTED: Prevents dirty reads; a transaction sees only committed changes.
• REPEATABLE READ: Ensures that if a transaction reads a row, no other transaction can modify that row until the first transaction is complete.\
• SERIALIZABLE: The highest isolation level; transactions are completely isolated from each other.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;