SQL Comparision Operator

SELECT * FROM TABLENAME WHERE column-name = value;

UPDATE TABLENAME SET column_name = value WHERE column_name = value;

DELETE FROM TABLENAME WHERE column_name = value;

SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'ORACLE';

As we can see in the output, only oracle department employees’ records are displayed.

Example 2: Write a query to display the employee records from the employees' table whose employee department is 'Oracle' or City is 'Pune'.

SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'ORACLE' OR CITY = 'PUNE';

From the above query, we are fetching the employee data from the employees' table where the employee department is equal to the oracle department or City is equal to Pune city. The first search will go for the Oracle department as records are done with the Oracle department, then the query will go for Pune city. Here we used multiple Equal operators using OR operator.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2002	BHAVESH	JAIN	65500	PUNE	FMW			2
3001	PRANOTI	SHENDE	55500	PUNE	JAVA			3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1

As we can see in the output, only oracle department employees' or Pune city records are displayed.

Example 3: Write a query to display the employee records from the employees' table whose City is 'Pune' and Salary is 65500.

SELECT * FROM EMPLOYEES WHERE CITY = 'PUNE'AND SALARY = 65500;

From the above query, we are fetching the employee data from the employees' table where employee city is equal to Pune city, and employee salary is 65500. Here we used multiple Equal operators using AND operator. AND the operator will return only those records whose both conditions are true.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
2002	BHAVESH	JAIN	65500	PUNE	FMW			2

As we can see in the output, only Pune city and 65500 salary records are displayed.

Example 4: Write a query to update the employee records from the employees’ table whose employee department is ‘FMW’.

UPDATE EMPLOYEES SET SALARY = 55500 WHERE DEPARTMENT = 'FMW';

We update the employee records from the above query from the employees' table whose department is FMW.

To cross-check whether the records are updated or not, we will run the SELECT statement.

SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'FMW';

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1003	NIKHIL	VANI	55500	JAIPUR	FMW			2
2002	BHAVESH	JAIN	55500	PUNE	FMW			2
3002	ANUJA	WANRE	55500	JAIPUR	FMW			2

As we can see, the records are updated successfully; whose department name is FMW.

Example 5: Write a query to update the employee records from the employees' table whose employee city is 'Pune' and the department is 'Java'.

UPDATE EMPLOYEES SET SALARY = 60000 WHERE CITY = 'PUNE' AND DEPARTMENT = 'JAVA';

From the above query, we update the employee records from the employees' table whose CityCity is Pune and department is Java.

To cross-check whether the records are updated or not, we will run the SELECT statement.

SELECT * FROM EMPLOYEES WHERE CITY = 'PUNE' AND DEPARTMENT = 'JAVA';

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
3001	PRANOTI	SHENDE	60000	PUNE	JAVA			3

Example 6: Write a query to delete the employee records from the employees’ table whose employee id is 5001.

DELETE FROM EMPLOYEES WHERE EMPLOYEEID = 5001;

From the above query, we are deleting the employee records from the employees’ table whose employee id is 5001.

To cross-check whether the record is deleted or not, we will run the SELECT statement.

SELECT * FROM EMPLOYEES;

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#			5
1003	NIKHIL	VANI	55500	JAIPUR	FMW			2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2002	BHAVESH	JAIN	55500	PUNE	FMW			2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3001	PRANOTI	SHENDE	60000	PUNE	JAVA			3
3002	ANUJA	WANRE	55500	JAIPUR	FMW			2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA			3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1

As we can see, the record of employee id 5001 is deleted successfully.

SQL Not Equal Operator

The Equal Not Operator displays only those records whose values are not similar to the query's specified value.

SQL Not Equal operator returns those data from the table if the value doesn’t meet the criteria in the given query.

Syntax to access data from the table using the NOT EQUAL operator

SELECT * FROM TABLENAME WHERE column_name != value;

Syntax to update data from the table using the NOT EQUAL operator

UPDATE TABLENAME SET column_name = value WHERE column_name != value;

Syntax to delete data from the table using the NOT EQUAL operator

DELETE FROM TABLENAME WHERE column_name != value;

Let's understand the below example, which explains how to execute NOT Equal Operator in SQL query:

Consider the existing tables, which have the following records:

Table: Employees

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#			5
1003	NIKHIL	VANI	55500	JAIPUR	FMW			2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2002	BHAVESH	JAIN	55500	PUNE	FMW			2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3001	PRANOTI	SHENDE	60000	PUNE	JAVA			3
3002	ANUJA	WANRE	55500	JAIPUR	FMW			2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA			3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING			4

Example 1: Write a query to display the employee records from the employees’ table whose employee salary is not equal to 55500.

SELECT * FROM EMPLOYEES WHERE SALARY != 55500;

From the above query, we are fetching the employee data from the employees' table where the employee salary is not equal to 55500.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#			5
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3001	PRANOTI	SHENDE	60000	PUNE	JAVA			3
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA			3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1

