Experiment 5: Implementation of different types of operators in SQL

a) Arithmetic operators
b) Logical operators
c) Comparison operators
d) Set operation

a) SQL Arithmetic Operators:

• In Structured Query Language, the arithmetic operators are used to perform mathematical
operations on the numerical values stored in the database tables.
• We can use these operators with the SELECT statement in SQL. We can also use the WHERE
clause in the SELECT statement for performing operations on particular rows.
• These types of operators are used between two numerical operands for performing addition,
subtraction, multiplication, and division operations.

Types of Arithmetic Operators: The arithmetic operators in SQL are categorized into the following five types

1) SQL Addition Operator (+)

2) SQL Subtraction Operator (-)
3) SQL Multiplication Operator (*)
4) SQL Division Operator (/)
5) SQL Modulus Operator (%)

SQL Addition Operator (+):

The SQL Addition Operator performs the addition on the numerical columns in the table.

If you want to add the values of two numerical columns in the table, then you have to specify both
columns as the first and second operand. You can also add the new integer value in the value of the
integer column.

Syntax of SQL Addition Operator:

1. SELECT Column_Name_1 Addition_Operator Column_Name2 FROM Table_Name;

Addition Operator with WHERE Clause

The addition operator can also be used with the WHERE clause in the SQL SELECT query.

41 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
The syntax for using the WHERE clause with the addition operator is given below:

1. SELECT Column_Name_1 Addition_Operator Column_Name2 FROM Table_Name WHE

RE Condition;

Implementation of Addition operator in SQL:

The following CREATE query creates the Employee table with five fields:

CREATE TABLE Employee

(
Employee_ID INT AUTO_INCREMENT PRIMARY KEY,
Emp_Name VARCHAR (50),
Emp_City VARCHAR (20),
Emp_Salary INT NOT NULL,
Emp_Bonus INT NOT NULL
);

The following INSERT query inserts the record of employees into the Employee table:

INSERT INTO Employee (Employee_ID, Emp_Name, Emp_City, Emp_Salary, Emp_Bonu

s) VALUES (101, Anuj, Ghaziabad, 25000, 2000),
(102, Tushar, Lucknow, 29000, 1000),
(103, Vivek, Kolkata, 35000, 2500),
(104, Shivam, Goa, 22000, 3000);

The following SELECT query shows the data of the Employee table:

SELECT * FROM Employee;

Employee_Id Emp_Name Emp_City Emp_Salary Emp_bonus

101 Anuj Ghaziabad 25000 2000
102 Tushar Lucknow 29000 1000
103 Vivek Kolkata 35000 2500
104 Shivam Goa 22000 3000

The following query adds the Emp_Salary and Emp_Bonus of each employee of the Employee table
using the addition operator:

42 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
SELECT Emp_Salary + Emp_Bonus AS Emp_Total_Salary FROM Employee;

Output:

The following query adds 15000 to the salary of each employee in the Emp_Salary column of the
Employee table:

SELECT Emp_Salary + 15000 AS Emp_Updated_Salary FROM Employee;

Output:

The following query performs the addition operation on the above Employee table with the WHERE
clause:

1. SELECT Emp_Salary + Emp_Bonus AS Emp_Total_Salary FROM Employee WHERE E

mp_Salary > 25000;

It shows only records of those employees whose Emp_Salary is greater than 25000:

Output:

43 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
SQL Subtraction Operator (-)

The SQL Subtraction Operator performs the subtraction on the numerical columns in the table.

If we want to subtract the values of one numerical column from the values of another numerical column,
then we have to specify both columns as the first and second operand. We can also subtract the integer
value from the values of the integer column.

Syntax of SQL Subtraction Operator:

1. SELECT Column_Name_1 Subtraction_Operator Column_Name2 FROM Table_Name;

Subtraction Operator with WHERE Clause

The subtraction operator can also be used with the WHERE clause in the SELECT query.

The syntax for using the WHERE clause with the subtraction operator is given below:

1. SELECT Column_Name_1 Subtraction_Operator Column_Name2 FROM Table_Name W

HERE Condition;

Implementation of Subtraction operator in SQL:

The following CREATE query creates the Employee table with five fields:

CREATE TABLE Employee

(
Employee_ID INT AUTO_INCREMENT PRIMARY KEY,
Emp_Name VARCHAR (50),
Emp_City VARCHAR (20),
Emp_Salary INT NOT NULL,
Emp_Panelty INT NOT NULL
);

The following INSERT query inserts the record of employees into the Employee table:
44 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 INSERT INTO Employee (Employee_ID, Emp_Name, Emp_City, Emp_Salary, Emp_Bonu
s) VALUES (101, Anuj, Ghaziabad, 25000, 500),
(102, Tushar, Lucknow, 29000, 1000),
(103, Vivek, Kolkata, 35000, 700),
(104, Shivam, Goa, 22000, 500);

The following SELECT query shows the data of the Employee table:

1. SELECT * FROM Employee;

Employee_Id Emp_Name Emp_City Emp_Salary Emp_Panelty

101 Anuj Ghaziabad 25000 500
102 Tushar Lucknow 29000 1000
103 Vivek Kolkata 35000 700
104 Shivam Goa 22000 500

The following query subtracts the values of the Emp_Panelty column from the Emp_Salary column of
the Employee table using the subtraction operator:

1. SELECT Emp_Salary - Emp_Panelty AS Emp_Total_Salary FROM Employee;

Output:

The following query performs the subtraction operation on the above Employee table with the WHERE
clause:

1. SELECT Emp_Panelty - Emp_Salary AS Emp_Total_Salary FROM Employee WHERE E

mployee_ID = 104;

It shows only records of those employees whose Employee_ID is 103:

45 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
Output:

The following query subtracts 10000 from the salary of each employee of the Employee table:

1. SELECT Emp_Salary - 10000 AS Emp_Updated_Salary FROM Employee;

Output:

SQL Multiplication Operator (*)

The SQL Multiplication Operator performs the multiplication on the numerical columns in the table.

If you want to multiply the values of two numerical columns, then you have to specify both columns as
the first and second operand. You can also multiply the integer value with the values of an integer
column.

Syntax of SQL Multiplication Operator:

1. SELECT Column_Name_1 Multiplication_Operator Column_Name2 FROM Table_Name;

Multiplication Operator with WHERE Clause

The multiplication operator (*) can also be used with the WHERE clause in the SELECT query.

The syntax for using the WHERE clause with the multiplication operator is given below:

46 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 1. SELECT Column_Name_1 Multilplication_Operator Column_Name2 FROM Table_Name
WHERE Condition;

Implementation of Multiplication operator in SQL:

The following CREATE query creates the Cars table with four fields:

CREATE TABLE Cars

(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
);

The following INSERT query inserts the record of cars into the Cars table:

INSERT INTO Cars (Car_Number, Car_Name, Car_Amount, Car_Price)

VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000);

The following SELECT query shows the data of the Cars table:

1. SELECT * FROM Cars;

Car_Number Car_Name Car_Amount Car_Price

2578 Creta 3 1500000
9258 Audi 2 3000000
8233 Venue 6 900000
6214 Nexon 7 1000000

The following query multiplies the values of the Car_Amount column with the Car_Price column of
the Cars table using the Multiplication operator:

1. SELECT Car_Amount * Car_Price AS Car_Total_Price FROM Cars;

47 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
Output:

The following query performs the multiplication operation on the above Cars table with the WHERE
clause:

1. SELECT Car_Amount * Car_Price AS Car_Total_Price FROM Cars WHERE Car_Price >

= 1000000;

It shows only those records of cars whose Car_Price is greater than and equal to 1000000.

Output:

SQL Division Operator (/)

The SQL Division operator divides the numerical values of one column by the numerical values of
another column.

Syntax of SQL Division Operator:

1. SELECT Column_Name_1 Division_Operator Column_Name2 FROM Table_Name;

Division Operator with WHERE Clause

The SQL division operator can also be used with the WHERE clause in the SELECT query.

48 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
The syntax for using the WHERE clause with the division operator is given below:

1. SELECT Column_Name_1 Division_Operator Column_Name2 FROM Table_Name <stron

