Oracle Table Management 🏗️

Mentor's Note: Creating a table is like building a room. You decide how many windows (columns) it has and what kind of floor (data types) it uses. If you build it wrong, the whole house might fall down later! 💡

---

🌟 The Scenario: The Startup Scaffolding 🚀

You are building a database for a new delivery app in Surat.

• Goal: You need a table for "Orders".
• The Design: Every order needs an ID (Auto-incrementing), a Status, and a Total (Automatically calculated).

---

💻 1. CREATE TABLE & Identity Columns

Oracle 12c introduced the IDENTITY clause for easy auto-incrementing IDs.

CREATE TABLE orders (
    order_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    status     VARCHAR2(20) NOT NULL,
    order_date DATE DEFAULT SYSDATE
);

---

💻 2. Virtual Columns (Automatic Logic) ⚙️

A column that doesn't store data but calculates it on the fly!

-- Scenario: Total = Price * Qty
CREATE TABLE order_items (
    qty    NUMBER,
    price  NUMBER,
    total  NUMBER GENERATED ALWAYS AS (qty * price) VIRTUAL
);

---

💻 3. Modifying Structures (ALTER TABLE)

• ADD: New features.
• MODIFY: Change data types.
• DROP: Remove columns.
• RENAME: Fix typos.

ALTER TABLE orders ADD customer_name VARCHAR2(100);
ALTER TABLE orders MODIFY status VARCHAR2(50);
ALTER TABLE orders RENAME COLUMN status TO order_status;

---

💻 4. Clearing Data (DROP vs TRUNCATE)

• DROP: Deletes the structure + data. 💣
• TRUNCATE: Wipes data instantly, keeps the structure. ⚡

TRUNCATE TABLE old_logs;

---

🏗️ Architect's Note: DDL & Locks 🛡️

When you run an ALTER TABLE command, Oracle takes an Exclusive Lock on the table.

• The Architect's Secret: If you run ALTER on a table while users are using it, their apps will hang!
• Tip: Always perform DDL changes during a maintenance window or use Oracle's Online DDL features (available in Enterprise Edition).

---

📈 Learning Path

• ← Back: Subqueries
• Next: Oracle Data Types →