SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL CRUD Operation

In any computer language, CRUD operation is a foundation. CRUD operation rules are applied in databases as well. These operations are the basic operations used to perform with any database system. CRUD operation is the backbone for communicating with the databases.

CRUD is an abbreviation for Create, Read, Update, and Delete.

Let's understand the CRUD operation in SQL with examples.

C - CREATE

R - READ

U – UPDATE

D – DELETE

1. CREATE operation

In CRUD operation, 'C' refers to CREATE, which means adding or inserting in the SQL table. In this operation, we will create a table using CREATE TABLE command, and after that, we will insert records in the table using INSERT INTO commands in the SQL.

Syntax for create table is as follows:

CREATE TABLE TableName (Column_Name1 DataType(Size), Column_Name2 DataType(Size), Column_Name3 DataType(Size));

Where:

  • TableName - Name of the table that we will assign to the table.
  • Column_Name – It is the field under which we will store records of the table.
  • DataType – Data Type is assigned to each column in the table. Data Type decides which type of data will be stored in the column like numeric, character, and date type data.

Syntax for Insertion:

INSERT INTO TableName VALUES (Value1, Value2, Value3);

To create a table in the SQL, first, we need to create a database or select a database if you have an existing database. Since we don't have any database, first, we will create a database using CREATE DATABASE command:

CREATE DATABASE CRUD;  

CRUD is the name of our database.   

If you want to check whether the database is created or not, use the SHOW DATABASES command to check the entire database in the system.

SHOW DATABASES;
SQL CRUD OPERATION

A CRUD database is successfully created. Now we will utilize USE and database name to select the database under which we will create a table and perform the operation.

USE CRUD;

We will create 4 Tables using the CREATE TABLE command in the CRUD database.

Table Names:

Table 1: Customer

Table 2: Mobile_Plan.

Table 3: Data_Plan

Table 4: Talktime_Plan

We will create each table one by one using the CREATE TABLE command.

Table Name: Talktime_Plan

CREATE TABLE Talktime_Plan (TalkTime_Id int primary key, Talktime_Limit int, Talktime_Price int); 

We will ensure the table we created as per the column name, proper data type, and size.

Use the below query:

DESC Talktime_Plan;

Output:

FieldTypeNullKeyDefaultExtra
TalkTime_Idint(11)NOPRINULL
Talktime_Limitint(11)YESNULL
Talktime_Priceint(11)YESNULL

The above output itself says that the table is created successfully.

Table Name: Data_Plan

CREATE TABLE Data_Plan (Data_Id int primary key, Data_Limit int, Data_Price int); 

We will ensure that the table we created is per the given column name, proper data type, and size. Use the below query:

DESC Data_Plan;

Output:

FieldTypeNullKeyDefaultExtra
Data_Idint(11)NOPRINULL
Data_Limitint(11)YESNULL
Data_Priceint(11)YESNULL

The above output itself says that the table is created successfully.

Data_Limit column values will be in GB.

Table Name: Mobile_Plan

CREATE TABLE Mobile_Plan (Package_Id int primary key, Data_Id int, Talktime_Id int, FOREIGN KEY(Data_Id) references Data_Plan(Data_Id), FOREIGN KEY(Talktime_Id) references Talktime_Plan(Talktime_Id)); 

We will ensure that the table we created is per the given column name, proper data type, and size. Use the below query:

DESC Data_Plan;

Output:

FieldTypeNullKeyDefaultExtra
Package_Idint(11)NOPRINULL
Data_Idint(11)YESMULNULL
Talktime_Idint(11)YESMULNULL

The above output itself says that the table is created successfully.

Data_id and Talktime_id are the Foreign Key in the Mobile_Plan table.

Table Name: Customer

CREATE TABLE Customer (Customer_Id int primary key, Customer_Name varchar(40), Package_Id int, FOREIGN KEY(Package_Id) references Mobile_Plan(Package_Id), Phone_Number int); 

We will ensure that the table we created is per the given column name, proper data type, and size. Use the below query:

DESC Data_Plan;

Output:

FieldTypeNullKeyDefaultExtra
Customer_Idint(11)NOPRINULL
Customer_Namevarchar(40)YESNULL
Package_Idint(11)YESMULNULL
Phone_Numbervarchar(10)YESNULL

The above output itself says that the table is created successfully.

Package_id is the Foreign Key in the Customer table.

Now, we will insert records into the created tables.

1 Table Name: Talktime_Plan

We will use the INSERT INTO command to insert records in the Talktime_Plan table.

INSERT INTO Talktime_Plan Values (1, 100, 150), (2, 50, 75), (3, 60, 90); 

2 Table Name: Data_Plan

We will use the INSERT INTO command to insert records in the Data_Plan table.

INSERT INTO Data_Plan Values (1, 2, 30), (2, 5, 70), (3, 4, 55); 

3 Table Name: Mobile_Plan

We will use the INSERT INTO command to insert the Mobile _Plan table records.

INSERT INTO Mobile_Plan Values (1, 2, 1), (2, 1, 2), (3, 1, 3); 

4 Table Name: Customer

We will use INSERT INTO command to insert records in the Customer table.

INSERT INTO Customer Values (1001, ‘Akash’, 2,’ 9844567841’), (1002,’Bhushan’, 1,’ 7802441245’), (1003, ‘Praveen’, 3,’ 7058804631’); 

2. READ Operation

In CRUD operation, 'R' refers to READ, retrieving the SQL table records. The SELECT statement is used to retrieve records from the SQL table. We can fetch the entire records from the table or fetch records that are only necessary from the table. We use the ASTERISK (*) symbol in a SELECT statement to fetch entire records from the table. We use the WHERE clause in a SELECT query for necessary records from the table.

