DATABASE MANAGEMENT SYSTEM

DDL (Data Definition Language)

DDL commands are used to define the structure of the database, including creating, altering, and deleting database objects such as tables, indexes, and views. These commands do not deal with the actual data manipulation but focus on defining the database schema.

Create Table:

• Purpose: Defines a new table and its columns.
• Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10,2)
);

Alter Table:

• Purpose: Modifies the structure of an existing table (e.g., adding or dropping columns).
• Example:

ALTER TABLE employees
ADD COLUMN department_id INT;

Drop Table:

• Purpose: Deletes an existing table and its data.
• Example:

DROP TABLE employees;

DML (Data Manipulation Language)

DML commands are used to manipulate the data stored in the database. These commands focus on the CRUD operations: Create, Read, Update, and Delete.

Insert:

• Purpose: Adds new records (rows) to a table.
• Example:

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000);

Select:

• Purpose: Retrieves data from one or more tables.
• Example:

SELECT * FROM employees WHERE department_id = 1;

Update:

• Purpose: Modifies existing records in a table.
• Example:

UPDATE employees SET salary = 55000 WHERE employee_id = 1;

Delete:

• Purpose: Removes records from a table.
• Example:

DELETE FROM employees WHERE employee_id = 1;

Data Control Language (DCL)

Data Control Language (DCL) commands in a Database Management System (DBMS) are used to control access to data and database objects. DCL commands primarily deal with defining and managing permissions and privileges for users and roles. Two key DCL commands are GRANT and REVOKE.

Grant Permissions:

• Purpose: Gives specific privileges to a user or a role.
• Example:

GRANT SELECT, INSERT ON employees
TO user1, user2;

Revoke Permissions:

• Purpose: Removes specific privileges from a user or a role.
• Example:

REVOKE UPDATE, DELETE ON employees
FROM user1;