THE ASIAN SCHOOL
KINGDOM OF BAHRAIN
GRADE:9 SUBJECT: COMPUTER SCIENCE
2.2 SQL OPERATORS
An operator is a reserved word or a character that is used to query ourdatabase in a SQL
expression. To query a database using operators, we use a WHERE clause. Operators are
necessary to define a condition in SQL, as they act as a connector between two or more
conditions.
Arithmetic Operators:
Arithmetic Operators are used for performing mathematical calculationssuch as Addition,
Subtraction, Multiplication, Modulus and Division represented by the expected +, -, *(star or
asterisk), %, and / forward slash symbols respectively on the given operand values. That is,
they areused to perform mathematical operations on two expressions of the sameor different
data types of numeric data.
The following table shows the list of all the Arithmetic operators inSQL.
1
Example:
SELECT 150 + 250; Output = 400
SELECT 145 - 75 Output = 70
SELECT 17 * 5; Output = 85
SELECT 49 / 7; Output = 7.0000
Note: DUAL is the default table in MYSQL. It is one row, one columndummy table.
Relational or Comparison Operator:
Comparison Operators in MySQL are used to compare two values i.e. these operators are
used for comparing one expression with another expression. The comparison operators
determine whether the two valuesare equal or a value is greater than the other, or less than
the other. The comparison operators can be applied to numbers, strings, and dates. The
result of a comparison can be TRUE, FALSE, or NULL (When one or both the expression
contains NULL values).
The following table shows the list of all the comparison operators inSQL.
2
EXAMPLE:
Emp Id Emp Name Emp Salary
201 Abhay 30000
202 Ankit 40000
203 Bheem 30000
204 Ram 29000
205 Sumit 30000
Suppose, we want to access all the records of those employees from
the Employee_details table whose salary is 30000. Then, we have to write the following query
in the SQL database:
SELECT * FROM Employee_details WHERE Emp_Salary = 30000;
In this example, we used the SQL equal operator with WHERE clause for getting the records
of those employees whose salary is 30000.
SQL Equal Not Operator (!=)
The Equal Not Operator in SQL shows only those data that do not match the query's specified
value.This operator returns those records or rows from the database views and tables if the
value of both operands specified in the query is not matched with each other.
Let's understand the below example which explains how to execute Not Operator in SQL
query
This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.
3
Emp Id Emp Name Emp Salary
201 Abhay 45000
202 Ankit 45000
203 Bheem 30000
204 Ram 29000
205 Sumit 29000
Suppose, we want to access all the records of those employees from
the Employee_details table whose salary is not 45000. Then, we have to write the following
query in the SQL database:
SELECT * FROM Employee_details WHERE Emp_Salary != 45000;
In this example, we used the SQL equal not operator with WHERE clause for getting the
records of those employees whose salary is not 45000.
SQL Greater Than Operator (>)
The Greater Than Operator in SQL shows only those data which are greater than the value
of the right-hand operand.
Let's understand the below example which explains how to execute Greater
ThanOperator (>) in SQL query:
This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.
Emp Id Emp Name Emp Salary
201 Abhay 45000
202 Ankit 45000
203 Bheem 30000
4
204 Ram 29000
205 Sumit 29000
Suppose, we want to access all the records of those employees from
the Employee_details table whose employee id is greater than 202. Then, we have to write the
following query in the SQL database:
SELECT * FROM Employee details WHERE Emp_Id > 202;
Here, SQL greater than operator displays the records of those employees from the above table
whose Employee Id is greater than 202.
SQL Greater Than Equals to Operator (>=)
The Greater Than Equals to Operator in SQL shows those data from the table which are
greater than and equal to the value of the right-hand operand.
Let's understand the below example which explains how to execute greater than equals
to the operator (>=) in SQL query:
This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.
Emp Id Emp Name Emp Salary
201 Abhay 45000
202 Ankit 45000
203 Bheem 30000
204 Ram 29000
205 Sumit 29000
Suppose, we want to access all the records of those employees from
the Employee_details table whose employee id is greater than and equals to 202. For this, we
have to write the following query in the SQL database:
SELECT * FROM Employee_details WHERE Emp_Id >= 202;
5
Here,'SQL greater than equals to operator' with WHERE clause displays the rows of those
employees from the table whose Employee Id is greater than and equals to 202.
SQL Less Than Operator (<)
The Less Than Operator in SQL shows only those data from the database tables which are
less than the value of the right-side operand.
This comparison operator checks that the left side operand is lesser than the right side operand.
If the condition becomes true, then this operator in SQL displays the data which is less than the
value of the right-side operand.
Let's understand the below example which explains how to execute less than operator (<)
in SQL query:
This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.
Emp Id Emp Name Emp Salary
201 Abhay 45000
202 Ankit 45000
203 Bheem 30000
204 Ram 29000
205 Sumit 29000
Suppose, we want to access all the records of those employees from
the Employee_details table whose employee id is less than 204. For this, we have to write the
following query in the SQL database:
SELECT * FROM Employee_details WHERE Emp_Id < 204;
Here,SQL less than operator with WHERE clause displays the records of those employees
from the above table whose Employee Id is less than 204.
SQL Less Than Equals to Operator (<=)
The Less Than Equals to Operator in SQL shows those data from the table which are lesser
and equal to the value of the right-side operand.
6
This comparison operator checks that the left side operand is lesser and equal to the right side
operand.
Let's understand the below example which explains how to execute less than equals to the
operator (<=) in SQL query:
This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.
mp Id Emp Name Emp Salary
201 Abhay 45000
202 Ankit 45000
203 Bheem 30000
204 Ram 29000
205 Sumit 29000
Suppose, we want to access all the records of those employees from
the Employee_details table whose employee id is less and equals 203. For this, we have to
write the following query in the SQL database:
SELECT * FROM Employee_details WHERE Emp_Id <= 203;
Here, SQL less than equals to the operator with WHERE clause displays the rows of those
employees from the table whose Employee Id is less than and equals 202.
Logical Operator:
If you want to combine more than one condition, then we need to use theLogical Operators
in MySQL. The Logical Operators are used to check for the truthness of some conditions.
Logical operators return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.
In MySQL, there are three Logical Operators available. They are as follows:
a) AND: TRUE if both Boolean expressions are TRUE.
b) OR: TRUE if one of the Boolean expressions is TRUE.
c) NOT: Reverses the value of any other Boolean operator.
7
a) Logical AND Operator in MySQL
The Logical AND operator in MySQL compares two conditions and returns TRUE if both
of the conditions are TRUE and returns FALSE when either is FALSE. If you want to select
rows that must satisfy all thegiven conditions, then in such cases you need to use the AND
operator.
Syntax to use AND Operator in MySQL:
Example:
SELECT * FROM employee WHERE department = ‘IT’ AND age = 28;
b)Logical OR Operator in MySQL
The logical OR operator is useful to create such compound conditions. The OR operator is
useful to add multiple conditions in a single SQL statement. It displays the data rows if any
one of the multiple conditionsis TRUE. If all the conditions are false the SQL statement
won’t return any result set.
The following is the syntax to use the logical OR operator in MySQL.
Example:
SELECT * FROM employee WHERE age = 25 OR age = 26;
c)Logical NOT Operator in MySQL:
The Logical NOT Operator in MySQL takes a single Boolean as an argument and changes
its value from false to true or from true to false. Ifwe want to select rows that do not satisfy
a condition, then you need to use the logical NOT operator. NOT results in the reverse of a
condition. That is, if a condition is satisfied, then the row is not returned.
8
Example:
SELECT * FROM Employee WHERE NOT Department = ‘IT’;
For example, if we want to fetch the Employees who do not belong to the IT Department,
then we need to use the NOT Operator as shown inthe above SQL query.
Special Operators
a) IN Operator in MySQL:
This operator selects values that match any value in the given list. The SQL IN condition is
used to help reduce the need for multiple OR conditions in a select statement.
Example:
SELECT * FROM Employee WHERE Department IN (‘IT’, ‘Finance’);
Once you execute the above SQL Query, you will get the following resultset which includes
only the IT and Finance departments employees.
b) BETWEEN Operator in MySQL:
A between operator defines a range of values that the column value mustfall within for the
condition to become true.
Example:
SELECT * FROM Employee WHERE Age BETWEEN 25 AND 27;
Once you execute the above SQL Query, you will get the following resultset which includes
only the employees whose age is between 25 and 27.
c) Like Operator in MYSQL:
9
The LIKE operator is used in a WHERE clause to search for a specified pattern in a
column.
There are two wildcards often used in conjunction withthe LIKE operator:
• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one, single character
The percent sign and the underscore can also be used in combinations!
LIKE Syntax
SELECT column1, column2, ...FROM table _name WHERE column
LIKE pattern;
Like Operator Using % in MySQL with Examples
The percent (%) means it will search for zero or more characters after the“%”. Here ‘%’ is
the wildcard character that we will use before or after characters to search for the required
matched string of characters.
Example: To Fetch all the employees whose names start with P. The following SQL query
will return all employees from the employee table whose name starts with the character ‘P’
followed by any string of characters.
SELECT * FROM Employee WHERE Name LIKE ‘P%’;
Like Operator Using _ (underscore) Wildcard Character in MySQL
The underscore (_) means it will exactly search for one character after the“_”.
SELECT * FROM Employee WHERE City LIKE ‘MUMBA_’;Once you execute the above
query, you will get the following output. Here the employees with city value contain MUMBA
and are of only 6 lettersare returned in the result set.
Here are some examples showing different LIKE operators with '%' and'_' wildcards:
10
LIKE Operator Description
WHERE Name LIKE 'a%' Finds any values that start with "a"
WHERE Name LIKE '%a' Finds any values that end with "a"
WHERE Name LIKE '%or%' Finds any values that have "or" in any position
WHERE Name LIKE '_r%' Finds any values that have "r" in the second position
WHERE Name LIKE 'a_%' Finds any values that start with "a" and are at least 2characters
in length
WHERE Name LIKE 'a %' Finds any values that start with "a" and are at least 3characters in
length
Reference Links:
https://www.w3schools.com/mysql/mysql_operators.asp
https://www.youtube.com/watch?v=tmAchsH3tGU
https://www.youtube.com/watch?v=VbwQT9s_CO0
---------------------------------------
11