As we can see in the output, only those records are displayed whose Salary is not equal to 55500.

Example 2: Write a query to display the employee records from the employees’ table whose employee city is not equal to Pune or department is not equal to FMW.

SELECT * FROM EMPLOYEES WHERE CITY != 'PUNE' AND DEPARTMENT != 'FMW';

From the above query, we are fetching the employee data from the employees’ table where employee city is not equal to Pune and Department is not equal to FMW.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1002	VAIBHAV	SHARMA	60000	NOIDA	C#			5
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA			3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING			4

As we can see in the output, only those records are displayed whose employee city is not equal to Pune and Department is not equal to FMW.

Example 3: Write a query to update the employee records from the employees’ table whose employee city is not equal to ‘Mumbai’ and department is not equal to 'Oracle’.

UPDATE EMPLOYEES SET SALARY = SALARY * 1.2 WHERE CITY != 'MUMBAI' AND DEPARTMENT != 'ORACLE';

From the above query, we are updating the employee records from the employees' table whose City is not equal to Mumbai and department is not equal to Oracle.

To cross-check whether the records are updated or not, we will run the SELECT statement.

SELECT * FROM EMPLOYEES WHERE CITY != 'MUMBAI' AND DEPARTMENT != 'ORACLE';

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1002	VAIBHAV	SHARMA	72000	NOIDA	C#			5
1003	NIKHIL	VANI	66600	JAIPUR	FMW			2
2002	BHAVESH	JAIN	66600	PUNE	FMW			2
3001	PRANOTI	SHENDE	72000	PUNE	JAVA			3
3002	ANUJA	WANRE	66600	JAIPUR	FMW			2
4002	ASHWINI	BAGHAT	65400	NOIDA	JAVA			3
5001	ARCHIT	SHARMA	66600	DELHI	TESTING			4

As we can see, the records are updated successfully, whose CityCity is not equal to Mumbai and the department is not equal to Oracle.

Example 4: Write a query to delete the employee records from the employees’ table whose employee city is not equal to ‘Pune’ and Manager Id is not equal to 2.

DELETE FROM EMPLOYEES WHERE CITY != 'PUNE' OR MANAGERID != 2;

From the above query, we are deleting the employee records from the employees’ table whose CityCity is not equal to 'Pune' or Manager id is not equal to 2.

To cross-check whether the record is deleted or not, we will run the SELECT statement.

SELECT * FROM EMPLOYEES;

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
2002	BHAVESH	JAIN	66600	PUNE	FMW			2

As we can see, the employee records whose CityCity is not equal to Pune or manager id is not equal to 2 are deleted successfully.

SQL Less Than Operator

This operator query displays only those records from the table which are less than from the right side of the SQL query.

The Less Than comparison operator checks in the query if the left-side value is lesser than the right-side value. If the condition meets the satisfied criteria, this operator displays the right side values.

Syntax to access data from the table using the LESS THAN operator

SELECT * FROM TABLENAME WHERE column_name < value;

Syntax to update data from the table using the LESS THAN operator

UPDATE TABLENAME SET column_name = value WHERE column_name < value;

Syntax to delete data from the table using the LESS THAN operator

DELETE FROM TABLENAME WHERE column_name < value;

Let's understand the below example, which explains how to execute LESS THAN Operator in SQL query:

Consider the existing tables, which have the following records:

Table: Emp

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#			5
1003	NIKHIL	VANI	50500	JAIPUR	FMW			2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2002	BHAVESH	JAIN	65500	PUNE	FMW			2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA			3
3002	ANUJA	WANRE	50500	JAIPUR	FMW			2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA			3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING			4

Example 1: Write a query to display the employee records from the emp table whose employee salary is less than 55500.

SELECT * FROM EMP WHERE SALARY < 55500;

From the above query, we fetched the employees' record from the emp table where employee salary is less than 55500.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1003	NIKHIL	VANI	50500	JAIPUR	FMW			2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3002	ANUJA	WANRE	50500	JAIPUR	FMW			2
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3

As we can see, only those records are displayed whose employee salary is less than 55500.

Example 2: Write a query to display the employee records from the emp table whose employee-manager id is less than 3.

SELECT * FROM EMP WHERE MANAGERId < 3;

From the above query, we are fetching the employees' records from the emp table where the employee-manager id is less than 3.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1003	NIKHIL	VANI	50500	JAIPUR	FMW			2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2002	BHAVESH	JAIN	65500	PUNE	FMW			2
3002	ANUJA	WANRE	50500	JAIPUR	FMW			2
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1

As we can see, only those records are displayed whose employee-manager id is less than 3.

SQL Less Than Equals to Operator

Less Than Equals to operator query displays only those data from the table which are less than and Equal to the right-side in the SQL query.

