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

Do you know that SQL is used by 40% of all developers globally? It is a tremendously powerful language used for database management, making it a preferred choice of about 67% of database administrators. Due to its efficiency and adaptability, SQL has consistently been a valuable tool for developers, data analysts, and businesses alike. Therefore, learning SQL can be beneficial if you wish to join the tech workforce. 

This blog will provide a collection of all SQL commands considered essential for the development and optimization of a database. Practice these commands to effectively use them on any dataset and produce quality results. This will enhance your domain-specific expertise and boost your chances of excelling in any technical assessment during the hiring process.

An Introduction to SQL Commands

Every industry, from finance to technology, deals with enormous quantities of information. As a result, these sectors analyze and extract valuable data. SQL is a language used to retrieve and handle information from relational databases.

SQL commands in dbms are directives for communicating with a dataset in anticipation of carrying out actions, operations, and inquiries on data. These commands can be applied to perform database searches as well as to create records, add information to tables, edit data, and discard tables.

Get a confirmed ₹35,000 total stipend on your first internship with our data analyst course with placement.

5 Different Types of SQL Commands

Various types of SQL commands serve different purposes in managing and manipulating databases. These types of commands include data definition language (DDL), which defines and modifies database structure; data manipulation language (DML), which is used to manipulate data within tables; data control language (DCL), which governs the access privileges and permissions; transactional control language, which manages the transactional aspect of database operations; and data query language (DQL), which allows retrieval and organization of data from a database.

These SQL commands are essential to perform SQL operations and aid developers and database administrators in ensuring data consistency, integrity, and accessibility. 

1. DDL or Data Definition Language

Data definition language (DDL) is a collection of SQL commands that are used to build, change, and delete database structures. They describe the database schema and are used to design the layout of the objects stored in the database. DDL commands do not affect the data in the database.

Here is the list of DDL commands:

CREATE: It is used in the creation of the database and includes items or objects, such as a table, views, index, or/and stored procedure function, and triggers.

Syntax for Creating a Table:

CREATE TABLE table_name (column1 datatype, column2 datatype, ….);

Example:

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), DOB DATE);

DROP: It is used to remove objects from any database. It deletes both the structure and record stored in the table. 

Syntax for Dropping a Table:

DROP TABLE table_name;

Example:

DROP TABLE EMPLOYEE;

ALTER: This can be utilized to change the database’s design or structure. The ALTER command can be used to either modify the characteristics of an existing attribute or add a new attribute altogether. 

Syntax to Modify Existing Column in the Table:

ALTER TABLE table_name MODIFY(column_definitions....); 

Example:

ALTER TABLE EMP_DETAILS MODIFY (NAME VARCHAR2(20));

Syntax to Add a New Column to the Table:

ALTER TABLE table_name ADD column_name COLUMN-definition;    

Example:

ALTER TABLE EMP_DETAILS ADD(ADDRESS VARCHAR2(20)); 

TRUNCATE: This command can be employed to eliminate all records or entries from a table, along with all allotted spaces for those items. Though the data is deleted from the table, the structure of the table remains. It is often used to clear large amounts of data from a table. 

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE EMPLOYEE

COMMENT: This command is used to insert comments into the data dictionaries. Comments help the developers or administrators to understand the intent and functionality of an object in a database.

Syntax:

COMMENT ON TABLE table_name IS 'This is a comment.';

Example:

COMMENT ON TABLE employees IS 'This table stores information about company employees.';

RENAME: It is used while renaming an existing object in the system of the database.

Syntax:

RENAME TABLE old_table_name TO new_table_name;

Example:

RENAME TABLE sales_data TO monthly_sales;

Explore our blog on delete and truncate in SQL to know more.

2. DQL or Data Query Language

Data query language (DQL) is a set of SQL commands that are used to query data inside schema objects. It fetches a schema relationship based on the query provided. With these commands, you can retrieve data from the database and create a structure for it. 

DQL has a SELECT command and its clauses that allow the user to extract data and execute actions on single or multiple tables. Query results for this command are stored in a temporary table that is later retrieved by the application or the front end to access the content of the database.

Syntax:

SELECT column1, column2, ... FROM table_name;

Example:

SELECT first_name, salary FROM employees;

