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

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

About these ads

46 thoughts on “SQL SERVER – Example of DDL, DML, DCL and TCL Commands

  1. 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?

  2. 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

    • 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

      • 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

        • 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

          • 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 ‘‘’.

          • Rashmi,

            Script is like below –

            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

  3. 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

  4. @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.

  5. 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.

  6. 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

  7. 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…

  8. 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…

  9. 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

  10. 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.

    • Fahim,
      What i understood from your question is that you need to return at least something whether you have or not any data for any particular column?

      ok, here you can simply go for ISNULL function which returns desired values of the developer if the value is null.

      e.g.

      Select ISNULL(ABC, “”)

      here abc is a column of varchar type datatype. if it has null value then ISNULL function will return a blank which will avoid throwing any exception.

      it depends on the columns datatype, if it is varchar, char then use double quotes and if int, double, decimal, float or similar datatypes then can go for 0.

      hope that helps!!! Cheers!!!

    • Sooria,
      Delete command deletes the rows one by one and maintains a log for every action. Delete does not play at table level whereas drop simply delete the table as well.
      e.g.
      delete from table1
      drop table1

      As it shows delete will delete the data from ‘table1′ where drop will remove the table1 itself from database.

  11. Hi sir
    I had a small doubt regarding writing queries on sql and ddl/dml

    Ex: consider the relations

    Workers(emp_name, company, slary);
    Company(company , city);
    can any one please help me in writing the following in sql, DDL/DML
    1.Find the employees in data base who lives in same city and on the same street of their manager
    2. create an assertion to impose the constraint that no employ draws an salary more than his manager.

    I got this question as it is in my exam but i failed to answer it properly. So please help me

    • Hi Adilakshmi,

      to add a column in an existing table you have to alter the table first,

      e.g. alter table table1

      then to add a column,
      alter column column1 datatype null

      then run the queries to enter data in the newly created column.

      say if you have a table from where you want to dump data in here then,

      insert into table1(column1) select column1 from table2
      innerjoin table1
      on table1.column2 = table2.column2.

      run all the queries together you will get the desired output.

      hope it helps!!!

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

    • e.g. alter table table1

      then to add a column,
      alter column column1 datatype null

      then run the queries to enter data in the newly created column.

      say if you have a table from where you want to dump data in here then,

      insert into table1(column1) select column1 from table2
      innerjoin table1
      on table1.column2 = table2.column2.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s