CRUD Operations in SQL

Create Operation

Create Operation is the first operation that we can perform on the database. If we want to work with a database, we first need to create it. This operation is used to add new data. The command CREATE is used to create a table. We can insert the data by using INSERT INTO command in a table. Let us understand this operation with the help of an example.

Before creating a table, we need to create a database. Let us create a database with the name codingninjasSQL.

You need to write the following query to create a database:

CREATE DATABASE codingninjasSQL;

Now, we will see that database was created successfully.

To use the codingninjasSQL database, we need to write the following command:

use codingninjasSQL;

 

Now, in the codingninjasSQL database, we need to create a table. Suppose we want to create a table with the name ninjas.

CREATE TABLE ninjas(ID INT PRIMARY KEY, FirstName VARCHAR(30), LastName VARCHAR(30), QuestionSolved INT);

For this example, we have considered four columns:

1. ID(Integer type and Primary Key)
    
2. FirstName(Varchar type)
    
3. LastName(Varchar type)
    
4. QuestionSolved(Integer type)
    

If we want to check whether the table is created or not. Then we can run the following command:

DESC ninjas;

 

This will produce the result:

Now we have to insert the data into the table ninjas. Now by using INSERT INTO, we can insert the data. We can insert data by writing the following query:

INSERT INTO ninjas(ID, FirstName, LastName, QuestionSolved) VALUES(1,"Narayan","Mishra", 60),(2,"Kanak","Rana", 80),(3,"Lakshya","Gupta", 40),(4,"Abhishek","Nayak", 55);

 

So, our data is inserted successfully. 

Now you might be thinking about how to check whether our data is inserted or not. Let us understand read operation in SQL.

Read Operation

The read operation in SQL helps us to retrieve the data from the database. We can check the data by using the SELECT statement. Let us understand this operation with the help of the codingninjasSQL database and ninjas table.

Suppose we want to retrieve the data of the ninjas table. Then we have to write the following query:

SELECT * FROM ninjas;

 

This will give us the result:

The * with the SELECT statement retrieves all the table's records.

Now, what if we want to update something in the table ninjas? Let us understand the update operation.

Update Operation

The update operation is used to update the data in the database. For making any update in the database, we need to use the UPDATE statement in SQL. Let us understand this operation with the help of the codingninjasSQL database and ninjas table.

Suppose we want to update the QuestionSolved by ID=1 as 60 to 100. Then we need to write the following query:

UPDATE ninjas SET QuestionSolved=100 where ID=1;

 

To check whether the QuestionSolved updated or not, we need to run the following query:

SELECT * FROM ninjas;

 

This will produce the updated output:

Now you may face a situation where you need to delete a record or all the record from the table. Let us understand the delete operation in SQL.

Delete Operation

The delete operation in SQL is used to delete a specific row with a condition. It is also used to delete all rows and columns without deleting a schema. To perform the delete operation, we can use the DELETE statement in SQL. Let us understand this operation with the help of the codingninjasSQL database and ninjas table.

Suppose we want to delete those rows in which QuestionSolved is less than 50. To perform this operation, we need to write the following query:

DELETE FROM ninjas WHERE QuestionSolved<50;

 

To check whether the row is deleted or not, we need to run the following query:

SELECT * FROM ninjas;

 

This will produce the updated result:

Suppose we want to delete all the records, then we need to run the following query:

DELETE FROM ninjas;

 

This will produce the result:

Must Read SQL Clauses

Frequently Asked Questions

Q. Are CRUD operations DDL or DML?

CRUD (Create, Read, Update, Delete) operations are primarily DML (Data Manipulation Language) operations. They involve manipulating and interacting with data records in a database, not altering the database structure, which is the focus of DDL (Data Definition Language).

Q. What is the difference between rest and CRUD?

REST (Representational State Transfer) is an architectural style for designing networked applications, while CRUD (Create, Read, Update, Delete) is a set of basic database operations for data management. REST can use CRUD operations to interact with resources, but it's a broader concept encompassing principles for designing web services and APIs.

Q. How many types of CRUD are there?

There are four main types of CRUD operations:

• Create: Adding new data.
• Read: Retrieving existing data.
• Update: Modifying or editing existing data.
• Delete: Removing data from a database or system.

Q. What is CRUD and REST API?

CRUD (Create, Read, Update, Delete) is a set of basic data management operations. REST (Representational State Transfer) API is an architectural style for designing web services that use HTTP methods for CRUD operations.

Q. What are the benefits of CRUD?

The benefits of CRUD operations are simplicity and efficiency in managing data. CRUD allows easy creation, retrieval, updating, and deletion of data, making it essential for database and application development.

Conclusion

In this article, we have discussed the CRUD operations in SQL. We have also discussed some examples. You can check out our other blogs to enhance your knowledge:

• SQL Introduction
   
• TCL Commands in SQL
   
• Update and Delete Query
   

We hope this blog helped you to understand the CRUD operations in SQL. You can refer to our guided paths on the Coding Ninjas Studio platform. You can check our course to learn more about DSA, DBMS, Competitive Programming, Python, Java, JavaScript, etc. 

To practice and improve yourself in the interview, you can also check out Top 100 SQL problems, Interview experience, Coding interview questions, and the Ultimate guide path for interviews. 

Happy Learning!!