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

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It includes a variety of commands grouped into different categories based on their functionality. In this article, we will explore the different types of SQL commands: DDL (Data Definition Language), DQL (Data Query Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language). Each command will be explained in detail with examples.

SQL Commands

Data Definition Language (DDL)

DDL commands are used to define and modify the structure of database objects such as tables, indexes, and views. The primary DDL commands are `CREATE`, `ALTER`, `DROP`, and `TRUNCATE`.

CREATE

The `CREATE` command is used to create a new database object, such as a table, view, or index.

Command Description Syntax
CREATE It is used to create a new table or database object. CREATE TABLE table_name (column1 datatype, column2 datatype, …);

Example:

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

ALTER

The `ALTER` command is used to modify an existing database object, such as adding, deleting, or modifying columns in a table.

Command Description Syntax
ALTER It is used to modify the structure of an existing table. ALTER TABLE table_name ADD column_name datatype;

Syntax:

--- ADD Column
ALTER TABLE table_name
ADD column_name datatype;

--- DROP Column
ALTER TABLE table_name
DROP COLUMN column_name;

-- MODIFY Column
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Example:

ALTER TABLE employees
ADD department VARCHAR(50);

ALTER TABLE employees
DROP COLUMN department;

ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(15, 2);

DROP

The `DROP` command is used to delete an existing database object, such as a table, view, or index.

Command Description Syntax
DROP It is used to delete an existing table, database, or other objects. DROP TABLE table_name;

Example:

DROP TABLE employees;

TRUNCATE

The `TRUNCATE` command is used to remove all records from a table, but the table structure remains.

Command Description Syntax
TRUNCATE It is used to delete all rows from a table without deleting the table itself. TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE employees;

Data Query Language (DQL)

DQL commands are used to query and retrieve data from a database. The primary DQL command is `SELECT`.

SELECT

Command Description Syntax
SELECT It is used to retrieve data from the database. SELECT column1, column2, … FROM table_name WHERE condition;

 The `SELECT` command is used to retrieve data from the database.

Example:

SELECT name, position, salary
FROM employees
WHERE salary > 50000;

Data Manipulation Language (DML)

DML commands are used to manipulate data stored in the database. The primary DML commands are `INSERT`, `UPDATE`, and `DELETE`.

INSERT

 The `INSERT` command is used to add new records to a table.

Command Description Syntax
INSERT It is used to add new rows of data to a table. INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Example:

INSERT INTO employees (id, name, position, salary, hire_date)
VALUES (1, 'John Doe', 'Manager', 75000, '2023-01-01');

UPDATE

 The `UPDATE` command is used to modify existing records in a table.

Command Description Syntax
UPDATE It is used to modify existing data in a table. UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

Example:

UPDATE employees
SET salary = 80000
WHERE id = 1;

DELETE

 The `DELETE` command is used to remove existing records from a table.

Command Description Syntax
DELETE It is used to delete existing rows from a table. DELETE FROM table_name WHERE condition;

Example:

DELETE FROM employees
WHERE id = 1;

MERGE

The MERGE command is used to perform an UPSERT operation, which inserts or updates data based on whether a condition is met.

Command Description Syntax
MERGE It is used to perform an UPSERT operation (insert or update data based on a condition). MERGE INTO target_table USING source_table ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 WHEN NOT MATCHED THEN INSERT (columns) VALUES (values);

Example : 

MERGE INTO employees AS target
USING (SELECT 1 AS employee_id, 'John' AS first_name, 'Doe' AS last_name, '[email protected]' AS email, '2023-01-01' AS hire_date, 65000 AS salary) AS source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
    UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, email, hire_date, salary)
    VALUES (source.employee_id, source.first_name, source.last_name, source.email, source.hire_date, source.salary);

Data Control Language (DCL)

DCL commands are used to control access to data in the database. The primary DCL commands are `GRANT` and `REVOKE`.

GRANT

The `GRANT` command is used to give permissions to users.

Command Description Syntax
GRANT It is used to give permissions to users. GRANT privilege_name ON object_name TO {user_name | PUBLIC | role_name};

Example:

GRANT SELECT, INSERT
ON employees
TO john_doe;

REVOKE

The `REVOKE` command is used to remove permissions from users.

Command Description Syntax
REVOKE It is used to take back permissions from users. REVOKE privilege_name ON object_name FROM {user_name | PUBLIC | role_name};

Example:

REVOKE SELECT, INSERT
ON employees
FROM john_doe;

Transaction Control Language (TCL)

