SQL CRUD Operation

In any computer language, CRUD operation is a foundation. CRUD operation rules are applied in databases as well. These operations are the basic operations used to perform with any database system. CRUD operation is the backbone for communicating with the databases.

CRUD is an abbreviation for Create, Read, Update, and Delete.

Let's understand the CRUD operation in SQL with examples.

C - CREATE

R - READ

U – UPDATE

D – DELETE

1. CREATE operation

In CRUD operation, 'C' refers to CREATE, which means adding or inserting in the SQL table. In this operation, we will create a table using CREATE TABLE command, and after that, we will insert records in the table using INSERT INTO commands in the SQL.

Syntax for create table is as follows:

CREATE TABLE TableName (Column_Name1 DataType(Size), Column_Name2 DataType(Size), Column_Name3 DataType(Size));

Where:

• TableName - Name of the table that we will assign to the table.
• Column_Name – It is the field under which we will store records of the table.
• DataType – Data Type is assigned to each column in the table. Data Type decides which type of data will be stored in the column like numeric, character, and date type data.

Syntax for Insertion:

INSERT INTO TableName VALUES (Value1, Value2, Value3);

To create a table in the SQL, first, we need to create a database or select a database if you have an existing database. Since we don't have any database, first, we will create a database using CREATE DATABASE command:

CREATE DATABASE CRUD;  

CRUD is the name of our database.   

If you want to check whether the database is created or not, use the SHOW DATABASES command to check the entire database in the system.

SHOW DATABASES;

A CRUD database is successfully created. Now we will utilize USE and database name to select the database under which we will create a table and perform the operation.

USE CRUD;

We will create 4 Tables using the CREATE TABLE command in the CRUD database.

Table Names:

Table 1: Customer

Table 2: Mobile_Plan.

Table 3: Data_Plan

Table 4: Talktime_Plan

We will create each table one by one using the CREATE TABLE command.

Table Name: Talktime_Plan

CREATE TABLE Talktime_Plan (TalkTime_Id int primary key, Talktime_Limit int, Talktime_Price int); 

We will ensure the table we created as per the column name, proper data type, and size.

Use the below query:

DESC Talktime_Plan;

Output:

Field	Type	Null	Key	Default	Extra
TalkTime_Id	int(11)	NO	PRI	NULL
Talktime_Limit	int(11)	YES		NULL
Talktime_Price	int(11)	YES		NULL

The above output itself says that the table is created successfully.

Table Name: Data_Plan

CREATE TABLE Data_Plan (Data_Id int primary key, Data_Limit int, Data_Price int); 

We will ensure that the table we created is per the given column name, proper data type, and size. Use the below query:

DESC Data_Plan;

Output:

Field	Type	Null	Key	Default	Extra
Data_Id	int(11)	NO	PRI	NULL
Data_Limit	int(11)	YES		NULL
Data_Price	int(11)	YES		NULL

The above output itself says that the table is created successfully.

Data_Limit column values will be in GB.

Table Name: Mobile_Plan

CREATE TABLE Mobile_Plan (Package_Id int primary key, Data_Id int, Talktime_Id int, FOREIGN KEY(Data_Id) references Data_Plan(Data_Id), FOREIGN KEY(Talktime_Id) references Talktime_Plan(Talktime_Id)); 

We will ensure that the table we created is per the given column name, proper data type, and size. Use the below query:

DESC Data_Plan;

Output:

Field	Type	Null	Key	Default	Extra
Package_Id	int(11)	NO	PRI	NULL
Data_Id	int(11)	YES	MUL	NULL
Talktime_Id	int(11)	YES	MUL	NULL

The above output itself says that the table is created successfully.

Data_id and Talktime_id are the Foreign Key in the Mobile_Plan table.

Table Name: Customer

CREATE TABLE Customer (Customer_Id int primary key, Customer_Name varchar(40), Package_Id int, FOREIGN KEY(Package_Id) references Mobile_Plan(Package_Id), Phone_Number int); 

We will ensure that the table we created is per the given column name, proper data type, and size. Use the below query:

DESC Data_Plan;

Output:

Field	Type	Null	Key	Default	Extra
Customer_Id	int(11)	NO	PRI	NULL
Customer_Name	varchar(40)	YES		NULL
Package_Id	int(11)	YES	MUL	NULL
Phone_Number	varchar(10)	YES		NULL

The above output itself says that the table is created successfully.

Package_id is the Foreign Key in the Customer table.

Now, we will insert records into the created tables.

1 Table Name: Talktime_Plan

We will use the INSERT INTO command to insert records in the Talktime_Plan table.

INSERT INTO Talktime_Plan Values (1, 100, 150), (2, 50, 75), (3, 60, 90); 

2 Table Name: Data_Plan

We will use the INSERT INTO command to insert records in the Data_Plan table.

INSERT INTO Data_Plan Values (1, 2, 30), (2, 5, 70), (3, 4, 55); 

3 Table Name: Mobile_Plan

We will use the INSERT INTO command to insert the Mobile _Plan table records.

INSERT INTO Mobile_Plan Values (1, 2, 1), (2, 1, 2), (3, 1, 3); 

4 Table Name: Customer

We will use INSERT INTO command to insert records in the Customer table.

INSERT INTO Customer Values (1001, ‘Akash’, 2,’ 9844567841’), (1002,’Bhushan’, 1,’ 7802441245’), (1003, ‘Praveen’, 3,’ 7058804631’); 

2. READ Operation

