Database Languages in DBMS

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language (DCL)
4. Transaction Control Language (TCL)
5. Data Query Language (DQL)

1. Data Definition Language (DDL)

DDL is used to define the database schema, i.e., the structure and organization of data in the database. It allows the user to create, modify, and delete database objects like tables, views, indexes, etc. Examples of DDL commands include CREATE, ALTER, and DROP.

For example, the following DDL command creates a table named “employees” with three columns: “id”, “name”, and “salary”.

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), salary INT );

Some of the tasks that come under Data Definition Language (DDL) include:

1. Creating tables: This involves defining the structure of the table, including the column names, data types, and constraints.
2. Modifying tables: This includes adding, modifying, or dropping columns, changing data types, and adding or removing constraints.
3. Creating indexes: This involves creating indexes on tables to improve performance.
4. Defining views: This involves creating a virtual table that is derived from one or more existing tables.
5. Creating sequences: This involves creating a sequence of numbers that can be used to generate unique values for primary key columns.
6. Creating synonyms: This involves creating an alternative name for a table, view, sequence, or other database object.
7. Granting or revoking permissions: This involves granting or revoking privileges to users or roles to perform specific actions on database objects.

These tasks are essential in creating and managing the database schema, which is the structure that defines the database objects and their relationships.

2. Data Manipulation Language (DML):

DML is used to manipulate the data stored in the database. It allows the user to insert, update, retrieve, and delete data from tables. Examples of DML commands include SELECT, INSERT, UPDATE, and DELETE, Truncate: It is used to remove all records from a table, Rename: It is used to rename an object. Comment: It is used to comment on the data dictionary.

Some of the tasks that come under DML are:

1. Inserting data into a table: This operation is used to add new data to an existing table in the database.
2. Updating existing data: This operation is used to modify or update existing data in a table.
3. Deleting data: This operation is used to remove data from a table.
4. Retrieving data: This operation is used to retrieve data from one or more tables in the database.
5. Sorting data: This operation is used to sort data in ascending or descending order based on one or more columns.
6. Filtering data: This operation is used to filter data based on specific criteria.
7. Joining tables: This operation is used to combine data from two or more tables in the database.
8. Grouping data: This operation is used to group data based on one or more columns.
9. Aggregating data: This operation is used to perform calculations on data, such as calculating the sum, average, or maximum value of a column.

For example, the following DML command inserts a new record into the “employees” table:

INSERT INTO employees (id, name, salary) VALUES (1, ‘John Smith’, 50000);

3. Data Control Language (DCL):

DCL is used to control access to the database. It allows the user to grant or revoke privileges to other users or roles. Examples of DCL commands include GRANT and REVOKE.

For example, the following DCL command grants SELECT privilege on the “employees” table to a user named “user1”:

GRANT SELECT ON employees TO user1;

Here are some tasks that come under DCL:

• Grant: It is used to give user access privileges to a database.
• Revoke: It is used to take back permissions from the user.

4. Transaction Control Language (TCL):

TCL is used to control transactions in the database. It allows the user to start, commit, or rollback transactions. Examples of TCL commands include COMMIT and ROLLBACK.

For example, the following TCL command commits a transaction:

COMMIT;

Here are some tasks that come under TCL:

• Commit: It is used to save the transaction on the database.
• Rollback: It is used to restore the database to original since the last Commit.

5. Data Query Language (DQL):

DQL is a subset of DML used specifically for querying data from the database. It allows the user to retrieve data from tables based on certain conditions. Examples of DQL commands include SELECT and FROM.

For example, the following DQL command retrieves all records from the “employees” table:

SELECT * FROM employees;

In conclusion, these different types of languages in DBMS provide a comprehensive set of tools to create, modify, manipulate, and control the data stored in a database. Understanding the purpose and usage of each type of language is important for efficient and effective management of a database.