# 🛠️ Mastering SQL DDL Commands: CREATE, ALTER, DROP, and More

When working with databases, the **Data Definition Language (DDL)** is your toolkit for creating and modifying database structures like tables, columns, and constraints. Whether you're building a database from scratch or evolving your schema, these commands are essential.

Let’s explore DDL commands with clear syntax examples and practical use cases.

---

## 🔧 1. CREATE – Build New Tables and Structures

### 🧱 Create a Basic Table

```sql
sqlCopyEditCREATE TABLE Student (
    S_ID NUMBER,
    Name CHAR(20),
    Marks NUMBER
);
```

### 🔑 Create a Table with Primary Key

```sql
sqlCopyEditCREATE TABLE Student (
    S_ID NUMBER PRIMARY KEY,
    Name CHAR(20),
    Marks NUMBER
);
```

### 🔗 Create a Table with Foreign Key

```sql
sqlCopyEditCREATE TABLE Orders (
    OrderID NUMBER PRIMARY KEY,
    StudentID NUMBER,
    FOREIGN KEY (StudentID) REFERENCES Student(S_ID)
);
```

### 📋 Create a Table from Another Table

This copies structure and data:

```sql
sqlCopyEditCREATE TABLE TopStudents AS
SELECT * FROM Student
WHERE Marks > 75;
```

---

## 🛠️ 2. ALTER – Modify Existing Tables

You can add or change columns, rename columns, or manage constraints.

### ➕ Add a Column

```sql
sqlCopyEditALTER TABLE Student
ADD Age NUMBER;
```

### 📝 Modify Column Data Type

```sql
sqlCopyEditALTER TABLE Student
MODIFY Name VARCHAR(30);
```

### ✏️ Rename a Column

```sql
sqlCopyEditALTER TABLE Student
RENAME COLUMN Name TO FirstName;
```

### ❌ Drop a Column

```sql
sqlCopyEditALTER TABLE Student
DROP COLUMN Age;
```

---

## 🔐 Adding Constraints AFTER Table Creation

### 🔑 Add Primary Key

```sql
sqlCopyEditALTER TABLE Student
ADD CONSTRAINT pk_student PRIMARY KEY (S_ID);
```

### 🔗 Add Foreign Key

```sql
sqlCopyEditALTER TABLE Orders
ADD CONSTRAINT fk_orders_student
FOREIGN KEY (StudentID) REFERENCES Student(S_ID);
```

### 📏 Add Unique Constraint

```sql
sqlCopyEditALTER TABLE Student
ADD CONSTRAINT unique_name UNIQUE (Name);
```

---

## 🔓 Dropping Constraints

```sql
sqlCopyEditALTER TABLE Student
DROP CONSTRAINT pk_student;
```

---

## 🧹 3. DROP – Permanently Delete Tables or Views

Deletes the table and all its data and structure.

```sql
sqlCopyEditDROP TABLE Student;
```

If the table has dependent constraints:

```sql
sqlCopyEditDROP TABLE Student CASCADE CONSTRAINTS;
```

---

## 🧽 4. TRUNCATE – Wipe All Rows (Keep Structure)

```sql
sqlCopyEditTRUNCATE TABLE Student;
```

⚠️ This is **faster than DELETE** but **cannot be rolled back**.

---

## 🔄 5. RENAME – Rename Tables

```sql
sqlCopyEditRENAME Student TO StudentRecords;
```

---

## 📊 Quick Reference Summary

| DDL Command | Purpose |
| --- | --- |
| `CREATE` | Create a table or object |
| `ALTER` | Modify structure (columns, constraints) |
| `DROP` | Permanently delete a table |
| `TRUNCATE` | Delete all rows, keep structure |
| `RENAME` | Change the name of a table |

---

## 🎓 Final Tips

* Always **name your constraints** for easier management.
    
* Use `ALTER` to evolve your schema instead of dropping and recreating tables.
    
* For development, test your DDL changes in a safe environment before applying them to production.