Javatpoint Logo
Javatpoint Logo

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.

Types of SQL Commands

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:


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:


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:


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:

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:


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:

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:


Types of SQL Commands

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.


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:


Types of SQL Commands

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.


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:


Types of SQL Commands

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:


Types of SQL Commands

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.


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:

Example:

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

Query:


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.


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:

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:


ID School_Name Number_Of_Students Number_Of_Teachers Number_Of_Classrooms EmailID
1 Boys Town Public School 1000 80 12 [email protected]
2 Guru Govind Singh Public School 800 35 15 [email protected]
3 Delhi Public School 1200 30 10 [email protected]
4 Ashoka Universal School 1110 40 40 [email protected]
5 Calibers English Medium School 9000 31 50 [email protected]
6 Cantonment Board High School 7050 41 60 [email protected]
7 Podar International School 12000 120 120 [email protected]
8 Barnes School 18000 100 100 [email protected]
9 D.S Kothari Kanya School 10000 120 125 [email protected]
10 Orchid International School 20000 200 180 [email protected]


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.


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:

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.


Types of SQL Commands

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.


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:

Example:

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

Query:


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:

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:

ID School_Name Number_Of_Students Number_Of_Teachers Number_Of_Classrooms EmailID
1 Boys Town Public School 1000 80 12 [email protected]
2 Guru Govind Singh Public School 800 35 15 [email protected]
3 Delhi Public School 1200 30 10 [email protected]
4 Ashoka Universal School 1110 40 40 [email protected]
5 Calibers English Medium School 9000 31 50 [email protected]
6 Cantonment Board High School 7050 41 60 [email protected]
7 Podar International School 12000 120 120 [email protected]
8 Barnes School 18000 100 100 [email protected]
9 D.S Kothari Kanya School 10000 120 125 [email protected]
10 Orchid International School 20000 200 180 [email protected]

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:

ID School_Name Number_Of_Students Number_Of_Teachers Number_Of_Classrooms EmailID
1 Boys Town Public School 1000 80 12 [email protected]
2 Guru Govind Singh Public School 800 35 15 [email protected]
4 Ashoka Universal School 1110 40 40 [email protected]
5 Calibers English Medium School 9000 31 50 [email protected]
6 Cantonment Board High School 7050 41 60 [email protected]
7 Podar International School 12000 120 120 [email protected]
8 Barnes School 18000 100 100 [email protected]
9 D.S Kothari Kanya School 10000 120 125 [email protected]
10 Orchid International School 20000 200 180 [email protected]

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:


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.


ID School_Name Number_Of_Students Number_Of_Teachers Number_Of_Classrooms EmailID
9 D.S Kothari Kanya School 10000 125 120 [email protected]

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:


Types of SQL Commands

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


ID School_Name Number_Of_Students Number_Of_Teachers Number_Of_Classrooms EmailID
1 Boys Town Public School 1000 80 12 [email protected]
2 Guru Govind Singh Public School 800 35 15 [email protected]
3 Delhi Public School 1200 30 10 [email protected]
4 Ashoka Universal School 1110 40 40 [email protected]
5 Calibers English Medium School 9000 31 50 [email protected]
7 Podar International School 12000 120 120 [email protected]
8 Barnes School 18000 100 100 [email protected]
9 D.S Kothari Kanya School 10000 120 125 [email protected]
10 Orchid International School 20000 200 180 [email protected]

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:


Types of SQL Commands

Types of SQL Commands

Types of SQL Commands

Types of SQL Commands

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.


Types of SQL Commands

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.


Types of SQL Commands

ID School_Name Number_Of_Students Number_Of_Teachers Number_Of_Classrooms EmailID
1 Boys Town Public School 1000 80 12 [email protected]
2 Guru Govind Singh Public School 800 35 15 [email protected]
3 Delhi Public School 1200 30 10 [email protected]
4 Ashoka Universal School 1110 40 40 [email protected]
5 Calibers English Medium School 9000 31 50 [email protected].com

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







Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA