DDL,DML,DCL,TCL

DML:

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

Examples: SELECT, UPDATE, INSERT statements

statements are used for managing data within schema objects. Some examples:

    SELECT - retrieve data from the a database
    INSERT - insert data into a table
    UPDATE - updates existing data within a table
    DELETE - deletes all records from a table, the space for the records remain
    MERGE - UPSERT operation (insert or update)
    CALL - call a PL/SQL or Java subprogram
    EXPLAIN PLAN - explain access path to data
    LOCK TABLE - control concurrenc


DDL

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

Examples: CREATE, ALTER, DROP statements

statements are used to define the database structure or schema. Some examples:

    CREATE - to create objects in the database
    ALTER - alters the structure of the database
    DROP - delete objects from the database
    TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
    COMMENT - add comments to the data dictionary
    RENAME - rename an obj

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.

Examples: GRANT, REVOKE statements


TCL

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

Examples: COMMIT, ROLLBACK statements

ROLLBACK is used for revoking the transactions until last commit.
COMMIT is used for commiting the transactions to the database.
Once we commit we cannot rollback. Once we rollback we cannot commit.
Commit and Rollback are generally used to commit or revoke the transactions that are with regard to DML commands.

-------------------------------------------------------------------------------------------------------------

ex:


--CREATING THE DATABASE

CREATE DATABASE SRIRAMA

--USHING THE DATABASE

USE SRIRAMA
GO

----CREATING THE TABLE


IF NOT EXISTS(SELECT * FROM SYS.OBJECTS WHERE name = 'BRANCH' AND [type] = 'U')
BEGIN
CREATE TABLE BRANCH
(
BRANCH_ID INT ,

BRANCH_NAME VARCHAR(20)

)
END


-----ADD THE NEW COLUMN TO THE BRANCH TABLE 

ALTER TABLE BRANCH ADD LOCATION VARCHAR(30)

---increase the size of the column 

ALTER TABLE BRANCH ALTER COLUMN LOCATION  VARCHAR(50)

---drop the column 
ALTER TABLE BRANCH DROP COLUMN LOCATION


----insert the records into the branch 
INSERT INTO BRANCH VALUES (1,'CSE') ----method 1

INSERT INTO BRANCH VALUES (2,'IT'),
                                                         (3,'ece') -----method 2

-----delete
DELETE FROM BRANCH WHERE ID=3 ---onley the id=3 record will be deleted 
DELETE FROM BRANCH   ---all the records will be deleted from branch table 


----insert the records into the branch 
INSERT INTO BRANCH VALUES (1,'CSE') 


INSERT INTO BRANCH VALUES (2,'IT'),
                                                         (3,'ece') -----method 2

---update the record in the branch table 
UPDATE BRANCH SET  BRANCH_NAME='IT' WHERE BRANCH_ID=1    ------ onley one record       
                                                                                                             is updated whose id=1
UPDATE BRANCH SET  BRANCH_NAME='ECE' --all records updated branchname as ECE

                                                     
----DROP THE TABLE 
DROP TABLE BRACH 

--Truncate 
TRUNCATE TABLE BRANCH 


THANKS,
venkat......