Types of SQL Commands

SQL commands are categorized into different types based on their functionality. These categories include DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language). Each type serves a specific purpose in managing and interacting with relational databases.

1. Data Definition Language (DDL)

DDL commands are used to define and manage database structures such as tables, indexes, and schemas. Common DDL commands include CREATE, ALTER, DROP, and TRUNCATE.

Examples:

      -- Create a table
      CREATE TABLE Employees (
          EmployeeID INT PRIMARY KEY,
          Name VARCHAR(50),
          Department VARCHAR(50)
      );

      -- Alter the table to add a new column
      ALTER TABLE Employees
      ADD Salary DECIMAL(10, 2);

      -- Drop the table
      DROP TABLE Employees;

      -- Remove all rows from a table without logging
      TRUNCATE TABLE Employees;
    

2. Data Manipulation Language (DML)

DML commands are used to manipulate data in the database. These include INSERT, UPDATE, DELETE, and SELECT.

Examples:

      -- Insert data into the table
      INSERT INTO Employees (EmployeeID, Name, Department, Salary)
      VALUES (1, 'John Doe', 'HR', 50000.00);

      -- Update existing data
      UPDATE Employees
      SET Salary = 55000.00
      WHERE EmployeeID = 1;

      -- Delete specific data
      DELETE FROM Employees
      WHERE EmployeeID = 1;

      -- Retrieve data
      SELECT * FROM Employees;
    

3. Data Control Language (DCL)

DCL commands are used to control access to the database. Common DCL commands include GRANT and REVOKE.

Examples:

      -- Grant privileges to a user
      GRANT SELECT, INSERT ON Employees TO 'username';

      -- Revoke privileges from a user
      REVOKE INSERT ON Employees FROM 'username';
    

4. Transaction Control Language (TCL)

TCL commands are used to manage transactions within the database. These include COMMIT, ROLLBACK, and SAVEPOINT.

Examples:

      -- Start a transaction and insert data
      BEGIN TRANSACTION;

      INSERT INTO Employees (EmployeeID, Name, Department, Salary)
      VALUES (2, 'Jane Smith', 'IT', 60000.00);

      -- Save a point in the transaction
      SAVEPOINT Save1;

      -- Roll back to the savepoint
      ROLLBACK TO Save1;

      -- Commit the transaction
      COMMIT;
    

Conclusion

SQL commands are classified into DDL, DML, DCL, and TCL to organize their functionality. Understanding these categories helps you effectively manage and interact with relational databases. Mastering these commands is essential for working with SQL.