Using Logical Conditions in SQL Statement

Logical Conditions :



Operator Meaning

• OR Returns TRUE if either component condition is true
• AND Returns TRUE if both component conditions are true
• NOT Returns TRUE if the following condition is false

Using the AND Operator :

• AND requires both conditions to be true
• SELECT empno, ename, sal FROM emp WHERE sal >=1000 AND job LIKE '%MAN%' ;

Using the OR Operator :

• OR requires any condition to be true
• SELECT empno, ename, sal FROM emp WHERE sal >=1000 OR job LIKE '%MAN%' ;

Using the NOT Operator :

• SELECT empno, ename, job FROM emp WHERE sal >=1000 AND job NOT IN (‘CLERK’,’SALESMAN’) ;
  

Using Comparison Conditions in SQL Statement

Comparison Conditions :



Operator Meaning

• < Less than
• <= Less than or equal to
• >= Greater than or equal to
• > Greater than
• = Equal to
• <> Not equal to
• BETWEEN Between two values (inclusive) ...AND...
• IN(set) Match any of a list of values
• LIKE Match a character pattern
• IS NULL Is a null value

Using Comparison Conditions :

• select*from emp where sal<3000;

Using the BETWEEN Condition :

• select*from emp where sal between 1 and 2999;

Using the IN Condition :

• Use the IN membership condition to test for values in a list:
• select*from emp where mgr in(7902,7698);
  

Using the LIKE Condition :

• Use the LIKE condition to perform wildcard searches of valid search string values
• Search conditions can contain either literal characters or numbers
  • % denotes zero or many characters
  • _ denotes one character
• SELECT * FROM emp WHERE ename LIKE 'S%' ;
  

Using the NULL Conditions :

• Test for nulls with the IS NULL operator
• SELECT ename, mgr FROM emp WHERE mgr IS NULL ;