Types of SQL Commands

SQL is a structured query language, which is used to deal with structured data. Structured data is data that is generally stored in the form of relations or tables.

Whenever we store the data in tables or relations, we need SQL commands. Moreover, these commands are also required to retrieve the data which is stored in tables.

Let us take a deeper dive into the classification of SQL commands with the help of practical examples. We will use the MySQL database for writing all the queries.

(A) DDL

• DDL stands for data definition language. DDL Commands deal with the schema, i.e., the table in which our data is stored.
• All the structural changes such as creation, deletion and alteration on the table can be carried with the DDL commands in SQL.
• Commands covered under DDL are:
  1. CREATE
  2. ALTER
  3. DROP
  4. TRUNCATE
  5. RENAME

Let us see each of the commands in the DDL category with more details.

1. CREATE:

In SQL, whenever we wish to create a new database or a table in a database, we use CREATE command.

Syntax to create a new database:

Syntax to create a table:

Example 1:

Write a query to create a database and give the name of the database as school.

Query:

Here, we have executed a CREATE DATABASE query followed by the database name 'SCHOOL'.

We will execute the following command to verify that the database 'SCHOOL' is created:

The results of the above command verify that the 'SCHOOL' database is created successfully.

Example 2:

Write a query to create a table in the database 'SCHOOL' and give the table's name as t_school.

To create a table 't_school' in the 'SCHOOL' database, we must select the 'SCHOOL' database.

To select the database in MySQL, we will execute the following query:

We have executed the 'USE command' followed by the database name, i.e., 'SCHOOL'.

Now just after the execution of this query, we will execute the following query:

Here, we have executed a CREATE TABLE query followed by the table name 't_school'.

We will execute the following command to verify that the table 't_school' is created:

The above command results verify that the 't_school' table is successfully created in the 'SCHOOL' database.

2. ALTER

In SQL, whenever we wish to alter the table structure, we will use the ALTER command. Using alter command, we can make structural changes to the table, such as adding a new column, removing or deleting an existing column from the table, changing the datatype of an existing column and renaming an existing column.

Let's look at the syntax before writing the queries using ALTER command.

Syntax of ALTER command to add a new column:

Syntax of ALTER command to delete an existing column:

Syntax of ALTER command to rename the existing table's column:

Syntax of ALTER command to change the datatype of an existing column:

Example 1:

Write a query to add a new column Board_of_Education in the t_school table of the datatype VARCHAR.

Before executing a query to add a new column to the 't_school' table, we will execute the following query to see the table structure:

Here, we have executed the 'ALTER command' on the table t_school followed the ADD keyword with the column 'Board_of_Education', the datatype VARCHAR and size 20. This simply means a new column named 'Board_of_Education' with the datatype VARCHAR and size 20 will be added to the existing 't_school' table.

Now, we will again apply the DESC command on the t_school table.

This verifies that the new column 'Board_of_Education' is successfully added to the t_school table.

Example 2:

Write a query to remove the column Board_of_Education from the t_school table.

Before executing a query to remove a column from the 't_school' table, we will execute the following query to see the table structure:

We have executed the 'ALTER command' on the table t_school followed the DROP COLUMN keyword with the column 'Board_of_Education'. This simply means that the existing column named 'Board_of_Education' will be removed from the 't_school' table.

Now, we will again apply the DESC command on the t_school table.

This verifies that the column 'Board_of_Education' has successfully removed from the t_school table.

Example 3:

Write a query to rename the column Number_of_Students to 'Count_Students' using the alter command from the t_school table.

Before executing a query to rename a column from the 't_school' table, we will execute the following query to see the table structure:

We have executed the 'ALTER command' on the table t_school followed by the RENAME COLUMN keyword. Here, Number_of_Students is the old column name and the column name specified after the TO keyword, i.e., Count_Students is the new column name. This simply means that the existing column named 'Number_of_Students' will be replaced by 'Count_Students' from the 't_school' table.

Now, we will again apply the DESC command on the t_school table.

This verifies that the column 'Number_of_Students' is successfully renamed to 'Count_Students' in the t_school table.

Example 4:

Write a query to change the datatype of 'Number_of_Students' and set the new datatype as 'VARCHAR' with size '20' in the t_school table.

Before executing a query to modify the column datatype and size in the 't_school' table, we will execute the following query to see the table structure:

We have executed the 'ALTER command' on the table t_school followed by the MODIFY keyword. Here, ID is the column name, and VARCHAR is the new datatype of the ID column followed by the size, i.e., 20.

Now, we will again apply the DESC command on the t_school table.

3. DROP

DROP command is used to remove or delete the table's records and the table's structure from the database.

Syntax:

Example:

Write a query to delete the t_school table from the SCHOOL database.

Query:

Here, we have executed a DROP TABLE command on the table 't_school'.

We will execute the following command to verify that the table 't_school' exists or not.

The above command results verify that the 't_school' table is successfully removed from the 'SCHOOL' database.

4. TRUNCATE

A TRUNCATE command is used to delete the table's records, but the table's structure will remain unaffected in the database.

Syntax:

Example:

Write a query to remove all the records from the 't_school' table.

