Skip to main content

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.

SQL
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.

SQL
ALTER TABLE Furniture
ADD COLUMN material VARCHAR(50);

DROP​

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

SQL
DROP TABLE Furniture;

TRUNCATE​

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

SQL
TRUNCATE TABLE Furniture;
Note

TRUNCATE deletes all data but keeps the table structure intact.

RENAME​

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

SQL
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.

SQL
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.

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

DELETE​

DELETE removes specific records from a table.

SQL
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.

SQL
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.

SQL
GRANT SELECT ON Furniture TO user1;

REVOKE​

The REVOKE command removes previously granted privileges.

SQL
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.

SQL
COMMIT;

ROLLBACK​

ROLLBACK undoes changes since the last commit.

SQL
ROLLBACK;

SAVEPOINT​

SAVEPOINT creates checkpoints in a transaction, allowing partial rollback.

SQL
SAVEPOINT my_savepoint;

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