SQL Categories - DDL, DML, DCL

SQL Categories: DDL, DML, DCL

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;

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;

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';