TCL commands are used to manage transactions in the database. The primary TCL commands are `COMMIT`, `ROLLBACK`, and `SAVEPOINT`.

COMMIT

The `COMMIT` command is used to save all changes made in the current transaction.

Command Description Syntax
COMMIT It is used to save the changes made during the current transaction. COMMIT;

Example:

UPDATE employees
SET salary = 80000
WHERE id = 1;
COMMIT;

ROLLBACK

The `ROLLBACK` command is used to undo changes made in the current transaction.

Command Description Syntax
ROLLBACK It is used to undo changes made during the current transaction. ROLLBACK;

Example:

UPDATE employees
SET salary = 80000
WHERE id = 1;
ROLLBACK;

SAVEPOINT

 The `SAVEPOINT` command is used to set a point in a transaction to which you can later roll back.

Command Description Syntax
SAVEPOINT It is used to undo changes made during the current transaction. SAVEPOINT savepoint_name;

Example:

SAVEPOINT sp1;
UPDATE employees
SET salary = 80000
WHERE id = 1;
ROLLBACK TO sp1;

Detailed Command Descriptions

Data Definition Language (DDL) Commands

CREATE

The `CREATE` command is fundamental in SQL as it is used to create new database objects. The following examples illustrate creating various database objects such as tables and indexes.

Creating a Table:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

Creating an Index:

CREATE INDEX idx_salary
ON employees (salary);

In these examples, the `CREATE TABLE` statement defines a new table named `departments` with columns for `id`, `name`, and `location`. The `CREATE INDEX` statement creates an index on the `salary` column of the `employees` table, which can speed up queries involving the salary column.

ALTER

The `ALTER` command is used to change the structure of an existing database object. It can add, modify, or drop columns in a table, as well as other alterations.

Adding a Column:

ALTER TABLE employees
ADD birth_date DATE;

Modifying a Column:

ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2);

Dropping a Column:

ALTER TABLE employees
DROP COLUMN birth_date;

These examples show how to use the `ALTER TABLE` statement to add a new column (`birth_date`), modify an existing column (`salary`), and drop a column (`birth_date`) from the `employees` table.

DROP

The `DROP` command is used to remove database objects permanently. This command deletes the object and all its data.

Dropping a Table:

DROP TABLE departments;

Dropping an Index:

DROP INDEX idx_salary;

These statements demonstrate how to use the `DROP` command to delete the `departments` table and the `idx_salary` index.

TRUNCATE

The `TRUNCATE` command is used to remove all rows from a table quickly. It is similar to the `DELETE` command without a `WHERE` clause but is faster and uses fewer system and transaction log resources.

Truncating a Table:

TRUNCATE TABLE employees;

This command deletes all rows in the `employees` table, effectively resetting it while retaining its structure.

Data Query Language (DQL) Commands

SELECT

The `SELECT` command is the most commonly used SQL command. It retrieves data from one or more tables and can include various clauses and functions to filter, sort, and aggregate data.

Basic SELECT Statement:

SELECT name, position, salary
FROM employees;

SELECT with WHERE Clause:

SELECT name, position, salary
FROM employees
WHERE salary > 50000;

SELECT with JOIN:

SELECT e.name, e.position, d.name as department
FROM employees e
JOIN departments d ON e.department_id = d.id;

SELECT with Aggregate Function:

SELECT department_id, AVG(salary) as average_salary
FROM employees
GROUP BY department_id;

These examples show how to use the `SELECT` statement to query data from the `employees` table with various conditions and joins.

Data Manipulation Language (DML) Commands

INSERT

The `INSERT` command is used to add new records to a table. It can insert a single row or multiple rows in one statement.

Inserting a Single Row:

INSERT INTO employees (id, name, position, salary, hire_date)
VALUES (2, 'Jane Smith', 'Developer', 60000, '2023-01-15');

Inserting Multiple Rows:

INSERT INTO employees (id, name, position, salary, hire_date)
VALUES 
    (3, 'Alice Johnson', 'Designer', 55000, '2023-02-01'),
    (4, 'Bob Brown', 'Analyst', 50000, '2023-03-01');

These statements illustrate how to use the `INSERT` command to add new records to the `employees` table.

UPDATE

The `UPDATE` command modifies existing records in a table. It can update one or more columns for rows that meet specific conditions.

Updating a Single Column:

UPDATE employees
SET salary = 70000
WHERE id = 2;

Updating Multiple Columns:

UPDATE employees
SET position = 'Senior Developer', salary = 65000
WHERE id = 2;

