Lesson 4 – Types of SQL Commands (DDL, DML, DCL, TCL Overview)

Welcome back, data explorer!

In our last lesson, we walked the streets of our City of Data and learned its fundamental layout: the districts (Tables), the street names (Columns), and the individual houses (Rows). We now have a solid blueprint of the city’s structure.

But a city isn’t just buildings and streets; it’s the people and the actions they perform that bring it to life. There are architects who design new skyscrapers, citizens who buy and sell goods, security guards who protect valuable assets, and bankers who ensure transactions are safe.

Each of these groups uses a specialized version of the city’s language to do their job. In the world of SQL, it’s exactly the same. The SQL language is divided into four main categories, or sublanguages, each designed for a specific purpose. Understanding these types of SQL commands is like learning the different professional jargons of the city.

In this lesson, we will meet the four key professionals of our data city and learn about the powerful types of SQL commands they use:

1. DDL (Data Definition Language) – The Language of the Architects.
2. DML (Data Manipulation Language) – The Language of the Citizens.
3. DCL (Data Control Language) – The Language of the Guards.
4. TCL (Transaction Control Language) – The Language of the Bankers.

Let’s begin our tour of the city’s professions.

Meet the Architects: What is Data Definition Language (DDL)?

Every city starts with a blueprint. Before any data can be stored, someone must design the structures that will hold it. This is the job of the Data Architects, and their specialized vocabulary is Data Definition Language (DDL).

DDL commands are used to define, create, and modify the structure of the database and its objects, like tables, indexes, and views. DDL is all about the schema or the blueprint of the database, not the data itself.

When you use a DDL command, you are making a permanent, structural change to the city. You’re not just changing the furniture inside a house; you’re building a new house, adding a new floor, or demolishing the building entirely.

The primary DDL commands are:

• CREATE: This is the command to build something new. You use it to CREATE TABLE, CREATE VIEW, or CREATE INDEX. It’s like an architect laying down the foundation for a new skyscraper (table) in the city.
• ALTER: Cities evolve. Sometimes you need to add a new lane to a road or a new room to a building. The ALTER TABLE command allows you to modify an existing table’s structure, such as adding a new column or changing a column’s data type.
• DROP: This is a powerful and dangerous command. DROP TABLE completely and permanently deletes a table and all the data within it. It’s the equivalent of demolishing a building and everything inside. There is no undo button.
• TRUNCATE: This command is like DROP‘s cousin. TRUNCATE TABLE removes all the rows (data) from a table instantly but leaves the empty structure (the building) intact. It’s faster than deleting rows one by one but should also be used with extreme caution.
• RENAME: As the name suggests, this command is used to change the name of a database object, like a table.

Understanding DDL is fundamental to all types of SQL commands because it sets the stage for everything else.

Check Your Understanding

1. Question: If you want to add a new column called phone_number to your existing Customers table, which DDL command would you use?
   • Answer: You would use the ALTER command (ALTER TABLE Customers ADD COLUMN phone_number TEXT;).
2. Question: What is the critical difference between DROP TABLE and TRUNCATE TABLE?
   • Answer: DROP TABLE deletes the table’s structure and all its data permanently. TRUNCATE TABLE deletes all the data but leaves the empty table structure behind.
3. Question: Are DDL commands easily reversible?
   • Answer: No. DDL commands make permanent changes to the database structure. Actions like DROP are generally irreversible without a backup, which is why they must be used with great care.

Meet the Citizens: What is Data Manipulation Language (DML)?

Once the architects have built the city, the citizens move in and start living their lives. They conduct business, move into new homes, update their information, and sometimes leave. These daily activities are managed with Data Manipulation Language (DML).

DML commands are used to insert, update, delete, and retrieve the actual data stored within the database tables. DML is concerned with the content of the database, not its structure.

This is the sublanguage you will use most frequently as a data analyst or developer. You’re not changing the blueprint of the city; you’re interacting with the people and goods inside it.

The core DML commands are:

• SELECT: This is the most famous SQL command. It’s used to retrieve data from one or more tables. It’s how you ask questions, like “Show me all products under ₹5000.” It’s the primary tool for all data analysis.
• INSERT: This command adds new rows of data into a table. When a new user signs up for your website, an INSERT statement is used to add their information to the Customers table. It’s like a new family moving into a newly built house.
• UPDATE: This command modifies existing data in a table. If a customer changes their city from Mumbai to Delhi, you would use an UPDATE statement to change the value in the city column for that specific customer’s row.
• DELETE: This command removes one or more rows from a table based on a condition. If a customer closes their account, you might use DELETE to remove their record. Unlike TRUNCATE, DELETE is a more careful, row-by-row operation.

For more information on the specifics of DML, you can refer to authoritative sources like the Oracle Database SQL Language Reference.

Check Your Understanding

1. Question: A user changes their profile picture on a social media app. Which DML command is likely executed?
   • Answer: An UPDATE command, to change the value in the profile_picture_url column for that user’s row.
