3 SQL (Structured Query Language) Explained

Key Concepts

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)

Data Definition Language (DDL)

Data Definition Language (DDL) is a subset of SQL used to define and manage the structure of database objects. DDL commands are used to create, alter, and delete database schemas and tables. The primary DDL commands include:

• CREATE: Used to create new tables, indexes, or other database objects.
• ALTER: Used to modify the structure of existing database objects.
• DROP: Used to delete existing database objects.

Example: Creating a Table

To create a table named "Employees" with columns for ID, Name, and Salary:

        CREATE TABLE Employees (
            ID INT PRIMARY KEY,
            Name VARCHAR(100),
            Salary DECIMAL(10, 2)
        );
    

Analogy: Blueprint for a House

Think of DDL as the blueprint for a house. Just as a blueprint defines the structure and layout of a house, DDL defines the structure of database objects. The CREATE command is like drawing the initial blueprint, ALTER is like making modifications to the blueprint, and DROP is like tearing down the blueprint entirely.

Data Manipulation Language (DML)

Data Manipulation Language (DML) is a subset of SQL used to manage data within database objects. DML commands are used to insert, update, and delete data in tables. The primary DML commands include:

• INSERT: Used to add new records to a table.
• UPDATE: Used to modify existing records in a table.
• DELETE: Used to remove records from a table.

Example: Inserting Data

To insert a new employee record into the "Employees" table:

        INSERT INTO Employees (ID, Name, Salary)
        VALUES (1, 'John Doe', 50000.00);
    

Analogy: Filling a Bookshelf

Think of DML as filling a bookshelf. The INSERT command is like adding a new book to the shelf, the UPDATE command is like replacing an existing book with a new one, and the DELETE command is like removing a book from the shelf. Just as you manage books on a shelf, DML helps you manage data in a database.

Data Control Language (DCL)

Data Control Language (DCL) is a subset of SQL used to control access to database objects. DCL commands are used to grant and revoke permissions to users. The primary DCL commands include:

• GRANT: Used to give specific permissions to users.
• REVOKE: Used to remove specific permissions from users.

Example: Granting Permissions

To grant a user named "Alice" the permission to select data from the "Employees" table:

        GRANT SELECT ON Employees TO Alice;
    

Analogy: Security System

Think of DCL as a security system for a house. The GRANT command is like giving someone a key to enter the house, while the REVOKE command is like taking the key away. Just as a security system controls who can enter a house, DCL controls who can access and manipulate data in a database.

Conclusion

Understanding the different subsets of SQL—Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL)—is crucial for effectively managing and controlling databases. By mastering these concepts, you can create, modify, and secure your database objects and data, ensuring a robust and efficient database management system.