What are the 3 main SQL statements?

In SQL (Structured Query Language), statements are categorized based on their functionality and the operations they perform on the database. Understanding these categories is fundamental for both using SQL effectively and preparing for SQL-related interview questions. The three main SQL statement categories are:

1. Data Definition Language (DDL)

Purpose:
DDL statements are used to define and manage all the structures in a database. They handle the creation, modification, and deletion of database objects such as tables, indexes, and schemas.

Common DDL Statements:

• CREATE: Creates a new database object (e.g., table, index).
  • Example:

    CREATE TABLE Employees (
        EmployeeID INT PRIMARY KEY,
        FirstName VARCHAR(50),
        LastName VARCHAR(50),
        Department VARCHAR(50)
    );

• ALTER: Modifies an existing database object.
  • Example:

    ALTER TABLE Employees
    ADD COLUMN Email VARCHAR(100);

• DROP: Deletes a database object permanently.
  • Example:

    DROP TABLE Employees;

• TRUNCATE: Removes all records from a table without deleting the table itself.
  • Example:

    TRUNCATE TABLE Employees;

Key Points:

• DDL commands automatically commit changes, meaning they cannot be rolled back.
• They are essential for setting up the database schema and making structural changes as needed.

2. Data Manipulation Language (DML)

Purpose:
DML statements are used to manipulate the data within the existing database objects. This includes inserting, updating, deleting, and retrieving data.

Common DML Statements:

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

    SELECT FirstName, LastName, Department
    FROM Employees
    WHERE Department = 'Sales';

• INSERT: Adds new records to a table.
  • Example:

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
    VALUES (1, 'John', 'Doe', 'Sales');

• UPDATE: Modifies existing records in a table.
  • Example:

    UPDATE Employees
    SET Department = 'Marketing'
    WHERE EmployeeID = 1;

• DELETE: Removes records from a table.
  • Example:

    DELETE FROM Employees
    WHERE EmployeeID = 1;

Key Points:

• DML commands are used frequently in day-to-day database operations.
• They can be part of transactions, allowing changes to be committed or rolled back as a unit.

3. Data Control Language (DCL)

Purpose:
DCL statements manage access to data within the database. They control permissions and security levels for users and roles.

Common DCL Statements:

• GRANT: Provides specific privileges to users or roles.
  • Example:

    GRANT SELECT, INSERT ON Employees TO UserA;

• REVOKE: Removes specific privileges from users or roles.
  • Example:

    REVOKE INSERT ON Employees FROM UserA;

Key Points:

• DCL commands are crucial for maintaining database security and ensuring that only authorized users can perform certain operations.
• They help in defining and enforcing access controls within the database environment.

Additional Categories (For Comprehensive Understanding)

While DDL, DML, and DCL are the primary categories, it's also helpful to be aware of other SQL statement categories:

• Data Query Language (DQL): Primarily involves the SELECT statement used for querying data.
• Transaction Control Language (TCL): Manages transactions within the database.
  • Common TCL Statements: COMMIT, ROLLBACK, SAVEPOINT.

However, for the purpose of foundational understanding and interview preparation, focusing on DDL, DML, and DCL will cover the essential SQL statements you need to know.

Summary

• DDL (Data Definition Language): Defines and manages database structures (CREATE, ALTER, DROP, TRUNCATE).
• DML (Data Manipulation Language): Manipulates data within those structures (SELECT, INSERT, UPDATE, DELETE).
• DCL (Data Control Language): Controls access and permissions (GRANT, REVOKE).

Mastering these categories and the associated statements will provide a strong foundation for effectively working with SQL and performing well in SQL-related interview scenarios.