The Basic Logical Operators Associated With SQL

1.    Introduction

The intention of this session is to make you aware of the strengths of the various logical operators used while writing various, sql (syntax) queries.It is human psychology, to neglect old techniques when new ones, with more facilities are introduced. To stick with old techniques when new ones are introduced, is again a drawback because you tend to loose the benefits offered by the new techniques, at the same time, if you forget old techniques. There might be a situation, where newer techniques, might not help you, find a solution, So when newer techniques fail to answer your requirements, then we will have to look at old ones to rescue us.

So friends, the old saying  ,old is  gold, though it might not glitter but one always has to have respect and faith in old techniques, with time I will definitely show that new facilities, some times are not so flexible while writing complicated queries. Under such conditions one has to make use of the older versions of technology.

Have a nice time while you browse through this session:

Let us first understand what Oracle actually means

Oracle

•         A priest or priestess ( In Ancient Greece or Rome) who acted as a channel for advice or prophecy from the gods and his or her authority final.
•         An authority which was always correct.
•         Oracle is an authority in a church who has answers for every question, posed by the confessor.
•         In Information Technology, (In Todays era) it is an acronym and when expanded : Oak Ridge Automatic Computer Logical Engine
2.    Logical Operators are Broadly classified as:
·        Logical operators that compare against a single value.
·        Logical operators that compare against a list of values.
·        Pattern Matching Logical Operators.
·        Logical Binding Operators.

Let us observe employee data base, shown below : the name of table is emp

EMPNO   ENAME    JOB           MGR        HIREDATE  SAL  COMM   DEPTNO

7369      SMITH     CLERK        7902     17-DEC-80     800         20     20

7499      ALLEN      SALESMAN   7698     20-FEB-81     1600      300   30

7521      WARD      SALESMAN   7698     22-FEB-81     1250      500   30

7566      JONES     MANAGER    7839     02-APR-81     2975      20     20

7654      MARTIN    SALESMAN   7698     28-SEP-81     1250      1400 30

7698      BLAKE     MANAGER    7839     01-MAY-81    2850      30     30

7782      CLARK     MANAGER    7839     09-JUN-81     2450      10     10

7788      SCOTT     ANALYST     7566     19-APR-87     3000      20     20

7839      KING       PRESIDENT               17-NOV-81   5000      10     10

7844      TURNER  SALESMAN   7698     08-SEP-81     1500      0      30

7876      ADAMS     CLERK         7788    23-MAY-87    1100      20     20

7900      JAMES      CLERK         7698    03-DEC-81     950         30     30

7902      FORD       ANALYST      7566    03-DEC-81     3000      20     20

7934      MILLER    CLERK         7782    23-JAN-82     1300      10     10

You can find the above table in oracle package, found in schema scott/tiger.

2.1  Logical operators that compare (test) against a single value.

Logical operators, shown in the table below, are used to test compare (against) single values.

 

Operator                     Description

     =                           Equal to

     >                           Greater than

     >=                        Greater than or equal to

     <                           Lesser than

     <=                        Lesser than or equal to

< > or ^ =  or ! =       Not equal to

Let us exercise some examples and observe the results fetched from emp

# Write a query to find all those employees whose salary is greater than 1000.

       select * from emp where sal >1000;

# Write a query to find all those employees whose salary is lesser than 2000;

       select * from emp where sal < 1000;

# Write a query to find all those employees whose salary is not equal to 1500;

       select * from emp where sal = 1500;

2.2   Logical operators that compare (test) against a list of values.

Logical operators, shown in the table below, are used to compare (test) against list of values.

Operator                                                            Description

IN(value1,value2,value3,…….,valueN) Returns those results which 

                                                                          satisfy the values listed in the 

                                                                          list.

NOT IN(value1,value2,value3,…….,valueN)     Returns those results 

                                                                          which satisfy the values not, listed in the  

                                                                          list.

BETWEEN value1 AND value2                             Returns those results which are equal to  

                                                                          value1 and value2 and any thing between  

                                                                          those values.

NOT BETWEEN value1 AND value2                      Returns those results which are less than   

                                                                          value1 and greater than value2.

 

Let us exercise some examples and observe the results fetched from emp

# Write a query to find all those employees whose salary is less than 1000 and greater than 2000.

     select * from emp where sal not between 1000 and 2000;

# Write a query to find all those employees whose salary is  equal to 800 or 950 or 1300

             select * from emp where sal  IN(800,950,1300);

# Write a query to find all those employees whose salary is not equal to 1000 or 950 or 1300.

Select * From emp where sal not IN(1000,950,1300);

# Write a query to find all those employees details whose salary is between 1000 and 2000.

Select * from emp where sal BETWEEN 1000 AND 2000;

2.3   Logical Binding Operators.

