Welcome to My Learning Hub!
Today, we’re diving deeper into SQL (Structured Query Language), exploring the five sub-languages that make SQL a versatile and powerful tool for managing databases. Let’s break down each sub-language and its key commands!
🔹 SQL Sub-Languages Overview 🔹
SQL is not just one language; it’s a combination of several specialized sub-languages, each designed to perform specific tasks related to databases.
1. DDL (Data Definition Language) 📐
DDL is used to define and manage the structure of database objects, such as tables, views, and indexes. These commands deal with the metadata of the database.
Key DDL Commands:
-
CREATE:
Used to create database objects like tables, views, and indexes. -
ALTER:
Modifies an existing database object, such as adding or deleting columns from a table. -
DROP:
Removes a database object permanently (e.g., dropping a table). -
FLASHBACK (Oracle 10g):
Allows you to view past versions of data, useful for recovering data from mistakes or system errors. -
PURGE (Oracle 10g):
Permanently removes dropped objects from the recycle bin in Oracle. -
TRUNCATE:
Removes all data from a table without deleting the table itself. -
RENAME:
Changes the name of an existing database object, like renaming a table.
2. DRL/DQL (Data Retrieval Language / Data Query Language) 🔍
DRL/DQL is used for retrieving data from the database. The main goal is to query the database and extract meaningful information.
Key DRL Command:
- SELECT:
Retrieves data from one or more tables in the database. It is the most commonly used SQL command for querying data.
3. DML (Data Manipulation Language) 🔄
DML is used to manipulate the data within the database—whether you need to add, update, or delete data.
Key DML Commands:
-
INSERT:
Adds new rows of data into a table. -
UPDATE:
Modifies existing data in a table. -
DELETE:
Removes data from a table, but leaves the table structure intact. -
INSERT ALL (Oracle 9i):
Inserts multiple rows into one or more tables in a single command. -
MERGE (Oracle 9i):
Combines insert, update, and delete operations into a single statement, often used to synchronize tables.
4. TCL (Transaction Control Language) 🔄
TCL deals with the management of database transactions. It ensures the integrity and consistency of the database during operations.
Key TCL Commands:
-
COMMIT:
Saves all changes made during the current transaction to the database. -
ROLLBACK:
Undoes changes made during the current transaction, reverting the database to its previous state. -
SAVEPOINT:
Sets a point within a transaction to which you can later roll back, allowing more granular control over changes.
5. DCL (Data Control Language) 🔒
DCL is used for defining and controlling access privileges to database objects, ensuring the security and integrity of the database.
Key DCL Commands:
-
GRANT:
Assigns specific privileges (like SELECT, INSERT, DELETE) to a user or role in the database. -
REVOKE:
Removes previously granted privileges from a user or role.
In Summary:
SQL provides a structured way to manage databases, and understanding these sub-languages helps us use SQL efficiently to:
- Define and structure data (DDL)
- Retrieve data (DRL/DQL)
- Manipulate data (DML)
- Manage transactions (TCL)
- Control data access (DCL)
With this knowledge, you’re well on your way to becoming proficient in SQL! Stay tuned for more content as we continue to explore SQL and other database topics.
Happy learning!
