Types of SQL CommandsSQL 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
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. ALTERIn 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. DROPDROP 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. TRUNCATEA 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. RENAMERename 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
Let us see each of the commands in the DML category with more details. 1. INSERTINSERT 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. SELECTA 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. UPDATEUPDATE 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. DELETEDELETE 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
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:
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).
Next TopicTCL Commands in SQL
|