Monday, October 22, 2012

Functions

ABS Function:
ABS returns the absolute value. The output is positive even if the input is negative:
  ABS(x) = x if x>=0
 ABS(x) = -x if x<0 data-blogger-escaped-pre="pre">    ABS can be useful for testing values that are "close". For example   this query shows each country that has area that is roughly   70 thousand. The value 70000 is the target value, 500 is the "tolerance"   so the test ABS(area-70000)<500 data-blogger-escaped-69500="69500" data-blogger-escaped-70000-500="70000-500" data-blogger-escaped-70000="70000" data-blogger-escaped-and="and" data-blogger-escaped-area="area" data-blogger-escaped-between="between" data-blogger-escaped-br="br" data-blogger-escaped-is="is" data-blogger-escaped-tests="tests" data-blogger-escaped-that="that" data-blogger-escaped-the="the">
SELECT name, area FROM bbc
 WHERE ABS(area-70000)<500 data-blogger-escaped-br="br">Result:
name area
Georgia 69700
Ireland 70182

CASE

CASE allows you to return different values under different conditions.
If there no conditions match (and there is not ELSE) then NULL is returned.
  CASE WHEN condition1 THEN value1
      WHEN condition2 THEN value2 
      ELSE def_value
 END

SELECT name, population
     ,CASE WHEN population<1000000 data-blogger-escaped-br="br" data-blogger-escaped-small="small" data-blogger-escaped-then="then">           WHEN population<10000000 data-blogger-escaped-br="br" data-blogger-escaped-medium="medium" data-blogger-escaped-then="then">           ELSE 'large'
      END
AS result

 FROM bbc where population like '3%' order by population asc ;

this query will list name, population and the result of CASE. It will consider only population
starting with number 3 and will order the results by ascending order of population.

Result:
name population result
Monaco 32000 small
Liechtenstein 34000 small
Bahamas 321000 small
The Maldives 338000 small
Brunei 374000 small
Malta 397000 small
Armenia 3000000 medium


 

No comments: