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 (https://blog.sqlauthority.com), Original Source

Previous Post
SQLAuthority News – Gandhinagar SQL Server User Group Meeting April 24, 2009
Next Post
SQL SERVER – Add or Remove Identity Property on Column

Related Posts

62 Comments. Leave new

  • venu gopal reddy
    August 25, 2011 11:41 pm

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

    Reply
  • can u explain or give image by creating ERD models link in creating database structure;

    Reply
  • what is the difference between DROP and DELETE command ..

    Reply
    • 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.

      Reply
      • delete command is basically use for delete only one row and one column one by one but drop command is use for delete the full table not any rows and column.

    • drop is used to delete the full table and delete is used to delete the particular row or coloum

      Reply
  • Should i use SQL server of Oracle to run these commands

    Reply
  • ya it is very good and it is helpful to me

    Reply
  • 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

    Reply
  • list some sample queries used in ddl&dml

    Reply
  • how to place a column (withdata) in exiting table that table is same table

    Reply
    • 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!!!

      Reply
  • Mallikarjuna
    May 16, 2013 2:09 pm

    Very Helpful Article for Beginners…….

    Reply
    • Priya Singh (@Priyasinghlko19)
      August 5, 2014 9:21 am

      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.

      Reply
  • AniRudh Khandal (@anirudhkhandal)
    November 4, 2014 3:51 pm

    under which category will “Use” command come?

    Reply
  • NSUBUGA BRIAN
    May 15, 2015 8:30 pm

    what are the other components of database manager?

    Reply
  • thank u good information

    Reply
  • please give me example transact script for DCL,

    Reply
  • shakti saini
    May 15, 2016 8:52 pm

    CREATE TABLE permission denied in database ‘master’. what is the solution for this

    Reply
  • drop is used to delete a entire table permanantly from the data base but
    delete is used to delete the data in the table

    Reply
  • thank you for sharing, i’ll learning it

    Reply
  • Rajat Puchnanda
    March 28, 2017 1:35 pm

    Hi,

    what is the difference between Purge and drop..?

    Reply
    • There is no command called “purge” in SQL Server. Truncate/delete are the commands to clear the data from the tables. Drop command would remove the object (table) completely.

      Reply
  • MaheswaraReddy
    June 16, 2019 6:47 am

    Dear friend,
    I have senario like I have two table data calld
    Family_A which contain seven columns,and Family_B which contains seven columns ,want to send these two table data into Family table using only with DDL commands how it is please let me know

    Reply

Leave a Reply

Menu