DATABASE MANAGEMENT SYSTEM

Database Management Systems (DBMS), DML (Data Manipulation Language), DDL (Data Definition Language), and DCL (Data Control Language) are three types of SQL (Structured Query Language) commands that serve different purposes in managing databases.

1. DML (Data Manipulation Language):

DML deals with the manipulation of data stored in the database. It includes commands that allow users to insert, update, retrieve, and delete data from the database. Common DML commands include:

SELECT

Retrieves data from one or more tables.

SELECT * FROM Students;

INSERT

Adds new records to a table.

INSERT INTO Students (StudentID, Name, Age, Major)
VALUES (1, 'Alice', 21, 'Computer Science');

​​​​​​​​​​​​​​UPDATE

Modifies existing records in a table.

UPDATE Students
SET Age = 22
WHERE StudentID = 1;

​​​​​​​DELETE

Removes records from a table.

DELETE FROM Students
WHERE StudentID = 1;

2. DDL (Data Definition Language):

DDL focuses on defining and managing the structure of the database, including tables, relationships, and constraints. DDL commands are used to create, alter, and delete database objects. Key DDL commands include:

CREATE

Defines new database objects such as tables, indexes, or views.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Major VARCHAR(100)
);

ALTER

Modifies the structure of existing database objects.

ALTER TABLE Students
ADD Email VARCHAR(100);

DROP

Deletes a table, index, or other database objects.

DROP TABLE Students;

TRUNCATE

Used to remove all records from a table, without deleting the table itself.

 

3. DCL (Data Control Language):

DCL is concerned with the control and management of access to data within the database. It includes commands that define user permissions and control the access rights to various database objects. Key DCL commands include:

GRANT

Provides specific privileges or permissions to users or roles.

GRANT SELECT, INSERT ON Students TO 'username';

REVOKE

Removes specific privileges previously granted to users or roles.

REVOKE INSERT ON Students FROM 'username';

PRACTICE QUESTION FOR COMPLETE COMMAND:

 

Creating a Table (DDL)

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    Position VARCHAR(50),
    Salary DECIMAL(10, 2)
);

Inserting Data into the Table (DML)

INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, Position, Salary)
VALUES (1, 'John', 'Doe', '1985-01-15', 'Manager', 75000.00);

Retrieving Data from the Table (DML)

SELECT * FROM Employees;

Updating Data in the Table (DML)

UPDATE Employees
SET Salary = 80000.00
WHERE EmployeeID = 1;

Deleting Data from the Table (DML)

DELETE FROM Employees
WHERE EmployeeID = 1;

Granting Permissions to a User (DCL)

GRANT SELECT, UPDATE ON Employees TO 'username';

Revoking Permissions from a User (DCL)

REVOKE UPDATE ON Employees FROM 'username';

Summary:

DML (Data Manipulation Language): Manages the manipulation of data within the database using SELECT, INSERT, UPDATE, and DELETE commands.

• DDL (Data Definition Language): Deals with the definition and management of the database structure using CREATE, ALTER, and DROP commands.
• DCL (Data Control Language): Controls and manages access to data within the database using GRANT and REVOKE commands.

These language components work together to ensure effective data management, security, and integrity in a relational database system.