Database Languages: An Overview

Just as we use languages to communicate with each other, databases have their own languages to interact with data. In the world of Database Management Systems (DBMS), these languages help define, manipulate, query, and control access to data. Let's explore the different types of database languages.

Data Definition Language (DDL)

Think of DDL as the blueprint for your database. It helps you set up the structure, like building the framework of a house.

CREATE

The CREATE command is used to create new database objects like tables, views, and schemas.

CREATE TABLE Furniture (
    furniture_id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(100),
    color VARCHAR(50),
    price DECIMAL(10,2)
);

ALTER

The ALTER command allows you to modify an existing database object, such as adding a new column to a table.

ALTER TABLE Furniture
ADD COLUMN material VARCHAR(50);

DROP

The DROP command is used to delete objects from the database.

DROP TABLE Furniture;

TRUNCATE

The TRUNCATE command removes all rows from a table, effectively emptying it without deleting its structure.

TRUNCATE TABLE Furniture;

RENAME

RENAME is used to change the name of a table or a column in a table.

ALTER TABLE Furniture RENAME COLUMN category TO item_type;

Data Manipulation Language (DML)

DML is like arranging furniture in your house. It helps you add, update, or remove items (data) in your database.

INSERT

The INSERT command adds new records to a table.

INSERT INTO Furniture (furniture_id, name, category, color, price)
VALUES (1, 'Sofa', 'Living Room', 'Brown', 599.99);

UPDATE

UPDATE modifies existing records in a table.

UPDATE Furniture
SET color = 'Black'
WHERE name = 'Table';

DELETE

DELETE removes specific records from a table.

DELETE FROM Furniture WHERE name = 'Lamp';

Data Query Language (DQL)

DQL is like asking questions about your furniture. It helps you retrieve specific data based on your needs.

SELECT

SELECT is used to query and retrieve data from the database.

SELECT * FROM Furniture WHERE price > 500.00;

This command fetches all furniture items with a price greater than $500.

Data Control Language (DCL)

DCL acts like the key to your rooms, controlling who can enter and make changes in the database.

GRANT

The GRANT command provides specific privileges to users.

GRANT SELECT ON Furniture TO user1;

REVOKE

The REVOKE command removes previously granted privileges.

REVOKE INSERT ON Furniture FROM user2;

Transaction Control Language (TCL)

TCL is your tool for managing and ensuring the integrity of your database transactions, similar to keeping chapters of a book safe.

COMMIT

COMMIT saves all changes made in the current transaction.

COMMIT;

ROLLBACK

ROLLBACK undoes changes since the last commit.

ROLLBACK;

SAVEPOINT

SAVEPOINT creates checkpoints in a transaction, allowing partial rollback.

SAVEPOINT my_savepoint;

TCL commands are essential for maintaining data consistency and integrity, especially in multi-user environments.