1. Data Definition Language (DDL)

DDL is used to define and manage database structures such as tables, schemas, and indexes. These commands modify the structure of the database.

Common DDL Commands:

• CREATE: Used to create a new table, database, index, or other objects.

CREATE TABLE employees (
   id INT PRIMARY KEY,
   name VARCHAR(100),
   age INT
);
 

ALTER: Modifies an existing database object, such as adding, deleting, or modifying columns in a table.

ALTER TABLE employees ADD COLUMN email VARCHAR(100);
 

DROP: Deletes an existing database object (e.g., table, view).

DROP TABLE employees;
 

TRUNCATE: Removes all rows from a table, but retains the table structure for future use.

TRUNCATE TABLE employees;
 

RENAME (Oracle 9i): Used to rename an existing database object such as a table or column.

RENAME employees TO staff;
 

2. Data Manipulation Language (DML)

DML is used to manipulate data in existing tables. These commands deal with the insertion, update, deletion, and merging of data.

Common DML Commands:

• INSERT: Adds new records to a table.

INSERT INTO employees (id, name, age) VALUES (1, 'John Doe', 30);
 

UPDATE: Modifies existing records in a table.

UPDATE employees SET age = 31 WHERE id = 1;
 

DELETE: Removes records from a table.

DELETE FROM employees WHERE id = 1;
 

MERGE (Oracle 9i): A combination of INSERT, UPDATE, and DELETE used to merge data into a table. It's often used for upserting (inserting or updating).

MERGE INTO employees e
USING new_employees ne
ON (e.id = ne.id)
WHEN MATCHED THEN
   UPDATE SET e.name = ne.name, e.age = ne.age
WHEN NOT MATCHED THEN
   INSERT (id, name, age) VALUES (ne.id, ne.name, ne.age);
 

3. Data Retrieval Language (DRL) / Data Query Language (DQL)

DQL is used for querying the database to retrieve data. The most common command is SELECT.

Common DQL Command:

• SELECT: Retrieves data from one or more tables based on the given criteria.

SELECT name, age FROM employees WHERE age > 30;
 

4. Transaction Control Language (TCL)

TCL is used to manage transactions in the database. These commands ensure that all the operations within a transaction are completed successfully or rolled back if there’s an error, maintaining data consistency.

Common TCL Commands:

• COMMIT: Commits a transaction, making all changes permanent.

COMMIT;
 

ROLLBACK: Rolls back the current transaction, undoing any changes made.

ROLLBACK;
 

SAVEPOINT: Sets a savepoint within a transaction to which you can later roll back if needed.

SAVEPOINT savepoint_name;
 

5. Data Control Language (DCL)

DCL is used to control access to data and database objects. These commands manage user permissions.

Common DCL Commands:

• GRANT: Gives a user or role specific privileges to perform actions on a database object.

GRANT SELECT, INSERT ON employees TO user1;
 

REVOKE: Removes a user's privileges to perform certain actions on a database object.

REVOKE INSERT ON employees FROM user1;