g>WHERE Condition;</strong>

Implementation of Division operator in SQL:

The following CREATE query creates the Cars table with four fields:

CREATE TABLE Cars

(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
);

The following INSERT query inserts the record of cars into the Cars table:

INSERT INTO Cars (Car_Number, Car_Name, Car_Amount, Car_Price)

VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 10, 1000000);

The following SELECT query shows the data of the Cars table:

1. SELECT * FROM Cars;

Car_Number Car_Name Car_Amount Car_Price

2578 Creta 3 1500000
9258 Audi 2 3000000
8233 Venue 6 900000
6214 Nexon 10 1000000

The following query divides the values of the Car_Price column by the Car_Amount column of
the Cars table using the Multiplication operator:

49 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 1. SELECT Car_Price / Car_Amount AS One_Car_Price FROM Cars;

Output:

The following query performs the division operation on the above Cars table with the WHERE clause:

1. SELECT Car_Price / Car_Amount AS One_Car_Price FROM Cars WHERE Car_Number

= 9258;

It shows the record of those cars whose Car_Number is 9258 from the Cars table.

Output:

SQL Modulus Operator (%)

The SQL Modulus Operator provides the remainder when the numerical values of one column are
divided by the numerical values of another column.

Syntax of Modulus Operator in SQL:

1. SELECT Column_Name_1 Modulus_Operator Column_Name2 FROM Table_Name;

Implementation of Modulus operator in SQL:

The following CREATE query creates the Student table with four fields:

CREATE TABLE Student

(
50 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 Student_ID INT PRIMARY KEY,
Student_Name VARCHAR (50),
Student_MathsINT,
Student_English INT NOT NULL
);

The following INSERT query inserts the record of the student into the Student table:

INSERT INTO Student (Student_ID, Student_Name, Student_Maths, Student_English) VA

LUES (201, Anuj, 30, 60),
(202, Tushar, 25, 100),
(203, Vivek, 30, 90),
(204, Shivam, 40, 80);

The following SELECT query shows the data of the Student table:

1. SELECT * FROM Student;

Student_Id Student_Name Student_Maths Student_English

201 Anuj 30 60
202 Tushar 25 100
203 Vivek 30 90
204 Shivam 40 80

The following query divides the marks Student_English column by Marks of Student_Maths of each
student in the Student table:

1. SELECT Student_English % Student_Maths AS Remainder FROM Student;

Output:

51 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 The following query performs the modulus operation on the above Student table with the WHERE
clause:

1. SELECT Student_English % Student_Maths AS Remainder FROM Student WHERE Stude

nt_Id >202;

It shows the record of those students whose Student_Id is greater than 202.

Output:

b) SQL Logical Operators:

• The Logical Operator is nothing but which returns the result in one form, i.e., either it will display the query
is true, or the query is false. The results displayed to combine or merge more than one true or false data.

Types of Logical Operators: The Logical Operators in SQL are as follows:

1) SQL AND OPERATOR

2) SQL OR OPERATOR
3) SQL NOT OPERATOR
4) SQL BETWEEN OPERATOR
5) SQL IN OPERATOR
6) SQL LIKE OPERATOR

Consider we have an employees table with the following data:

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24
2 Tejaswini Naik 75000 Delhi System Engineer 2019-12-24 23
3 Anuja Sharma 40000 Jaipur Manager 2021-08-15 26

52 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24
5 Rucha Jagtap 45000 Bangalore Project Manager 2020-08-09 23
6 Rutuja Deshmukh 60000 Bangalore Manager 2019-07-17 26
7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24
8 Sana Sheik 45000 Pune Software Engineer 2020-09-10 26
9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25
10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24
11 Simran Khanna 45500 Kolhapur HR 2019-01-02 26
12 Shivani Wagh 50500 Delhi Software Developer 2016-09-10 25
13 Kiran Maheshwari 50000 Nashik HR 2013-12-12 23
14 Tejal Jain 40000 Delhi Project Manager 2017-11-10 25
15 Mohini Shah 38000 Pune Software Developer 2019-03-05 20

1. SQL AND Operator

