DDL, DML, DCL & TCL statements in SQL (Database basics)

hello friends welcome back to sequel with Manoj today in this video we'll talk about the sequel language what are its types and in how many forms it is classified so you might have heard of DD l LD ml d c ln maybe TCL so we'll discuss all of these things in this video today so let me just give you a brief description about these so in sequel language irrespective of the database - like sequel server oracle or my sequel and if you talk in terms of the sequel standard that the ANSI standard every sequel language has you know some classified set of statements right and these are classified into different different type of languages right like like the DDL that is the data definition language second one is the data manipulation language third one is the data control language and fourth one is the transaction control language okay okay so first one is the D real right saudi riyal is data definition language so by data definition language it makes clear that you know with this DDL you define your data means in sequel or any other sequel product like sequel server oracle or my sequel the data is stored in form of tables okay a relational table so a detail statement you can create alter drop the database objects with the create statement you can define new objects object means tables views store procedures and functions right with the alter statement you can modify the definition of an existing object like you can modify a table you can add a new column in a table you can modify the data type of an existing column right or you can remove the column also with the alter statement you can update the stored procedure you can modify the story by using alter statement you can also modify the new definition with the alter statement the third is the drop statement with the drop statement you can remove existing objects like you can drop the tables we can drop the views you can drop store features and functions truncate table truncate table is used to remove all rows from a table without locking the individual row definitions and this truncate table comes under so this truncate table looks like a DML but it comes under DDL because it will reset some properties over table so it is for DDL because it really defines the definition of a table for example if you have identity column in a table and if you are truncating the table which is having which is having data so it will remove all the rows and it will also reset the identity seed to the value that it was defined earlier right when the table was created so truncate table also comes under DDL okay now the second thing is DML that is data manipulation language so with the term manipulation it is clear that you know these languages are used to modify the data all right like the statement used to retrieve insert update remove and manage data within the database objects like the Select statement so with the Select statement lot of people have you know some confusion regarding this statement that is select statement a DML because with select statement users retrieve the data you don't modify the data you don't insert and you don't delete the data right but is a select DML so select is a DML because when you not only retrieve the data but you can change the nature of the data that you are retrieving it by using various functions that are provided in sequel by using aggregate functions okay by joining the tables and getting the meaningful data in some some other way okay so let's say you have a first name and last name two columns and you can combine those two columns and get first name last name as a combined name right like my name is Manoj Manoj is present in one column that is first name and Pandey my caste is present in the second column right but you can combine both columns and can have a one column named that has a value as Manoj Monday right so here with the select statement you manipulated the data right that is stored in the so the data that you are retrieving is different from the data that is stored in the table second is the insert statement insert statement you used to insert one or more rows in a table the update statement with a bread statement you can update the existing data that is present in the table you can either update a row or you can either update multiple rows at once you can also update multiple columns or you can update single column at okay any combination of that the delete statement removes one or more rows from a particular table bulkinsert so bulkinsert is a utility that imports data from external files into your database table okay then the merge statement so merge statement is a combination of insert update and delete statement so if you have to do these three operations at once in a in in a transaction in that single transaction then you quit then you can have a merge statement so this is based upon two tables you have a source table and you have a target table so if you have to keep both the tables in sync so you can use the merge statement right we'll see in our coming videos about all these type of statements how they work okay so far now I am just talking about at a very high level what exactly are these statements then DCL that is the data control language as a database administrator you can have some control on the data and you know this statement is used to control the access of data storing database and provide data security right so these details are under the control of DBA that is the database administrator so first statement is the grant statement so with the grant statement of database administrator crane can grant permissions to other users to access the tables so let's say a DBA can give grant permission just to select the data from a table so that user cannot update the table right that user cannot insert a row cannot update a row cannot delete rows from a table he can only do a select from the table revoke revoke statement removes the previously granted or denied permission so let's say the DBA just wants to revoke that permission so that the user cannot be able to even select the data so by using the revoke statement the user will not be able to do anything on that particular table so with execute as you can set the execution context of a session for a particular user so you can execute a statement based upon some other user on on his particular user context right execute as Clause so execute as can also be used as clause in the in the database object definition like the functions and the stored procedures so within the storage here you can use exec execute as so whichever username is passed on that particular execute as so the stored procedure or the functions will execute on behalf of that particular user by switching the context to for that particular user okay last one is the reward reward means whatever execute as context you have set for that particular user it will revert to the original user that was logged into that particular session okay so reward basically switches back the execute context back to the color of the last execute as statement okay the last thing is the TCL that is transaction control language so transaction control language is basically the internal sequel engine how it basically behaves and how does it helps in maintaining the concurrency and the durability of the transactions so this statement used to manage the changes made by DML statements it allows statements to be grouped together into a logical transactions okay so with the transaction control language you have begin transaction and you have commit transaction and you have rollback transactions so these three go together okay so when you are executing a particular statement so you can bind those particular statements within begin transaction and commit transactions whatever statement is between those all will you know execute in a single transaction so either all of them will be executed or none of them will be executed right so begin transaction marks the starting point of an explicit local transaction okay so this transaction increments the transient by one the commit transaction marks the end of a successful implicit transactions right so complete so commit transaction means that the transaction is committed so let's say within begin and commit transaction you have four insert statements so they will insert four records at once and if anything fails in between they will roll back the previous insert statements that were done right third is the rollback transaction it rolled backs the transaction that you have started right begin transaction rollback the begin transaction if you have specified and a third one is a last one is the save transaction so this try so this same transaction creates a point within a transaction okay so so all these things I'll be talking in detail but for this topic this is just the high-level overview I have given right so this is all about all these d dl d ml d CL and TCL languages I hope that you like this video and please let me know if you want to know something in detail about these statements so please subscribe this channel whenever I add a new video you will get to know about this okay thank you very much have a nice day you

Channel: SQL with Manoj

Views: 515,467

Rating: 4.75598 out of 5

Keywords: SQL Server, SQL, Microsoft SQL Server, DDL, DML, DCL, TCL, Oracle, PostgreSQL, My SQL, Database Basics

Id: kx6m8ABU4ZE

Length: 9min 35sec (575 seconds)

Published: Fri Apr 15 2016