2. Question: Your company’s blog has a new article. What command is used to add it to the Articles table in the database?
   • Answer: An INSERT command is used to add a new row containing the article’s title, content, author, and publication date.
3. Question: What is the main difference in purpose between SELECT and the other three DML commands?
   • Answer: SELECT is used only for reading or retrieving data, it doesn’t change the data. INSERT, UPDATE, and DELETE are all used to modify the data in the table.

Meet the Guards: What is Data Control Language (DCL)?

Our city has valuable assets—banks, government buildings, and private homes. You can’t let just anyone walk in and take whatever they want. The city needs a security force to manage permissions and access. This is the job of Data Control Language (DCL).

DCL commands are used to manage user access and permissions to the database. It’s all about security—who can do what, and where.

As a beginner analyst, you might not write DCL commands often, as this is typically the responsibility of a Database Administrator (DBA). However, understanding what they do is crucial for knowing how database security works.

The two primary DCL commands are:

• GRANT: This command gives a specific user specific permissions. For example, the DBA can GRANT a junior analyst SELECT permission on the Products table, but not DELETE permission. This means the analyst can read the product data but cannot accidentally remove it.
• REVOKE: This is the opposite of GRANT. It takes away permissions from a user. If an employee leaves the company, their access to the database can be immediately revoked.

DCL is the gatekeeper of the database, ensuring that data is only accessed and modified by authorized users. For a deeper look into how permissions work, you can explore resources like the PostgreSQL documentation on Privileges.

Check Your Understanding

1. Question: A new marketing intern joins the company. They need to be able to see customer data to build reports but should not be able to change any customer details. Which two DCL commands might the DBA use?
   • Answer: The DBA would first GRANT SELECT ON Customers TO marketing_intern;. They would ensure the intern does not have UPDATE or DELETE permissions.
2. Question: Is DCL used to change the data or the structure of a table?
   • Answer: Neither. DCL is used to change permissions related to who can access or modify the data and structure.

Meet the Bankers: What is Transaction Control Language (TCL)?

In our city, some actions are more complex than others. Buying a coffee is a simple, one-step action. But transferring money from your savings account to your checking account is a two-step process: (1) money must leave savings, and (2) money must arrive in checking.

What if the power goes out after step 1 but before step 2? The money is gone from savings but never arrived in checking! This is a disaster. To prevent this, the city’s bankers use a special set of protocols called Transaction Control Language (TCL).

TCL commands are used to manage transactions in the database. A transaction is a sequence of operations performed as a single, logical unit of work. All the operations in a transaction must succeed; if any part fails, the entire transaction fails.

This ensures the database remains in a consistent state, a concept known as ACID compliance.

The main TCL commands are:

• BEGIN TRANSACTION (or START TRANSACTION): Marks the beginning of a transaction. It’s like telling the banker, “I’m about to do something important, please watch closely.”
• COMMIT: This command saves all the work done in the transaction permanently. It’s the final approval. Once you COMMIT, the changes are visible to everyone and cannot be undone.
• ROLLBACK: This command undoes all the work done since the transaction began. If step 2 of our bank transfer failed, the banker would issue a ROLLBACK, and the money would magically reappear in the savings account as if nothing ever happened.
• SAVEPOINT: This allows you to set a specific point within a complex transaction that you can roll back to, without having to undo the entire transaction.

TCL is the safety net that guarantees data integrity during complex operations.

Check Your Understanding

1. Question: When booking a flight online, the system needs to reserve your seat and process your payment simultaneously. Why is this a perfect use case for a transaction?
   • Answer: Because you don’t want the system to take your money if it fails to reserve your seat. The entire process (payment + reservation) must be treated as a single unit. It should either fully COMMIT (succeed) or fully ROLLBACK (fail).
2. Question: What is the purpose of COMMIT?
   • Answer: To make the changes made during a transaction permanent and final.

Conclusion: The Four Pillars of SQL

We’ve now met the key players in our City of Data. You’ve learned that the types of SQL commands are not just a random list of words but a well-defined set of tools for different professions:

• DDL for the architects who build the city.
• DML for the citizens who live and work in it.
• DCL for the guards who protect it.
• TCL for the bankers who ensure its financial integrity.

While you will spend most of your time speaking DML as a data explorer, understanding this complete picture is what separates a novice from a true professional. You now have a mental framework for every SQL command you will ever learn.

Now that you know the language categories, let’s zoom back in and start learning the most fundamental grammar rules.

➡️ Next Lesson: How SQL Works: The SQL Engine

Key Takeaways

• SQL commands are categorized into four main types: DDL, DML, DCL, and TCL.
• DDL (Data Definition Language) defines and manages the database’s structure (e.g., CREATE, ALTER, DROP).
• DML (Data Manipulation Language) is used to interact with the data inside tables (e.g., SELECT, INSERT, UPDATE, DELETE).
• DCL (Data Control Language) manages user permissions and access rights (e.g., GRANT, REVOKE).
• TCL (Transaction Control Language) manages transactions to ensure data consistency (e.g., COMMIT, ROLLBACK).

Frequently Asked Questions (FAQ)