These examples demonstrate how to use the `UPDATE` command to change the salary and position of an employee in the `employees` table.

DELETE

The `DELETE` command is used to remove existing records from a table. It can delete all rows or specific rows based on a condition.

Deleting Specific Rows:

DELETE FROM employees
WHERE id = 3;

Deleting All Rows:

DELETE FROM employees;

These statements show how to use the `DELETE` command to remove records from the `employees` table.

Data Control Language (DCL) Commands

GRANT

The `GRANT` command is used to provide specific privileges to users or roles. These privileges control what operations the users can perform on the database objects.

Granting Privileges to a User:

GRANT SELECT, INSERT
ON employees
TO john_doe;

Granting Privileges to a Role:

GRANT SELECT, INSERT
ON employees
TO role_employee;

These examples illustrate how to use the `GRANT` command to provide select and insert privileges on the `employees` table to a user and a role.

REVOKE

The `REVOKE` command is used to remove specific privileges from users or roles.

Revoking Privileges from a User:

REVOKE SELECT, INSERT
ON employees
FROM john_doe;

Revoking Privileges from a Role:

REVOKE SELECT, INSERT
ON employees
FROM role_employee;

These statements demonstrate how to use the `REVOKE` command to remove select and insert privileges on the `employees` table from a user and a role.

Transaction Control Language (TCL) Commands

COMMIT

The `COMMIT` command is used to save all changes made in the current transaction. Once a transaction is committed, the changes become permanent.

Committing a Transaction:

UPDATE employees
SET salary = 80000
WHERE id = 2;
COMMIT;

This example shows how to use the `COMMIT` command to save changes made to the `employees` table.

ROLLBACK

The `ROLLBACK` command is used to undo changes made in the current transaction. It reverts the database to the state it was in before the transaction began.

Rolling Back a Transaction:

UPDATE employees
SET salary = 80000
WHERE id = 2;
ROLLBACK;

This example demonstrates how to use the `ROLLBACK` command to undo changes made to the `employees` table.

SAVEPOINT

The `SAVEPOINT` command is used to set a point within a transaction to which you can later roll back.

Creating a Savepoint:

SAVEPOINT sp1;

Rolling Back to a Savepoint:

ROLLBACK TO sp1;

This example illustrates how to use the `SAVEPOINT` command to create a savepoint and then roll back to it.

Important SQL Commands

Some of the most important SQL commands are:

  1. SELECT: Used to retrieve data from a database.
  2. INSERT: Used to add new data to a database.
  3. UPDATE: Used to modify existing data in a database.
  4. DELETE: Used to remove data from a database.
  5. CREATE TABLE: Used to create a new table in a database.
  6. ALTER TABLE: Used to modify the structure of an existing table.
  7. DROP TABLE: Used to delete an entire table from a database.
  8. WHERE: Used to filter rows based on a specified condition.
  9. ORDER BY: Used to sort the result set in ascending or descending order.
  10. JOIN: Used to combine rows from two or more tables based on a related column between them.

Summary Table of SQL Commands

 

Command Description Syntax
CREATE Creates a new database object CREATE TABLE table_name (column1 datatype, column2 datatype, …);
ALTER Modifies an existing database object ALTER TABLE table_name ADD column_name datatype;
DROP Deletes an existing database object DROP TABLE table_name;
TRUNCATE Removes all records from a table TRUNCATE TABLE table_name;
SELECT Retrieves data from the database SELECT column1, column2, … FROM table_name WHERE condition;
INSERT Adds new records to a table INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);
UPDATE Modifies existing records in a table UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;
DELETE Removes existing records from a table DELETE FROM table_name WHERE condition;
GRANT Provides specific privileges to users or roles `GRANT privilege_name ON object_name TO {user_name
REVOKE Removes specific privileges from users or roles `REVOKE privilege_name ON object_name FROM {user_name
COMMIT Saves all changes made in the current transaction COMMIT;
ROLLBACK Undoes changes made in the current transaction ROLLBACK;
SAVEPOINT Sets a point within a transaction to which you can roll back SAVEPOINT savepoint_name;

Conclusion

Understanding SQL commands is crucial for working effectively with databases. DDL, DQL, DML, DCL, and TCL commands each serve specific purposes, from defining and modifying the structure of database objects to managing data and controlling access. By mastering these commands, you can efficiently create, query, update, and manage your database systems. This comprehensive guide provides detailed explanations and examples to help you become proficient in using SQL commands.

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

Published on 01-Jul-2024 10:48:00

You may also like this!