The SQL AND operator is used with the where clause in the SQL Query. AND operator in SQL returns
only those records which satisfy both the conditions in the SQL query.

Let's understand the below example, which explains how to execute AND operator in an SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table where the
designation is 'Project Manager' and the City to which the employee belongs to is Mumbai.

Query:

1. mysql> SELECT * FROM employees WHERE City = "Mumbai" AND Designation = "Proj
ect Manager";

Here we have written a SELECT query with a WHERE clause on the City column and Designation
column with 'AND' operator in between both the conditions. Any record in the employees table that
meets both conditions, i.e., the city to which the employee belongs is Mumbai, and their designation is
Project Manager, will only be considered in output.

You will get the following output:

E_ID Name Salary City Designation Date_of_Joining Age

53 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24
10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24

There are only two records in the employees table whose city name is equal to 'Mumbai' and designation
name is equal to 'Project Manager'.

2. SQL BETWEEN Operator

This operator displays the records which fall between the given ranges in the SQL query. The results of
the BETWEEN operator include begin and end values of the given range.

Let's understand the below example, which explains how to execute BETWEEN operator in an SQL
query.

Example:

Write a query to retrieve only those records of an employee from the employees table where employee
salary lies between 50000 to 90000.

Query:

1. mysql> SELECT * FROM employees WHERE Salary BETWEEN 50000 AND 90000;

Here we have written a SELECT query with a WHERE clause on the Salary column with the
'BETWEEN' operator. BETWEEN operator is followed by beginning and end values 50000 and 90000
respectively with 'AND' operator in between. Any record in the employees table that meets the
condition, i.e., the employee's salary is between 50000 and 90000, will only be considered in output.

You will get the following output:

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24
2 Tejaswini Naik 75000 Delhi System Engineer 2019-12-24 23
4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24
6 Rutuja Deshmukh 60000 Bangalore Manager 2019-07-17 26
7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24
9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25
10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24
12 Shivani Wagh 50500 Delhi Software Developer 2016-09-10 25

54 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 13 Kiran Maheshwari 50000 Nashik HR 2013-12-12 23

There are nine records in the employees table whose salary falls between 50000 to 90000.

3. SQL OR Operator

The SQL OR operator is used with the where clause in an SQL Query. AND operator in SQL returns
only those records that satisfy any of the conditions in the SQL query.

Let's understand the below example, which explains how to execute OR operator an SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table where the
employee's designation is 'System Engineer' or the city to which the employee belongs is Mumbai.

Query:

1. mysql> SELECT * FROM employees WHERE Designation = "System Engineer" OR City

= "Mumbai";

Here we have written a SELECT query with a WHERE clause on the City column and Designation
column with the 'OR' operator in between both the conditions. Any record in the employees table that
meets any of the conditions, i.e., the city to which the employee belongs is Mumbai, or their designation
is System Engineer, will only be considered in output.

You will get the following output:

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24
2 Tejaswini Naik 75000 Delhi System Engineer 2019-12-24 23
4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24
7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24
10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24

There are only five records in the employees table whose city name is equal to 'Mumbai' or the
employee's designation is equal to 'System Engineer'.

55 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
4. SQL IN Operator

When we want to check for one or more than one value in a single SQL query, we use IN operator with
the WHERE clause in a SELECT query.

Let's understand the below example, which explains how to execute IN operator in an SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table where the city to
which the employee belongs to is either Mumbai, Bangalore, or Pune.

Query:

1. mysql> SELECT * FROM employees WHERE City IN ("Mumbai", "Bangalore", "Pune");

Here we have written a SELECT query with a WHERE clause on the City column followed by IN
operator. Since we wanted only those records that belongs to Mumbai, Bangalore, or Pune, we have
passed Mumbai, Bangalore, and Pune as parameters to the IN operator. So, if the City value of any

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24
4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24
5 Rucha Jagtap 45000 Bangalore Project Manager 2020-08-09 23
6 Rutuja Deshmukh 60000 Bangalore Manager 2019-07-17 26
8 Sana Sheik 45000 Pune Software 2020-09-10 26
Engineer
9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25
10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24
15 Mohini Shah 38000 Pune Software 2019-03-05 20
Developer
record matches with the places passed to the IN operator will only be considered in output.

