DBMS DATABASE LANGUAGES


DBMS is an interface between the end user and the database. Also, managing the data, the database engine, and the database schema in order to facilitate the organization and manipulation of data.


DATABASE LANGUAGES are used to read, update and store data in a database. There are several such languages that can be used for this purpose; one of them is SQL (Structured Query Language).


There are Basically 4 Types of DBMS database languages:

  1. Data Definition Languages (DDL)

  2. Data Manipulation Languages (DML)

  3. Data Control Languages (DCL)

  4. Transaction Control Languages (TCL)


DATA DEGINITION LANGUAGES


Data Definition Language (DDL) statements are used to classify the database structure or schema. It is a type of language that allows the DBA or user to depict and name those entities, attributes, and relationships that are required for the application along with any associated integrity and security constraints. Here are the lists of tasks that come under DDL:

  • CREATE - used to create objects in the database

  • ALTER - used to alters the structure of the database

  • DROP - used to delete objects from the database

  • TRUNCATE - used to remove all records from a table, including all spaces allocated for the records are removed

  • COMMENT - used to add comments to the data dictionary

  • RENAME - used to rename an object


Data Manipulation Language


A language that offers a set of operations to support the fundamental data manipulation operations on the data held in the database. Data Manipulation Language (DML) statements are used to manage data within schema objects. Here are the lists of tasks that come under DML:

  • SELECT - It retrieves data from a database

  • INSERT - It inserts data into a table

  • UPDATE - It updates existing data within a table

  • DELETE - It deletes all records from a table, the space for the records remain

  • MERGE - UPSERT operation (insert or update)

  • CALL - It calls a PL/SQL or Java subprogram

  • EXPLAIN PLAN - It explains the access path to data

  • LOCK TABLE - It controls concurrency


Data Control Language


There are two other forms of database sub-languages. The Data Control Language (DCL) is used to control privilege in Databases. To perform any operation in the database, such as for creating tables, sequences, or views, we need privileges. Privileges are of two types,

  • System - creating a session, table, etc. are all types of system privilege.

  • Object - any command or query to work on tables comes under object privilege. DCL is used to define two commands. These are:

  • Grant - It gives user access privileges to a database.

  • Revoke - It takes back permissions from the user.


Transaction Control Language (TCL)


Transaction Control statements are used to run the changes made by DML statements. It allows statements to be grouped into logical transactions.

  • COMMIT - It saves the work done

  • SAVEPOINT - It identifies a point in a transaction to which you can later roll back

  • ROLLBACK - It restores the database to original since the last COMMIT

  • SET TRANSACTION - It changes the transaction options like isolation level and what rollback segment to use


Sofia Sondh

The Tech Platform

www.thetechplatform.com