Data Definition Language (DDL) 🚀

Prerequisites: Database Basics (Logic Snippet)

Mentor's Note: DDL is about the Structure, not the data. Think of it as building the house itself before you move the furniture in! 💡

---

🌟 The Scenario: The Architect's Blueprint 🏛️

Imagine you are building a new luxury apartment complex.

• CREATE: You design the Blueprint (Number of rooms, floor plan). You are building the structure. 🏗️
• ALTER: You decide to add a Balcony to every room later. You are changing the existing structure. 🛠️
• DROP: The building is old and dangerous. you Demolish it entirely. 💥
• The Result: You are managing the "Container," not the people living inside. ✅

---

📖 Key DDL Commands

1. CREATE

Used to create a new table or database object.

CREATE TABLE students (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    age NUMBER
);

2. ALTER

Used to modify an existing object (add/remove columns).

ALTER TABLE students ADD (email VARCHAR2(50));

3. DROP

Deletes the table and ALL its data permanently.

DROP TABLE old_records;

---

🎨 Visual Logic: The Schema Lifecycle

graph LR
    A[Design 📝] -- CREATE --> B[New Table 📋]
    B -- ALTER --> C[Updated Structure 🛠️]
    C -- DROP --> D[Deleted / Empty Space 💨]

---

💻 Implementation: The Schema Lab

Creating a TableModifying a Table

-- 🛒 Scenario: Setting up a library system
-- 🚀 Action: Defining the book structure

CREATE TABLE books (
    book_id NUMBER(5) PRIMARY KEY,
    title VARCHAR2(200) NOT NULL,
    author VARCHAR2(100),
    price NUMBER(8,2)
);

-- 🛍️ Outcome: A new empty "container" for books.

-- 🚀 Action: Add a column for Published Year

ALTER TABLE books 
ADD (published_year NUMBER(4));

---

📊 Sample Dry Run

Command	Action	Data Inside?	Structure Changes?
CREATE	Build box 📦	No	New box added.
ALTER	Add divider 📏	Unaffected	Box modified.
DROP	Burn box 🔥	DESTROYED	Box removed.

---

📉 Complexity Analysis

• Execution: DDL commands are Auto-Committed in Oracle. This means once you run them, you cannot "Undo" (Rollback) the change! ⚠️ Be very careful.

---

🎯 Practice Lab 🧪

Task: The Employee Setup

Task: Create a table EMPLOYEES with emp_id, emp_name, and join_date. Then, add a salary column using ALTER. Hint: Use VARCHAR2 for names and DATE for dates. 💡

---

💡 Interview Tip 👔

"Interviewers often ask: 'What is the difference between DROP and TRUNCATE?' Answer: DROP deletes the structure + data. TRUNCATE deletes ONLY the data but keeps the empty structure!"

---

💡 Pro Tip: "Good design is a lot like clear thinking made visual." - Edward Tufte

---

← Back: Subqueries | Next: Data Manipulation (DML) →