A collection of related data is called a database. Database Management System is the most popular way to handle these databases. A relational database management system is used to store data in the form of tables. SQL (Structured Query Language) is the language through which the users can interact with the database systems.
SQL commands are used to perform operations on the database such as create, insert, delete or drop data tables. These commands are categorized into different categories. They are DDL, DQL, DCL and DML.
In this article, we will take a closer look at the types of SQL commands, primarily at DDL and DML.
DML vs DDL
Basis of comparison
This language is used for describing the structure of a database.
This language is used for modifying and fetching data from a database.
The DDL commands cannot be classified further.
The DML commands can be divided into Procedural DML and Declarative DML.
Commonly used commands
The commands under DDL are Create, Drop, Truncate, Alter and Rename.
The commands under DDL are Select, Update, Delete and Insert.
The statements cannot be rolled back.
Rollback is possible for the DML statements.
There is no such use of the Where clause.
The Where clause is used for specifying conditions while manipulating data in a database.
These commands affect the complete database.
These commands will affect only one or more records in the table.
Types of SQL Commands
Developed by IBM way back in 1970, SQL is the standard programming language for handling databases. It has different types of commands to handle database queries. The major types are:
DDL stands for Data Definition Language. The different commands in the language are used for defining the structure or schema of a database. The SQL commands that fall under DDL are:
DML stands for Data Manipulation Language. The commands in this language are used for modifying or altering the contents of the database. The SQL commands within DML are as follows:
TCL (Transaction Control Language)
The commands that come under this language can only use the DML commands for its operations. You can manage the transactions of the database using these commands. Additionally, you can group Query statements into logical statements. The commands that fall under this category are:
DCL (Data Control Language)
This language provides commands that you can use to control the data access within a database. These commands are used to offer or take back privileges from other users of the database:
DQL (Data Query Language)
Data queries can be performed within the schema objects using DQL. These commands basically used for pulling data from databases. The fetching of data and its corresponding results will depend upon the conditions provided by the user. The only command that comes under DQL is:
What is DDL?
DDL is used to develop the structure of the database objects and the database itself. The changes made by the commands are saved permanently within the database. This is because the commands are all auto committed. The database objects DDL handles include views, tables, schemas and indexes.
They are considered a subset of SQL. The commands under DDL are as follows:
This command is used to create a new table within the database. It also creates objects within the database. The syntax for this command is:
CREATE TABLE <tablename>
( column_name 1 data type,
column_name 2 data type,
column_name 10 data type
You can use this command to remove or drop an entire table or database. The command destroys the structure of the table and the data within it. The syntax is as follows:
DROP TABLE < name_of_table>;
DROP DATABASE <name_of_database>;
This command is used for deleting all the records from a table. The spaces assigned for the records are also removed. But, this command does not destroy the structure of the database. If this command is used, the primary key of the table is initialised. The syntax is:
The Rename command is used for renaming the name of an existing table. The syntax is as follows:
RENAME old_name to new_name;
Comments can be written in SQL in three ways:
Single line comment – These comments start and end within the same line. The syntax is:
--this is a single line comment
Multi-line comment – These comments can occupy multiple lines. The syntax is:
/* This is a multi-line comment
about a Select statement*/
This command is used for altering the structure of the table within a database. This can be used to add, delete or modify the columns of a table. The syntax is:
1.ALTER TABLE <table_name>
ADD <column_name datatype>;
2.ALTER TABLE <table_name>
DROP COLUMN <column_name datatype>;
3.ALTER TABLE <table_name>
MODIFY <column_name column_type>;
What is DML?
DML is used for manipulating and managing the data within a database. DML commands allow you to select, delete, modify and insert information. The commands in DML are not auto committed. So, the modifications made within the database are not saved permanently. Therefore, performing rollback operations is possible.
There are two types of DML. They are Procedural DML and Declarative DML. In Procedural DML, the process of retrieving data is defined. But in the case of Declarative DML, which particular data needs to be obtained is described. It is easier for any user to use Declarative DML as they can only specify only what type of data they require.
The different type of DML commands are as follows:
This command is used for fetching some specific data from the database. A condition can also be mentioned for retrieving data from the database. The result may be in the form of one or more tables.
The syntax is:
SELECT * FROM employee
where Salary > 50,000;
Here, * is the universal selector that indicates all rows and columns matching the criteria has to be selected.
The command has some option clauses. They are Where, Order By, Group By, As and Having.
You can use this command for adding some data in an existing table. The syntax is as follows:
INSERT INTO <table_name> (`column_name A` <datatype>, `column_name B` <datatype> <database_name>)
VALUES (`value 1`, `value 2`);
The Update command is used for updating and modifying the current records in a table. Using this command you can alter the data for one or more records. The syntax for the command is:
SET salary = 78,000
Here, Employee is the table name and salary is the name of the column. The condition is specified using the emp_name.
The Delete command is used for deleting specific records from a table. This removal will be based on a certain condition. The command can delete all records matching the specified condition. The syntax is as follows:
DELETE FROM Students
WHERE Stud_ID = '001';
If we do not provide the Where condition, all the records in the table will be deleted.
For handling records in any database, both DDL and DML are necessary. When handling a large database consisting of multiple records, you need to use them properly. While filtering data from a table, make sure you have mentioned the condition properly in the Where clause. If you do not mention a condition, the entire table will be deleted.
For making any structural modifications, the commands mentioned under DDL will come in handy.