Certain clauses are used together with the SELECT command to retrieve relevant information. These include: 

• JOIN Clause: It is used to combine rows from two or more tables based on one related column between them. There are different types of JOIN clauses, including INNER JOIN, RIGHT JOIN, LEFT JOIN, and FULL JOIN.

Syntax: SELECT columns FROM table1 [JOIN TYPE] JOIN table2 ON table1.column_name = table2.column_name;

Example: Here is a SQL query that uses INNER JOIN to retrieve information from the employee table.

SELECT employees.first_name, employees.last_name, departments.department_name 

FROM employees 

INNER JOIN departments 

ON employees.department_id = departments.department_id;

• WHERE Clause: It is used with the SELECT command to filter records based on specific conditions. 

Syntax: SELECT column1, column2, … FROM table_name WHERE condition;

Example: SELECT product_name, price FROM products WHERE category = ‘Electronics’;

• ORDER BY Clause: It is used to sort the results in ascending or descending order. 

Syntax: SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Example: Here is an example of an SQL query to select specific columns from the ‘students’ table. 

SELECT first_name, last_name 

FROM students 

ORDER BY age ASC, last_name DESC;

• GROUP BY Clause: It is used with aggregate functions to group the result according to one or more columns. 

Syntax: SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

Example: Here’s an example of an SQL query using the SELECT command with ‘SUM’ aggregate function to add the sales amounts grouped by ‘region’. 

SELECT region, SUM(amount) 

FROM sales 

GROUP BY region;

3. DML or Data Manipulation Language

DML (Data Manipulation Language) refers to SQL instructions that deal with the alteration of information stored within a database, which constitutes the majority of SQL queries. This is a SQL statement component that governs the database and information access. The DCL statements are categorized together with data manipulation statements. Here is the list of DML commands:

Here is the list of DML commands:

INSERT: This command inserts data into an existing table. You can insert multiple rows in a table using this command. You can also insert data either by specifying the column names in the SQL query or by not specifying the column name. 

Syntax:

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

Example:

INSERT INTO students (first_name, last_name, age) VALUES ('John', 'Doe', 20);

UPDATE: This command is employed in updating the existing data in a table. The condition specified in the query decides which row is to be updated. If no condition is specified, all the records are updated. You can update single as well as multiple columns. 

Syntax:

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

Example: Here’s an example of an SQL query to update the salary of an employee: 

UPDATE employees 

SET salary = 60000 

WHERE employee_id = 101;

DELETE: This command is used to remove records from a database table. WHERE clause is used with this command to specify which rows are to be deleted. If the WHERE clause is not added, all the rows will get deleted. 

Syntax:

DELETE FROM table_name WHERE condition;

Example: The following SQL query will delete all the rows from the ‘employees’ table where the ‘department_id’ is equal to 5.

DELETE FROM employees
WHERE department_id = 5; 

LOCK: This command is used for concurrent table control. The LOCK command ensures data consistency and prevents conflicts between transactions. 

Syntax:

LOCK TABLE table_name [ IN lock_mode ];

Different lock modes in this command include ROW SHARE, ROW EXCLUSIVE, SHARE, and EXCLUSIVE. 

Example:

LOCK TABLE employees IN ROW EXCLUSIVE

CALL: This command invokes a PL/SQL or JAVA subprogram. It can execute a standalone procedure or a procedure defined within a type. The data types of the parameters passed by the CALL statement must be the SQL data types.  

Syntax:

CALL procedure_name(argument1, argument2, ...);

Example:

CALL get_employee_info(123);

In the above example, the SQL query calls the stored procedure ‘get_employee_info’ with the argument ‘123’. It will retrieve and display the content stored in the procedure with respect to the employee ID 123 from the employees table.

EXPLAIN PLAN: It is used to determine the execution plan that the database follows to execute a specified SQL statement. 

Syntax:

EXPLAIN <sql_statement>;

Example:

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

The above example will display the execution plan for the ‘SELECT’ query. 

4. DCL or Data Control Language

These SQL commands comprise GRANT and REVOKE, which primarily interact with the database system’s rights, permits, and other restrictions. Here is the list of DCL commands:

Here is the list of DCL commands:

GRANT: It grants people database access or special privileges on database objects.

Syntax:

GRANT permission_type ON object_type::object_name TO user_role;

Example:

GRANT SELECT ON TABLE employees TO user_john;

REVOKE: It removes the user’s access permissions granted using the GRANT command.

Syntax:

REVOKE permission_type ON object_type::object_name FROM user_role;

Example:

REVOKE SELECT ON TABLE employees FROM user_john;

5. TCL or Transaction Control Language

A transaction is a collection of tasks that are executed as a single entity. Each transaction commences with a particular task and finishes when all of the tasks throughout the group are accomplished. The transaction fails when one or more of the tasks misses. As a consequence, a transaction has just two outcomes: either it succeeds or it fails. Here is the list of TCL commands:

Here is the list of TCL commands:

BEGIN: This command initiates a transaction. It marks the beginning of a block of SQL statements that are considered as a single unit. 

Syntax:

BEGIN [TRANSACTION];

Example: In the following example, we start the transaction with the ‘BEGIN TRANSACTION’ command, update the status of specific orders in the ‘Orders’ table, and then commit the transaction to save the changes. 

BEGIN TRANSACTION;

-- Update the "Status" column of the "Orders" table

UPDATE Orders

SET Status = 'Shipped'

WHERE OrderID = 123;

COMMIT;

COMMIT: This command commits or terminates a transaction. It is used to permanently save changes made during a transaction to the database. 

Syntax:

COMMIT;

Example: The following example begins a transaction, performs SQL statements, and commits the changes to the database. 

BEGIN;

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

COMMIT;

ROLLBACK: This command reverts a transaction if an error appears. It will undo changes made during a transaction and are not saved to the database. .

Syntax:

ROLLBACK;

Example: In the following code, we will undo the changes with the ROLLBACK command. 

BEGIN;
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

-- Something went wrong, let's rollback the changes

ROLLBACK;

SAVEPOINT: It creates a transaction save point. You can set a point within a transaction to which you can later roll back. It does not rollback the entire transaction but only up to a certain point. 

Syntax  to Create a Savepoint:

SAVEPOINT savepoint_name;

Syntax to Rollback to the Savepoint:

ROLLBACK TO SAVEPOINT savepoint_name;

Example: Refer to the following example to understand how to create and rollback to a savepoint using SQL. 

BEGIN;

-- Make some updates

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

-- Create a savepoint after the first update

SAVEPOINT update1;

-- Make another set of updates

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

-- Oops, something went wrong, let's roll back to the first update

ROLLBACK TO SAVEPOINT update1;

-- Continue with other operations if needed

-- Commit the transaction

COMMIT;

SET TRANSACTION: It defines the transaction’s parameters.

Syntax:

SET TRANSACTION [ transaction_characteristics ];

Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

SQL Commands List and Syntax

SQL basic commands are straightforward and declarative expressions. The syntax is simple to grasp, and handling records in SQL databases systematically and reliably is not difficult.

It is important to know that SQL instructions are case insensitive, which renders them even more useful. Minor mistakes, such as incorrect capitalization, will not derail your script. Yet, it is customary to express the commands in all uppercase to ensure clarity. This strategy will also be used in the following table. You can also pursue an online SQL course to gain practical expertise in using these commands. 

The following is a list of frequently used SQL commands in DBMS along with their syntax and description. 

