Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

ON SQL functions




Building examples of using SQL functions supported by Oracle
Check the post here (in construction)

SQL Functions (11gR2)





Single-row F Aggregate F Analytic F
Object Reference F Model F OLAP F


Single-row functions

  •  Numeric functions  (26, 11gR2)
  • ABS
  • ACOS
  •  ASIN
  • ATAN
  • ATAN2
  • BITAND
  • CEIL
  • COS
  • COSH
  • EXP
  • FLOOR
  • LN
  • LOG
  • MOD
  • NANVL
  • POWER
  • REMAINDER
  • ROUND (number)
  • SIGN
  • SIN
  • SINH
  • SQRT
  • TAN
  • TANH
  • TRUNC (number)
  • WIDTH BUCKET 
  •  Character F returning character values (22, 11gR2)
  • CHR
  • CONCAT
  • INITCAP
  • LOWER
  • LPAD
  • LTRIM
  • NCHR
  • NLS_INITCAP
  • NLS_LOWER
  • NLS_UPPER
  • NLSSORT
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • REPLACE
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TREAT
  • TRIM
  • UPPER
  •  Character F returning number values (5, 11gR2)
  • ASCII
  • INSTR
  • LENGTH
  • REGEXP_COUNT
  • REGEXP_INSTR

  •  NLS Character F (3, 11gR2)
  • NLS_CHARSET_DECL_LEN
  • NLS_CHARSET_ID
  • NLS_CHARSET_NAME

  • Datetime F (28, 11GR2)
  •  ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT (datetime)
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEW_TIME
  • NEXT_DAY
  • NUMTODSINTERVAL
  • NUMTOYMINTERVAL
  • ORA_DST_AFFECTED
  • ORA_DST_CONVERT
  • ORA_DST_ERROR
  • ROUND (date)
  • SESSIONTIMEZONE
  • SYS_EXTRACT_UTC
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR (datetime)
  • TO_DSINTERVAL
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_YMINTERVAL
  • TRUNC (date)
  • TZ_OFFSET

  • General comparison F (2, 11gR2)
  • GREATEST
  • LEAST

  •  Conversion F (38, 11gR2)
  • ASCIISTR
  • BIN_TO_NUM
  • CAST
  • CHARTOROWID
  • COMPOSE
  • CONVERT
  • DECOMPOSE
  • HEXTORAW
  • NUMTODSINTERVAL
  • NUMTOYMINTERVAL
  • RAWTOHEX
  • RAWTONHEX
  • ROWIDTOCAR
  • ROWIDTONCHAR
  • SCN_TO_TIMESTAMP
  • TIMESTAMP_TO_SCN
  • TO_BINARY_FLOAT
  • TO_BINARY_DOUBLE
  • TO_BLOB
  • TO_CHAR (character)
  • TO_CHAR (datetime)
  • TO_CHAR (number)
  • TO_CLOB
  • TO_DATE
  • TO_DSINTERVAL
  • TO_LOB
  • TO_MULTI_BYTE
  • TO_NCHAR (character)
  • TO_NCHAR (datetime)
  • TO_NCHAR (number)
  • TO_NCLOB
  • TO_NUMBER
  • TO_SINGLE_BYTE
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_YMINTERVAL
  • TRANSLATE ... USING
  • UNISTR

  •  Large Object F (3, 11gR2)
  • BFILENAME
  • EMPTY_BLOB
  • EMPTY_CLOB

  •  Collection F (5, 11gR2)
  • CARDINALITY
  • COLLECT
  • POWERMULTISET
  • POWERMULTISET_BY_CARDINALITY
  • SET

  •  Hierarchical F (1, 11gR2)
  • SYS_CONNECT_BY_PATH
  •  Data Mining F (12, 11gR2)
  • CLUSTER_ID
  • CLUSTER_PROBABILITY
  • CLUSTER_SET
  • FEATURE_ID
  • FEATURE_SET
  • FEATURE_VALUE
  • PREDICTION
  • PREDICTION_BOUNDS
  • PREDICTION_COST
  • PREDICTION_DETAILS
  • PREDICTION_PROBABILITY
  • PREDICTION_SET

  •  XML F (26, 11gR2)

  • Encoding and Decoding F (4, 11gR2)
  • DECODE
  • DUMP
  • ORA_HASH
  • VSIZE

  • NULL-related F (6, 11gR2)
  • COALESCE
  • LNNVL
  • NANVL
  • NULLIF
  • NVL
  • NVL2

  • Environment and Identifier F (6, 11gR2)
  • SYS_CONTEXT
  • SYS_GUID
  • SYS_TYPEID
  • UID
  • USER
  • USERENV


    Aggregate Functions
    (41, 11gR2)
    Operate on groups of rows
    Appear in SELECT, ORDER BY and HAVING

    • AVG
    • COLLECT
    • CORR
    • CORR *
    • COUNT
    • COVAR POP
    • COVAR SAMP
    • CUME DIST
    • DENSE RANK
    • FIRST
    • GROUP_ID
    • GROUPING
    • GROUPING_ID
    • LAST
    • LISTAGG
    • MAX
    • MEDIAN
    • MIN
    • PERCENT_RANK
    • PERCENTILE_CONT
    • PERCENTILE_DISC
    • RANK
    • STATS_BINOMIAL_TEST
    • STATS_CROSSTAB
    • STATS_F_TEST
    • STATS_KS_TEST
    • STATS_MODE
    • STATS_MW_TEST
    • STATS_ONEW_WAY_ANOVA
    • STATS_T_TEST *
    • STATS_WSR_TEST
    • STDDEV
    • STDDEV_POP
    • STDDEV_SAMP
    • SUM
    • SYS_XMLAGG
    • VAR_POP
    • VAR_SAMP
    • VARIANCE
    • XMLAGG
    • Linear Regression Functions (9, 11gR2)
    • REGR_SLOPE
    • REGR_INTERCEPT
    • REGR_COUNT
    • REGR_R2
    • REGR_AVGX
    • REGR_AVGY
    • REGR_SXX
    • REGR_SYY
    • REGR_SXY


    Analytic Functions
    (32, 11gR2)
    Commonly used on Data Warehousing environments
    Analytic functions are the last set of operations performed in a query, followed only by the ORDER BY clause
    All joins and the WHERE, GROUP BY and HAVING clauses are completed prior to the analytic functions are processed
    Good to compute cumulative, moving, centered, and reporting aggregates

    • AVG *
    • CORR *
    • COUNT *
    • COVAR_POP *
    • COVAR_SAMP *
    • CUME_DIST
    • DENSE_RANK
    • FIRST
    • FIRST_VALUE *
    • LAG
    • LAST
    • LAST_VALUE *
    • LEAD
    • LISTAGG
    • MAX *
    • MIN *
    • NTH VALUE *
    • NTILE
    • PERCENT_RANK
    • PERCENTILE_CONT
    • PERCENTILE_DISC
    • RANK
    • RATIO_TO_REPORT
    • ROW_NUMBER
    • STDDEV *
    • STDDEV_POP *
    • STDDEV_SAMP *
    • SUM *
    • VAR_POP *
    • VAR_SAMP *
    • VARIANCE *
    • Linear Regression Functions *

    • AVG



    (a) Reporting  each employee's salary and the average salary of the employees within the same department.
    
    SELECT first_name || ' ' || last_name AS emp_name,
           department_id,
           salary,
           AVG (salary) OVER (PARTITION BY department_id) AS avg_dept_sal
      FROM hr.employees;
    
    EMP_NAME       DEPARTMENT_ID   SALARY  AVG_DEPT_SAL
    ------------------  -------------  ------- ------------
    Adam Fripp     50  8200 3475.55556
    Alana Walsh     50  3100 3475.55556
    Alberto Errazuriz 80  12000 8955.88235
    Alexander Hunold 60  9000 5760
    Alexander Khoo  30  3100 4150
    Alexis Bull  50  4100 3475.55556
    Allan McEwen  80  9000 8955.88235
    Alyssa Hutton  80  8800 8955.88235
    ...
    


    (a) Lists manager_id, employee's name, hire_date, salary and the average salary of all employees that have the same manager
    
    -- note the AVG is calculated over all rows within each partition.
    -- no windowing clause is present
      SELECT manager_id,
             last_name,
             hire_date,
             salary,
             AVG (salary) OVER (PARTITION BY manager_id) AS c_mavg
        FROM hr.employees
    ORDER BY manager_id, hire_date, salary;
    
    MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
    ---------- ------------------------- --------- ---------- ----------
           100 De Haan                   13-JAN-01      17000      11100
           100 Raphaely                  07-DEC-02      11000      11100
           100 Kaufling                  01-MAY-03       7900      11100
           100 Hartstein                 17-FEB-04      13000      11100
           100 Weiss                     18-JUL-04       8000      11100
           ...
           100 Zlotkey                   29-JAN-08      10500      11100
           101 Mavris                    07-JUN-02       6500     8983.2
           ...
           101 Whalen                    17-SEP-03       4400     8983.2
           102 Hunold                    03-JAN-06       9000       9000
           103 Austin                    25-JUN-05       4800       4950
           ...
    
    
    
    (b)Lists employee salary and average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee.
    
    -- note the query has
    -- 1. a partition clause (partition rows by manager)
    -- 2. a windowing clause (within each partition, AVG considers only the rows immediately preceding and following each employees' row).
    
      SELECT manager_id,
             last_name,
             hire_date,
             salary,
             AVG (
                salary)
             OVER (PARTITION BY manager_id
                   ORDER BY hire_date
                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                AS c_mavg
        FROM hr.employees
    ORDER BY manager_id, hire_date, salary;
    
    MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
    ---------- ------------------------- --------- ---------- ----------
           100 De Haan                   13-JAN-01      17000      14000
           100 Raphaely                  07-DEC-02      11000 11966.6667
           ...
           100 Cambrault                 15-OCT-07      11000 7766.66667
           100 Mourgos                   16-NOV-07       5800       9100
           100 Zlotkey                   29-JAN-08      10500       8150
           101 Mavris                    07-JUN-02       6500       8250
           101 Baer                      07-JUN-02      10000 9502.66667
           ...
           101 Whalen                    17-SEP-03       4400       8204
           102 Hunold                    03-JAN-06       9000       9000
           103 Austin                    25-JUN-05       4800       4800
           ...
    




    • FIRST_VALUE and LAST_VALUE
    (a) For each employee, return employee salary and the name of the employee that works in the same department and has the lowest salary 
    
      SELECT department_id,
             last_name,
             salary,
             FIRST_VALUE (last_name)
             OVER (PARTITION BY department_id
                   ORDER BY salary ASC, hire_date
                   ROWS UNBOUNDED PRECEDING)
                AS lowest_sal
        FROM hr.employees
    ORDER BY department_id, last_name;
    
    DEPARTMENT_ID LAST_NAME                     SALARY LOWEST_SAL               
    ------------- ------------------------- ---------- -------------------------
               10 Whalen                          4400 Whalen                   
               20 Fay                             6000 Fay                      
               20 Hartstein                      13000 Fay                      
               30 Baida                           2900 Colmenares               
               30 Colmenares                      2500 Colmenares               
               30 Himuro                          2600 Colmenares               
               30 Khoo                            3100 Colmenares               
               30 Raphaely                       11000 Colmenares               
               30 Tobias                          2800 Colmenares               
               40 Mavris                          6500 Mavris                   
     ...
               80 Zlotkey                        10500 Kumar                    
               90 De Haan                        17000 De Haan                  
               90 King                           24000 De Haan                  
               90 Kochhar                        17000 De Haan                  
              100 Chen                            8200 Popp                     
              100 Faviet                          9000 Popp                     
              100 Greenberg                      12008 Popp                     
              100 Popp                            6900 Popp                     
              100 Sciarra                         7700 Popp                     
              100 Urman                           7800 Popp                     
    ...
    
    
    (b) For each employee of department 50, return salary of employees on the previous and following rows of the record set.
     SELECT department_id,
             last_name,
             salary,
             FIRST_VALUE (salary)
             OVER (ORDER BY salary ASC
                   ROWS between 1 preceding and current row) as previous_sal,
             LAST_VALUE (salary)
             OVER (ORDER BY salary ASC
                   ROWS between current row and 1 following) as next_sal
        FROM hr.employees
    where department_id =50;
    
    DEPARTMENT_ID LAST_NAME                     SALARY PREVIOUS_SAL   NEXT_SAL
    ------------- ------------------------- ---------- ------------ ----------
               50 Olson                           2100         2100       2200
               50 Philtanker                      2200         2100       2200
               50 Markle                          2200         2200       2400
               50 Gee                             2400         2200       2400
               50 Landry                          2400         2400       2500
               50 Patel                           2500         2400       2500
               50 Vargas                          2500         2500       2500
               50 Marlow                          2500         2500       2500
               50 Perkins                         2500         2500       2500
               50 Sullivan                        2500         2500       2600
    ...
    







    • RANK
    SELECT department_id,
           last_name,
           salary,
           commission_pct,
           RANK ()
           OVER (PARTITION BY department_id
                 ORDER BY salary DESC, commission_pct DESC)
              "Rank"
      FROM hr.employees
     WHERE department_id = 80;
    


    Object Reference Functions
    • DEREF
    • MAKE_REF
    • REF
    • REFTOHEX
    • VALUE

    Model Functions
    • CV
    • ITERATION_NUMBER
    • PRESENTNNV
    • PRESENTV
    • PREVIOUS

    OLAP Functions
    • CUBE_TABLE

    Data Cartridge Functions
    • DATAOBJ_TO_PARTITION


    User-Defined Functions