What are SQL CRUD operations?

All operations you can do on any data can be boiled down to Create, Read, Update, and Delete (CRUD). You can create something new, you can read it, update it, and finally delete it if you wish.

So, naturally it makes sense that SQL would have these 4 operations within the language. The following table shows the corresponding SQL command to the CRUD operation. Also, in this table you can see the HTTP equivalents that were covered in the article; what are CRUD operations. It is good to see how CRUD can be found in many ideas across computing.

Now you have a broad understanding of what SQL CRUD operations are, the next step is to look a bit closer and see them in action. A codebot can do CRUD in its sleep, so let’s use a running example and look at some source code. The running example is a movie application and the following screenshot shows the data for the application. For techies, they call this an entity diagram or a database schema. These terms are actually pretty straight forward to understand so stick with it.

• In the middle in a Movie entity with 2 attributes; Title and Description of type String.
• On the left is a Category entity with 1 attribute; Name of type String.
• On the right is an Actor entity with 1 attribute; Name of type String.
• The Movie has a many-to-many relationship a Category.
• And finally the Movie has a many-to-many relationship an Actor.

For the rest of this article, we are going to look at how to practice your SQL using the movie application. It is recommended to signup at codebots.app so you can follow along. You might just be amazed at how much you can actually do already and learn about SQL CRUD operations.

SQL Examples

The following 4 sub-sections demonstrate some SQL examples for CRUD. A general example of SQL is showed and then a specific example for the movie application is displayed.

The best way to practice these SQL statement is to do them. Once you have deployed your application using docker (you will be given instructions on this on the platform), you can use pgAdmin to manage your database. This link has some simple step-by-step instructions on how to connect to your database using pgAdmin.

Create

To create a new row in a table, the INSERT INTO statement is used and looks something like:

INSERT INTO table_name (column1, column2, column3 ) VALUES (value1, value2, value3);

To insert a new movie record into a table for the movie application, you can use pgAdmin and use something like:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
INSERT INTO movie_entity (id, title, description, created, modified) VALUES (uuid_generate_v4(), 'Star Wars: Episode I - The Phantom Menace', 'The Phantom Menace is a 1999 American epic space-opera film written and directed by George Lucas, produced by Lucasfilm, distributed by 20th Century Fox and stars Liam Neeson, Ewan McGregor, Natalie Portman, Jake Lloyd, Ian McDiarmid, Anthony Daniels, Kenny Baker, Pernilla August, and Frank Oz', Now(), Now());

In this script you will notice the CREATE EXTENSION statement, this is to insert the id of the row. See the uuid_generate_v4() function, that will insert a UUID for you. The following screenshot shows you what it will look like in pgAdmin.

Read

To read the rows from a table, the SELECT statement is used and looks something like:

SELECT column1, column2 FROM table_name;

To get all of the movies from our database, you can use pgAdmin and do something like this:

SELECT title, description FROM movie_entity;

If you want to get more specific, the WHERE clause can help.

Update

To update a row in a table, the UPDATE statement is used and looks something like this:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

To update a record from the movies application, you can use pgAdmin and do something like:

UPDATE movie_entity SET title = 'Star Wars: Episode II - Attack of the Clones' WHERE title = 'Star Wars: Episode I - The Phantom Menace';

Note that in the WHERE clause the title of the record is used to indicate which row to update. Sometimes people use the id or the like.

Delete

To delete a record from the table, the DELETE statement is used and looks something like this:

DELETE FROM table_name WHERE condition;

To delete a record from the movies application, you can use pgAdmin and do something like:

DELETE FROM movie_entity WHERE title = 'Star Wars: Episode II - Attack of the Clones';

Application design

If you are still reading by this point, I am going to assume you want to get better at SQL CRUD operations and it’s time to get a bit more technical. So get ready and hold on!

The truth is, it is becoming more rare that developers need to write standard SQL statements like the ones above. It is good to know them but there are way cooler tools you can use to get going quicker. The vast majority of applications these days use what is called an Object Relational Mapping (ORM) tool. An ORM maps the database schema to the equivalent classes in your programming language and a lot of these SQL queries are abstracted away. This can save you a lot of time. But there is usually a way to include a SQL statement somewhere as required if you have some custom query.

For SpringBot we use Hibernate and for C#Bot we use the Entity Framework. In the following screenshot you can see some code that SpringBot wrote. In this case, it is the MovieRepository class and this can be used to do all of the SQL CRUD operations we have described above. Way quicker too!

Summary

CRUD relates very closely to SQL. SQL provides language statements that allow you to do create, read, update, and delete your data. If you want to learn more about the specifics of SQL then W3schools has some great little examples to wet your appetite.

Further to learning about the relationship between SQL and CRUD, we took some time to see how a codebot can help you out. For our running movie application database, we stepped through how to use pgAdmin so we could see the underlying database and we took a quick look at some of the code that a bot can write for you. Codebots can do the CRUD SQL operations saving you time in getting your project up and running, allowing you to focus on customising your app and coding on the fun stuff!

Written by Eban Escott

Founder of Codebots

Dr Eban Escott received his Doctorate from UQ (2013) in Model-Driven Engineering and his Masters from QUT (2004) in Artificial Intelligence. He is an advocate of using models as first class artefacts in software engineering and creating not just technologies, but methodologies that enhance the quality of life for software engineers.