As we know, CRUD operations act as the foundation of any computer programming language or technology. So before taking a deeper dive into any programming language or technology, one must be proficient in working on its CRUD operations. This same rule applies to databases as well.
Let us start with the understanding of CRUD operations in SQL with the help of examples. We will be writing all the queries in the supporting examples using the MySQL database.
1. Create:
In CRUD operations, ‘C’ is an acronym for create, which means to add or insert data into the SQL table. So, firstly we will create a table using CREATE command and then we will use the INSERT INTO command to insert rows in the created table.
CREATE TABLE Table_Name (ColumnName1 Datatype, ColumnName2 Datatype,..., ColumnNameN Datatype); where,
- Table_Name is the name that we want to assign to the table.
- Column_Name is the attributes under which we want to store data of the table.
- Datatype is assigned to each column. Datatype decides the type of data that will be stored in the respective column.
Syntax for insertion of data in table:
INSERT INTO Table_Name (ColumnName1,...., ColumnNameN) VALUES (Value 1,....,Value N),....., (Value 1,....,Value N); Prior to the creation of a table in SQL, we need to create a database or select an existing database. Since we already had a database, we will select the database with the USE command.
mysql> USE dbe; 
Now, we will write a query to create a table named employee in the database named dbe.
mysql> CREATE TABLE employee(ID INT PRIMARY KEY, First_Name VARCHAR(20), Last_Name VARCHAR(20), Salary INT, Email_Id VARCHAR(40)); 
To ensure that the table is created as per the column names, data types and sizes which we have assigned during table creation, we will execute the following query:
mysql> DESC employee; You will get the following output:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | int(11) | NO | PRI | NULL | |
| First_Name | varchar(20) | YES | NULL | ||
| Last_Name | varchar(20) | YES | NULL | ||
| Salary | int(11) | YES | NULL | ||
| Email_Id | varchar(40) | YES | NULL |
The above results verify that the table is created successfully as per the requirements.
We will execute the following query to insert multiple records in the employee table:
INSERT INTO employee(ID, First_Name, Last_Name, Salary, Email_Id) VALUES(1, "Neeta", "Korade", 59000, "[email protected]"), (2, "Sushma", "Singh", 62000, "[email protected]"), (3, "Kavita", "Rathod", 27000, "[email protected]"), (4, "Mrunalini", "Deshmukh", 88000, "[email protected]"), (5, "Swati", "Patel", 34000, "[email protected]"), (6, "Laxmi", "Kadam", 44000, "[email protected]"), (7, "Lalita", "Shah", 66000, "[email protected]"), (8, "Savita", "Kulkarni", 31000, "[email protected]"), (9, "Shravani", "Jaiswal", 38000, "[email protected]"), (10, "Shweta", "Wagh", 20000, "[email protected]"); 
2. Read:
In CRUD operations, ‘R’ is an acronym for read, which means retrieving or fetching the data from the SQL table. So, we will use the SELECT command to fetch the inserted records from the SQL table. We can retrieve all the records from a table using an asterisk (*) in a SELECT query. There is also an option of retrieving only those records which satisfy a particular condition by using the WHERE clause in a SELECT query.
Syntax to fetch all the records:
SELECT *FROM TableName; Syntax to fetch records according to the condition:
SELECT *FROM TableName WHERE CONDITION; Example 1:
Write a query to fetch all the records stored in the employee table.
Query:
mysql> SELECT *FROM employee; Here, an asterisk is used in a SELECT query. This means all the column values for every record will be retrieved.
You will get the following output after executing the above query:
| ID | First_Name | Last_Name | Salary | Email_Id |
|---|---|---|---|---|
| 1 | Neeta | Korade | 59000 | [email protected] |
| 2 | Sushma | Singh | 62000 | [email protected] |
| 3 | Kavita | Rathod | 27000 | [email protected] |
| 4 | Mrunalini | Deshmukh | 88000 | [email protected] |
| 5 | Swati | Patel | 34000 | [email protected] |
| 6 | Laxmi | Kadam | 44000 | [email protected] |
| 7 | Lalita | Shah | 66000 | [email protected] |
| 8 | Savita | Kulkarni | 31000 | [email protected] |
| 9 | Shravani | Jaiswal | 38000 | [email protected] |
| 10 | Shweta | Wagh | 20000 | [email protected] |
All the records are successfully retrieved from the employee table.
Example 2:
Write a query to fetch only those records from the employee table whose salary is above 35000.
Query:
mysql> SELECT *FROM employee WHERE Salary > 35000; Here, an asterisk is used in a SELECT query. This means all the column values for every record will be retrieved. We have applied the WHERE clause on Salary, which means the records will be filtered based on salary.
You will get the output as follows:
| ID | First_Name | Last_Name | Salary | Email_Id |
|---|---|---|---|---|
| 1 | Neeta | Korade | 59000 | [email protected] |
| 2 | Sushma | Rathod | 62000 | [email protected] |
| 4 | Mrunalini | Deshmukh | 88000 | [email protected] |
| 6 | Laxmi | Kadam | 44000 | [email protected] |
| 7 | Lalita | Shah | 66000 | [email protected] |
| 9 | Shravani | Jaiswal | 38000 | [email protected] |
There are six records in the employee table whose salary is above 35000.
3. Update:
In CRUD operations, ‘U’ is an acronym for the update, which means making updates to the records present in the SQL tables. So, we will use the UPDATE command to make changes in the data present in tables.
Syntax:
UPDATE Table_Name SET ColumnName = Value WHERE CONDITION; Example 1:
Write a query to update an employee’s last name as ‘Bose’, whose employee id is 6.
Query:
mysql> UPDATE employee SET Last_Name = "Bose" WHERE ID = 6; 
Here in the SELECT query, we have used the SET keyword to update an employee’s last name as ‘Bose’. We want to update an employee’s last name only for the employee with id 6, so we have specified this condition using the WHERE clause.
To ensure that an employee’s last name with employee id 6 is updated successfully, we will execute the SELECT query.
mysql> SELECT *FROM employee; | ID | First_Name | Last_Name | Salary | Email_Id |
|---|---|---|---|---|
| 1 | Neeta | Korade | 59000 | [email protected] |
| 2 | Sushma | Singh | 62000 | [email protected] |
| 3 | Kavita | Rathod | 27000 | [email protected] |
| 4 | Mrunalini | Deshmukh | 88000 | [email protected] |
| 5 | Swati | Patel | 34000 | [email protected] |
| 6 | Laxmi | Bose | 44000 | [email protected] |
| 7 | Lalita | Shah | 66000 | [email protected] |
| 8 | Savita | Kulkarni | 31000 | [email protected] |
| 9 | Shravani | Jaiswal | 38000 | [email protected] |
| 10 | Shweta | Wagh | 20000 | [email protected] |
The results above verify that an employee’s last name with employee id 6 is now changed to ‘Bose’.
Example 2:
Write a query to update the salary and email id of an employee as ‘35000’ and ‘[email protected]’, respectively, whose employee id is 10.
Query:
mysql> UPDATE employee SET Salary = "35000", Email_Id= "[email protected]" WHERE ID = 10; 
Here in the UPDATE query, we have used the SET keyword to update an employee’s salary as ‘35000’ and the email id as ‘[email protected]’. We want to update the salary and email id of an employee only for the employee with id 10, so we have specified this condition using the WHERE clause.
To ensure that the salary and email id of an employee with employee id 10 is updated successfully, we will execute the SELECT query.
mysql> SELECT *FROM employee; | ID | First_Name | Last_Name | Salary | Email_Id |
|---|---|---|---|---|
| 1 | Neeta | Korade | 59000 | [email protected] |
| 2 | Sushma | Singh | 62000 | [email protected] |
| 3 | Kavita | Rathod | 27000 | [email protected] |
| 4 | Mrunalini | Deshmukh | 88000 | [email protected] |
| 5 | Swati | Patel | 34000 | [email protected] |
| 6 | Laxmi | Bose | 44000 | [email protected] |
| 7 | Lalita | Shah | 66000 | [email protected] |
| 8 | Savita | Kulkarni | 31000 | [email protected] |
| 9 | Shravani | Jaiswal | 38000 | [email protected] |
| 10 | Shweta | Wagh | 35000 | [email protected] |
The results above verify that the salary and email id of an employee with employee id 10 is now changed to ‘35000’ and ‘[email protected]’, respectively.
4. Delete:
In CRUD operations, ‘D’ is an acronym for delete, which means removing or deleting the records from the SQL tables. We can delete all the rows from the SQL tables using the DELETE query. There is also an option to remove only the specific records that satisfy a particular condition by using the WHERE clause in a DELETE query.
Syntax to delete all the records:
DELETE FROM TableName; Syntax to delete records according to the condition:
DELETE FROM TableName WHERE CONDITION; Example 1:
Write a query to delete the employee record from the employee table whose salary is above 34000.
Query:
mysql> DELETE FROM employee WHERE Salary = 34000; 
Here we have applied the DELETE query on the employee table. We want to delete only the employee record whose salary is 34000, so we have specified this condition using the WHERE clause.
We will execute the SELECT query to ensure that the employee record with salary as 34000 is deleted successfully.
mysql> SELECT *FROM employee; | ID | First_Name | Last_Name | Salary | Email_Id |
|---|---|---|---|---|
| 1 | Neeta | Korade | 59000 | [email protected] |
| 2 | Sushma | Singh | 62000 | [email protected] |
| 3 | Kavita | Rathod | 27000 | [email protected] |
| 4 | Mrunalini | Deshmukh | 88000 | [email protected] |
| 6 | Laxmi | Bose | 44000 | [email protected] |
| 7 | Lalita | Shah | 66000 | [email protected] |
| 8 | Savita | Kulkarni | 31000 | [email protected] |
| 9 | Shravani | Jaiswal | 38000 | [email protected] |
| 10 | Shweta | Wagh | 35000 | [email protected] |
The results above verify that the employee with a salary of 34000 no longer exists in the employee table.
Example 2:
Write a query to delete all the records from the employee table.
First, let us see the employee table, which is available currently.
mysql> SELECT *FROM employee; 
To remove all the records from the employee table, we will execute the DELETE query on the employee table.
mysql> DELETE FROM employee; 
We will execute the SELECT query to ensure that all the records are deleted successfully from the employee table.
mysql> SELECT *FROM employee; 
The results above verify that the employee table does not contain any record now.
Leave a Reply