Database Languages in DBMS

Database Management Systems (DBMS) use specialized languages to interact with and manipulate data. These languages are categorized based on their functionality and purpose. Below are the main types of database languages:

1. Data Definition Language (DDL)

• Purpose: Used to define and modify the structure of the database schema.

• Operations:

  • CREATE: Creates new database objects (e.g., tables, indexes, views).

  • ALTER: Modifies the structure of existing database objects.

  • DROP: Deletes database objects.

  • TRUNCATE: Removes all records from a table but retains the table structure.

  • RENAME: Renames database objects.

• Examples:

  sql

  CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(50), Department VARCHAR(50) );

  
  

2. Data Manipulation Language (DML)

• Purpose: Used to retrieve, insert, update, and delete data in the database.

• Operations:

  • SELECT: Retrieves data from the database.

  • INSERT: Adds new records to a table.

  • UPDATE: Modifies existing records in a table.

  • DELETE: Removes records from a table.

• Examples:

  sql

  INSERT INTO Employees (EmployeeID, Name, Department) VALUES (1, 'John Doe', 'HR');

  
  

3. Data Control Language (DCL)

• Purpose: Used to control access to data in the database.

• Operations:

  • GRANT: Gives users access privileges to the database.

  • REVOKE: Removes access privileges from users.

• Examples:

  sql

  GRANT SELECT ON Employees TO 'user1';

  
  

4. Transaction Control Language (TCL)

• Purpose: Used to manage transactions in the database.

• Operations:

  • COMMIT: Saves changes made during a transaction.

  • ROLLBACK: Undoes changes made during a transaction.

  • SAVEPOINT: Sets a point within a transaction to which you can roll back.

• Examples:

  sql

  COMMIT; ROLLBACK;

  
  

5. Data Query Language (DQL)

• Purpose: Used to query and retrieve data from the database.

• Operations:

  • SELECT: Retrieves data from one or more tables.

• Examples:

  sql

  SELECT Name, Department FROM Employees WHERE EmployeeID = 1;

  
  

6. Procedural Language Extensions

• Purpose: Extends SQL with procedural programming capabilities.

• Examples:

  • PL/SQL (Oracle): Combines SQL with procedural features like loops and conditions.

  • T-SQL (Microsoft SQL Server): Extends SQL with procedural programming.

• Example:

  sql

  BEGIN IF (SELECT COUNT(*) FROM Employees) > 0 THEN PRINT 'Records exist'; END IF; END;

  
  

Summary of Database Languages:

Language	Purpose	Key Commands
DDL	Define/modify database structure	CREATE, ALTER, DROP, TRUNCATE
DML	Manipulate data	SELECT, INSERT, UPDATE, DELETE
DCL	Control access to data	GRANT, REVOKE
TCL	Manage transactions	COMMIT, ROLLBACK, SAVEPOINT
DQL	Query and retrieve data	SELECT
Procedural	Extend SQL with procedural programming	PL/SQL, T-SQL

These languages collectively enable users and applications to interact with databases effectively, ensuring data integrity, security, and efficient management.