CRUD Operations

What is CRUD?
In the context of database systems, CRUD refers to the ability to Create, Read, Update, and Delete data or a record. Fundamentally, these four operations allow us to manage data stored in a database. As such, these operations are implemented by databases.

How can you perform CRUD operations using SQL?

Let’s assume that you want to start a small business that offers products to customers.
You want to manage and maintain the records of your products at any given time.
To do this,
You need a database to store data about the products.
You should be able to add new products to this database.
You should be able to view existing products in this database when needed.
You should be able to update an existing product in this database.
You should be able to remove a product you no longer sell in this database.

Given the above scenario, let’s create a database using MySQL.
The name of this database will be called MyShop.

SQL Statement
CREATE DATABASE MyShop;

This database will have a table that allows us to store the product data. This table will be called Products.
This table will have columns (data fields) that will help us define each product. To keep it simple, the columns or data fields will be product ID, product name, and product price.

SQL Statement
CREATE TABLE Products (productID int, productName varchar(200), productPrice Double);

Before proceeding, it is important to specify a data field or table column that will be used to uniquely identify each product in our database. The data field is referred to as the primary KEY.

SQL Statement
ALTER TABLE Products ADD PRIMARY KEY(productID);

Now that we have a database and a table to store product information. Let’s add 3 products. This can be done using the INSERT keyword.

SQL Statement

INSERT INTO Products (productID, productName, productPrice) VALUES (1001,'ABC Smartphone', 299.99);
INSERT INTO Products (productID, productName, productPrice) VALUES (1034,'XYZ Laptop', 450.00);
INSERT INTO Products (productID, productName, productPrice) VALUES (2004,'USB drive', 19.50);


Let’s view all the products that we have in the database. This can be done using the SELECT keyword.

SQL Query
SELECT * FROM Products;

Let’s return only the product that matches a given ID. This can be done using the SELECT keyword with a WHERE clause for filtering the records.

SQL Query
SELECT * FROM Products WHERE productID=1034;

Assuming we want to update the price of a specific product (XYZ Laptop), this can be done using the UPDATE keyword.

SQL Statement
UPDATE Products SET productPrice=399.99 WHERE productID=1034;

Finally, to remove or delete a product that we don’t need anymore (for example the USB drive), we can use the DELETE keyword with the WHERE clause.

SQL Statement
DELETE FROM Products WHERE productID=2004;

In summary, we have seen how to create a simple database using MySQL, access this database using SQL, and perform a CRUD operation.
Create = INSERT
Read = SELECT
Update = UPDATE
Delete = DELETE