Saturday 19 August 2017

SQL Language in Oracle: DDL, DML, DRL, DCL and TCL Statements

Overview

SQL stands for Structured Query Language and its basically divided into 5 types of language statements i.e. DDL, DML, DRL, DCL and TCL. These languages are used to create, modify, load, control and retrieve the data from the database

Types of SQL Statements

DDL - Data Definition Language

Its used to create and modify database objects such as tables etc. DDL are used to define metadata in the database. By default DDL is auto commit, when ever you a execute a DDL commit is triggered

  • CREATE : Its used to create the table, schema etc
  • ALTER : Modifying existing table such as adding column, dropping column
  • DROP : Delete table or any other database objects
  • TRUNCATE : It clears all the data inside a table
  • RENAME : Change the name of the table or any other object

DML - Data Manipulation Language

Its used to insert, update and delete the data from the tables, hence its called as Data manipulation language

  • INSERT : It adds rows or records to the table
  • UPDATE : Its used to modify information of rows in table
  • DELETE : It delete records or rows from the table
  • MERGE : It allows to join data source and perform action on target table 

DRL - Data Retreival Language 

This language is used to fetch the records from the table

  • SELECT : Retrieves the data from the tables

DCL - Data Control Language 

This language is used to control the level of access to users on the database objects

  • GRANT : Allows users to access particular object in the database
  • REVOKE : Prohibits user from accessing particular object in the database

TCL - Transaction Control Language

This language is used to manage transactions within the database 

  • COMMIT : Used to save the data
  • ROLLBACK : Used to revert the changes made
  • SAVEPOINT : Its used to identify point in transaction, where we can rollback

No comments:

Post a Comment