SQL Categories - DDL, DML, DCL

SQL Categories: DDL, DML, DCL

1. DDL (Data Definition Language)

DDL commands define the structure of the database. They create, modify, and delete schema objects such as tables and indexes.

CREATE: Creates a new table or database object.

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50)
);

ALTER: Modifies an existing object’s structure.

ALTER TABLE employees ADD COLUMN phone_number VARCHAR(15);

DROP: Deletes an entire table or object.

DROP TABLE employees;

TRUNCATE: Removes all records from a table, but keeps the table structure.

TRUNCATE TABLE employees;

1. DML (Data Manipulation Language)

DML commands manage the data inside tables. They are used to retrieve, insert, update, and delete records.

SELECT: Retrieves data from a table.

SELECT * FROM employees;

INSERT: Adds new data into a table.

INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (1, 'John', 'Doe', 'HR');

UPDATE: Modifies existing data in a table.

UPDATE employees
SET department = 'Finance'
WHERE employee_id = 1;

DELETE: Removes data from a table.

DELETE FROM employees WHERE employee_id = 1;

1. DCL (Data Control Language)

DCL commands control the access permissions to the data in the database.

GRANT: Grants privileges to a user.

GRANT SELECT, INSERT ON employees TO 'john_user';

REVOKE: Removes privileges from a user.

REVOKE SELECT, INSERT ON employees FROM 'john_user';

Summary:

DDL: Defines database structure (CREATE, ALTER, DROP).

DML: Manipulates data within tables (SELECT, INSERT, UPDATE, DELETE).

DCL: Controls permissions (GRANT, REVOKE).