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

Introduction

Structured Query Language (SQL) Commands from the name itself it’s very obvious that we are going to discuss different SQL Commands and SQL Commands in DBMS but before that, we will be discussing what SQL is, its use, and its types. In an era when massive volumes of data are generated, there is a constant need to manage data in databases. SQL is the foundation of relational databases, which are one of the most common databases. As a result, SQL abilities are required in almost every employment role. In this blog on SQL Commands, we will deep dive over the most important commands and statements to know in SQL.

Now,

What is SQL?

SQL is the industry standard for database management. It is a database language that allows you to manage and retrieve data from relational databases. All relational database management systems(RDBMS) like SQL Server, MySQL, Oracle, Sybase, Postgres, MS-Access use SQL as their standard database language. 

Why do we use SQL?

There are plenty of reasons for using SQL, listing below a few important reasons for using it:

• It enables users to access data stored in a relational database management system (RDBMS).
  
• It helps in describing the data in a better way.
  
• It enables you to define data in a database and manipulate that data.
  
• You can create and delete databases and tables in DBMS using SQL statements.
  
• SQL allows you to build a view, a stored procedure, and a function in a database.
  
• Permissions for users can be configured on tables, processes, and views.

Types of SQL Commands

In this section, we will be discussing types of SQL commands. SQL Commands are divided into five broad categories – DDL, DML, DCL, TCL, and DQL. Each category is further explained below:

1. Data Definition Language(DDL):

The Data Definition Language is made up of SQL commands that can be used to design the database structure. It simply handles database schema descriptions and is used to construct and modify the structure of database objects in the database. DDL refers to a set of SQL instructions for creating, modifying, and deleting database structures, but not data. A general user, who should be accessing the database through an application, should not execute these instructions. All DDL commands are auto-committed, which means they store all database changes permanently. Different DDL commands that construct the database structure are:

• CREATE Command:  The database or its objects are created with this command (like table, index, function, views, store procedure, and triggers). There are two types of CREATE statements in SQL, one is for the creation of a database and the other for a table. 

A database is a systematic collection of data. To store data in a well-structured manner, the first step with SQL is to establish a database. To build a new database in SQL, use the CREATE DATABASE statement.

Syntax: CREATE DATABASE db_name;
db_name : name of the database(any name can be given)

Example: CREATE DATABASE Company; 
The above example will create a database named Company.

We’ve already learned how to create databases. To save the information, we’ll need a table. In SQL, the CREATE TABLE statement is used to make a table. A table is made up of rows and columns, as we all know. As a result, while constructing tables, we must give SQL all relevant information, such as the names of the columns, the type of data to be stored in the columns, the data size, and so on. Let’s look at how to utilize the CREATE TABLE statement to create tables in SQL in more detail.

Syntax:  CREATE TABLE table_name(
column1 data_type(size),
column2 data_type(size),
column3 data_type(size),
column4 data_type(size),
…..
);

Here,
table_name is the name of a table,
column1 is the name of the first column, 
data_type refers to the type of data that will be stored in this column. For instance, int is used to represent integer data. 
size refers to the maximum amount of data that can be stored in a certain column. 

Example: CREATE TABLE Employee(Emp_Name VARCHAR2(20), DOB DATE, Mobile INT(10), Email VARCHAR2(20));

The above command will create the table schema that look like:

• DROP Command: The DROP command can be used to delete a whole database or simply a table that means entire data will also be deleted. The DROP statement deletes existing objects such as databases, tables, indexes, and views.
  

Syntax: For dropping table: DROP TABLE table_name;
For dropping database: DROP DATABASE db_name;

Example: DROP TABLE Employee; 
DROP DATABASE Company;

The first command in the example will drop the Employee table and the second one will drop the entire database.

• ALTER Command: In an existing table, this command is used to add, delete/drop, or edit columns. It can also be used to create and remove constraints from a table that already exists.

To add a new column:
Syntax: ALTER TABLE table_name ADD column_name COLUMN-definition;
Example: ALTER TABLE Employee ADD Address VARCHAR2(20);
To modify the existing column:

