CRUD OPERATIONS IN SQL

Introduction:

CRUD operations are fundamental concepts of every SQL user. In this article we will walk through how to start working with them.

What Is SQL?

SQL - Structured Query Language. SQL is a programming language for storing and processing information in a relational database. SQL has become a language of Data. So, in order to be data driven, people need to know how to access and analyze data. SQL basically stores data in a particular structured tabular manner.

SQL has variety of functions that is used to manipulate data easily which is a most important part of data analysis. This gives user an ability to use data for transformation and to analyze in order to generate reports for better business understanding.

What Is CRUD?

CRUD is an acronym stands for Create, Read, Update and Delete. These are four essential operations of any persistent storage system, like a database. CRUD is often used to describe user interface conventions that facilitate viewing, searching and modifying information, a lot using computer based forms and reports. This is very simple functionality consisting of four basic operations to take a first step towards communicating with data.

Let’s get familiar with these basic simple concepts of CRUD Operations in SQL.

These are the four most basic operations that can be performed with most traditional database systems and they are the backbone for communicating with any database. CRUD in database terms also mentioned as DML (Data Manipulation Language) Statements. Data Manipulation Language is used to manage or manipulate existing data in database Tables. To understand the data as data analyst it is very important to learn how these basic operations work.

When it comes to executing queries, you can use SQL Server Management Studio , PostgreSQL, SQLCMD etc.., based on your preference. I have executed my queries using SQL Server Management Studio.

CREATE :

The CREATE operation is to create a new database and its objects, it also refers to create new records within the table using INSERT statement.

• Create Database.

• Create Tables.

• Insert Rows

CREATE DATABASE command will create a new SQL database.

Syntax:

CREATE DATABASE database name;

Above statement will create a database in server where you want your data to be stored.

CREATE TABLE statement is used to creates a new table in database.

Syntax:

CREATE TABLE table name (column1, column2, column3… );

This statement will create table with defined columns in the database.

INSERT INTO statement is used to insert new records in form of rows into a table. This command starts with the INSERT INTO keyword and followed by the table name, column names, and the values.

There are two ways to write INSERT INTO:

1. Specify both the column names and the values

INSERT INTO table name (column1, column2, column3… )
VALUES (value1, value2, value3…);

2. Specify only values. However, make sure the order of the values are in the same order as the columns in the table.

INSERT INTO table name 
VALUES (value1, value2, value3…);

In the example, We are creating database named Factory and table named Employees with one row of data insert into your Employees table. Later you can add more rows using INSERT INTO command into an Employees table.

Notes: In modern era most database software provider added a feature to create database and it’s objects through GUI methodology, But in traditional ways SQL commands are widely been used to create database and their objects(table, views, triggers etc…)

READ:

READ function refers to SELECT command, widely used for data retrieval operation in databases. The word ‘’READ” stands for retrieval of specific records or read it from a listed tables. SQL uses the SELECT command to retrieve the data.

SELECT statement is used to retrieve data from table in database.

Syntax:

SELECT * from table name;

This statement will show all the values of defined table name.

For example, let’s look at the list of all Employees in Employee table.

In above example ‘*’ sign refers to ‘All’. So when you select all the data from database object Employee table, It will return all the columns and rows of the table.

If We want to retrieve specific data based on some conditions, then we can use “WHERE” clause along with “SELECT” statement

For Example, If I want to see records of only Female Employees of the Factory.

Notes: We can specify column name or use multiple condition by adding “AND” , “OR” to narrow search to retrieve specific records While working with large datasets. Other than that GROUP BY, HAVING, ORDER BY , LIMIT etc.. are very important programming clauses used by analyst to retrieve – READ data frequently.

UPDATE:

The UPDATE operation is used to alter/modify existing records of a table.

While using the UPDATE statement, two must require conditions need to keep on check.

1. Define the target table.

2. Define columns that needs to be modified along with the associated values (and maybe specific rows, depending upon the requirement).

While using rows ,you want to make sure the you limit the number of rows that are updated in each query to avoid concurrency issues.

Syntax:

UPDATE table name
SET column1 = value1, column2 = value2, ...
WHERE condition;

In Example shown below, Let’s try to update the FirstName and date of a specific Employee in the Employees table.

Now in the table you can see that in the existing record of an employee’s FirstName and date was ‘Mark’ And ‘2020-02-01’ and we have modify those records with ‘Jason’ And ‘2020-02-16’. Those changes are reflected in Employees table now.

​Notes: The WHERE clause specifies which records should be updated. Be extra cautious while updating records. If you exclude the WHERE clause in UPDATE statement then ALL the records of the table will be updated!

DELETE:

The DELETE operation is used to remove one or more records from the table of the database.

When using a DELETE statement, you will define the target table name and in some cases specific row(s) that wanted to be removed from the table. Use WHERE clause to delete record(s) to specify condition.

Syntax:

DELETE from table name
WHERE condition;

For Example, Let's remove record of an employee(s) who joined on or before 1st, May 2020.

As seen in above image, query condition is used in where clause to remove specific records. in the first table you can see there are four records of employees. After using delete, employee(s) who joined after 1st May 2020 has been displayed (Table below)

Notes: If you want to delete all the records from the table you can omit the use of WHERE. That will delete all the records from the table.

SUMMARY:

So far, we went over what CRUD is and how it’s used in SQL. We also discussed how to implement CRUD Operations in SQL Server. It is designated as the foundation of SQL operations in any database products. This knowledge is essential for building successful analytical skills today. Not to forget a great way to reinforce your understanding of data.

To summarize this article, we conclude that CRUD plays an important role in SQL and we need to understand the necessary skills of SQL for Data Science.