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:

CREATE DATABASE DatabaseName;

Syntax to create a table:

CREATE TABLE TableName (ColumnName1 datatype, ColumnName2 datatype,….., ColumnName3 datatype);

Example 1:

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

Query:

mysql> CREATE DATABASE SCHOOL;

Types of SQL Commands

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:

mysql> SHOW DATABASES;

Types of SQL Commands

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:

mysql> USE SCHOOL;

Types of SQL Commands

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:

mysql> CREATE TABLE t_school(ID INT PRIMARY KEY, School_Name VARCHAR(40), Number_Of_Students INT, Number_Of_Teachers INT, Number_Of_Classrooms INT, EmailID VARCHAR(40));

Types of SQL Commands

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:

mysql> SHOW TABLES;

Types of SQL Commands

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:

ALTER TABLE table_name ADD column_name datatype(size);

Syntax of ALTER command to delete an existing column:

ALTER TABLE table_name DROP COLUMN column_name;

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

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

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

ALTER TABLE table_name MODIFY column_name datatype(size);

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:

mysql> DESC t_school;

Types of SQL Commands

mysql> ALTER TABLE t_school ADD Board_of_Education VARCHAR(20);

Types of SQL Commands

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.

mysql> DESC t_school;

Types of SQL Commands

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:

mysql> DESC t_school;

Types of SQL Commands

mysql> ALTER TABLE t_school DROP COLUMN Board_of_Education;

Types of SQL Commands

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.

mysql> DESC t_school;

Types of SQL Commands

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:

mysql> DESC t_school;

Types of SQL Commands

mysql> ALTER TABLE t_school RENAME COLUMN Number_of_Students TO Count_Students;

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.

mysql> DESC t_school;

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:

mysql> DESC t_school;

Types of SQL Commands

mysql> ALTER TABLE t_school MODIFY ID VARCHAR(20);

Types of SQL Commands

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.

mysql> DESC t_school;

Types of SQL Commands

3. DROP

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

Syntax:

DROP TABLE table_name;

Example:

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

Query:

mysql> DROP TABLE t_school;

Types of SQL Commands

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.

mysql> SHOW TABLES;

Types of SQL Commands

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:

TRUNCATE TABLE table_name;

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:

mysql> SELECT *FROM t_school;

ID	School_Name	Number_Of_Students	Number_Of_Teachers	Number_Of_Classrooms	EmailID
1	Boys Town Public School	1000	80	12	btps15@gmail.com
2	Guru Govind Singh Public School	800	35	15	ggps25@gmail.com
3	Delhi Public School	1200	30	10	dps101@gmail.com
4	Ashoka Universal School	1110	40	40	aus17@gmail.com
5	Calibers English Medium School	9000	31	50	cems@gmail.com
6	Cantonment Board High School	7050	41	60	cbhs@gmail.com
7	Podar International School	12000	120	120	podaris89@gmail.com
8	Barnes School	18000	100	100	barnes99@gmail.com
9	D.S Kothari Kanya School	10000	120	125	ksmh@gmail.com
10	Orchid International School	20000	200	180	ois@gmail.com

mysql> TRUNCATE TABLE t_school;

Types of SQL Commands

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.

mysql> SELECT *FROM t_school;

Types of SQL Commands

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:

RENAME TABLE old_table_name TO new_table_name;

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.

mysql>SHOW TABLES;

Types of SQL Commands

mysql> RENAME TABLE t_school TO tbl_school;

Types of SQL Commands

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.

mysql>SHOW TABLES;

Types of SQL Commands

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:

INSERT INTO table_name VALUES (Column1_Value, Column2_Value, …., ColumnN_Value);

Example:

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

Query:

mysql> INSERT INTO t_school(ID, School_Name, Number_of_Students, Number_of_Teachers, Number_of_Classrooms, EmailID) VALUES(1, "Boys Town Public School", 1000, 80, 12, "btps15@gmail.com"), (2, "Guru Govind Singh Public School", 800, 35, 15, "ggps25@gmail.com"), (3, "Delhi Public School", 1200, 30, 10, "dps101@gmail.com"), (4, "Ashoka Universal School", 1110, 40, 40, "aus17@gmail.com"), (5, "Calibers English Medium School", 9000, 31, 50, "cems@gmail.com"), (6, "Cantonment Board High School", 7050, 41, 60, "cbhs@gmail.com"), (7, "Podar International School", 12000, 120, 120, "podaris89@gmail.com"), (8, "Barnes School", 18000, 100, 100, "barnes99@gmail.com"), (9, "D.S Kothari Kanya School", 10000, 120, 125, "ksmh@gmail.com"), (10, "Orchid International School", 20000, 200, 180, "ois@gmail.com");