Syntax: ALTER TABLE MODIFY(COLUMN DEFINITION….);

Example: ALTER TABLE Employee MODIFY(Emp_Name VARCHAR2(25));

The above command will modify the ‘Emp_Name’ column to data type VARCHAR2 with size 25.

• TRUNCATE Command: used to indicate the table’s extents for deallocation (empty for reuse). This procedure removes all data from a table quickly, usually circumventing a number of integrity checking processes. It was included in the SQL:2008 standard for the first time. It is somewhat equivalent to the delete command.

Syntax: TRUNCATE TABLE  table_name;
Example: TRUNCATE TABLE Employee;

The above command will delete the data from the ‘Employee’ table but not the table.

2. Data Manipulation Language:

The SQL commands that deal with manipulating data in a database are classified as DML (Data Manipulation Language), which covers the majority of SQL statements. It’s the part of the SQL statement that regulates who has access to the data and the database. DCL statements are grouped together with DML statements. The DML command is not auto-committed, which means it cannot preserve all database modifications permanently. They have the potential to be rolled back. 

Different DML commands are:

• INSERT Command: It is used to insert data into a table’s row.
  

Syntax: INSERT INTO TABLE_NAME  (col1, col2, col3,…. col N)  
VALUES (value1, value2, value3, …. valueN);  

Or  
INSERT INTO TABLE_NAME   
VALUES (value1, value2, value3, …. valueN);  

Example: INSERT INTO Employee(Emp_Name, DOB, Mobile, Email)
VALUES(‘Joe’, ‘1995-02-16’, 7812865845, ‘joe@gmail.com’);

The above command will insert the mentioned values in the ‘Employee’ table.

• UPDATE Command: In SQL, the UPDATE statement is used to update data in an existing database table. We can use the Alter statement to update single or several columns depending on our needs.
  

Syntax: UPDATE table_name SET column1 = value1, column2 = value2,…
WHERE condition;

Here,
table_name: name of the table
column1: name of first , second..
value1: new value for first, second….
condition: condition to select the rows for which the values of columns need to be    updated.

Example: UPDATE Employee SET Mobile=9935467903 WHERE Emp_Name=’Joe’;

The SET statement is used in the above query to assign new values to a specific column, and the WHERE clause is used to select the rows for which the columns need to be modified. If we don’t utilize the WHERE clause, all of the rows’ columns will be updated. As a result, the WHERE clause is utilized to select certain rows.

So, the example query will update the mobile number of the employee with name ‘Joe’.

• DELETE Command: In SQL, the DELETE statement is used to delete records from a table. Depending on the condition we set in the WHERE clause, we can delete a single record or numerous records.
  

Syntax: DELETE FROM table_name [WHERE condition];

Example: DELETE FROM Employee WHERE Emp_Name=’Joe’;

The above command will delete the record for employee with name ‘Joe’ from ‘Employee’ table.

3. Data Control Language(DCL):

DCL (Data Controlling Language) is a query language that allows users to retrieve and edit data held in databases. The types of Data Controlling Language commands include Grant and Revoke.

• GRANT Command: User access privileges to a database are given by this command. It can be used to grant SELECT, INSERT, UPDATE, and DELETE privileges to a user on a single table or several tables.

Syntax: GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

Example: GRANT INSERT, SELECT on accounts to Alex

Using this command, Alex has been granted permissions on accounts database objects like he can query or insert into accounts. 

• REVOKE Command: To take back permissions from the user REVOKE command is used. It is used to revoke a privilege (by default) or a specific command, such as UPDATE or DELETE, depending on the situation.

Syntax: REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}

Example: REVOKE INSERT, SELECT on accounts from John

Using this command, the permissions of John like query or insert on accounts database objects has been removed. 

4. Transaction Control Language(TCL):

Transaction Control Language (TCL) instructions are used in the database to manage transactions. This command is used to handle the DML statements’ modifications. TCL allows you to combine your statements into logical transactions.

Below mentioned are some of the TCL commands:

• COMMIT Command: To save all the transactions in the database Commit is used.
  

Syntax: COMMIT;

