Type of SQL Commands

In SQL (Structured Query Language), there are several types of commands that are used to interact with a database. These commands can be broadly classified into the following categories:

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Query Language (DQL)
4. Data Control Language (DCL)
5. Transaction Control Language (TCL)

Let's discuss each of these categories in detail:

1. Data Definition Language (DDL)

Data Definition Language (DDL) commands are used to define the structure of the database. These commands are used to create, modify, and delete database objects such as tables, indexes, views, etc. Some common DDL commands include:

• CREATE: Used to create new database objects like tables, indexes, views, etc.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50),
  emp_salary DECIMAL(10, 2)
);

• ALTER: Used to modify the structure of existing database objects.

ALTER TABLE
  employees
ADD
  COLUMN emp_dept VARCHAR(50);

• DROP: Used to delete database objects.

DROP TABLE employees;

• TRUNCATE: Used to remove all the records from a table.

TRUNCATE TABLE employees;

2. Data Manipulation Language (DML)

Data Manipulation Language (DML) commands are used to manipulate the data stored in the database. These commands are used to insert, update, delete, and retrieve data from the database. Some common DML commands include:

• INSERT: Used to insert new records into a table.

INSERT INTO
  employees (emp_id, emp_name, emp_salary)
VALUES
  (1, 'Alice', 50000);

• UPDATE: Used to update existing records in a table.

UPDATE
  employees
SET
  emp_salary = 55000
WHERE
  emp_id = 1;

• DELETE: Used to delete records from a table.

DELETE FROM
  employees
WHERE
  emp_id = 1;

• SELECT: Used to retrieve data from a table.

SELECT * FROM employees;

3. Data Query Language (DQL)

Data Query Language (DQL) commands are used to retrieve data from the database. The most common DQL command is the SELECT statement, which is used to retrieve data from one or more tables based on certain conditions.

SELECT
  emp_name,
  emp_salary
FROM
  employees
WHERE
  emp_salary > 50000;

4. Data Control Language (DCL)

Data Control Language (DCL) commands are used to control access to the database. These commands are used to grant or revoke privileges to users and roles. Some common DCL commands include:

• GRANT: Used to grant privileges to users or roles.

GRANT
SELECT
,
INSERT
  ON employees TO user1,
  user2;

• REVOKE: Used to revoke privileges from users or roles.

REVOKE
SELECT
,
INSERT
  ON employees
FROM
  user1;

5. Transaction Control Language (TCL)

Transaction Control Language (TCL) commands are used to manage transactions in the database. These commands are used to control the changes made by DML commands. Some common TCL commands include:

• COMMIT: Used to save the changes made by DML commands.

COMMIT;

• ROLLBACK: Used to undo the changes made by DML commands.

ROLLBACK;

• SAVEPOINT: Used to set a savepoint within a transaction.

SAVEPOINT sp1;

These are the different types of SQL commands that are used to interact with a database. Understanding these commands is essential for working with databases effectively.