Before executing a query to remove the records from the 't_school' table, we will execute the SELECT query to see records present in the table:

Here, we have executed the 'TRUNCATE command' on the table t_school. This simply means that all the records from the 't_school' table will be removed, keeping the table structure as it is in the database.

Now, we will again apply the SELECT query on the t_school table.

The results above show that all the records from the 't_school' table are removed successfully.

5. RENAME

Rename COMMAND is used to give a new name to an existing table.

Syntax to rename a table:

Example:

Write a query to rename the t_school table as tbl_school.

We will execute the SHOW TABLES command before executing a query to rename the 't_school' table.

We have executed the 'RENAME command' on the table t_school followed by the 'TO' keyword with the new table name. This simply means that the 't_school' table will now be renamed to tbl_school.

Now, we will again execute the SHOW TABLES command.

The results above show that the table t_school is not present in the list. Instead, tbl_school is present in the list, which means the table is now successfully renamed as tbl_school.

(B) DML

• DML stands for Data Manipulation Language. Using DML commands in SQL, we can make changes in the data present in tables.
• Whenever we wish to manipulate the data or fetch the data present in SQL tables, we can use DML commands in SQL.
• DML commands in SQL will change the data, such as inserting new records, deleting or updating existing records from the SQL tables. We can also retrieve all the data from SQL tables according to our requirements.
• Commands covered under DDL are:
  1. INSERT
  2. SELECT
  3. UPDATE
  4. DELETE

Let us see each of the commands in the DML category with more details.

1. INSERT

INSERT command is used to insert records in a table. We can insert a single as well as multiple records for a single table at the same time.

Syntax:

Example:

Write a query to insert 10 records in the t_school table.

Query:

Since we wanted to insert ten records in a table, so instead of writing the INSERT command ten times, we have written a single INSERT command to insert multiple records at a time.

2. SELECT

A SELECT command is used to retrieve the records from the table. According to our requirements, we can retrieve all the records or some specific records from the table. Whenever we want to retrieve some specific records from the table, then we have to specify the WHERE clause in a SELECT query. WHERE clause will contain a condition, any record that matches the condition will be considered as a part of the output.

Syntax to retrieve all the records:

Syntax to retrieve some specific records:

Example 1:

Write a query to retrieve all the column values from all the records of the t_school table.

Query:

Here, we have executed a SELECT query with the asterisk (*) on the t_school table. This will retrieve all the column values for all the records from the t_school table.

Output:

All the records are retrieved successfully from the t_school table.

Example 2:

Write a query to retrieve all the column values of only those schools which has more than 11 classrooms.

Query:

We have executed a SELECT query with the asterisk (*) on the t_school table followed by a WHERE clause condition. Due to the WHERE clause condition, only those records with a value greater than 11 in the 'Number_of_classrooms' column will only be retrieved.

Output:

There are nine schools in the t_school table, which has more than 11 classrooms.

3. UPDATE

UPDATE command works for the values present in the table. Whenever we wish to update a value for any record present in a table, we will use the UPDATE command in SQL.

Syntax:

Example:

Write a query to update a record with ID 9 and set the updated value of the Number_of_Teachers and Number_of_Classrooms as 125 and 9, respectively.

Query:

We will execute the SELECT query to verify whether the number of teachers and classrooms is updated for the record with ID as 9.

4. DELETE

DELETE command is used to remove records from a table.

Syntax:

Example:

Write a query to remove the record whose ID is 6 in the t_school table.

Query:

We will execute the SELECT query to verify whether the record with ID 6 is deleted or not.

The results show that the record with ID 6 is deleted successfully from the t_school table.

(C) DCL

• DCL stands for Data Control Language.
• Whenever we want to control the access to the data present in SQL tables, we will use DCL commands in SQL. Only the authorized users can access the data stored in the tables.
• Every user will have some pre-defined privileges; accordingly, the data can be accessed by that particular user. Using the DCL commands in SQL, we can give privileges to the user on the SQL database and tables, or we can also revoke the given privileges from the user.
• Commands covered under DCL are:

1. GRANT

Access privileges can be assigned to a user for the databases and tables using the GRANT command.

2. REVOKE

All the access privileges which are already assigned to the user can be revoked by using the REVOKE command.

(D) TCL:

• TCL stands for Transaction Control Language. TCL commands are generally used in transactions.
• Using TCL commands in SQL, we can save our transactions to the database and roll them back to a specific point in our transaction. We can also save a particular portion of our transaction using the SAVEPOINT command.
• Commands covered under TCL are:

1. COMMIT:

To save all the operations executed in a particular transaction, we need to execute a commit command just after the transaction completion.

2. ROLLBACK

Using the rollback command in SQL, you can roll to the last saved state of a transaction.

3. SAVEPOINT

Using the SAVEPOINT command, you can assign a name to a specific part of the transaction.

Example:

Till this point, we have started a transaction, inserted records into it, committed the transaction and also created a SAVEPOINT ins after insertion.

We have deleted a record and created a savepoint del after deletion, but later we thought that we needed the record we had recently deleted. So, we will roll back to the SAVEPOINT ins.

After rolling back to ins, we can see that all the records are retrieved (including the deleted record).