DBMS Languages
Database management system (DBMS) is a software system that enables users to define, create, maintain, and control access to a database. There are several languages used in DBMS to perform various operations. In this blog post, we will discuss the different languages used in DBMS.
DBMS languages are required to practically implement the design of the database. They are of following types:
DDL (Data Definition Language)
DCL (Data Control Language)
DML (Data Manipulation Language)
TCL (Transaction Control Language)
DDL (Data Definition Language)
DDL is used to store and access the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.
Some of the tasks of DDL are:
Create:
It is used to create objects in the database.
Alter:
It is used to alter the structure of the database.
Drop:
It is used to delete objects from the database.
Rename:
It is used to rename an object.
Comment:
It is used to comment on the data dictionary.
DML (Data Manipulation Language)
It is used for accessing and manipulating data in a database. It handles user requests.
Some of the tasks of DML are:
Select:
It is used to retrieve data from a database.
Insert:
It is used to insert data into a table.
Update:
It is used to update existing data within a table.
Delete:
It is used to delete all records from a table.
Call:
It is used to call a structured query language.
DCL (Data Control Language)
Some tasks that come under DCL:
Grant:
Revoke:
TCL (Transaction Control Language)
Some tasks that come under TCL are:
Commit:
Rollback:
Here are some examples of languages used in DBMS:
- SQL (Structured Query Language): SQL is the most widely used language for managing relational databases. It is used to create, modify, and query databases. Here’s an example of a SQL query:
SELECT * FROM customers WHERE country=’USA’;
- PL/SQL (Procedural Language/Structured Query Language): PL/SQL is an extension of SQL that adds procedural programming features to SQL. It is used to create stored procedures, functions, and triggers. Here’s an example of a PL/SQL block:
DECLARE x NUMBER := 10; BEGIN IF x > 5 THEN DBMS_OUTPUT.PUT_LINE(‘x is greater than 5’); ELSE DBMS_OUTPUT.PUT_LINE(‘x is less than or equal to 5’); END IF; END;
- T-SQL (Transact-SQL): T-SQL is a dialect of SQL that is used by Microsoft SQL Server. It includes additional features like transaction management, error handling, and cursors. Here’s an example of a T-SQL query:
SELECT FirstName, LastName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE OrderDate BETWEEN ’01/01/2022′ AND ’12/31/2022′;
- PL/pgSQL (Procedural Language/PostgreSQL): PL/pgSQL is a procedural language used by PostgreSQL. It is similar to PL/SQL and includes features like loops, conditionals, and exception handling. Here’s an example of a PL/pgSQL function:
CREATE FUNCTION get_total_price(order_id INT) RETURNS NUMERIC AS $$ DECLARE total_price NUMERIC := 0; BEGIN SELECT SUM(quantity*price) INTO total_price FROM order_items WHERE order_id = $1; RETURN total_price; END;
