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 −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

And, this is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

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 −

INSERTINTO Customers (customer_id, first_name, last_name, email, phone)VALUES(4,'Deb','Mallick','[email protected]','456-789-0123');

The Updated Customers Table would be as follows −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012
4	Deb	Mallick	[email protected]	456-789-0123

Example: Adding a New Product

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

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

The Updated Products Table would be as follows −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5
104	Desk	Furniture	250.00	8

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 −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012
4	Deb	Mallick	[email protected]	456-789-0123

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 −

product_name	stock_quantity
Office Chair	5

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 ='[email protected]'WHERE customer_id =1;

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

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012
4	Deb	Mallick	[email protected]	456-789-0123

Example: Restocking a Product

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

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

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

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	10
104	Desk	Furniture	250.00	8

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 −

DELETEFROM Customers
WHERE customer_id =4;

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

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

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 −

DELETEFROM Products
WHERE product_id =104;

The Updated Products Table will now look as follows −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	10

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.