What is the Database Language? Explain DML and DDL.

by

Database languages are specialized programming languages that are used to communicate with and manipulate databases. There are two main categories of database languages: data definition languages (DDL) and data manipulation languages (DML).

In other words, a database language is a programming language that is used to create, modify and maintain the structure and content of a database. There are two main types of database languages: Data Definition Language (DDL) and Data Manipulation Language (DML).

Types of Database Language

  1. Data Definition Language (DDL): This subset of SQL is used for defining, altering, and managing the structure of database objects such as tables, indexes, and schemas. Common DDL commands include CREATE, ALTER, and DROP.
  2. Data Manipulation Language (DML): DML is used for inserting, updating, deleting, and retrieving data within the database. Key DML commands are INSERT, UPDATE, DELETE, and SELECT.
  3. Data Control Language (DCL): DCL commands manage permissions and access control for database users and roles. The most common DCL statements are GRANT and REVOKE, which are used to control access to data and database operations.
  4. Transaction Control Language (TCL): TCL commands manage the transactions in a database, ensuring data integrity and consistency. Examples include COMMIT, ROLLBACK, and SAVEPOINT, which help manage changes made by DML operations, allowing them to be committed or undone.

1). Data Definition Language (DDL)

DDL is a set of commands that are used to define the database schema. It is used to create, modify, and delete database objects such as tables, indices and users. This includes specifying the structure of the database, as well as any constraints or rules that should be enforced on the data. Examples of DDL commands include <strong>CREATE</strong>, <strong>ALTER</strong>, and <strong>DROP</strong>.

  • CREATE: Builds a new database, table, or other objects. For example, CREATE TABLE defines a new table structure.
  • ALTER: Modifies the structure of an existing database object, such as adding a column to an existing table.
  • DROP: Removes an existing database object, deleting it and its contained data permanently.
  • TRUNCATE: Removes all records from a table, including all spaces allocated for the records are removed.
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL);
ALTER TABLE users ADD security_question VARCHAR(255);
DROP TABLE users;

2). Data Manipulation Language (DML)

DML is a set of commands that are used to manipulate the data stored in a database. It is used to insert, update, delete, and retrieve data from the database. This includes operations such as inserting, updating, and deleting data. Examples of DML commands include <strong>SELECT</strong>, <strong>INSERT</strong>, <strong>UPDATE</strong>, and <strong>DELETE</strong>.

  • SELECT: Retrieves data from a database and allows the use of various clauses to refine query results.
  • INSERT: Adds new records to a table.
  • UPDATE: Modifies existing records within a table.
  • DELETE: Removes existing records from a table.

For example, the following SQL statements are examples of DDL and DML commands:

INSERT INTO Students (StudentID, StudentName, Age) VALUES (1, 'Alice', 20);
SELECT * FROM Students;
UPDATE Students SET Age = 21 WHERE StudentID = 1;
DELETE FROM Students WHERE StudentID = 1;

Both DDL and DML are essential parts of working with databases, and they are often used together in order to create a functional database system.

3). Data Control Language (DCL)

DCL commands deal with rights, permissions, and other controls of the database system. They help define who can access what in a database.

  • GRANT: Gives a user permission to perform specified actions on database objects.
  • REVOKE: Removes previously granted or denied permissions from a user.

GRANT SELECT ON Students TO User1;
REVOKE SELECT ON Students FROM User1;

4). Transaction Control Language (TCL)

TCL commands manage the changes made by DML statements, allowing those changes to be done fully or undone if necessary. This ensures data integrity.

  • COMMIT: Saves all changes made during the current transaction, making them permanent in the database.
  • ROLLBACK: Reverts changes made during the current transaction, returning the affected data to its previous state.
  • SAVEPOINT: Sets a point within a transaction that you can roll back to, without affecting the entire transaction.
INSERT INTO Students (StudentID, StudentName, Age) VALUES (2, 'Bob', 22);
SAVEPOINT SavePoint1;
UPDATE Students SET Age = 23 WHERE StudentID = 2;
ROLLBACK TO SavePoint1;
COMMIT;