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

SQL (Structured Query Language) is a standardized programming language used to manage, query, and manipulate relational databases. It enables users to perform operations such as:

• Data Definition (DDL): Creating, altering, and deleting database structures (e.g., tables, indexes).
• Data Manipulation (DML): Inserting, updating, deleting, and retrieving data within a database.
• Data Control (DCL): Controlling access to data (e.g., granting or revoking permissions).
• Transaction Control (TCL): Managing database transactions to ensure data consistency (e.g., COMMIT, ROLLBACK).

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

Structured Query Language (SQL) is the foundation of managing and interacting with relational databases. SQL commands are divided into categories based on their functionality. Understanding these categories is crucial for effective database management and operations.

1. Data Definition Language (DDL)

DDL commands are used to define and modify the structure of database objects such as tables, schemas, and indexes.

Common DDL Commands:

• CREATE: Used to create new database objects.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

• ALTER: Used to modify / alter an existing database structure.

ALTER TABLE customers ADD email VARCHAR(100);

• DROP: Deletes database objects permanently.

DROP TABLE customers;

• TRUNCATE: Removes all records from a table but retains its structure.

TRUNCATE TABLE customers;

2. Data Query Language (DQL)

DQL is focused on querying and retrieving data from the database.

Common DQL Command:

• SELECT: Retrieves data from one or more tables.

SELECT * FROM customers;
SELECT name, age FROM customers WHERE age > 25;

3. Data Manipulation Language (DML)

DML commands allow you to manipulate data within database tables.

Common DML Commands:

• INSERT: Add / insert new records to a table.

INSERT INTO customers (id, name, age) VALUES (1, 'Venkat', 35);

• UPDATE: Modifies / update existing records in a table.

UPDATE customers SET age = 30 WHERE id = 1;

• DELETE: Removes records from a table.

DELETE FROM customers WHERE id = 1;

4. Data Control Language (DCL)

DCL commands manage access permissions and security of the database .

Common DCL Commands:

• GRANT: Assigns specific privileges to users.

GRANT SELECT, INSERT ON customers TO user_name;

• REVOKE: Removes previously granted privileges.

REVOKE INSERT ON customers FROM user_name;

5. Transaction Control Language (TCL)

TCL commands deal with transactions, ensuring the consistency and integrity of the database.

Common TCL Commands:

• COMMIT: Saves all changes made in the current transaction.

COMMIT;

ROLLBACK: Undoes changes made in the current transaction.

ROLLBACK;

SAVEPOINT: Creates a point within a transaction to which you can roll back.

SAVEPOINT savepoint_name;

SET TRANSACTION: Defines properties for the current transaction.

SET TRANSACTION READ ONLY;

Important SQL Commands

1.CREATE TABLE: Used to create a new table in a database.

2.ALTER TABLE: Used to modify the structure of an existing table.

3.DROP TABLE: Used to delete an entire table from a database.

4.WHERE: Used to filter rows based on a specified condition.

5.JOIN: Used to Join /combine rows from two or more tables based on a related column between them.

6. ORDER BY: Used to sort the result set in ascending or descending order.

7. SQL OPERATOR: And /OR

8.SQL INDEX

9.SQL VIEW

10.STORED PROCEDURE

• Create procedure
• insert procedure
• select procedure

Conclusion on SQL Commands

SQL commands are categorized into distinct groups based on their functionalities, each serving a vital role in database management:

1. DDL (Data Definition Language):
   Used for defining and modifying the structure of a database. Examples include CREATE, ALTER, and DROP. DDL commands ensure that databases are well-structured and capable of storing data efficiently.
2. DQL (Data Query Language):
   Focused on retrieving data from the database using queries like SELECT. It helps users fetch specific information, analyze data, and derive insights effectively.
3. DML (Data Manipulation Language):
   Enables interaction with data through commands like INSERT, UPDATE, and DELETE. These commands allow for dynamic data management in databases.
4. DCL (Data Control Language):
   Manages access to data using commands like GRANT and REVOKE. DCL ensures security by defining permissions and roles.
5. TCL (Transaction Control Language):
   Manages the execution of transactions using commands like COMMIT, ROLLBACK, and SAVEPOINT. TCL ensures the consistency and reliability of database operations.

Kickstart your career by joining Kaashiv Infotech‘s SQL Server Program, where you will gain expertise in technologies such as Dotnet, Java, SQL, Full Stack Development, and Front End Technologies, while working on real-time projects.