#16 Database Modifications

SQL Crash Course #16

All the course material is stored in the SQL Crash Course repository.

Hi everyone! Josep and Cornellius Yudha Wijaya from Non-Brand Data here 👋🏻

As promised, today we are publishing the next two issues of our SQL Crash Course – From Zero to Hero! 🚀

I am sure you are here to continue our SQL Crash Course Journey!📚

If this is your first time or you’ve forgotten what we’ll cover, we will examine seven key SQL topics, each divided into multiple posts across Non-Brand Data and DataBites.

📚 Previously in Advanced SQL…

Remember last time we already saw:

📌 #13 – Recursion
📌 #14 – Views

Today, we will explore an exciting new topic in our SQL learning:

Database Modifications⚙️

In today’s topic, we have two new brand issues:

📌 #15 – CRUD Operations:

Cornellius Yudha Wijaya

in

Non-Brand Data

will teach you how to interact with the database.

📌 #16 - Database Modification: The article you are currently reading, where you will learn how to perform modifications on an existing database

So don’t miss out—let’s keep the SQL momentum going!

🧱 What are Database Modifications?

While CRUD operations modify data, database modification statements change the structure of the database. These include:

• CREATE: Make new tables or structures.

• ALTER: Modify existing tables (e.g., add or rename columns).

• DROP: Remove tables or columns entirely.

These commands give you control over your database’s blueprint.

🛠 When to Use Each Operation

CREATE
To define the shape of new data containers (tables, indexes, etc.).

ALTER
To adapt the structure as your project grows (e.g., adding a new column to track performance).

DROP
To safely remove obsolete tables or columns no longer in use.

Let’s get hands-on with the newsletter schema!👇🏻

#1. CREATE

Use this to define tables before inserting any data.

-- Create the base structure for newsletters, posts, and interactions
CREATE TABLE newsletters (
    id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE posts (
    id VARCHAR(10) PRIMARY KEY,
    newsletter_id VARCHAR(10),
    name VARCHAR(255),
    published_at DATE,
    FOREIGN KEY (newsletter_id) REFERENCES newsletters(id)
);

CREATE TABLE interactions (
    id VARCHAR(10) PRIMARY KEY,
    post_id VARCHAR(10),
    datetime DATETIME,
    user VARCHAR(50),
    type_of_interaction VARCHAR(50),
    points INT,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

🧠 Why CREATE: It’s the foundation of every database project—design tables before you can use them.

#2. ALTER

Change an existing table’s structure—add, rename, or remove columns.

-- Add a new column to store whether a post is active
ALTER TABLE posts ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

-- Rename the "name" column in newsletters to "newsletter_name"
ALTER TABLE newsletters CHANGE COLUMN name newsletter_name VARCHAR(255);

-- Change the data type of points to FLOAT (for fractional scoring)
ALTER TABLE interactions MODIFY COLUMN points FLOAT;

🧠 Why ALTER: Databases evolve—ALTER keeps your schema flexible and future-proof.

#3. DROP

Remove entire tables or specific columns. Always double-check before running DROP!

-- Remove the "is_active" column from posts
ALTER TABLE posts DROP COLUMN is_active;

-- Delete a table you no longer use
DROP TABLE interactions;

🧠 Why DROP: Clean up your database when features are deprecated or data is archived elsewhere.

🔑 Best Practices for Safe Database Modifications

1. Use IF EXISTS or IF NOT EXISTS Prevent errors if a table or column already exists (or doesn't):

CREATE TABLE IF NOT EXISTS logs (...);
DROP TABLE IF EXISTS old_table;

2. Backup Before Major Changes
   Create a backup of the current schema and data before any irreversible DROP or complex ALTER.

3. Minimize Downtime in Production
   Time your structural changes during off-hours or use migrations to ensure continuity.

🏁 Conclusions – Building Strong Foundations

• CREATE designs your data model from scratch

• ALTER adapts your database as you grow

• DROP lets you prune outdated structures

💡 Pro Tips:

• Keep a version-controlled schema with tools like dbt or Alembic.

• Use test databases to try changes before applying them to production.

• Document every change for collaboration and traceability.

Mastering database modification gives you the power to scale, adapt, and optimize your data architecture 🚀

👉🏻 SQL playground with all Database Modification examples

How to Get Started 🚀

Over the coming weeks, we’ll guide you through:

✅ SQL Fundamentals
✅ Intermediate SQL
✅ Advanced SQL
✅ Database Operations
✅ Writing Efficient Queries

Once you grasp the basics, practice is key! Start working on real-world projects and solving hands-on data problems.

What’s Next? ➡️

This is the first of many posts about the upcoming SQL Courses. It will only explain what SQL is in its crude form.

To get the full experience and fully immersed in the learning:

👉 Subscribe to Databites.tech (By Josep)

👉 Subscribe to Non-Brand Data (By Cornellius)

👉 Check out the SQL Crash Course GitHub repo

👉 Share with your friend and whoever needs it!

🗓️ Every Thursday, you will have two new issues in your inbox!

Let’s dive in and make SQL less scary, more fun, and way more useful! 🚀

Josep & Cornellius