Using SQL WHERE Clause With Comparison Operators - ProgrammerTech
Language:

Search

Using SQL WHERE Clause With Comparison Operators

  • Share this:
Using SQL WHERE Clause With Comparison Operators

Comparison operators in the where clause

Comparative transactions in the databases are used in the condition clause in order to facilitate the process of inventorying data more, which are:

  1. The and - Between parameter limits data between two numbers.
  2. The IN parameter limits the data to a set of values.
  3. The Like parameter '%,_' limits data by matching text or characters.
  4. The IS Null parameter enumerates the null data.

We will explain all these transactions in detail one by one with some examples to illustrate the concepts.

 

Limit data between two numbers

The values ​​between, and are used to display data between two numbers or values, let's have Sead = 3000, Omer = 5000, and Reem = 2000, display the data between 1000 and 3000.

SQL> select name ,sal
	From emp
	where sal Between 1000 And 3000

The result will be

Name

Sal

Seam

3000

Reem

2000

As we noticed in the previous table, it only showed us the data that was between 1000 and 3000 and was ignored. We have an Omer value of 5000 because we only asked him between 3000 and 1000.

 

Restrict data into a set of values

IN is used to confine the data to a set of values, let's have the values ​​Sead = 101, omer = 102, and sara = 103 Show us the numbers included in 101 and 103.

SQL>select name , id
    From emp
    where id IN (101,103)

Name

Sal

Sead

101

sara

103

 

Sort data by matching text or characters Like '%,_'

This command is used to search for specific text within a text field where the text character mentioned in the condition clause is matched.

  1. The symbol % This symbol means the letter(s) '%A' used to match texts starting with the letter A regardless of the characters.
  2. The symbol 'A%' is used to match texts ending with the letter A regardless of the other letters that precede it.
  3. The symbol '%A%' is used to search for texts containing the letter A.
  4. Symbol '_' This symbol is used to match only one character.
  5. Symbol 'A_' This symbol is used when we want to search for the text of the second letter in it is A.

Example

Let us have the following table containing the data of the employees: name, profession and salary.

Name

Job

Sal

Sead

saller

3000

Omer

leader

5000

Reem

leader

2000

Required:

  • Display the names of employees whose names begin with the letter o.
  • Show employee work that ends with the letter r.
  • Offer the work of employees whose name is the second letter e.
  • Offer the work of employees whose name is the third letter e.
SQL>select name , job , sal
    From emp
    where name Like 'o%'

Name

Job

Sal

Omer

leader

5000

SQL>select name , job , sal
    From emp
    where job LIKE '%r'

Name

Job

Sal

Sead

saller

3000

Omer

leader

5000

Reem

leader

2000

The cell Job all has an r ending in it, so this table shows us.

SQL>select name , job , sal
    From emp
    where name LIKE '_e%'

Name

Job

Sead

saller

Reem

leader

SQL>select name , job , sal
    From emp
    where name LIKE '_ _e%'

Name

Job

Omer

leader

Reem

leader

 

Restriction of empty data

IS Null counts the null data from the table, and the = parameter cannot be used for null values.

Example

We have a table with employee data that we will take from our previous example and add a new employee to it.

Name

Job

Sead

saller

Omer

leader

Reem

leader

sara

 

It is required to display the name and work of the employee who has no work

SQL>select name , job , sal
    From emp
    where job Is Null

Name

Job

sara

 

 

Boolean operators in the where clause

Boolean operators are used to form more than one condition in the where clause.

  1. The AND operator is TRUE if both statements are TRUE.
  2. The OR operator is TRUE if one of the two statements is TRUE.
  3. The NOT parameter reverses the outcome of the condition if FALSE returns TRUE.

We will explain all these transactions in detail, one by one.

 

AND operator

This parameter is used to display data if both conditions are true.

Example

Display the salaries of employees whose salaries are greater than or equal to 1100 and who work as a clerk.

select sal ,job
from emp
where sal>=1100  AND  job='CLERK';

SAL

JOB

1100

CLERK

1300

CLERK

 

operand OR

This is used to display data if either condition is true.

Example

Offer the job and salary of employees whose salaries are greater than 2500 or whose job is in the administration

Select  Sal, Job from emp
where sal>2500 OR  job=' MANAGER ';

SAL

JOB

2975

MANAGER

2850

MANAGER

2450

MANAGER

3000

ANALYST

5000

PRESIDENT

3000

ANALYST

The parameter NOT

This parameter reverses the result of a conditional statement if TRUE is set to FALSE and is used to reverse other operands if it comes with them.

Example

Display the names of employees who are not working in the following jobs ('CLERK', 'MANAGER', 'ANALYST')

SELECT ename , job
FROM emp
WHERE job NOT IN ( 'CLERK' , 'MANAGER' , 'ANALYST' ) ;

SAL

JOB

ALLEN

SALESMAN

WARD

SALESMAN

MARTIN

SALESMAN

KING

PRESIDENT

TURNER

SALESMAN

Practical 1

Write the query sentence to display the names and salaries of employees whose salaries are greater than 1500, so that the result appears as follows.

SAL

JOB

ALLEN

1600

JONES

2975

BLAKE

2850

CLARK

2450

SCOTT

3000

KING

5000

FORD

3000

answer

SQL> select ename ,sal from emp
2  where sal >1500;

Practical 2

Write the query statement to display the names and date of appointment of employees appointed in 1982 so that the result appears as follows.

ENAME

HIREDATE

SCOTT

09-DEC-82

MILLER

23-JAN-82

answer

SQL> select ename ,hiredate 
2 from emp 
3 where hiredate LIKE '%82';

Practical 3

Write the query sentence to display the names, salaries and commission of the employees who take commission so that it appears as follows

ENAME

SAL

COMM

ALLEN

1600

300

TURNER

1500

0

MARTIN

1250

1400

WARD

1250

500

answer

SQL> SElect ename ,sal ,comm 
  2  from emp;

Practical 4

Write the query clause to display the names of the employees whose names have the third letter A, so that the result appears as follows

ENAME

BLAKE

CLARK

ADAMS

answer

SQL>select ename ,sal 
2 from emp 
3 where ename LIKE'__A%';

Practical 5

Write the query statement to display the names of employees whose names include the letters 'LL', so that it appears as follows.

ENAME

ALLEN

MILLER

answer

SQL>select ename ,sal 
2 from emp
3 where ename LIKE'%LL%';

Wazeer Almuliky

Wazeer Almuliky

شاب يمني من صنعاء بكلريوس تقنية معلومات مهندس قطع إلكترونية و مبرمج تطبيقات اندرويد, خبرة في قواعد البيانات و الجافا, أهتم في الاكترونيات و لغات البرمجة منها فلاتر والجافا وسي شارب