Database Languages in DBMS – DDL, DML, DCL, DQL

Programming languages which are used to create and operate database, known as database language like Structured Query language aka SQL etc.

Most of database languages are non-procedural, means the language focus on “what to do instead of how to do?”

We can further divide database languages in these 4 categories as per the type of users:

  1. Data definition language aka DDL
  2. Data manipulation language aka DML
  3. Data control language aka DCL
  4. Data query language aka DQL

Database Languages in DBMS

DDL

It is used to specify or create database schema like new database or tables creation along with their properties. It is commonly used by Database administrator.

Sample codes are as per given below:

CREATE: To create new database or tables

ALTER: To change the structure of table

DROP: To delete objects from table

TRUNCATE: To remove all records from a table

COMMENT: To add comments to the data dictionary

It also updates data dictionary.

Data dictionary is kind of metadata about the database. In simple words data dictionary is like a map of the database, which contain all information about the database.

Example:

After passing this code a table will be formed named “employee”.

Table employee
Id Name address

DML

It is used by end users to access or perform operations on a database or table. It helps users to manipulate the database or a table.

DML is further classify in two sections:

  • Procedural DML: In this section user needs to define “what data is needed and how to get that?” like PL/SQL.
  • Non-Procedural DML: In this section user needs to define just “what is needed?”. Its not required to define any procedure to get that data. Like SQL.

Sample codes are as per given below:

INSERT: To insert data in table

SELECT: To select data from a table or complete table

UPDATE: To update existing data in table

MODIFY: To modify or change any data in table

DELETE: To delete all records from a table without remove spaces allotted.

REMOVE: To remove selected data from a table

LOCK TABLE: To control concurrency

Example:

After applying this command, the table employee will be look like this:

Table employee
Id Name Address
01 Ajay C5, Anand Vihar, Pune

DCL

This part is like components of SQL that are used to control access to data and database along with database users. These are function which help to manage any type of approach to database to maintain security and integrity.

Sample codes are as per given below:

COMMIT: To save the work done till current.

ROLLBACK: To revert the database till the last COMMIT point.

SAVE POINT: To make a save point so that we can revert to only that point in case of any changes needs to be done. By default, COMMIT point is a SAVE POINT, but SAVE POINT is not a COMMIT point.

GRANT/REVOKE: To give or take back permission from users of database.

SET TRANSACTION: To give or take back permission from users of database, and to set limit of permissions.

DQL

This is a format language which is used to get or retrieve any data from the database. This format creates different queries. Also, it helps database to understand the reaming part of the query except commands. All queries in database belongs to DQL.

Leave a Comment

Your email address will not be published.