In CRUD operation, 'R' refers to READ, retrieving the SQL table records. The SELECT statement is used to retrieve records from the SQL table. We can fetch the entire records from the table or fetch records that are only necessary from the table. We use the ASTERISK (*) symbol in a SELECT statement to fetch entire records from the table. We use the WHERE clause in a SELECT query for necessary records from the table.

Syntax to fetch entire records is as follows:

SELECT * FROM Table_Name;

Syntax using WHERE clause:

SELECT * FROM Table_Name WHERE condition;

Let's see examples using the SELECT statement.

Example 1: Write a query to fetch the entire records from the Customer Table.

SELECT * FROM Customer; 

We used the ASTERISK (*) symbol in the SELECT statement to fetch the entire records from the table.

The output of the above query is as follows:  sql-crud-operation

All records from Customer Table are fetched.

Example 2: Write a query to fetch only those records from the Talktime_Plan where Talktime_Price is greater than 80.

SELECT * FROM Talktime_Plan WHERE Talktime_Price > 80;

In the above query, we retrieve only those records from Talktime_Plan Table where Talktime_Price is greater than 80.

 The output of the above is as follows:

There are only two records in the Talktime_Plan whose Talktime_Price is greater than 80.

Example 3: Write a query to browse all the plans, i.e., all the records from the Mobile_Plan, Data_Plan, and Talktime_Plan.

SELECT Mobile_Plan.Package_Id, Mobile_Plan.Data_Id, Mobile_Plan.Talktime_Id, Data_Plan.Data_Limit, Data_Plan.Data_Price, Talktime_Plan.Talktime_Limit, Talktime_Plan.Talktime_Price  from Mobile_Plan INNER JOIN Data_Plan on Mobile_Plan.Data_Id = Data_Plan.Data_Id INNER JOIN Talktime_Plan on Mobile_Plan.Talktime_Id = Talktime_Plan.Talktime_Id ;

In the above query, we join three tables to fetch all the records from Mobile_Plan Table, Data_Plan Table, and Talktime_Plan Table.

The output of the above query is as given below:

Example 4: Write a query to fetch the entire records from the Mobile_Plan Table.

SELECT * FROM Mobile_Plan;  

We used the ASTERISK (*) symbol in the SELECT statement to fetch the entire records from the table.

The output of the above query is as follows:

All records from Mobile_Plan Table are fetched.

Example 5: Write a query to fetch the entire records from the Data_Plan Table. The following output:

SELECT * FROM Data_Plan; 

We used the ASTERISK (*) symbol in the SELECT statement to fetch the entire records from the table.

The output of the above query is as follows:

All records from Data_Plan Table are fetched.

Example 6: Write a query to fetch the entire records from the Talktime_Plan Table. The following output:

SELECT * FROM Talktime_Plan;  

We used the ASTERISK (*) symbol in the SELECT statement to fetch the entire records from the table.

The output of the above query is as follows:

All records from Talktime_Plan Table are fetched.

3. UPDATE Operation

In CRUD operation, 'U' refers to UPDATE, which means to modify the records available in the SQL tables. To modify available data in the tables, we will use the UPDATE statement. We can update multiple rows at a time or update a single row using the WHERE clause condition.

Syntax:

UPDATE TableName SET Column_Name = Value WHERE condition;

Let’s see examples using UPDATE statement.

Example 1: Write a query to update a Data_ id as 3 whose Package_Id is 2.

UPDATE Mobile_Plan SET Data_Id = 3 WHERE Package_Id = 2;

In the UPDATE query, we have used the SET keyword to modify a Data_id as 3. We have used the WHERE clause to UPDATE Data_id as 3, whose Package_Id is 2.

We will ensure that the query we executed to modify the Data_id as 3 with the Package_Id 2 is modified successfully. We will execute the SELECT statement:

SELECT * FROM Mobile_Plan;

Example 2: Write a query to update a Customer Package_Id as 3 whose Customer_Id is 1001.

UPDATE Customer SET Package_Id = 3 WHERE Customer_Id = 1001;

In the UPDATE query, we have used the SET keyword to modify a Package_id as 3. We have used the WHERE clause to UPDATE Package_id as 3, whose Customer_Id is 1001.

We will ensure that the query we executed to modify the Data_id as 3 with the Package_Id 2 is modified successfully. We will execute the SELECT statement:

SELECT * FROM Customer;

4. DELETE Operation

In CRUD operation, 'D' refers to DELETE, which means to delete the data from the SQL tables. To delete records from the SQL table, we will use the DELETE statement. If we want to delete specific records from the table, we will use the WHERE clause in the query.

Syntax:

DELETE FROM Table_Name;

Syntax using WHERE clause:

DELETE FROM Table_Name WHERE Conditions;

Examples using an UPDATE statement are given below:

Example 1: Write a query to delete customer records from the Customer table whose Customer_Id = 1003.

DELETE FROM Customer WHERE Customer_Id = 1003;

We want to delete the record of a Customer whose Customer_Id is 1003. We used the DELETE query to delete the record using WHERE clause-specific conditions.

We will execute a SELECT query to ensure that the record we delete from the Customer table whose Customer_Id is 1003 is deleted successfully.

Example 2: Write a query to delete entire customer records from the Customer table.

DELETE FROM Customer;

To delete entire records from Customer Table, we will execute a DELETE statement.

We will execute the SELECT query to ensure that the record we delete from the Customer table is deleted successfully.

The Customer table is empty, which means records are deleted successfully.