Database Languages in DBMS

Databases are used to store information. The Database is related to both software and hardware here The Software is used for accessing the data in the form of Software applications, and The Hardware is used for storing the data in the memory or hard disk.

Any Database provides an interface between the end user and the database by using this interface, the end user can access the database data. It is a very secure means before accessing database data we need to authenticate first, and then we get access to the database. We have different types of Database Languages which are represented in the below image.

output

Types of Database Languages in DBMS

Now I will explain Database Languages namely DDL, DCL, DML, and TCL. Each category language is used for a different purpose Below I explain each category with proper examples.

DDL (Data Definition Language)

• The DDL stands for Data Definition Language, Which is used for define the database’s internal structure and Pattern of the Database.
• Basically, The DDL is used for creating tables, indexes, constraints, and schema in the Database.
• By using DDL statements we can able to create the architecture of the required database.

Below I list out types of database commands that are used in DDL. By using those queries we can able to perform the DDL on the Database.

• Create It is used to create objects in the database
• Alter It is used for change or alter the structure of the database objects
• Drop It is used for delete objects from the database
• Truncate It is used for remove all records from a table
• Rename It is used to rename the object in database
• Comment It is used for comment on the data dictionary.

Now I will explain each command with example for better understanding the concepts.

Create

It is one the command in DDL which is used for creating objects in database means creating Tables, Users, Triggers, functions and other objects. Here I will show how to create a table by using create command from DDL.

Syntax:

CREATE TABLE Students (
    column1 INT,
    column2 VARCHAR(50),
    column3 INT
);

Example:

create table

Alter

It is one the command in DDL which is used for change or alter the structure of the database or table. I already created students table with some columns you can see in the above image. Now by using Alter command I add new column that weight to the students table.

Syntax:

ALTER TABLE Students ADD column_name;

Example:

Alter command

Drop

This Drop command is used for delete objects from the database. In this example I drop the students table

Syntax:

DROP Table Table_name;

Example:

drop

Truncate

Truncate command is used for remove all records from a table. Now I remove rows from the Student table.

Syntax:

TRUNCATE TABLE table_name;

Example:

truncate

Rename

It is used to rename the object in database. Now I rename the table from students to ClassMembers. Below I provide the example for better understanding.

Syntax:

ALTER TABLE Old_Table_Name RENAME TO New_Table_Name;

Example:

Rename

Comment

It is used for comment on the data dictionary. We have two different comments namely Single line comment and Multi-line comments.

Single Line

-- This is a single-line comment

Multi-line comment

/*
This is a
multi-line comment
*/

DCL (Data Control Language)

The DCL stands for Data Control Language means these commands are used to retrieve the saved data from database. And one more thing is the DCL execution is Transactional that means It have roll back parameters. we have two tasks under the Data Control Language below I listed them

• Grant It is used for give user access to the database
• Revoke It is used for to take back the access or permissions from the user

Now I will explain these commands with proper examples for better understanding

Grant

Basically The grant command is used for provide database access to the new user. Here I create one user then I give the access to the database.

Syntax:

GRANT privileges
ON object
TO user_or_role [WITH GRANT OPTION];

Example:

GRANT SELECT, INSERT ON students TO user;

Revoke

The Revoke command is used to to take back database access from the user. Now I provide the example in the below

Syntax:

REVOKE privileges ON object FROM user_or_role;

Example:

REVOKE ALL PRIVILEGES ON students FROM user;

DML (Data Manipulation Language)

The Data Manipulation Language is used to Manipulate the data in the database by using different commands. In this category we can able to perform Insert new data into Table, Update existing data in the Table, Delete Data from the Table and other functions we can perform on data by using these DML commands. Below I listed those commands for your reference

• Select It is used for select data from the Table based on the requirements
• Insert It is sued for Inserting data into existing table
• Update It is used for update data in the Table based on the requirement
• Delete It is used for delete data from the Table
• Merge It is used for upsert operations
• Call It is used for call a structured query language or Java sub program
• Lock Table It have ability to control the concurrency

Now I will explain each command with proper example for better understanding the concept.

Select

The Select command is used for select required data based on conditions from a existing Table. Here I select all data from the ClassMembers Table.

Syntax:

SELECT * FROM Table_Name

Example:

Select Command

Insert

The Insert command is used for inserting new data into Table. Now I insert a new data into ClassMembers Table. Below I provide the example.

Syntax:

INSERT INTO Table_Name (Column 1, Column 2, Column 3, Column 4) VALUES (Value 1, Value 2,Value 3, Value 4);

Example:

Insert command

Update

The Update command is used for update information In the Table. Now I will update name John Doe to Roman in the ClassMemebers Table. Below I provide that Example you can update any row or columns data.

Syntax:

UPDATE Table_Name SET Name = 'New_Value' WHERE Name = 'Ola_Value';

Example:

Update

Delete:

The Delete command is used for delete data from the Table. Here I delete Student Id with 2 from the ClassMembers. Below I provide the Example for your reference.

Syntax:

DELETE FROM Table_Name WHERE Column  = Value;

Example:

Delete Command

Merge

The Merge command is used for perform upsert operation means It inserts rows that doesn’t exist and updates rows that do.

Example:

MERGE INTO target_table AS target
USING source_table AS source
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);

CALL

The Call command is used for call the user defined functions

Example:

CALL user_defined_function(parameter 1, parameter 2);

LOCK TABLE

The lock table command is used for lock the table for preventing access from the others for same Table

Syntax:

LOCK TABLE your_table IN EXCLUSIVE MODE;

Example:

LOCK TABLE ClassMembers IN EXCLUSIVE MODE;

TCL ( Transaction Control Language )

The TCL full form is Transaction Control Language commands are used to run the changes made by the DML commands And one more thing is TCL can be grouped into a logical transaction. And We have two different commands in this category below I listed them for reference.

• Commit It is used for save the transaction on the Database. And these very useful in banking sectors.
• Rollback It is used for restore the database to original state from last commit. This command also plays an important role in Banking Sectors.

Now I will explain these two commands for your reference. And I provide proper examples below I provide those example

Commit

The Commit command is used for save the transaction in the database and changes are made by the DML statements. Now I will provide the example below Here I used ClassMembers Table. Here I inserted a new data into Table then I commit It.

Syntax:

Database Operation
Commit

Example:

commit

ROLLBACK

Rollback command is used for restore the database to original state from last commit. Here I provide the example and in this I already commit the you can see in the above, Now I delete one row from the Table then again rollback the previous database state.

Syntax:

ROLLBACK;

Example:

rollback

Conclusion

In this article I will explained Database Languages In Database which are used for different purposes. We have DDL, DCL, DML, TCL these are different categories. The DDL language is used for used for define the database internal structure and Pattern of the Database. And the DCL commands are used to control the data from the user means It can provide control on the Database, Table and Data. The DML commands are used to manipulate the Data in Table like Inserting, updating, deleting of the Data finally the TCL commands are used to save and restore the previous state of Database.

Frequently Asked Questions on Database Languages in DBMS – FAQs

What is Database?

Database is collection of data which is stored electronically in our System or Cloud.

What are types Database languages in JDBC?

Data Definition Language, Data Control Language, Data Manipulation Language, Transaction Control Language.

What is Transaction Control Language in JDBC?

Transaction Control Language is one of the language in JDBC. Which is used for controlling and managing the transactions in database.

What is use of commit command in TCL?

The commit command is used for save transaction in the database.