Binding operators are used to test two or more conditions in a query.

Operator                                         Description

AND                           Study the truth table of AND below, to know how AND  

                                  binding operator works.

OR                             Study the truth table of OR below, to know how OR binding  

                                  operator works.

 

Truth Table of AND

Consider a dummy query below

Select * from where expressionA AND expressionB.

ExpressionA       ExpressionB       Result(out put)

True                    True                    True

True                    False                    False

False                    True                    False

False                    False                    False

Here expressionA and expressionB are conditions in a query. If any of these expression is satisfied then it is considered to be true and if not satisfied it is considered false.

In the above table, consider the first case, if ExpressionA is true and ExpressionB is also true then the query fetches results from the table. In rest all cases the table does not return any results.

Let’s see some examples and observe the results fetched from emp

# write a query to display those employee details who belong to whose job is manager and salary greater than 2000.

select * From emp where job=’MANAGER’ and sal>2000;

# write a query to display those employee details who belong to whose job is manager and salary greater than 2000 and less than 3000.

select * From emp where job = ‘MANAGER’ and (sal>2000 and sal<3000);

Truth Table of OR

Consider a dummy query below

Select * from where expressionA OR expressionB.

ExpressionA       ExpressionB       Result(out put)

     True                 True                  True

     True                  False                  True

     False                 True                  True

     False                 False                  False

Here expressionA and expressionB are conditions in a query. If any of these expression is satisfied then it is considered to be true and if not satisfied it is considered false.

In the above table, consider the first three cases’, if either ExpressionA is true or ExpressionB is true or both of the expressions are true then the query fetches results from the table. In last case the table does not return any result.

Let’s see some examples and observe the results fetched from emp

# write a query to display details of those employees whose job is either manager of clerk.

select * from emp where job =’MANAGER’ OR job=’CLERK’;

# write a query to display details of those employees’, whose job is manager and salary less than or equal to 2000 and greater than or equal to 2500.

select * From emp where job =’MANAGER’ and (sal<=2000 or sal>=2500);

Among AND / OR,which has higher priority?

AND takes higher precedence over OR: consider the generalized query

where expressionA AND (expressionB OR expressionC);

Now lets analyze with the help of a TRUTH TABLE : AND operation is done with the result of expressionB OR expressionC

                expressionA        expressionB OR expressionC       result

                   true                           true                                          true

                    true                           false                                            false   

                    false                          true                                    false

                   false                          false                                         false

compare the above two truth tables and find the difference by yourself.

2.5   Pattern Matching Operators.

Consider the Climate table shown

CityName              MinTemp MaxTemp           DatedOn

Secunderabad          27                      33      12-feb-2008

Hyderabad                24                      35      23-nov-2008

Ahmedabad               34                      45      26-mar-2008

Gaziabad                   23                      34      24-sep-2008

Some times we would like to fetch that data which might be of a particular pattern. For example Hyderabad, secunderabad, ahmedabad, gaziabad etc, here the common matching pattern is names of cities ending with BAD. Now, if I want to get details of those cities whose names end with bad then I should write a query that would not care for those characters, prefixed to BAD, in the names of the cities. How do I achieve this?

Fortunately oracle provides a operator called LIKE which when associated with percent sign %, called a wild card would suppress all the characters represented by it(%).

Lets’ understand with the help of a query.

Select * from climate where CityName Like ‘D’;

Characters to the left of BAD are suppressed using the wild card (percent %) and the results are fetched based on the BAD. execute this query and check the results for your self.

# Write a query to get details of temperatures of those cities whose names end with     

    BAD.

        Select * from climate where CityName Like ‘D’;

Remember

ü      % would suppress any number of characters (even one lakh character).

ü      % can be used any number of times with LIKE operator.

Consider slightly different situation. Now think of words which would almost look alike but for a minute difference, for example words BED,BUD,BID. Now imagine you have a table called dictionary in your database and you want to retrieve all the words, with three alphabets, beginning with B and ending with D. How do I achieve? Fortunately oracle provides another special character called position marker (represented by a under score “_”,an underscore character in the keyboard), which when used along with LIKE operator would bring the required results.

 # Write a query to get details of words having three alphabets, beginning with B and Ending with D.

        Select * from dictionary where word LIKE ‘B_D’;

Remember

ü      Position marker will suppress only one character.

ü      To suppress two characters, two position markers have to be used.

Now consider a situation where words beginning with b and ending with n and having any two alphabets, like BEEN,BRED,BEED,BEAN etc are supposed to be fetched from dictionary database. The following query would help bringing the results

Select * from dictionary where word LIKE ‘B__D’;

Here in the above query two position markers are used.

NOTE

Ø      any number of % (wild card) and  position marker(an underscore character in the keyboard).
Ø      One can also use both of them as a combination and any number of times in a condition.