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

The foundations of relational databases are Structured Query Language

Introduction to Structured Query Language

It is important to manage data in databases when people are constantly depending upon the data. SQL is the standard form of language. This language enables the user to have access to manipulate, retrieved, and stored data inside the Relational Database Management System.

A Relational Database Management System (RDBMS) is an accumulation of programs. These programs ensure that the IT experts interact, create, update, and administer using the relational database. The data within the RDBMS is stored in the form of tables. So, to extract and make use of these data, SQL is used.

Functions of SQL

Before you get to know about the functionalities of SQL, it is important to know how you can get structured data from the databases.

Let’s take up an example, the database is a container that contains certain things and you are willing to get the things from the container. So there has to be some way to get the things and things in databases refer to the structured data.

Now, to get these data from the database you need to execute the queries against the databases. These are some of the functionalities that SQL can perform:

• Against the databases, SQL is used to execute queries.
• It also ensures retrieving the data from the databases.
• In databases, SQL can effectively insert records.
• SQL will also help in updating the records in databases.
• For deleting records in databases, SQL can be used.
• SQL can also be used to create new databases.
• For the creation of new tables in new databases, SQL is used.
• The stored procedure can be used in a database using SQL.
• View creation can also be done using SQL.
• Setting up permission and granting access to the databases can also be done using SQL.

Various types of SQL commands

The commands that are mainly used as the SQL are essential to set up communication between RDBMS and the user. These commands are listed in five categories. The illustration is as follows:

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

Data Definition Language (DDL)

DDL has got the SQL commands which are used to define the database schema. The commands are logically used to create and modify the objects within the database. The commands are executed to create, alter, and drop the database structure. 

CREATE Command

This is used to create the database objects which include tables, functions, indexes, procedures, views, and triggers.

Syntax:             

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

  Example:

 CREATE TABLE Employee(    PersonID int,
              LastName varchar(255),
              FirstName varchar(255),
              Address varchar(255),
              City varchar(255)
);

DROP Command

This is used to drop any specific objects from the database.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Shippers;

ALTER Command

This functions to modify certain structures within the objects.

Syntax:

ALTER TABLE table_name

Example:

ALTER TABLE Employee
ADD Email varchar(255);

TRUNCATE Command

The command is specifically used to remove all the data within an object without hampering the actual object structure.

Syntax:

TRUNCATE TABLE  table_name;

Example:

TRUNCATE TABLE Employee;

COMMENT Command

if a comment is required in the data dictionary, then the comment function is used.

Example:

SELECT * FROM Employee -- WHERE City='California';

RENAME Command

This is used to rename an existing object within the database.

Syntax:

ALTER TABLE table_name
RENAME TO new_table_name;

 Example:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

Data Modification Language (DML)

This is to manipulate the language within the database. Several commands are executed to bring some modifications to the existing database objects.

The list of DML commands is listed as follows:

INSERT Command

for inserting data within the objects of the database.

Syntax:

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

Example:

INSERT INTO Employee(EmployeeName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

UPDATE Command

for updating the data within the database.

Syntax:

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

Example:

UPDATE Employee
SET ContactName = 'Sayantan Giri', City= 'Kolkata'
WHERE EmployeeID = 1;

DELETE Command

This is to delete unwanted data from the objects. To delete, a specific where clause is being used so that the essential data is not impacted.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Employee WHERE CustomerName='Abc Def';

• CALL: this is to call a subprogram like JAVA etc.
• LOCK: this is to lock and control the objects that are currently in use.
• EXPLAIN PLAN: this suggests the path through which the data is getting inserted within the objects.

Data Control Language (DCL)

The commands are used to control the access of database objects. It is not necessary that everyone could access the database objects using SQL. Therefore, to prevent unnecessary access to the database DCL commands are used.

The DCL commands include REVOKE and GRANT

REVOKE Command

When a user is expected not to have access then that particular user is being revoked using the command called REVOKE.

Example:

REVOKE select, insert, delete, update on table Employee from user abc;

GRANT Command

for some of the objects, the specific user has to have access. At that time the GRANT command is used for granting access to the user within the database objects.

Example:

GRANT select, insert, delete, update on table Employee to user bcd;

Transaction Control Language (TCL)

within the database objects, there has to be certain transaction and the TCL commands are used to control the transactions within the database.

Some of the TCL commands are as follows:

• ROLLBACK: when there is an error encountered and you are expecting to eliminate it by going back to the previous state then the ROLLBACK command is performed.
• COMMIT: this is to create an ending line for any of the execution taking place within the database.
• SET TRANSACTION: for certain transactions, specific characteristics are expected. This command will specify those characteristics. 
• SAVEPOINT: it creates a saving point within the transaction. Using this command, you can create a partition for a particular transaction.

Data Query Language

The DQL is used for executing queries on the data within the objects of a database. The execution is done by using the SELECT statement. The statement is executed within the database to fetch the data from the database objects and then this data is used for several operations. When a query of a SELECT statement is fired against an object, especially a table then the result gets compiled into a temporary table which gets reflected as output by a program.

SELECT Command

This is specifically used for retrieving the data from the database objects.

Syntax:

SELECT column1, column2, ...
FROM table_name;

Example:

SELECT CustomerName, City FROM Customers;

Conclusion

After going through the above discussions, it becomes easy for an individual to understand SQL. The various types of SQL and its utilities.

This would enlighten users to construct and manipulate a wide range of data within the database. This will also help people to get the correct data that is expected from a bunch or set of data within a table.