Tutorial Addendum on SQL - Operations and Expressions

Predicate Operations

    

Predicate operations are operations that can be evaluated to accord a action

    

of true or false:

    

1. "Comparison" Predicates: Compares two ethics of the aforementioned blazon according to

    

a predefined order. There are 6 allegory operations:

    

    
• Equal: amount = value
  
      



    
• Not Equal: amount <> value
  
      



    
• Less Than: amount < value
  
      



    
• Greater Than: amount > value
  
      



    
• Less Than or Equal: amount <= value
  
      



    
• Greater Than or Equal: amount >= value
  
      



    

2. "Between" Predicates: Analysis a accustomed amount adjoin a amount range.

    

There are 2 "between" operations:

    

    
• Between: amount Amid amount AND value
  
      



    
• Not Between: amount NOT Amid amount AND value
  
      



    

3. "In" Predicates: Analysis a accustomed amount adjoin a account of values.

    

There are 2 "in" operations:

    

    
• In: amount IN (value, value, ..., value)
  
      



    
• Not In: amount NOT IN (value, value, ..., value)
  
      



    

4. "Like" Predicates: Analysis a accustomed appearance cord adjoin a character

    

pattern. By absence "Like" operations are not case sensitive.

    

There are 2 "Like" operations:

    

    
• Like: cord LIKE pattern
  
      



    
• Not Like: cord NOT LIKE pattern
  
      



    

"pattern" is a appearance cord with wildcard characters and escape sequences:

    

    
• "_": Apery one and alone one individual character.
  
      



    
• "%": Apery zero, one and some characters.
  
      



    
• "\_": Apery appearance "_".
  
      



    
• "\%": Apery appearance "%".
  
      



    

Examples:

    

 

    
 abc  LIKE  Abc  -- true

    
 3.14  LIKE  _.__  -- true

    
 3.14159  LIKE  3.%  -- true

    
 _yang  LIKE  \_yang  -- true

    
 4.75%  LIKE  _.__\%  -- true

    

5. "Null" Predicates: Analysis a accustomed amount adjoin absent value.

    

There are 2 "Null" operations:

    

    
• Null: amount IS NULL
  
      



    
• Not Null: amount IS NOT NULL
  
      



    

Examples:

    

 

    
1 IS Absent -- false

    
 a  IS NOT Absent -- true

    
NULL IS Absent -- true

    
NULL IS NOT Absent -- false

    

Date and Time Operations

    

The alotof frequently acclimated date and time operations are:

    

    
• data_time_value + interval_value
  
      



    
• data_time_value - interval_value
  
      



    
• interval_value + data_time_value
  
      



    

Examples:

    

 

    
DATE  1999-01-01  + Breach  3  MONTH

    
DATE  2000-03-01  - Breach  1  DAY -- watch out bound years!

    
INTERVAL  48  HOUR + TIMESTAMP  1999-01-01 00:00:00