SQL Command	Syntax	Description
ALTER TABLE	ALTER TABLE table_name ADD column_name datatype;	This is used to add, modify, or remove columns in a table.
AND	SELECT column_name(s)FROM table_nameWHERE column_1 = value_1  AND column_2 = value_2;	This is an operator that is used to combine two different conditions. It displays a record if both the conditions are true.
AS	SELECT column_name AS ‘Alias’ FROM table_name;	This operator is used for renaming a table or column using an alias name. Note that the alias only remains for the duration of the query.
AVG	SELECT AVG(column_name)FROM table_name;	This is used to find the average of the numerical values in a column.
BETWEEN	SELECT column_name(s)FROM table_nameWHERE column_name BETWEEN value_1 AND value_2;	This clause is used to filter the result within a specified range.
CASE	SELECT column_name,CASE WHEN condition THEN ‘Result_1’WHEN condition THEN ‘Result_2’ELSE ‘Result_3’ENDFROM table_name;	This expression is used to execute a block of conditional statements. It goes through the conditions and returns a value when the first condition is met.
COUNT	SELECT COUNT(column_name)FROM table_name;	This expression requires a column name as an argument. It counts the row number when the column is not NULL.
CREATE TABLE	CREATE TABLE table_name (  column_1 datatype,   column_2 datatype,   column_3 datatype);	This is used to create a table inside a database. You can define the name of the table and columns inside it using this command.
DELETE	DELETE FROM table_nameWHERE some_column = some_value;	This command simply removes the rows from a table.
GROUP BY	SELECT column_name, COUNT(*)FROM table_nameGROUP BY column_name;	This is a clause used with the SELECT statement to arrange identical data into groups.
HAVING	SELECT column_name, COUNT(*)FROM table_nameGROUP BY column_nameHAVING COUNT(*) > value;	This clause checks the condition defined by GROUP BY clause in a group of rows. It is essentially used for aggregate functions.
INNER JOIN	SELECT column_name(s)FROM table_1JOIN table_2  ON table_1.column_name = table_2.column_name;	This is used to merge rows from separate tables where there are matching values in a field common in both the tables.
INSERT	INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, ‘value_2’, value_3);	You can insert new rows in a table with this command.
IS NULL/ IS NOT NULL	SELECT column_name(s)FROM table_nameWHERE column_name IS NULL;	This operator searches for null or empty values. It is used with the WHERE clause.
LIKE	SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern;	If you have to search for a pattern in a column, use this keyword together with WHERE clause
LIMIT	SELECT column_name(s)FROM table_nameLIMIT number;	This expressions specifies the maximum number of rows required in the output.
MAX	SELECT MAX(column_name)FROM table_name;	This function returns the highest value from a selected column. The argument in this function should be a numerical value.
MIN	SELECT MIN(column_name)FROM table_name;	This function returns the smallest value from a selected column. It operates on numerical values.
OR	SELECT column_nameFROM table_nameWHERE column_name = value_1   OR column_name = value_2;	This is an operator used to limit the result set to display rows where either of the conditions is TRUE.
ORDER BY	SELECT column_nameFROM table_nameORDER BY column_name ASC | DESC;	You can use this clause to sort your results by a certain column. Sorting can be done numerically or alphabetically, in descending or ascending order.
OUTER JOIN	SELECT column_name(s)FROM table_1LEFT JOIN table_2  ON table_1.column_name = table_2.column_name;	This is a method of combining two or more tables. The result includes unmatched rows of one or both of the tables. So, even if the condition is not met, mixed rows are issued.
ROUND	SELECT ROUND(column_name, integer)FROM table_name;	This function is useful in rounding off the numerical entries in a column to the specified number of decimal places or integer value.
SELECT	SELECT column_name FROM table_name;	It is a command used to retrieve information from a database.
SELECT DISTINCT	SELECT DISTINCT column_nameFROM table_name;	This command is used to specify that the statement is a query and returns distinct values in the columns.
SUM	SELECT SUM(column_name)FROM table_name;	This function gives an output with the sum of data from the specified column. You can only use the SUM command on columns with numerical values.
UPDATE	UPDATE table_nameSET some_column = some_valueWHERE some_column = some_value;	This command allow you to modify table rows.
WHERE	SELECT column_name(s)FROM table_nameWHERE column_name operator value;	It is a clause that filters rows from the result set based on specified conditions. It limits the output to display only those rows for which the condition is TRUE.
WITH	WITH temporary_name AS (SELECT *FROM table_name)SELECT *FROM temporary_nameWHERE column_name operator value;	You can store the result of a query in a transient table with this command. It saves the output under an alias.

Conclusion

The internet is rife with databases that hold immense quantities of information on virtually any topic you can think of. With the various types of SQL commands in DBMS, you can construct and modify a huge spectrum of data structures. After reading this blog, you can simply experiment with SQL commands and communicate with their dataset.

Which SQL command do you find most useful while working with databases? Share your answer with us in the comments section below. If you’ve applied for an SQL-based job recently or are thinking of exploring the domain, brush up your technical knowledge with the top SQL interview questions.

FAQs