What are the subsets of SQL?
There are four subsets of SQL
- Data definition language (ddl)
- Data manipulation language (dml)
- Data control language (dcl)
- Transaction control language (tcl)
So let’s see each and every command of these four subsets subsets of sql
Data definition language (ddl)
Which is used to create any new database or any database object like table view function or stored procedure index etc.
The existing database or database objects so the create will create the definition of of the object and alter will modify the existing definition of any object.
Which is used to delete the existing databases or any other database objects so by using drop you can delete drop the table or you can drop a view right so the first command here we are creating the definition of the database object here altering the definition of the object and here we are destroying the object and the next one is trunk.
Used to remove all rows from a table so it will not log any row by row basis logging so it will just make an entry that it has deleted all rows from the table so that it will have a minimal logging and also truncate comes under the data definition language because it resets the some of the properties.
Data manipulation language (dml)
Statement which is used to retrieve all rows from a table or a view.
New rows to a table we use insert command.
If you want to modify the existing data of a table or a view you can use update.
Used to remove all records from a table if you are using without a where clause so if you want to remove specific rows from a table we should use the where clause if you are trying to update a view which is combination of multiple tables it will not let you update.
The merge will perform the update insert and delete operations based on the join between the source and target table so if it is matching on the business key or the primary key it will update if it is not matching the on the primary key it will insert that row to the target if it is not matching on the source right if the if the primary key is not matching with a source right it will just delete the entry in the target so that’s how the merge command works.
Data control language (dcl)
The grant command is used to provide access to a database or database objects.
If you want to remove access to that database or its object to a particular user you can do that through the revoke command dna which is used to deny the access to database and its objects.
As statement that means it will set the execution context of a session that means it can execute as a different user right the next one is the revert revert will switch the execution context back to the caller of the last execute as statement so it will just switch back to the context.
Transaction control language (tcl)
This makes a starting point of an explicit local transaction it makes the starting point of the transaction and it will increment the entered transaction count by one in sql server so basically this is a system variable that is at tran count.
This marks the end of the successful transaction.
It will revert the values back to the starting of the execution so it will not make any changes to the data or the what are the actions that are being done will be rolled back or if you are using a save point inside the transaction it will roll back till that particular point right.
It will set a save point within a transaction so it offers a mechanism to roll back the portion of transaction.
check : Internet Of Things IoT
What we have discussed in the blog there are four subsets of sql one is the data definition language so it will create the definition of the database or the database objects so create alter drop truncate etc are the major commands in the ddl dml data manipulation language in this we have select insert update delete and merge select is also considered as data manipulation language because with which you can manipulate the data suppose if we have a employee table which is having first name and last name and using select you can concatenate the data you can use aggregate functions you can also derive the columns on the fly while using the select statement .
so you can also use order by class to retrieve the data and you can sort the data whichever the way we want which means the way the data stored in the database is different when you use order by or when you aggregate the columns or when you derive the new columns that’s why the select has been categorized under the data manipulation language next one is dcl data control language that is grant revoke dna executa statement and reward the next one is transaction control language that is begin transaction commit transaction rollback transaction and save transaction.