DBMS - CRUD Operations in SQL

To work with a relational database, one must understand how to create a database with SQL and then learn how to insert, read, update, and delete the data, because these are the basic operations for transactions. CRUD stands for Create, Read, Update, and Delete. The four fundamental operations for managing data in a database. If databases were like a library, the CRUD operations would be the ways we can add new books, search for a specific title, edit details of an existing book, or remove one from the shelf.

Rea this chapter to get a good understanding of all the four CRUD operations. We will take a sample table and a set of examples to explain the CRUD operations in detail.

Sample Tables and Data

To understand the CRUD operations, we must take examples. We will use the following sample table throughout this chapter −

Here is the Customers table −

And, this is the Products table −

With these tables in place, let us move ahead and explore the CRUD operations!

The Create Operation (Inserting Data into Tables)

Creating data that involves adding new rows to a table using the INSERT statement. This is like putting new files into a cabinet or adding new contacts to our phone contacts.

Example: Adding a New Customer

Use the following SQL command to insert a new customer row in the existing Customers table −

INSERT INTO Customers (customer_id, first_name, last_name, email, phone)
VALUES (4, 'Deb', 'Mallick', 'deb.mallick@example.com', '456-789-0123');

The Updated Customers Table would be as follows −

Example: Adding a New Product

Use the following command to insert a new product row into the Products table −

INSERT INTO Products (product_id, product_name, category, price, stock_quantity)
VALUES (104, 'Desk', 'Furniture', 250.00, 8);

The Updated Products Table would be as follows −

The Read Operation (Fetching Data from Tables)

After creating we must read the data from the database. For that the 'R' in CRUD comes. This is done using the SELECT statement. It is like opening a book and searching through it to find the information we need.

Example: Fetch All Customers

Fetch all the data from the Customers table −

SELECT * FROM Customers;

It will get you the following output −

Since we inserted a new record (Deb) in the Customers table, it is present there in the current table.

Example: Fetch Products with Stock Below 10

Now, let's use the WHERE clause to filter the data and fetch something specific −

SELECT product_name, stock_quantity
FROM Products
WHERE stock_quantity < 10;

This query will fetch all the products with their stock quantity less than 10 −

The Update Operation (Modifying the Existing Data)

Updating the data means changing the values in existing rows, for which we use the UPDATE command. Think of it as editing a contact’s phone number on our phone.

Example: Updating a Customer’s Email

You can use the following command to update a customer's email in the existing records −

UPDATE Customers
SET email = 'amrita.newemail@example.com'
WHERE customer_id = 1;

After executing the above query, the Updated Customers Table would look as follows −

Example: Restocking a Product

Use the following query to update the Products table after restocking a product −

UPDATE Products
SET stock_quantity = stock_quantity + 5
WHERE product_id = 103;

Now, the Updated Products Table would reflect the updated stock quantities −

The stock of "Office Chair" has now been updated from 5 to 10.

The Delete Operation (Removing Data from Tables)

Deleting the data means removing existing rows from a table, which is done using the DELETE statement. Consider erasing a contact from our phone. We are deleting the record but not the whole table.

Example: Deleting a Customer

The following query shows how you can use the DELETE command to remove a customer’s record from the Customers table −

DELETE FROM Customers
WHERE customer_id = 4;

With the above query, the Updated Customers Table would look as follows −

Notice that Deb’s record has been removed.

Example: Removing a Product from Inventory

Use the following query to remove a product from the inventory −

DELETE FROM Products
WHERE product_id = 104;

The Updated Products Table will now look as follows −

The "Desk" product is no longer in the inventory.

Why CRUD Operations Are Important

CRUD operations are the most basic operations for any database operator; they allow us to:

• Add new information (like customers or products)
• Retrieve specific details when needed
• Keep the data up to date
• Remove outdated or incorrect data

Without CRUD operations, we cannot update a database, in which case the data will be historical and of no use in real-time applications.

Conclusion

In this chapter, we explained in detail how to perform CRUD operations in SQL. We used examples to demonstrate how to apply the INSERT command for creating new records in a database and the SELECT command for reading and retrieving data. We also explained how to use the UPDATE command for modifying the existing records and the DELETE command for removing data from an existing table.

To conclude, CRUD operations provide us the power to handle complex data efficiently and make our databases relevant in real-world scenario.