DATABASE MANAGEMENT SYSTEM: Types of database languages

What are database Sub languages?

A data sublanguage mainly has two parts:

Data Definition Language (DDL) and
Data Manipulation Language (DML).
Data Control Language (DCL)
Transaction Control Language (TCL)

The Data Definition Language is used for specifying the database schema and the Data Manipulation Language is used for both reading and updating the database. These languages are called data sub-languages as they do not include constructs for all computational requirements.

Computation purposes include conditional or iterative statements that are supported by the high-level programming languages. Many DBMSs have a capability to embed the sublanguage in a high-level programming language such as ‘Fortran’, ‘C’, C++, Java, or Visual Basic. Here, the high-level language is sometimes referred to as the host language as it is acting like a host for this language. To compile the embedded file, the commands in the data sub-language are first detached from the host-language program and are substituted by function calls. The pre-processed file is then compiled and placed in an object module which gets linked with a DBMS-specific library that is having the replaced functions, and executed based on requirement. Most data sub-languages also supply non-embedded or interactive commands which can be input directly using terminal.

Data Definition Language

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 retrieve data from the 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 access path to data
LOCK TABLE – It controls concurrency

Data Control Language

There is another two forms of database sub-languages. The Data Control Language (DCL) is used to control privilege in Database. 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 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 database.
Revoke – It takes back permissions from user.

Transaction Control Language (TCL)

Transaction Control statements are used to run the changes made by DML statements. It allows statements to be grouped together 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 database to original since the last COMMIT
SET TRANSACTION – It changes the transaction options like isolation level and what rollback segment to use