#15 CRUD Operations

SQL Crash Course #15

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

Hi everyone! Cornellius and Josep Ferrer from DataBites 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 Operations⚙️

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

📌 #15 – CRUD Operations: The article you are currently reading, where you will learn how to interact with the database.

📌 #16 - Database Modification:

Josep Ferrer

in

DataBites

will teach you how to perform modifications on an existing database

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

---

📝What are CRUD Operations?

CRUD operations—Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE)—are the foundation of database interactions.

While "Read" (SELECT) retrieves data, CRUD operations emphasize the three modification processes: INSERT, UPDATE, and DELETE. These commands allow you to add, modify, and remove data, making them essential for maintaining dynamic and accurate databases.

🛠When to Use Each Operation

We can summarize the CRUD operations into:

• INSERT: Adds new records to a table.

• UPDATE: Modifies existing records.

• DELETE: Removes records from a table.

These operations ensure that your database can follow the requirements and any changes to the dataset.

Here are examples of when you want to use each operation:

1. INSERT:

   • Add new newsletters, posts, or interactions.

   • Migrate data between tables (e.g., archiving old interactions).

2. UPDATE:

   • Correct typos in post names or interaction details.

   • Adjust interaction points or newsletter names.

3. DELETE:

   • Remove outdated interactions or test data.

   • Clean up posts with no engagement.

Let’s see how CRUD Operations in action!👇🏻

#1. INSERT

The operation is useful for adding new rows to a table. Generally the syntax follows the following structure:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Let’s use a few examples with the Newsletter data:

Single Row INSERT

-- Add a newsletter
INSERT INTO newsletters (id, name) 
VALUES ('1113C', 'Analytics Weekly');

-- Add a post to the new newsletter
INSERT INTO posts (id, newsletter_id, name, published_at)
VALUES ('1113C001', '1113C', 'Introduction to Python', '2024-06-01');

Bulk INSERT

-- Add multiple interactions for a post
INSERT INTO interactions (id, post_id, datetime, user, type_of_interaction, points) 
VALUES 
  ('INT9991', '1112A003', '2024-05-02 09:00:00', 'user5', 'like', 5),
  ('INT9992', '1112A003', '2024-05-02 10:30:00', 'user6', 'share', 8);

INSERT from Another Table

-- Archive interactions older than 2024 into a history table
CREATE TABLE interactions_archive AS
SELECT * FROM interactions
WHERE datetime < '2024-01-01';

🧠 Why INSERT: Efficiently populates tables by adding individual entries, batches, or filtered data from existing tables.

---

#2. UPDATE

The operation modifies existing data in a table. The standard query follows the syntax below:

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;  -- Always include a WHERE clause!

For example, you can use UPDATE to update the current point in the newsletter database. You can even chain it with the subquery.

UPDATE with Subquery

-- Increase points by 2 for interactions on "DataBites" newsletter posts
UPDATE interactions
SET points = points + 2
WHERE post_id IN (
  SELECT id FROM posts 
  WHERE newsletter_id = '1112A'  -- "DataBites" newsletter
);

🧠 Why UPDATE: Ensures the accuracy and relevance of data, such as dynamically adjusting metrics.

---

#3. DELETE

Just like the name implies, the operation removes rows from a table. The standard query follows the syntax below:

DELETE FROM table_name
WHERE condition;  -- Always filter rows to avoid accidental deletion!

For example, we can use the DELETE operation to remove posts from our newsletter that have received no engagement. You can still chain it with the subquery.

Delete with Subquery

-- Delete posts with zero interactions
DELETE FROM posts
WHERE id NOT IN (
  SELECT post_id FROM interactions
);

🧠 Why DELETE: Maintains database efficiency by eliminating obsolete data.

---

🔑 Best Practices for Safe CRUD Operations

1. Always Use WHERE in UPDATE/DELETE

Avoid accidental mass updates or deletions.

-- ❌ Risky: Deletes ALL interactions!
DELETE FROM interactions;

-- ✅ Safe: Targets specific records.
DELETE FROM interactions WHERE datetime < '2024-01-01';

2. Test with SELECT First

Preview changes before executing:

-- Test which posts will be deleted
SELECT * FROM posts 
WHERE id NOT IN (SELECT post_id FROM interactions);

3. Soft Delete vs. Hard Delete

Add an is_active column to mark records as inactive instead of deleting them:

ALTER TABLE posts ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
UPDATE posts SET is_active = FALSE WHERE id = '1112A003';  -- Mark as inactive

---

🏁 Conclusion

The Core of Data Management:

• INSERT populates your database with new content.

• UPDATE keeps your data accurate and meaningful.

• DELETE ensures your database remains clutter-free.

💡 Pro Tips:

• Back up regularly, especially before performing bulk operations.

• Indexes: Speed up WHERE clauses in UPDATE/DELETE (e.g., index post_id in interactions).

• Permissions: Restrict access to prevent accidental data loss.

By mastering these operations using the provided schema, you can confidently manage your database and ensure your database remains relevant 🚀

👉🏻 SQL playground with all CRUD Operations

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