SQL SERVER – Example of DDL, DML, DCL and TCL Commands « SQL Server Journey with SQL Authority

« SQLAuthority News – Gandhinagar SQL Server User Group Meeting April 24, 2009

SQL SERVER – Add or Remove Identity Property on Column »

SQL SERVER – Example of DDL, DML, DCL and TCL Commands

May 2, 2009 by pinaldave

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT – Retrieves data from a table
INSERT - Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a savepoint within a transaction

Reference : Pinal Dave (http://blog.SQLAuthority.com), Original Source

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology | 27 Comments

27 Responses

on May 4, 2009 at 5:17 pm | Reply Brian Tkatch

The terms DML and DDL are used commonly enough amongst developers trying to get a point across. DCL is something i rarely see outside of books.

BTW, wouldn’t SAVE TRANSACTION also be part of TCL?
on May 4, 2009 at 5:24 pm | Reply pinaldave

Hi Brian,

Good Suggestions!

SAVE TRANSACTION belongs to TCL as it is controlling the transaction. I will update the post now as per your feedback.

Kind Regards,
Pinal
- on January 23, 2011 at 7:46 am | Reply Rashmi Ranjan Fatesingh
  
  Hi Pinal .
  
  Your articles are very help full .
  
  I want find the short foem of a name.
  
  Suppose i have a table student , which has a column , Name.
  Suppose name is —- Rashmi Ranjan Fatesingh.
  
  I want to see it as — R R Fatesingh
  
  Please help me
  - on January 25, 2011 at 3:20 pm | Reply madhivanan
    
    select name,
    left(parsename(replace(name,’ ‘,’.'),3),1)+’ ‘+left(parsename(replace(name,’ ‘,’.'),2),1)+’ ‘+
    parsename(replace(name,’ ‘,’.'),1) as short_name
    from
    (
    select ‘Rashmi Ranjan Fatesingh’ as name
    ) f
    - on January 26, 2011 at 2:46 pm | Reply Rashmi Ranja Fatesingh
      
      i wrote the query like
      
      select name ,
      left(parsename(replace(name,”,’.'),3),1) +’
      ‘+ left(parsename(replace(name,”,’.'),2),1)+”+
      parsename(replace(name,”,’.'),1) as shortname
      from
      (
      select ‘Rashmi Ranjan Fatesingh’ as name
      ) student
      
      its showing me result like
      name shortname
      Rashmi Ranjan Fatesingh null
      - on January 28, 2011 at 3:58 pm madhivanan
        
        Note that ” means single quote space and single quote
      - on January 28, 2011 at 9:38 pm Rashmi Ranjan Fatesingh
        
        now i have written the query like this
        
        select name ,
        left(parsename(replace(name,’ ‘,’.'),3),1)+’ ‘
        +left(parsename(replace(name,’ ‘,’.'),2),1)+’ ‘
        + parsename(replace(name,’ ‘,’.'),1) as shortname
        from
        (
        select ‘Rashmi Ranjan Fatesingh’ as name
        ) student
        
        *************************************
        but it is showing error like this
        
        Msg 102, Level 15, State 1, Line 7
        Incorrect syntax near ‘‘’.
on August 27, 2009 at 4:06 pm | Reply Darshan

What about the BackUP and Restore Command ?
on September 25, 2009 at 3:01 pm | Reply mahmood

As a web developer, i find that info very intesting and benificail. never heard before.

thanks for nice explanation.
on February 16, 2010 at 10:58 am | Reply durugaiah

hi its a nice description abt sql server .. i want to need more points from this way
on March 10, 2010 at 10:21 am | Reply Nuzrath TM

hi pinaldave

i want to create a single trigger which monitor the inserted, Updated and Deleted transaction records to my customer table, and the effected datas should be archive to a table with the following tag

if a transaction Inserted TAG= INS
if a transaction updated TAG= UPD
if a Transaction Deleted TAG= DEL

and also should send a notification mail to operator

please help me.
thanks
on March 10, 2010 at 11:01 am | Reply Imran Mohammed

@Nusrath,

You gave us your requirement which is good, but what is your question. What kind of help you want, please explain in detail and be very specific and provide more information if you want to notify through email on a scheduled basis or every occurrence of event (Insert, update, delete)

~ IM.
on March 10, 2010 at 4:23 pm | Reply Nuzrath TM

thanks for your response Mr. Imran Mohamed

Ok i’ll explain all.

i have a Database call posdb and the database contains following tables

1. customer table contains following columns

([cust_id] ,
[name] ,
[address] ,
[city],
[state],
[zip])

2. customer_archive table contains following columns

([cust_id] ,
[name] ,
[address] ,
[city],
[state],
[zip],
[IID],
[Tdate],
[user])

now my question is

1.how to create a DML trigger to accomplish following requirements.

#if a user delete records on customer table, the deleted records are should be saved on customer_archive Table with IID value = ‘DEL’ and deleted user.

#if a user update records on customer table, the updated records are should be saved on customer_archive Table with IID value = ‘UPD’ and deleted user.

#if a user insert records on customer table, the inserted records are should be saved on customer_archive Table with IID value = ‘INS’and deleted user.

and when the trigger fired notify the operator via email

and i am sorry im not fluent in English
please help me.
on May 21, 2010 at 5:56 pm | Reply Pawan Bhise

Good Notes
on May 25, 2010 at 10:37 am | Reply Sreeraj

Its good and quite simple…
Thanks
on July 17, 2010 at 4:23 pm | Reply nishant

quite simple to understand
on October 12, 2010 at 12:11 pm | Reply bhaskar

it,s good……but i export more
on January 18, 2011 at 1:44 pm | Reply Kriti

Really good 1.. thanx for summing up everything in an easy to understand fashion
on February 13, 2011 at 1:48 pm | Reply venkatesh

really good easy to learning the steps
on February 15, 2011 at 5:43 pm | Reply Manish

Hello Sir,

I always your articles and it always help me but now in this article i have little bit confuse because of “Select command”.

1.) if select command comes in DML than which command comes in DQL ?

Second thing is that in most interview normally interviewer always ask one question that “Can we use DML commands in Function ?”
may be its answer is NO.

because we can’t use insert,update,delete command in Function. Now what about in that answer is that wrong ?

Please help me. in this
on February 18, 2011 at 6:57 am | Reply lj082024

Hi sir..
Good Day….

I wnated to ask some help from you, could you please give me some example of ddl (data definition language) its our assignment form our class database menagement.

Thank you, God bless…
on February 18, 2011 at 6:58 am | Reply lj082024

Hi sir..

Good Day….

I wanted to ask some help from you, could you please give me some example of ddl(data definition language) its our assignment form our class database menagement.

Thank you, God bless…
- on February 18, 2011 at 3:44 pm | Reply madhivanan
  
  CREATE, ALTER and DROP statements
on February 21, 2011 at 12:42 pm | Reply Jeyaraman.S

I need some examples on TCL,DCL commands with syntax.
on March 31, 2011 at 11:01 am | Reply sravanthi

hi ,
i need more information on sql & plsql in interview point of view,can u suggest me on which topics i should be more perfect ..
thanku verymuch
on May 25, 2011 at 4:24 pm | Reply Fahim Manzoor

Hi,
I have a logical issue which i am trying to solve from a long time but no success. Hopefully you will be able to help me.

The thing is iam trying to move an Excel report to SSRS plateform, The data in excel is populated through formulaes and if the condition doesnt satisfy it return 0 but the same thing doesnt apply in Sql. if the Condition doesnt satisfy it doesnt return anything not even zero and beacuse of which the column is also not reflecting in the report. so is there any logic which you can help me with. if the data for the column is not there will i be able to atleast show the column heading.

Thanks,
Fahim.
on August 25, 2011 at 11:41 pm | Reply venu gopal reddy

hi sir..
can u explain about GRANT command with EXAMPLES..please