Types of SQL Commands

To manage a global retail powerhouse, SQL is divided into five functional categories. Each category represents a different way of interacting with the database, optimized for specific tasks.

• DDL (Data Definition Language): These are the "Blueprints". Use DDL to create, modify, or destroy the structures (tables, indexes, views) where data lives. Example: Creating a 'transactions' table to track customer orders.
• DML (Data Manipulation Language): These are the "Actions". Use DML to add, change, or remove the actual data within those structures. Example: Updating stock levels after a Black Friday shipment.
• DQL (Data Query Language): These are the "Questions". Use DQL to retrieve specific information from your data to drive strategy. Example: Asking "Which regions have the highest revenue growth?"
• DCL (Data Control Language): This is the "Security". Use DCL to manage who can see or change which data. Example: Giving the New York Regional Manager access to local sales only.
• TCL (Transaction Control Language): This is the "Insurance". Use TCL to ensure that groups of actions either all succeed perfectly or all fail safely. Example: Ensuring that if a bank transfer fails, the item remains 'Unsold' (Rolling back).

Note: We will dive deep into each of these categories individually in the upcoming chapters of this course.

DDL (Data Definition Language) is for the "Architects". It represents the structure of your business. When HQ decides to launch a new product line like LuxeHome, or opens a new distribution center in Singapore, DDL is used to build the digital containers for that information. Key commands include:

• CREATE: Build a new table or index.
• ALTER: Modify an existing structure (e.g., adding a 'currency_code' column).
• DROP: Permanently remove a structure.
• TRUNCATE: Keep the structure but instantly wipe all data inside.

DML (Data Manipulation Language) is for the "Operators". It is the most active part of a retail database, running thousands of times per second. Every time a customer taps their card, a price is slashed for a flash sale, or an item is returned, DML is at work. Key commands include:

• INSERT: Add a new record (a sale, a shipment, a customer).
• UPDATE: Change existing values (adjusting inventory, clearing balances).
• DELETE: Remove a specific record.

DQL (Data Query Language) is the power tool of Data Scientists and Retail Strategists. it consists primarily of the SELECT command, but its power is limitless when joined with complex logic.

In our global retail powerhouse, DQL allows us to move from "What happened?" to "Why did it happen?". By querying millions of rows across different countries, we can identify that TechZero watches are selling 40% faster in Tokyo than in Berlin because of localized marketing campaigns. DQL transforms raw data into high-value business intelligence.

While DDL, DML, and DQL handle the data itself, DCL (Data Control Language) and TCL (Transaction Control Language) handle the environment in which that data exists.

DCL ensures that sensitive financial data is protected. Commands like GRANT and REVOKE allow HQ to ensure that a Store Manager can see their local sales but not the salary information of the CEO. Security is the foundation of trust in global retail.

TCL ensures that the database stays perfectly accurate even when things go wrong. Commands like COMMIT and ROLLBACK act as a safety net. Imagine a customer's payment is processed but the internet cuts out before the stock is updated. TCL ensures that either BOTH happen, or NEITHER happen, preventing "ghost" stock errors.