Example: UPDATE Employee SET DOB=’1995-02-17’ WHERE Emp_Name=’Joe’;
COMMIT;
This example will insert the dob in the table which have name = Joe and then COMMIT the changes in the database.

• ROLLBACK Command: All modifications must be canceled if any of the SQL grouped statements produce an error. The term “rollback” refers to the process of undoing changes. This command can only be used to reverse transactions that have occurred since the last COMMIT or ROLLBACK command.

Syntax: ROLLBACK;

Example: UPDATE Employee SET DOB=’1995-02-17’ WHERE Emp_Name=’Joe’;
ROLLBACK;

This example would insert the dob in the table which have name = Joe and then ROLLBACK the changes in the database. Thus, this operation would not impact the table.

• SAVEPOINT Command: It’s used to roll back a transaction to a specific point rather than the complete transaction.
  

Syntax: SAVEPOINT SavepointName;

Among all transactions, this command is exclusively used to create SAVEPOINT.
ROLLBACK is a command that is used to undo a set of transactions.

The syntax for rollback to savepoint command:
ROLLBACK TO SavepointName;

Example: SAVEPOINT S1; //savepoint created
DELETE FROM Employee WHERE Emp_Name = ‘Joe’; //deleted
SAVEPOINT S2; //Savepoint created.

5. Data Query Language(DQL): 

DQL statements are used to query the data contained in schema objects. The DQL Commands goal is to return a schema relation depending on the query supplied to it. DQL can be defined as follows: It’s a part of a SQL statement that lets you get data from a database and put it in order. The SELECT statement is included. This command allows you to extract data from a database in order to conduct actions on it. This is the same as the relational algebra projection operation. When a SELECT statement is executed on a table or set of tables, the result is compiled into a new temporary table, which is then displayed or received by the program, i.e. a front-end. 

The database server examines the chosen clause, which is the first and one of the last clauses of the select statement. The reason for this is that we need to know all of the alternative columns that could be included in the final result set before we can decide what to include in it.

            Syntax: SELECT expressions FROM Tables WHERE conditions;

            Example: SELECT Emp_Name FROM Employee WHERE Mobile=9935467903;

The above command will select the record from the ‘Employee’ table where the mobile number is ‘9935467903’.

Conclusion

So far we have discussed all the five categories of SQL Commands i.e DCL, DML, DDL, TCL and DQL and it’s subtypes. We’ve gone through each command in detail with its syntax and example  that will assist you in writing queries. The SQL commands’ allows you to construct and manipulate a wide range of database objects with the different commands. After going through this blog one can easily play with the SQL commands and interact with their database easily.

FAQS

Q.1: What are some of the basic SQL Commands?

Ans: Some basic SQL commands are divided into five categories i.e. DDL, DML, DCL, TCL, and DQL. Among these categories, each category has its subtypes that are CREATE, INSERT, DROP, DELETE, UPDATE, etc.

Q.2: What is SQL syntax code?

Ans: The syntax is a collection of rules and recommendations that SQL adheres to. All SQL statements begin with one of the following keywords: SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW, and terminate with a semicolon (;).

Q.3: How do I write a SQL query?

Ans: Below mentioned points help us in writing SQL Query:

• Provide correct formatting for the query
  
• Specify the SELECT fields instead of using SELECT*
  
• Discard correlated queries if they are not needed.
  
• Try to limit the results obtained by the query.
  
• Discard DISTINCT clause if possible
  
• Avoid using functions in predicates
  
• AND, OR, NOT operator should be avoided 
  
• Use WHERE clause instead of HAVING
  

Q.4: What is DDL in SQL?

The SQL commands that can be used to describe the database structure are collectively known as Data Definition Language. It simply works with database schema descriptions and is used to create and modify the database’s database object structure. DDL refers to a set of SQL instructions for creating, modifying, and deleting database structures but not data. A common user should access the database using an application rather than using these instructions.   

Additional Resources

• SQL Interview Questions
• SQL IDEs
• Difference Between SQL and PLSQL
• SQL Vs NoSQL
• SQL Vs MySQL
• SQL Books