Types of SQL Commands

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:

SELECT *FROM table_name;

Syntax to retrieve some specific records:

SELECT *FROM table_name WHERE condition;

Example 1:

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

Query:

mysql> SELECT *FROM t_school;

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:

ID	School_Name	Number_Of_Students	Number_Of_Teachers	Number_Of_Classrooms	EmailID
1	Boys Town Public School	1000	80	12	btps15@gmail.com
2	Guru Govind Singh Public School	800	35	15	ggps25@gmail.com
3	Delhi Public School	1200	30	10	dps101@gmail.com
4	Ashoka Universal School	1110	40	40	aus17@gmail.com
5	Calibers English Medium School	9000	31	50	cems@gmail.com
6	Cantonment Board High School	7050	41	60	cbhs@gmail.com
7	Podar International School	12000	120	120	podaris89@gmail.com
8	Barnes School	18000	100	100	barnes99@gmail.com
9	D.S Kothari Kanya School	10000	120	125	ksmh@gmail.com
10	Orchid International School	20000	200	180	ois@gmail.com

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:

mysql> SELECT *FROM t_school WHERE Number_Of_Classrooms > 11;

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:

ID	School_Name	Number_Of_Students	Number_Of_Teachers	Number_Of_Classrooms	EmailID
1	Boys Town Public School	1000	80	12	btps15@gmail.com
2	Guru Govind Singh Public School	800	35	15	ggps25@gmail.com
4	Ashoka Universal School	1110	40	40	aus17@gmail.com
5	Calibers English Medium School	9000	31	50	cems@gmail.com
6	Cantonment Board High School	7050	41	60	cbhs@gmail.com
7	Podar International School	12000	120	120	podaris89@gmail.com
8	Barnes School	18000	100	100	barnes99@gmail.com
9	D.S Kothari Kanya School	10000	120	125	ksmh@gmail.com
10	Orchid International School	20000	200	180	ois@gmail.com

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:

UPDATE table_name SET column_name = value WHERE condition;

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:

mysql> UPDATE t_school SET Number_Of_Teachers = 125, Number_Of_Classrooms = 120 WHERE ID=9;

Types of SQL Commands

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

mysql> SELECT *FROM t_school WHERE ID=9;

ID	School_Name	Number_Of_Students	Number_Of_Teachers	Number_Of_Classrooms	EmailID
9	D.S Kothari Kanya School	10000	125	120	ksmh@gmail.com

4. DELETE

DELETE command is used to remove records from a table.

Syntax:

DELETE FROM table_name;

Example:

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

Query:

mysql> DELETE FROM t_school WHERE ID = 6;

Types of SQL Commands

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

mysql> SELECT * FROM t_school;

ID	School_Name	Number_Of_Students	Number_Of_Teachers	Number_Of_Classrooms	EmailID
1	Boys Town Public School	1000	80	12	btps15@gmail.com
2	Guru Govind Singh Public School	800	35	15	ggps25@gmail.com
3	Delhi Public School	1200	30	10	dps101@gmail.com
4	Ashoka Universal School	1110	40	40	aus17@gmail.com
5	Calibers English Medium School	9000	31	50	cems@gmail.com
7	Podar International School	12000	120	120	podaris89@gmail.com
8	Barnes School	18000	100	100	barnes99@gmail.com
9	D.S Kothari Kanya School	10000	120	125	ksmh@gmail.com
10	Orchid International School	20000	200	180	ois@gmail.com

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:

mysql> START TRANSACTION;

Types of SQL Commands

mysql> COMMIT;

Types of SQL Commands

mysql> SET autocommit = 0;

Types of SQL Commands

mysql> SAVEPOINT ins;

Types of SQL Commands

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

mysql> DELETE FROM t_school WHERE ID = 3;

Types of SQL Commands

mysql> SAVEPOINT del;

Types of SQL Commands

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.

mysql> ROLLBACK TO ins;

Types of SQL Commands

mysql> SELECT *FROM t_school;

ID	School_Name	Number_Of_Students	Number_Of_Teachers	Number_Of_Classrooms	EmailID
1	Boys Town Public School	1000	80	12	btps15@gmail.com
2	Guru Govind Singh Public School	800	35	15	ggps25@gmail.com
3	Delhi Public School	1200	30	10	dps101@gmail.com
4	Ashoka Universal School	1110	40	40	aus17@gmail.com
5	Calibers English Medium School	9000	31	50	cems@gmail.com

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

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz