🏗️ SQL Foundations: DDL and Schema Operations

The Story of Building Your Data Kingdom

Imagine you want to build a LEGO city. Before you can place any cars, people, or trees, you need to build the buildings first. You need to decide: How big? How many rooms? What shape?

SQL DDL (Data Definition Language) is exactly like being the architect of your data city. You design the buildings (tables), create neighborhoods (schemas), and decide where everything goes—before any actual data moves in!

🎭 The Main Characters

🏰 CREATE DATABASE – Building Your Kingdom

Think of a database like a big toy box. Before you can put your toys (data) inside, you need the box itself!

Simple Example

CREATE DATABASE my_school;

That’s it! You just created a toy box named “my_school”. Now you can put tables (toys) inside it.

What Happens?

graph TD
    A[You type CREATE DATABASE] --> B[SQL creates empty space]
    B --> C[Your database exists!]
    C --> D[Ready for tables]

Real-Life Example

Imagine starting a pet store business:

CREATE DATABASE happy_pets_store;

Now you have a place to store information about:

• Pets 🐕
• Customers 👨‍👩‍👧
• Sales 💰

💥 DROP DATABASE – Careful! This Destroys Everything!

DROP is like throwing your entire toy box in the trash—toys and all! Once you do this, everything is gone forever.

Simple Example

DROP DATABASE old_project;

⚠️ Big Warning!

Imagine your mom saying: “If you don’t clean up, I’m throwing away your LEGO city!”

That’s what DROP DATABASE does. No undo button!

Safe Practice

-- Always check before dropping
DROP DATABASE IF EXISTS old_project;

The IF EXISTS part means: “Only throw it away if it actually exists. Don’t show an error if it’s already gone.”

🏠 CREATE TABLE – Building Individual Rooms

A table is like a building inside your city. Each building has rooms (columns), and each room stores specific things.

The Basic Recipe

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(2)
);

What Does Each Part Mean?

Visual Explanation

graph TD
    A[CREATE TABLE students] --> B[Column: id]
    A --> C[Column: name]
    A --> D[Column: age]
    A --> E[Column: grade]
    B --> F[Each column = a room]

Real Example: Pet Store

CREATE TABLE pets (
    pet_id INT PRIMARY KEY,
    pet_name VARCHAR(50),
    species VARCHAR(30),
    price DECIMAL(10,2)
);

Now you can store:

• Pet #1: Buddy, Dog, $299.99
• Pet #2: Whiskers, Cat, $149.99

🗑️ DROP TABLE – Demolishing a Building

Just like DROP DATABASE but for one building (table) only.

Simple Example

DROP TABLE old_records;

Safe Way

DROP TABLE IF EXISTS old_records;

Remember This Story

Little Timmy had a LEGO police station. One day, he dropped it on the floor—crash! All the pieces scattered, and his brother accidentally threw them away.

That’s DROP TABLE. The building AND everything inside = gone.

🧹 TRUNCATE TABLE – Spring Cleaning

What if you want to keep the building but remove everyone inside? That’s TRUNCATE!

Simple Example

TRUNCATE TABLE students;

DROP vs TRUNCATE

Visual Story

graph TD
    A[TRUNCATE TABLE students] --> B[All rows deleted]
    B --> C[Table structure stays]
    C --> D[Ready for new data!]

When to Use?

Imagine your classroom whiteboard:

• DROP TABLE = Throw away the whiteboard
• TRUNCATE TABLE = Erase everything, keep the whiteboard

🔧 ALTER TABLE – Renovating Your Building

Buildings change over time! Maybe you need:

• A new room (add column)
• Remove an old room (drop column)
• Rename a room (rename column)
• Change a room’s purpose (modify column)

Adding a Column

ALTER TABLE students
ADD email VARCHAR(100);

Now every student record has space for an email!

Removing a Column

ALTER TABLE students
DROP COLUMN old_phone;

That room is gone!

Changing a Column

ALTER TABLE students
MODIFY COLUMN age SMALLINT;

Changed the room from storing big numbers to small ones.

Renaming a Column

ALTER TABLE students
RENAME COLUMN grade TO class_level;

Same room, new name on the door!

All ALTER Operations

graph TD
    A[ALTER TABLE] --> B[ADD column]
    A --> C[DROP column]
    A --> D[MODIFY column]
    A --> E[RENAME column]
    A --> F[ADD constraint]
    A --> G[DROP constraint]

📁 Schema Fundamentals – Organizing Your City

A schema is like a neighborhood in your city. You might have:

• Residential area (user data)
• Business district (sales data)
• Parks (temporary data)

Why Use Schemas?

Imagine having 1000 LEGO buildings all mixed together. Chaos! But if you organize them:

• Red baseplate = Houses
• Blue baseplate = Shops
• Green baseplate = Schools

That’s what schemas do for databases!

Simple Example

CREATE SCHEMA sales;
CREATE SCHEMA inventory;
CREATE SCHEMA customers;

Now you have three organized neighborhoods!

Creating Tables Inside Schemas

CREATE TABLE sales.orders (
    order_id INT PRIMARY KEY,
    amount DECIMAL(10,2)
);

CREATE TABLE inventory.products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

Visual Organization

graph TD
    A[Database: my_store] --> B[Schema: sales]
    A --> C[Schema: inventory]
    A --> D[Schema: customers]
    B --> E[Table: orders]
    B --> F[Table: returns]
    C --> G[Table: products]
    C --> H[Table: stock]
    D --> I[Table: profiles]
    D --> J[Table: addresses]

🏗️ Managing Schemas – Full Control

Creating a Schema

CREATE SCHEMA hr_department;

Creating Schema with Authorization

CREATE SCHEMA marketing
AUTHORIZATION marketing_manager;

This means: Create the marketing neighborhood, and put “marketing_manager” in charge.

Dropping a Schema

DROP SCHEMA old_project;

Warning: Some databases won’t let you drop a schema if it has tables inside!

Force Drop (Empty First)

DROP SCHEMA old_project CASCADE;

CASCADE means: “Delete everything inside too!”

Changing Current Schema

SET search_path TO sales;
-- Now all your commands work in the 'sales' neighborhood

🎯 Putting It All Together – A Complete Story

Let’s build a school database from scratch:

Step 1: Create the Database

CREATE DATABASE riverside_school;

Step 2: Create Schemas

CREATE SCHEMA academics;
CREATE SCHEMA administration;

Step 3: Create Tables

CREATE TABLE academics.students (
    student_id INT PRIMARY KEY,
    full_name VARCHAR(100),
    grade_level INT,
    enrollment_date DATE
);

CREATE TABLE academics.courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    teacher_name VARCHAR(100)
);

CREATE TABLE administration.staff (
    staff_id INT PRIMARY KEY,
    name VARCHAR(100),
    role VARCHAR(50),
    salary DECIMAL(10,2)
);

Step 4: Modify as Needed

-- Oops! We forgot email for students
ALTER TABLE academics.students
ADD email VARCHAR(100);

-- Change salary to bigger number storage
ALTER TABLE administration.staff
MODIFY COLUMN salary DECIMAL(12,2);

The Journey Map

graph TD
    A[CREATE DATABASE] --> B[CREATE SCHEMA]
    B --> C[CREATE TABLE]
    C --> D[ALTER TABLE as needed]
    D --> E[Your data city is ready!]

🌟 Key Takeaways

1. DATABASE = Your entire LEGO city
2. SCHEMA = Neighborhoods in your city
3. TABLE = Individual buildings
4. CREATE = Build something new
5. DROP = Destroy completely (careful!)
6. TRUNCATE = Empty but keep structure
7. ALTER = Renovate and modify

💡 Remember This!

“Before you can store data, you must build the house to keep it in.”

DDL commands are your architect tools. You design first, then fill with data later!

🚀 You’re Ready!

You now understand how to:

• ✅ Create and drop databases
• ✅ Build and remove tables
• ✅ Clean tables with TRUNCATE
• ✅ Modify tables with ALTER
• ✅ Organize with schemas
• ✅ Manage your entire data kingdom!

Next step: Fill your buildings with actual data using DML (Data Manipulation Language)! 🎉