Syntax to fetch entire records is as follows:

SELECT * FROM Table_Name;

Syntax using WHERE clause:

SELECT * FROM Table_Name WHERE condition;

Let's see examples using the SELECT statement.

Example 1: Write a query to fetch the entire records from the Customer Table.

SELECT * FROM Customer; 

We used the ASTERISK (*) symbol in the SELECT statement to fetch the entire records from the table.

The output of the above query is as follows:  sql-crud-operation

SQL CRUD OPERATION

All records from Customer Table are fetched.

Example 2: Write a query to fetch only those records from the Talktime_Plan where Talktime_Price is greater than 80.

SELECT * FROM Talktime_Plan WHERE Talktime_Price > 80;

In the above query, we retrieve only those records from Talktime_Plan Table where Talktime_Price is greater than 80.

 The output of the above is as follows:

SQL CRUD OPERATION

There are only two records in the Talktime_Plan whose Talktime_Price is greater than 80.

Example 3: Write a query to browse all the plans, i.e., all the records from the Mobile_Plan, Data_Plan, and Talktime_Plan.

SELECT Mobile_Plan.Package_Id, Mobile_Plan.Data_Id, Mobile_Plan.Talktime_Id, Data_Plan.Data_Limit, Data_Plan.Data_Price, Talktime_Plan.Talktime_Limit, Talktime_Plan.Talktime_Price  from Mobile_Plan INNER JOIN Data_Plan on Mobile_Plan.Data_Id = Data_Plan.Data_Id INNER JOIN Talktime_Plan on Mobile_Plan.Talktime_Id = Talktime_Plan.Talktime_Id ;

In the above query, we join three tables to fetch all the records from Mobile_Plan Table, Data_Plan Table, and Talktime_Plan Table.

The output of the above query is as given below:

SQL CRUD OPERATION

Example 4: Write a query to fetch the entire records from the Mobile_Plan Table.

SELECT * FROM Mobile_Plan;  

We used the ASTERISK (*) symbol in the SELECT statement to fetch the entire records from the table.

The output of the above query is as follows:

SQL CRUD OPERATION

All records from Mobile_Plan Table are fetched.

Example 5: Write a query to fetch the entire records from the Data_Plan Table. The following output:

SELECT * FROM Data_Plan; 

We used the ASTERISK (*) symbol in the SELECT statement to fetch the entire records from the table.

The output of the above query is as follows:

SQL CRUD OPERATION

All records from Data_Plan Table are fetched.

Example 6: Write a query to fetch the entire records from the Talktime_Plan Table. The following output:

SELECT * FROM Talktime_Plan;  

We used the ASTERISK (*) symbol in the SELECT statement to fetch the entire records from the table.

The output of the above query is as follows:

SQL CRUD OPERATION

All records from Talktime_Plan Table are fetched.

3. UPDATE Operation

In CRUD operation, 'U' refers to UPDATE, which means to modify the records available in the SQL tables. To modify available data in the tables, we will use the UPDATE statement. We can update multiple rows at a time or update a single row using the WHERE clause condition.

Syntax:

UPDATE TableName SET Column_Name = Value WHERE condition;

Let’s see examples using UPDATE statement.

Example 1: Write a query to update a Data_ id as 3 whose Package_Id is 2.

UPDATE Mobile_Plan SET Data_Id = 3 WHERE Package_Id = 2;

In the UPDATE query, we have used the SET keyword to modify a Data_id as 3. We have used the WHERE clause to UPDATE Data_id as 3, whose Package_Id is 2.

We will ensure that the query we executed to modify the Data_id as 3 with the Package_Id 2 is modified successfully. We will execute the SELECT statement:

SELECT * FROM Mobile_Plan;
SQL CRUD OPERATION

Example 2: Write a query to update a Customer Package_Id as 3 whose Customer_Id is 1001.

UPDATE Customer SET Package_Id = 3 WHERE Customer_Id = 1001;

In the UPDATE query, we have used the SET keyword to modify a Package_id as 3. We have used the WHERE clause to UPDATE Package_id as 3, whose Customer_Id is 1001.

We will ensure that the query we executed to modify the Data_id as 3 with the Package_Id 2 is modified successfully. We will execute the SELECT statement:

SELECT * FROM Customer;
SQL CRUD OPERATION

4. DELETE Operation

In CRUD operation, 'D' refers to DELETE, which means to delete the data from the SQL tables. To delete records from the SQL table, we will use the DELETE statement. If we want to delete specific records from the table, we will use the WHERE clause in the query.

Syntax:

DELETE FROM Table_Name;

Syntax using WHERE clause:

DELETE FROM Table_Name WHERE Conditions;

Examples using an UPDATE statement are given below:

Example 1: Write a query to delete customer records from the Customer table whose Customer_Id = 1003.

DELETE FROM Customer WHERE Customer_Id = 1003;

We want to delete the record of a Customer whose Customer_Id is 1003. We used the DELETE query to delete the record using WHERE clause-specific conditions.

We will execute a SELECT query to ensure that the record we delete from the Customer table whose Customer_Id is 1003 is deleted successfully.

SQL CRUD OPERATION

Example 2: Write a query to delete entire customer records from the Customer table.

DELETE FROM Customer;

To delete entire records from Customer Table, we will execute a DELETE statement.

We will execute the SELECT query to ensure that the record we delete from the Customer table is deleted successfully.

SQL CRUD OPERATION

The Customer table is empty, which means records are deleted successfully.



ADVERTISEMENT
ADVERTISEMENT