Understanding the basic concepts of CRUD in SQL

Hi, welcome back. In this post, we are going to understand the basic concepts of SQL query and why it is very crucial for developers. Every developer at some point of time in his developer journey might have come across SQL. But before getting started, I'd like to explain what is SQL.

What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a Relational Database.

It is the standard language for Relational Database System.

The term CRUD stands for CREATE, READ, UPDATE and DELETE or DESTROY.

Let's start with the CREATE statement in SQL.

One more thing. If you want to try these queries, you can try them online and you don't have to configure the database locally. Here is the link to an online database.

SQL Online IDE - for Data Science

CREATE

The create statement can be used for both Database and Table creation. Let's see the database creation.

Creating Database

We'll start with the query for database creation.

CREATE DATABASE ecommerce;

This query will help you in creating a database named ecommerce.

Now, let's try to create a table with the CREATE statement.

If you don't know what a table is, read more about it here. Table (database) - Wikipedia)

Creating Table

For creating a table in the database, we need to pass column names with their data types in the query.

Here I'll be creating a users table.

CREATE TABLE users (
    id INT NOT NULL, 
    name STRING,
    PRIMARY KEY (id)
);

The above query will create a table named users and, it will have columns named id, name.

To identify every row uniquely, we have id and, we increment them automatically and we'll also set them as PRIMARY KEY and, it should be NOT NULL.

INSERT

Now we are done with the creating database and table we can now try to store some data in the users table.

To insert data in the table, we have the INSERT statement.

INSERT INTO users
VALUES (1, "Pratik Sah");

It will add a new row in the users table and the values will be stored in the respective columns or fields.

Try inserting some rows on your own.

READ

We already have stored some useful data in the table, time to retrieve them.

For reading, we have the SELECT statement.

Suppose we want to access name column from the table, we'll write:

SELECT name FROM users;

If we want to select everything from the table, we use * for that.

SELECT * FROM users;

CONDITIONS

Suppose we want to read only those data which satisfies a particular condition, then we'll use WHERE in our query.

SELECT * FROM users WHERE name = "Pratik";

Here, we'll get those results only which have name equals to Pratik in the table users.

We can also use id for querying a particular row from the table. For example, SELECT name FROM users WHERE id=1 and it will return us the name of the row where the id will be 1.

UPDATE

We now have our data stored in our table. We may want to change the name of the user or maybe the user wants to change the email. In that case, we can use the update statement.

UPDATE users
SET name='Pratik'
WHERE id=1;

The above statement will help us to update the name of the user with id equals to 1. We can use this statement for making any changes stored in the table.

DELETE

Sometimes what happens is, suppose a user wants to delete his account from our portal. We may want to delete his record from our users table and to that, we use the DELETE statement.

The delete statement helps us in deleting the data from our table.

DELETE FROM users
WHERE id=1;

Now, this will delete the entire row from the users table where id will be 1.

NOTE: Don't forget to use the WHERE or else it will delete the entire row from the table.

Sometimes we want to add a completely new column in our table. Now, in that case, we have to ALTER our table.

ALTER

ALTER is used to add, delete or modify columns in an existing table.

Suppose we want to add a new field in our users table where we want to store the age of user. We'll add a new column using the ALTER keyword.

ALTER TABLE users
ADD age INT;

Now, this command will add a new column named age with the data type INT in our users table.

Well, this was all for today's post and if you found it helpful, please leave a like, share this post with your friends & comment below if you have any doubts.

Thanks for your time and I'll meet you in my next post. Take care and happy coding🙂!