Tutorials Hut

Tutorials Hut

  • SQL Testing




  • SQL Statements :DML, DDL, DCL
    & TCL

    SQL statements /queries  are instructions which is used to communicate with the database.

    SQL statements are categorized into four different types of statements, which are given below

    Sql Statements

    1) Data Definition Language (DDL)

    • DDL statements are used to define the data base structure or schema.
    • DDL changes the structure of the table for example creating a table, altering a table and deleting a table etc.
    • DDL statements are auto-committed that means it permanently save all the changes in the database.

    Below are four types of DDL commands in SQL:

    1) CREATE : 

    SQL CREATE TABLE statement is used to create table in a database.

    Syntax:

    Create database database-name;
    Create Table TableName (COLUMN_NAME DATATYPES[,....]);

    2) ALTER

    ALTER TABLE is used to add, rename, modify or drop columns

    Syntax:

    ALTER TABLE table_name ADD column_name COLUMN-definition;    

    To modify existing column in the table:

    ALTER TABLE MODIFY(COLUMN DEFINITION....); 

    3) DROP

    It is used to remove or delete a table from the SQL database.

    Syntax:

    drop table table-name;

    4) TRUNCATE

    Used to delete all the rows from the table and free the space containing the table.

    Syntax:

    truncate table table-name; 

    2) Data Manipulation language (DML)

    • DML statements are used for managing data within database.
    • DML statements are not auto-committed that means changes are not permanent to database, they can be rolled back.

    Below are four types of DML commands in SQL:

    1) SELECT 

      • The SELECT query used to select the data from database.
      • Used to retrieve records from one or more tables in your SQL database

    Syntax:

    SELECT expressions
    FROM tables
    [WHERE conditions]
    [ORDER BY expression [ ASC | DESC ]];

    2) INSERT

    Insert command is used to insert data into a table

    Syntax:

    INSERT INTO TABLE_NAME  (col1, col2, col3,.... col N)  
    VALUES (value1, value2, value3, .... valueN);  
    Or 
    INSERT INTO TABLE_NAME    
    VALUES (value1, value2, value3, .... valueN);

    3) DELETE

    Delete command is used to delete data from a table. Delete command can also be used with condition to delete a particular row.

    Syntax:

    DELETE FROM table WHERE condition

    4) UPDATE

    Update Command is used to  update a row of a table. 

    Syntax:

    UPDATE Table SET column_name =value [WHERE condition]

    3) Data Control Language (DCL)

    DCL (Data Control Language) commands /statements are useful to give “rights & permissions.”

    1)GRANT

    Grant command is use to give user access privileges to a database.

    Syntax:

    GRANT privilege_nameON object_name
    TO {user_name |PUBLIC |role_name}
    [WITH GRANT OPTION];

    2) REVOKE

    REVOKE is used to take back permissions from the user.

    Syntax:

    REVOKE privilege_nameON object_name
    FROM {user_name |PUBLIC |role_name}

    4) Transaction Control Language (TCL)

    TCL commands deal with the transaction within the database.

    1) COMMIT

    COMMIT commands  is used to save all the transactions to the database.

    Syntax:

    Commit;

    2) ROLLBACK

    ROLLBACK is used to undo transactions that have not already been saved to the database.

    Syntax:

    ROLLBACK; 
    Recommended Articles:

     

     

     

     

     

     

     

     


















  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Scroll to Top