The Less Than Equal to comparison operator check in the query if the left-side value is lesser than an Equal to the right-side value. If the condition meets the satisfied criteria, this operator displays those lesser than and Equal to the right side values.

Syntax to access data from the table using the LESS THAN EQUALS to the operator

SELECT * FROM TABLENAME WHERE column_name <= value;

Syntax to update data from the table using the LESS THAN EQUALS to the operator

UPDATE TABLENAME SET column_name = value WHERE column_name <= value;

Syntax to delete data from the table using the LESS THAN EQUALS to the operator

DELETE FROM TABLENAME WHERE column_name <= value;

Let's understand the below example, which explains how to execute LESS THAN EQUALS to Operator in SQL query:

Consider the existing tables, which have the following records:

Table: Emp

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#			5
1003	NIKHIL	VANI	50500	JAIPUR	FMW			2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2002	BHAVESH	JAIN	65500	PUNE	FMW			2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA			3
3002	ANUJA	WANRE	50500	JAIPUR	FMW			2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA			3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING			4

Example 1: Write a query to display the employee records from the emp table whose employee salary is less than equals to 58000.

SELECT * FROM EMP WHERE SALARY <= 58000;

From the above query, we are fetching the employees' records from the emp table where employee salary is less than equal to 58000.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1003	NIKHIL	VANI	50500	JAIPUR	FMW			2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA			3
3002	ANUJA	WANRE	50500	JAIPUR	FMW			2
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3
5001	ARCHIT	SHARMA	55500	DELHI	TESTING			4

As we can see, only those records are displayed whose employee salary is less than equals to 58000.

Example 2: Write a query to display the employee records from the emp table whose employee-manager id is less than equal to 2.

SELECT * FROM EMP WHERE MANAGERId <= 2;

From the above query, we are fetching the employees' records from the emp table where the employee-manager id is less than equal to 2.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1003	NIKHIL	VANI	50500	JAIPUR	FMW			2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2002	BHAVESH	JAIN	65500	PUNE	FMW			2
3002	ANUJA	WANRE	50500	JAIPUR	FMW			2
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1

As we can see, only those records are displayed whose employee-manager id is less than equal to 2.

SQL Greater Than Operator

This operator query displays only those records from the table which are greater than from the right side of the SQL query.

The query's Greater Than comparison operator checks if the left-side value is greater than the right-side value. If the condition met the satisfied criteria, this operator displays those greater than the right-side values.

Syntax to access data from the table using the GREATER THAN operator

SELECT * FROM TABLENAME WHERE column_name > value;

Syntax to update data from the table using the GREATER THAN operator

UPDATE TABLENAME SET column_name = value WHERE column_name > value;

Syntax to delete data from the table using the GREATER THAN to operator

DELETE FROM TABLENAME WHERE column_name > value;

Let's understand the below example, which explains how to execute GREATER THAN Operator in SQL query:

Consider the existing tables, which have the following records:

Table: Emp

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#			5
1003	NIKHIL	VANI	50500	JAIPUR	FMW			2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE			1
2002	BHAVESH	JAIN	65500	PUNE	FMW			2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#			5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA			3
3002	ANUJA	WANRE	50500	JAIPUR	FMW			2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA			3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA			3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING			4

Example 1: Write a query to display the employee records from the emp table whose employee salary is greater than 60000.

SELECT * FROM EMP WHERE SALARY > 60000;

From the above query, we fetched the employees' records from the emp table where employee salary is greater than 60000.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
2002	BHAVESH	JAIN	65500	PUNE	FMW			2
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4

As we can see, only those records are displayed whose employee salary is greater than 60000.

SQL Greater Than Equals to Operator

This operator query displays only those records from the table greater than and Equal to the right-side in the SQL query.

The query checks the Greater Than Equal to comparison operator if the left-side value is greater than and Equal to the right-side value. If the condition meets the criteria, this operator displays those values greater than and Equal to the right-side values.

Syntax to access data from the table using the GREATER THAN EQUALS TO operator

SELECT * FROM TABLENAME WHERE column_name >= value;

Syntax to update data from the table using the GREATER THAN EQUALS TO operator

UPDATE TABLENAME SET column_name = value WHERE column_name >= value;

Syntax to delete data from the table using the GREATER THAN EQUALS TO operator

DELETE FROM TABLENAME WHERE column_name >= value;

Example 1: Write a query to display the employee records from the emp table whose employee salary is greater than equals to 60000.

SELECT * FROM EMP WHERE SALARY >= 60000;

From the above query, we are fetching the employees’ record from the emp table where employee salary is greater than equals to 60000.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT			MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE			1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#			5
2002	BHAVESH	JAIN	65500	PUNE	FMW			2
4001	RAJESH	GOUD	60500	MUMBAI	TESTING			4
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE			1

As we can see, only those records are displayed whose employee salary is greater than equals to 60000.