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

Updated: December 28, 2024

SQL Command

SQL (Structured Query Language) commands are used to communicate with databases. They support the creation, reading, updating, and deleting of data kept in databases. These commands are classified according to their functions; for example, Data Definition Language (DDL) is used to create and alter database structures, while Data Manipulation Language (DML) is used to handle data, among other categories. Understanding SQL commands is essential for anyone working with databases, as they allow you to manage data efficiently.

Short Overview of SQL

Structured Query Language, or SQL, is the common language for interacting with databases. It helps in managing data by allowing users to perform tasks such as retrieving, updating, and deleting information. SQL is widely used in many applications, from small websites to large enterprise systems, making it an important tool for anyone working with data.

The instructions required to communicate with the database are called SQL commands. They are grouped into different types based on their purpose. For example, some commands help you create and modify a database’s structure, while others let you add, change, or delete data. Each type of command serves a specific role in managing a database effectively.

Types of SQL Commands

The main categories of SQL commands are as follows:

Data Definition Language (DDL)

Database structure can be configured and changed using commands in the Data Definition Language (DDL). These commands allow you to create new databases or tables, change their structure, and delete them when needed. With DDL, you can define how the data will be stored and organized, but it doesn’t deal with the actual data itself. Common DDL commands include CREATE, ALTER, and DROP.

List of DDL Commands

The list of DDL commands is as follows:

CREATE: Used to create a new database, table, or other database objects.

ALTER: Modifies the structure of an existing table, like adding, deleting, or changing columns.

DROP: Deletes a table, database, or any other database object permanently.

TRUNCATE: Removes all data from a table but keeps the table structure for future use.

RENAME: Changes the name of an existing table or other database objects.

Data Manipulation Language (DML)

Data Manipulation Language (DML) instructions are used to work with the data inside a database. These commands let you add new data, update existing information, or delete data that is no longer needed. DML helps you manage the actual data, allowing you to control what is stored in your tables. Common DML commands include INSERT, UPDATE, and DELETE.

List of DML Commands

The list of DML commands is as follows:

INSERT: Adds new data into a table.

UPDATE: Modifies existing data in a table.

DELETE: Removes data from a table.

SELECT: Retrieves data from the database (often considered part of DQL but commonly used with DML).

Data Control Language (DCL)

To regulate access and restrict who can do what with a database’s data, Data Regulate Language (DCL) commands are employed. These commands help you give or take away permissions for different users, allowing you to control who can view, modify, or manage the data. DCL ensures that only authorized users can perform certain actions. Common DCL commands include GRANT and REVOKE.

List of DCL Commands

The list of DCL commands is as follows:

GRANT: Gives specific permissions to a user or role, allowing them to perform certain actions on the database.

REVOKE: Removes previously granted permissions from a user or role, restricting their access to the database.

Transaction Control Language (TCL)

Database transactions are managed using commands in the Transaction Control Language (TCL). A transaction is a series of actions that, in order to maintain data integrity, must be finished all at once. TCL commands help you control these transactions by allowing you to save changes, undo them if something goes wrong, or set specific points to roll back to. Common TCL commands include COMMIT, ROLLBACK, and SAVEPOINT.

List of TCL Commands

The list of TCL commands is as follows:

COMMIT: Saves all the changes made during a transaction, making them permanent in the database.

ROLLBACK: Undoes changes made during a transaction, restoring the database to its previous state.

SAVEPOINT: Sets a specific point within a transaction that you can roll back to if needed, allowing for partial undoing of changes.

Data Query Language (DQL)

To get data out of a database, use Data Query Language (DQL) instructions. The main purpose of DQL is to query and display information stored in the tables. With DQL, you can specify exactly what data you want to see, filter results, and sort them in a particular order. The most common DQL command is SELECT, which allows users to fetch data based on specific criteria.

List of DQL Commands

The list of DQL commands is as follows:

SELECT: Retrieves data from one or more tables in the database based on the specified criteria. This is the primary and most commonly used DQL command.

Important SQL Commands

Some of the most frequently used and important SQL commands include:

SELECT: Retrieves data from the database based on specific conditions.

INSERT: Adds new records (data) into a table.

UPDATE: Modifies existing data in a table.

DELETE: Removes records from a table.

CREATE: Creates a new database, table, or other database objects.

ALTER: Changes the structure of an existing table, like adding or deleting columns.

DROP: Deletes a table or database permanently.

GRANT: Gives permission to a user or role to access or modify the database.

REVOKE: Removes previously granted permissions from a user or role.

COMMIT: Saves all changes made during a transaction, making them permanent in the database.

SQL Commands with Examples

Here is a curated list of popular SQL commands, along with their examples.

SQL Command	Example
SELECT	*SELECT FROM customers;** (Retrieves all data from the customer’s table.)
INSERT	INSERT INTO customers (name, age) VALUES (‘John Doe’, 30); (Adds a new customer named John Doe, who is 30 years old.)
UPDATE	UPDATE customers SET age = 31 WHERE name = ‘John Doe’; (Updates John Doe’s age to 31.)
DELETE	DELETE FROM customers WHERE name = ‘John Doe’; (Removes John Doe from the customer table.)
CREATE	CREATE TABLE orders (order_id INT, product_name VARCHAR(50)); (Creates a new table named orders.)
ALTER	ALTER TABLE orders ADD order_date DATE; (Adds a new column for order date to the orders table.)
DROP	DROP TABLE orders; (Deletes the orders table permanently.)
GRANT	GRANT SELECT ON customers TO user1; (Gives user1 permission to read data from the customers table.)
REVOKE	REVOKE SELECT ON customers FROM user1; (Removes user1’s permission to read data from the customer’s table.)
COMMIT	COMMIT; (Saves all changes made during the current transaction.)

Conclusion about Command of SQL

The management and manipulation of data in a database requires the use of SQL commands. They allow users to create, read, update, and delete data efficiently. Understanding these commands helps you interact with databases effectively, whether you are building applications, analyzing data, or maintaining database systems. By mastering SQL commands, you can ensure that you have the skills needed to work with data in a structured and organized way, making it easier to retrieve the information you need.

FAQS – Command in SQL

What are SQL commands for updating data?

The UPDATE command is one of the SQL commands used to update data. You can modify the current data in a table with this command. To modify particular records, for instance, use the UPDATE table name SET column name = new value WHERE condition.

What are DDL commands in SQL?

Data Definition Language, or DDL for short, is a set of SQL instructions used to specify and control a database’s structure. Common DDL commands include CREATE (to make new tables), ALTER (to change existing tables), and DROP (to delete tables). These commands help set up how data is organized in the database.

What are SQL commands for deleting data?

The SQL command used for deleting data is the DELETE command. This command removes records from a table. For example, you can use DELETE FROM table_name WHERE condition; to delete specific rows.

What are DML SQL commands?

DML stands for Data Manipulation Language. DML SQL commands are used to work with the data in a database. Common DML commands include SELECT (to retrieve data), INSERT (to add new data), UPDATE (to change existing data), and DELETE (to remove data).

What are ALTER SQL commands?

ALTER SQL commands are used to change the structure of an existing database object, like a table. For example, you can use ALTER TABLE table_name ADD column_name data_type; to add a new column, or ALTER TABLE table_name DROP COLUMN column_name; to remove a column.

What SQL commands are used in a database management system?

SQL commands used in a database management system include all the commands mentioned above, such as CREATE (to create tables), SELECT (to read data), INSERT (to add data), UPDATE (to change data), and DELETE (to remove data).