Building examples of using SQL functions supported by Oracle
Check the post here (in construction)
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
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
Subscribe to:
Posts (Atom)