SQL Command Types (DDL, DML, DQL, TCL, DCL)
SQL commands are grouped by what they do. Knowing the groups helps you understand the commands you have learned.
+-------------------+---------------------------------------+
| Command group | Common commands |
+-------------------+---------------------------------------+
| DDL (Definition) | CREATE, ALTER, DROP, TRUNCATE |
| DML (Manipulation)| INSERT, UPDATE, DELETE |
| DQL (Query) | SELECT |
| TCL (Transaction) | BEGIN, COMMIT, ROLLBACK, SAVEPOINT |
| DCL (Control) | GRANT, REVOKE |
+-------------------+---------------------------------------+
1. DDL: Data Definition Language
Used to create and change the structure of tables, views and indexes.
CREATE TABLE departments (...);
ALTER TABLE employees ADD COLUMN salary INT;
DROP TABLE departments;
2. DML: Data Manipulation Language
Used to change the data inside tables.
INSERT INTO employees VALUES (...);
UPDATE employees SET age = 30 WHERE id = 1;
DELETE FROM employees WHERE id = 5;
3. DQL: Data Query Language
Used to read data from tables.
SELECT * FROM employees;
SELECT name, department FROM employees WHERE age > 30;
4. TCL: Transaction Control Language
Used to manage transactions.
BEGIN;
UPDATE employees SET department = 'Sales' WHERE id = 4;
COMMIT;
5. DCL: Data Control Language
Used to control who can access the data. A database administrator uses these to give or take away permissions.
GRANT SELECT ON employees TO report_user;
REVOKE SELECT ON employees FROM report_user;
Each group has its own job. In the exercise, you will use a DDL command and then a DML command.