SQL - FUNCTIONS
A function is a subprogram that is used to return a single value.
Scalar functions:
The Types Of Functions:These functions operate on single rows only and produce one result per row.
Types:
a. Character function:
It used to accept character inputs and return either character or number values as output.
Case Manipulative Function:
lower : SQL> select lower('TESDB') from dual; LOWER ----- tesdb Upper : SQL> select lower('tesdb') from dual; UPPER ---- TESDB Initcap : SQL> select Initcap('tesdb') from dual; Initcap ---- Tesdb
Character Manipulative Function
concat: SQL> select 'Welcome'||' to '||'Tesdb' message 2 from dual; welcome to Tesdb Lpad : SQL> select lpad(ename,'10','*')from emp; LPAD(ENAME,'10','*') ---------------------------------------- *****SMITH *****ALLEN ******WARD *****JONES ****MARTIN *****BLAKE *****CLARK *****SCOTT ******KING ****TURNER *****ADAMS *****JAMES ******FORD ****MILLER 14 rows selected Rpad : SQL> select rpad(ename,'10','*') from emp; RPAD(ENAME,'10','*') ---------------------------------- SMITH***** ALLEN***** WARD****** JONES***** MARTIN**** BLAKE***** CLARK***** SCOTT***** KING****** TURNER**** ADAMS***** JAMES***** FORD****** MILLER**** 14 rows selected. Substring : SQL> select substr(ENAME,2,4) from emp; SUBSTR(ENAME,2,4) ---------------- MITH LLEN ARD ONES ARTI LAKE LARK COTT
LENGTH :
SQL> select job,length(job) from emp; JOB LENGTH(JOB) --------- ----------- CLERK 5 SALESMAN 8 SALESMAN 8 MANAGER 7 SALESMAN 8 MANAGER 7 MANAGER 7 ANALYST 7 PRESIDENT 9 SALESMAN 8 CLERK 5
REPLACE :
SQL> select replace('Test','t','db') from dual;
REPLA
-----
Tesdb
Translate :
SQL> select translate('Teste','te','db') from dual;
TRANS
-----
Tbsdb
Trim :
SQL> select trim('g' from 'gogoldg') from dual;
TRIM
-----
ogold
b. Numeric function:
Absolute : SQL> select abs(-125.2578) from dual; ABS(-125.2578) -------------- 125.2578 Ceil : SQL> select ceil(33.3) from dual; CEIL(33.3) ---------- 34 Floor : SQL> select floor(32.3) from dual; FLOOR(32.3) ----------- 32 Round : SQL> select round(32.67) from dual; ROUND(32.67) ------------ 33 Power : SQL> select power(10,3) from dual; POWER(10,3) ----------- 1000 Sqrt : SQL> select sqrt(81) from dual; SQRT(81) ---------- 9 Mod : SQL> select mod(25,5) from dual; MOD(25,5) ---------- 0
c. Date Function:
It used to accept date input and return date values as output.
SQL> select sysdate from dual; SYSDATE --------- 30-SEP-23 SQL> select systimestamp from dual; SYSTIMESTAMP ---------------------------------------- 30-SEP-23 10.30.33.277915 AM +05:30
Months_between: SQL> select months_between(sysdate,'20-june-2023') from dual; MONTHS_BETWEEN(SYSDATE,'20-JUNE-2023') -------------------------------------- 3.33676187 SQL> select hiredate,months_between(sysdate,hiredate) from emp; HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE) --------- -------------------------------- 17-DEC-80 513.43361 20-FEB-81 511.336835 22-FEB-81 511.272319 02-APR-81 509.917481 28-SEP-81 504.078771 01-MAY-81 508.949739 09-JUN-81 507.691674 19-APR-87 437.369093 17-NOV-81 502.43361 08-SEP-81 504.723932 23-MAY-87 436.240061 03-DEC-81 501.885223 03-DEC-81 501.885223 23-JAN-82 500.240061 14 rows selected. Add_months: SQL> select sysdate,add_months(sysdate,3) from dual; SYSDATE ADD_MONTH --------- --------- 30-SEP-23 31-DEC-23 Last_day: SQL> select sysdate,last_day(sysdate) from dual; SYSDATE LAST_DAY() --------- --------- 30-SEP-23 30-SEP-23 Next_day: SQL> select sysdate,next_day(sysdate,'monday') from dual; SYSDATE NEXT_DAY() --------- --------- 30-SEP-23 02-OCT-23 SQL> select sysdate,trunc(sysdate,'month') from dual; SYSDATE TRUNC(SYS) --------- --------- 30-SEP-23 01-SEP-23
d. Conversion Function :
To_char : SQL> select sysdate,to_char(sysdate,'month') from dual; SYSDATE TO_CHAR(SYSDATE,'MONTH') --------- -------------------------- 30-SEP-23 september To_date : SQL> select sysdate,to_date('10-12-22','dd-mm-yyyy') from dual; SYSDATE TO_DATE()' --------- --------- SEP-23 10-DEC-22 To_number : SQL> select to_number('1000','9999') from dual; TO_NUMBER('1000','9999') ------------------------ 1000
e. Miscellaneous Function :
SQL> select sal,comm,nvl(comm,0) from emp; SAL COMM NVL(COMM,0) ---------- ---------- ----------- 800 0 1600 300 300 1250 500 500 2975 0 1250 1400 1400 2850 0 2450 0 3000 0 5000 0 1500 0 0 1100 0 950 0 3000 0 1300 0 Nvl2: SQL> select sal,comm,nvl2(comm,0,10),sal+nvl2(comm,0,10) from emp; SAL COMM NVL2(COMM,0,10) SAL+NVL2(COMM,0,10) ---------- ---------- --------------- ------------------- 800 10 810 1600 300 0 1600 1250 500 0 1250 2975 10 2985 1250 1400 0 1250 2850 10 2860 2450 10 2460 3000 10 3010 5000 10 5010 1500 0 0 1500 1100 10 1110 950 10 960 3000 10 3010 1300 10 1310 Nullif: SQL> select ename,job,length(ename) as “length”, floor(nullif((length(ename)+length(job)),10)) as "nullif" from emp; ENAME JOB ???LENGTH??? nullif ---------- --------- ------------ ---------- SMITH CLERK 5 ALLEN SALESMAN 5 13 WARD SALESMAN 4 12 JONES MANAGER 5 12 MARTIN SALESMAN 6 14 BLAKE MANAGER 5 12 CLARK MANAGER 5 12 SCOTT ANALYST 5 12 KING PRESIDENT 4 13 TURNER SALESMAN 6 14 ADAMS CLERK 5 JAMES CLERK 5 FORD ANALYST 4 11 MILLER CLERK 6 11 Coalesce: SQL> select coalesce(null,'B','C','D','E','F') from dual; C --- B Decode: SQL> select job,decode(job,'CLERK','CK','SALESMAN','SM','MANAGER', 'MG','ANALYST','AT','PRESIDENT','PT') from emp; JOB DE --------- -- CLERK CK SALESMAN SM SALESMAN SM MANAGER MG SALESMAN SM MANAGER MG MANAGER MG ANALYST AT PRESIDENT PT SALESMAN SM CLERK CK CLERK CK ANALYST AT CLERK CK
Aggregate Function :
- An aggregate function performs a calculation
- A set of values deals and returns a single values.
- Its also know as multiple row function.
Count: SQL> select count(comm) from emp; COUNT(COMM) ----------- 4 Sum: SQL> select sum(sal) from emp; SUM(SAL) ---------- 29025 Average: SQL> select avg(sal) from emp; AVG(SAL) ---------- 2073.21429 Min: SQL> select min(sal) from emp; MIN(SAL) ---------- 800 Max: SQL> select max(sal) from emp MAX(SAL) ---------- 5000 Order by: SQL> select ename,job,sal from emp order by sal desc; ENAME JOB SAL ---------- --------- ---------- KING PRESIDENT 5000 FORD ANALYST 3000 SCOTT ANALYST 3000 JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 MILLER CLERK 1300 WARD SALESMAN 1250 MARTIN SALESMAN 1250 ADAMS CLERK 1100 JAMES CLERK 950 SMITH CLERK 800 Group by: SQL> select deptno from emp group by deptno; DEPTNO ---------- 30 Having: SQL> select deptno from emp group by deptno having deptno=30 DEPTNO ---------- 30 20 10 Distinct: SQL> select distinct(deptno) from emp; DEPTNO ---------- 30 20 10
Analytical Function :
Windowing Clause :
- Range between unbound preceeding and current row--default.last row in window changes as current row changes.
- Range between current row and unbounded following.first row in window changes as current row changes.
- Range between unbounded preceeding and unbounded following.all rows are included.
RANK : SQL> select ename,deptno,sal,rank() over(partition by deptno order by sal desc)rank_sal from emp; ENAME DEPTNO SAL RANK_SAL ---------- ---------- ---------- ---------- KING 10 5000 1 CLARK 10 2450 2 MILLER 10 1300 3 SCOTT 20 3000 1 FORD 20 3000 1 JONES 20 2975 3 ADAMS 20 1100 L4 SMITH 20 800 5 BLAKE 30 2850 1 ALLEN 30 1600 2 TURNER 30 1500 3 MARTIN 30 1250 4 WARD 30 1250 4 JAMES 30 950 6 DENSE_RANK : SQL> select ename,deptno,sal,dense_rank() over(partition by deptno order by sal desc)denserank_sal from emp ENAME DEPTNO SAL DENSERANK_SAL ---------- ---------- ---------- ------------- KING 10 5000 1 CLARK 10 2450 2 MILLER 10 1300 3 SCOTT 20 3000 1 FORD 20 3000 1 JONES 20 2975 2 ADAMS 20 1100 3 SMITH 20 800 4 BLAKE 30 2850 1 ALLEN 30 1600 2 TURNER 30 1500 3 MARTIN 30 1250 4 WARD 30 1250 4 JAMES 30 950 5 Lead : SQL> select ename,deptno,sal,lag(sal,2)over(order by sal asc) lagresult from emp ENAME DEPTNO SAL LAGRESULT ---------- ---------- ---------- ---------- SMITH 20 800 JAMES 30 950 ADAMS 20 1100 800 WARD 30 1250 950 MARTIN 30 1250 1100 MILLER 10 1300 1250 TURNER 30 1500 1250 ALLEN 30 1600 1300 CLARK 10 2450 1500 BLAKE 30 2850 1600 JONES 20 2975 2450 SCOTT 20 3000 2850 FORD 20 3000 2975 KING 10 5000 3000 Lag : SQL> select ename,deptno,sal,lag(sal,2)over(order by sal asc) lagresult from emp ENAME DEPTNO SAL LAGRESULT ---------- ---------- ---------- ---------- SMITH 20 800 JAMES 30 950 ADAMS 20 1100 800 WARD 30 1250 950 MARTIN 30 1250 1100 MILLER 10 1300 1250 TURNER 30 1500 1250 ALLEN 30 1600 1300 CLARK 10 2450 1500 BLAKE 30 2850 1600 JONES 20 2975 2450 SCOTT 20 3000 2850 FORD 20 3000 2975 KING 10 5000 3000 First_value : Finds first value from a set of value SQL> select distinct first_value(sal) over(order by sal asc range between unbounded preceding and unbounded following) result from emp; RESULT ---------- 800 Last_value: Finds last value from a set of value SQL> select distinct last_value(sal) over(order by sal asc range between unbounded preceding and unbounded following) result from emp; RESULT -------- 5000 Nth value: Finds nth value from a set of value SQL> select distinct nth_value(sal) over(order by sal asc range between unbounded preceding and unbounded following) result from emp; RESULT ---------- 1500