You will get the following output:

There are only eight records in the employees table where the city to which the employee belongs is
either Mumbai, Bangalore, or Pune.

56 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
5. SQL NOT Operator

NOT operator in SQL shows those records from the table where the criteria is not met. NOT operator
is used with where clause in a SELECT query.

Let's understand the below example, which explains how to execute NOT operator in SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table where the
employee's designation is not Project Manager.

Query:

1. mysql> SELECT * FROM employees WHERE NOT Designation = "Project Manager";

Here we have written a SELECT query with a WHERE clause on the Designation column followed by
NOT operator. Since we wanted only those records whose designation is other than a project manager,
we have given the designation value as Project Manager to the NOT operator. So, if the designation
value of any record does not match with the value given to the NOT operator will only be considered
in output.

You will get the following output:

E_ID Name Salary City Designation Date_of_Joining Age

2 Tejaswini Naik 75000 Delhi System Engineer 2019-12-24 23
3 Anuja Sharma 40000 Jaipur Manager 2021-08-15 26
4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24
6 Rutuja Deshmukh 60000 Bangalore Manager 2019-07-17 26
7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24
8 Sana Sheik 45000 Pune Software 2020-09-10 26
Engineer
9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25
11 Simran Khanna 45500 Kolhapur HR 2019-01-02 26
12 Shivani Wagh 50500 Delhi Software 2016-09-10 25
Developer
13 Kiran Maheshwari 50000 Nashik HR 2013-12-12 23

57 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 15 Mohini Shah 38000 Pune Software 2019-03-05 20
Developer

There are eleven records in the employees table whose designation is not a project manager.

6. SQL LIKE Operator

LIKE Operator in SQL displays only those data from the table which matches the pattern specified in
the query. Percentage (%) and underscore (_) are the two wildcard operators used with LIKE Operator
to perform pattern matching tasks.

Let's understand the below example, which explains how to execute the LIKE operator in an SQL query.

Example:

Write a query to retrieve only those records of employees from the employees table whose salary starts
with the digit 5.

Query:

1. mysql> SELECT * FROM employees WHERE Salary LIKE "5%";

Here we have written a SELECT query with a WHERE clause on the Salary column followed by the
LIKE operator. Since we wanted only those records whose salary starts with the digit 5, we have given
the value to the LIKE operator as '5%'. So, if the salary value of any record starts with the digit 5,
followed by any other digit will only be considered in output.

You will get the following output:

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24
7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24
9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25
12 Shivani Wagh 50500 Delhi Software Developer 2016-09-10 25
13 Kiran 50000 Nashik HR 2013-12-12 23
Maheshwari

There are five records in the employees table whose salary starts with the digit 5.

58 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
c) SQL Comparison Operators:

• The SQL Operators which compare the values of two columns in the database tables are called as comparison
operators.
• In SQL, comparison operators are always used in the WHERE clause with the SELECT, UPDATE, and
DELETE statements.

Types of Comparison Operators: The comparison operators in SQL are categorized into the following six operators
category

1) SQL Equal Operator (=)

2) SQL Not Equal Operator (!=)
3) SQL Greater Than Equals to Operator (>=)
4) SQL Less Than Operator (<)
5) SQL Greater Than Operator (>)
6) SQL Less Than Equals to Operator (<=)

SQL Equal Operator (=)

This type of comparison operator selects only those data from the table which matches the specified
value.

This operator is highly used by the database users in Structured Query Language.

This operator returns TRUE rows from the database table if the value of the column is same as the value
specified in the query.

The following syntax accesses the data from the table by using the Equal operator:

1. SELECT * FROM Table_Name WHERE Column_Name = Value;

The syntax to update the data in the table by using the Equal operator is given below:

1. UPDATE Table_Name SET Column_Name = Value WHERE Column_Name = Value;

The syntax to delete the data from the table by using the Equal operator is given below:

1. DELETE FROM Table_Name WHERE Field_Name = Value;

Example of SQL Equal operator

59 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
The following CREATE query creates the Employee table with five fields:

CREATE TABLE Employee

(
Employee_ID INT,
Emp_Name VARCHAR (50),
Emp_City VARCHAR (20),
Emp_Salary INT NOT NULL,
Emp_Bonus INT NOT NULL
);

The following SELECT query shows the data of the Employee table:

1. SELECT * FROM Employee;

Employee_Id Emp_Name Emp_City Emp_Salary Emp_Bonus

101 Anuj Ghaziabad 35000 2000
102 Tushar Lucknow 29000 3000
103 Vivek Kolkata 35000 2500
104 Shivam Goa 22000 3000

The following query shows the record of those employees from the Employee table whose Emp_Salary
is 35000:

1. SELECT * FROM Employee WHERE Emp_Salary = 35000;

Output:

Employee_Id Emp_Name Emp_City Emp_Salary Emp_Bonus

101 Anuj Ghaziabad 35000 2000
103 Vivek Kolkata 35000 2500

The following query updates the Emp_Salary of those employees whose Emp_Bonus is 3000:

1. UPDATE Employee SET Emp_Salary = 35000 WHERE Emp_Bonus = 3000;

To check the result of the above UPDATE query, write the following statement:

1. SELECT * FROM Employee;

60 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
Output:

Employee_Id Emp_Name Emp_City Emp_Salary Emp_Bonus

101 Anuj Ghaziabad 35000 2000
102 Tushar Lucknow 35000 3000
103 Vivek Kolkata 35000 2500
104 Shivam Goa 35000 3000

The following query deletes the record of those employees whose Emp_City is 'Goa':

1. DELETE FROM Employee WHERE Emp_City = 'Goa';

SQL NOT Equal Operator (!=)

This type of comparison operator selects only those data from the table which does not match with the
specified value.

This operator returns TRUE rows from the database table if the value of the column is not same as the
value specified in the query.

The syntax to access the data from the table by using the NOT Equal operator is given below:

1. SELECT * FROM Table_Name WHERE Column_Name != Value;

The syntax to update the data in the table by using the NOT Equal operator is given below:

1. UPDATE Table_Name SET Column_Name = Value WHERE Field_Name != Value;

The syntax to delete the data from the table by using the NOT Equal operator is given below:

1. DELETE FROM Table_Name WHERE Field_Name != Value;

Example of SQL NOT Equal operator

The following CREATE query creates the Cars table with four fields:

CREATE TABLE Cars

(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),

61 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
);

The following INSERT query inserts the record of cars into the Cars table:

INSERT INTO Cars (Car_Number, Car_Name, Car_Amount, Car_Price)

VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000);

The following SELECT query shows the data of the Cars table:

1. SELECT * FROM Cars;

Car_Number Car_Name Car_Amount Car_Price

2578 Creta 3 900000
9258 Audi 2 1100000
8233 Venue 6 900000
6214 Nexon 7 1000000

The following query shows the record of those cars from the Cars table whose Car_Price is not equal
to 900000:

1. SELECT * FROM Cars WHERE Car_Price != 900000;

Output:

Car_Number Car_Name Car_Amount Car_Price

9258 Audi 2 1100000
6214 Nexon 7 1000000

The following query updates the Car_Name of those cars whose Car_Number is not equal to 9258 or
whose Car_Amount is not equal to 6:

1. <p>UPDATE Cars SET Car_Name = 'Mercedes' WHERE Car_Number != 9258 OR Car_A

mount != 6;</p>

62 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
To check the result of the above UPDATE query, write the following statement:

1. SELECT * FROM Cars;

Output:

Car_Number Car_Name Car_Amount Car_Price

2578 Mercedes 3 900000
9258 Audi 2 1100000
8233 Venue 6 900000
6214 Mercedes 7 1000000

The following query deletes the record of those cars whose Car_Name is not equal to Audi.

1. DELETE FROM Cars WHERE Car_Name != 'Audi';

SQL Greater Than Operator (>)

This type of comparison operator selects, modifies, and deletes only those data from the table which
are greater than the value specified in the query.

The following syntax accesses the data from the table by using the Greater Than operator:

1. SELECT * FROM Table_Name WHERE Column_Name > Value;

The syntax to update the data in the table by using the Greater Than operator is given below:

1. UPDATE Table_Name SET Column_Name = Value WHERE Column_Name > Value;

The syntax to delete the data from the table by using the Greater Than operator is given below:

1. DELETE FROM Table_Name WHERE Field_Name > Value;

Example of SQL Greater Than Operator

The following CREATE statement creates the Cars_Details table with four fields:

CREATE TABLE Cars_Details

(
Car_Number INT PRIMARY KEY,

63 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
);

The following INSERT query inserts the record of cars into the Cars_Details table:

INSERT INTO Cars_Details (Car_Number, Car_Name, Car_Amount, Car_Price)

VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000);

The following SELECT query shows the data of the Cars_Details table:

1. SELECT * FROM Cars_Details;

Car_Number Car_Name Car_Amount Car_Price

2578 Creta 3 900000
9258 Audi 2 1100000
8233 Venue 6 900000
6214 Nexon 7 1000000

The following query shows the record of those cars whose Car_Number is greater than 6000:

1. SELECT * FROM Cars_Details WHERE Car_Number > 6000;

Output:

Car_Number Car_Name Car_Amount Car_Price

9258 Audi 2 1100000
8233 Venue 6 900000
6214 Nexon 7 100000

SQL Greater Than Equals to Operator (>=)

This type of comparison operator retrieves, modifies, and deletes only those data from the table which
are greater than and equal to the given value.

64 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
The syntax to access the data from the table by using Greater Than Equals To operator is given
below:

SELECT Column_Name1, Column_Name2, ….., Column_NameN FROM Table_Name WHERE

Column_Name >= Value;

The syntax to update the data in the table by using Greater Than Equals To operator is given
below:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name >= Value;

The syntax to delete the data from the table by using Greater Than Equals To operator is given
below:

DELETE FROM Table_Name WHERE Column_Name >= Value;

Example of SQL Greater Than Equals To Operator

The following CREATE statement creates the Student_Details table with five fields:

CREATE TABLE Student_Details

(
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR (50),
Student_MathsINT NOT NULL,
Student_English INT NOT NULL,
Student_Total_Marks INT NOT NULL
);

The following INSERT query inserts the record of student into the Student_Details table:

INSERT INTO Student_Details (Student_ID, Student_Name, Student_Maths, Student_Engli

sh, Student_Total_Marks) VALUES (201, Anuj, 30, 60, 90),
(202, Tushar, 25, 100, 125),
(203, Vivek, 30, 90, 120),
(204, Shivam, 40, 80, 120);

The following SELECT query shows the data of the Student_Details table:

1. SELECT * FROM Student_Details;

65 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 Student_Id Student_Name Student_Maths Student_English Student_Total_Marks
201 Anuj 30 60 90
202 Tushar 25 100 125
203 Vivek 30 90 120
204 Shivam 40 80 120

The following query shows the record of those students from the Student_Details table whose
Total_Marks is greater than and equal to 120.

1. SELECT * FROM Student_Details WHERE Student_Total_Marks>= 120;

Output:

Student_Id Student_Name Student_Maths Student_English Student_Total_Marks

202 Tushar 25 100 125
203 Vivek 30 90 120
204 Shivam 40 80 120

SQL Less Than Operator (<)

This type of comparison operator in SQL selects only those data from the table which are less than the
given value.

The following syntax accesses the data from the table by using the Less Than operator:

1. SELECT * FROM Table_Name WHERE Column_Name < Value;

The syntax to update the data in the table by using the Less Than operator is given below:

1. UPDATE Table_Name SET Column_Name = Value WHERE Column_Name < Value;

The syntax to delete the data from the table by using the Less Than operator is given below:

1. DELETE FROM Table_Name WHERE Field_Name < Value;

Example of SQL Less Than operator

The following CREATE statement creates the Cars_Details table with four fields:

66 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 CREATE TABLE Cars_Details
(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
);

The following INSERT query inserts the record of cars into the Cars_Details table:

INSERT INTO Cars_Details (Car_Number, Car_Name, Car_Amount, Car_Price)

VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000);

The following SELECT query shows the data of the Cars_Details table:

1. SELECT * FROM Cars_Details;

Car_Number Car_Name Car_Amount Car_Price

2578 Creta 3 900000
9258 Audi 2 1100000
8233 Venue 6 900000
6214 Nexon 7 1000000

The following query shows the record of those cars whose Car_Amount is less than 6:

1. SELECT * FROM Cars_Details WHERE Car_Amount < 6;

Output:

Car_Number Car_Name Car_Amount Car_Price

2578 Creta 3 900000
9258 Audi 2 1100000

SQL Less Than Equals to Operator (<=)

67 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
This type of comparison operator selects only those data from the table which are less than and equal
to the given value.

The syntax to access the data from the table by using the Less Than Equals To operator is given
below:

1. SELECT Column_Name1, Column_Name2, ….., Column_NameN FROM Table_Name W

HERE Column_Name <= Value;

The syntax to update the data in the table by using the Less Than Equals To operator is given
below:

1. UPDATE Table_Name SET Column_Name = Value WHERE Column_Name <= Value;

The syntax to delete the data from the table by using the Less Than Equals To operator is given
below:

1. DELETE FROM Table_Name WHERE Column_Name <= Value;

Example of SQL Less Than Equals To Operator

The following CREATE statement creates the Student_Details table with five fields:

CREATE TABLE Student_Details

(
Student_Id INT NOT NULL,
Student_Name VARCHAR (50),
Student_MathsINT NOT NULL,
Student_English INT NOT NULL,
Student_Total_Marks INT NOT NULL
);

The following INSERT query inserts the record of student into the Student_Details table:

INSERT INTO Student_Details (Student_ID, Student_Name, Student_Maths, Student_Engli

sh, Student_Total_Marks) VALUES (201, Anuj, 30, 60, 90),
(202, Tushar, 25, 100, 125),
(203, Vivek, 30, 90, 120),
(204, Shivam, 40, 80, 120);

68 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 The following SELECT query shows the data of the Student_Details table:

1. SELECT * FROM Student_Details;

Student_Id Student_Name Student_Maths Student_English Student_Total_Marks

201 Anuj 30 60 90
202 Tushar 25 100 125
203 Vivek 30 90 120
204 Shivam 40 80 120

The following query shows the record of those students from the Student_Details table whose
Student_Id is less than and equal to 202.

1. SELECT Student_Id, Student_Maths, Student_English, Student_Total_Marks FROM Stude

nt_Details WHERE Student_Id <= 202;

Output:

Student_Id Student_Maths Student_English Student_Total_Marks

201 30 60 90
202 25 100 125

d) SQL Set Operations: The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation:

1) Union
2) Union All
3) Intersect
4) Minus

1. Union Operation:
• The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
• In the union operation, all the number of datatype and columns must be same in both the tables on which
UNION operation is being applied.
• The union operation eliminates the duplicate rows from its result set.

69 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi
 Syntax:

1. SELECT column_name FROM table1

2. UNION
3. SELECT column_name FROM table2;

2. Union All: Union All operation is equal to the Union operation. It returns the set without removing duplication
and sorting the data.

Syntax:

1. SELECT column_name FROM table1

2. UNION ALL
3. SELECT column_name FROM table2;

3. Intersect:
• It is used to combine two SELECT statements. The Intersect operation returns the common rows from
both the SELECT statements.
• In the Intersect operation, the number of datatype and columns must be the same.
• It has no duplicates and it arranges the data in ascending order by default.

Syntax:

1. SELECT column_name FROM table1

2. INTERSECT
3. SELECT column_name FROM table2;

4. Minus:

• It combines the result of two SELECT statements. Minus operator is used to display the rows which are
present in the first query but absent in the second query.
• It has no duplicates and data arranged in ascending order by default.

Syntax:

1. SELECT column_name FROM table1

2. MINUS
3. SELECT column_name FROM table2;

70 | P a g e
Department of CSE, Madhav Institute of Technology & Science (MITS) Gwalior
Subject: Database Management System, Faculty: Dr. Kuldeep N. Tripathi