Understanding the Different Types of SQL Commands

What Are the Different Types of SQL Commands?

SQL commands are categorized based on their functionality in managing and interacting with relational databases. These commands help developers and database administrators perform various operations, from defining the structure of a database to manipulating and securing data.

Here are the primary types of SQL commands:

1. DDL (Data Definition Language)

DDL commands define and modify the structure of database objects such as tables, indexes, and schemas.

Key Commands:

• CREATE: Creates a new table, view, or database object.

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

• ALTER: Modifies an existing database object like a table or column.

  ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• DROP: Deletes database objects like tables or views.

  DROP TABLE employees;

• TRUNCATE: Deletes all records from a table without logging individual row deletions.

  TRUNCATE TABLE employees;

2. DML (Data Manipulation Language)

DML commands are used to manipulate data stored in the database, allowing you to retrieve, insert, update, and delete records.

Key Commands:

• INSERT: Adds new records to a table.

  INSERT INTO employees (id, name, position, salary)
  VALUES (1, 'John Doe', 'Manager', 75000);

• UPDATE: Modifies existing records in a table.

  UPDATE employees
  SET salary = 80000
  WHERE id = 1;

• DELETE: Removes records from a table.

  DELETE FROM employees
  WHERE id = 1;

3. DQL (Data Query Language)

DQL commands are focused on querying data from the database. The SELECT statement is the only command in this category.

Key Command:

• SELECT: Retrieves specific data from one or more tables.

  SELECT name, position, salary
  FROM employees
  WHERE salary > 50000;

4. DCL (Data Control Language)

DCL commands control access to the database by managing permissions and security.

Key Commands:

• GRANT: Assigns specific permissions to users.

  GRANT SELECT, INSERT ON employees TO user1;

• REVOKE: Removes previously granted permissions.

  REVOKE INSERT ON employees FROM user1;

5. TCL (Transaction Control Language)

TCL commands manage database transactions to ensure data consistency and integrity.

Key Commands:

• COMMIT: Saves all changes made during a transaction.

  COMMIT;

• ROLLBACK: Undoes changes made during a transaction.

  ROLLBACK;

• SAVEPOINT: Creates a point within a transaction to which you can roll back.

  SAVEPOINT save1;

• SET TRANSACTION: Defines properties for a transaction.

  SET TRANSACTION READ ONLY;

Examples of Combining Commands

Here’s how different SQL commands work together:

1. DDL + DML + DQL Example:

   -- Define a new table
   CREATE TABLE products (
       id INT PRIMARY KEY,
       name VARCHAR(100),
       price DECIMAL(10, 2)
   );

   -- Insert data into the table
   INSERT INTO products (id, name, price)
   VALUES (1, 'Laptop', 1200.00);

   -- Query the data
   SELECT * FROM products;

1. TCL with DML:

   BEGIN TRANSACTION;

   INSERT INTO employees (id, name, position, salary)
   VALUES (2, 'Jane Smith', 'Developer', 65000);

   SAVEPOINT save1;

   UPDATE employees
   SET salary = 70000
   WHERE id = 2;

   ROLLBACK TO save1;

   COMMIT;

Conclusion

SQL commands are fundamental tools for interacting with and managing relational databases. Understanding these categories—DDL, DML, DQL, DCL, and TCL—allows developers and database administrators to build, modify, and secure databases effectively while maintaining data integrity. Each type of command plays a vital role in the full lifecycle of database management.

Hi, I'm Abhay Singh Kathayat!
I am a full-stack developer with expertise in both front-end and back-end technologies. I work with a variety of programming languages and frameworks to build efficient, scalable, and user-friendly applications.
Feel free to reach out to me at my business email: kaashshorts28@gmail.com.