SQL - FUNCTIONSA 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 ----- tesdbUpper : SQL> select lower('tesdb') from dual; UPPER ---- TESDBInitcap : SQL> select Initcap('tesdb') from dual; Initcap ---- TesdbCharacter Manipulative Function: concat: SQL> select 'Welcome'||' to '||'Tesdb' message 2 from dual; welcome to TesdbLpad : 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 selectedRpad : 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 COTTLENGTH : 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 5REPLACE : SQL> select replace('Test','t','db') from dual; REPLA ----- TesdbTranslate : SQL> select translate('Teste','te','db') from dual; TRANS ----- TbsdbTrim : SQL> select trim('g' from 'gogoldg') from dual; TRIM ----- ogoldb. Numeric function: ![]() SQL> select abs(-125.2578) from dual; ABS(-125.2578) -------------- 125.2578Ceil : SQL> select ceil(33.3) from dual; CEIL(33.3) ---------- 34Floor : SQL> select floor(32.3) from dual; FLOOR(32.3) ----------- 32Round : SQL> select round(32.67) from dual; ROUND(32.67) ------------ 33Power : SQL> select power(10,3) from dual; POWER(10,3) ----------- 1000Sqrt : SQL> select sqrt(81) from dual; SQRT(81) ---------- 9Mod : SQL> select mod(25,5) from dual; MOD(25,5) ---------- 0c. 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:30Months_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-23Last_day: SQL> select sysdate,last_day(sysdate) from dual; SYSDATE LAST_DAY( --------- --------- 30-SEP-23 30-SEP-23Next_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-23d. Conversion Function : ![]() To_char : SQL> select sysdate,to_char(sysdate,'month') from dual; SYSDATE TO_CHAR(SYSDATE,'MONTH') --------- -------------------------- 30-SEP-23 septemberTo_date : SQL> select sysdate,to_date('10-12-22','dd-mm-yyyy') from dual; SYSDATE TO_DATE(' --------- --------- SEP-23 10-DEC-22To_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 0Nvl2: 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 1310Nullif: 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 11Coalesce: SQL> select coalesce(null,'B','C','D','E','F') from dual; C --- BDecode: 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 CKAggregate Function : ![]() Count: SQL> select count(comm) from emp; COUNT(COMM) ----------- 4Sum: SQL> select sum(sal) from emp; SUM(SAL) ---------- 29025Average: SQL> select avg(sal) from emp; AVG(SAL) ---------- 2073.21429Min: SQL> select min(sal) from emp; MIN(SAL) ---------- 800Max: SQL> select max(sal) from emp MAX(SAL) ---------- 5000Order 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 800Group by: SQL> select deptno from emp group by deptno; DEPTNO ---------- 30Having: SQL> select deptno from emp group by deptno having deptno=30 DEPTNO ---------- 30 20 10Distinct: SQL> select distinct(deptno) from emp; DEPTNO ---------- 30 20 10Analytical Function : Windowing Clause : 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 6DENSE_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 5Lead : SQL> select ename,deptno,sal,lead(sal,2) over(order by sal asc) leadresult from empLag : 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 3000First_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 ---------- 800Last_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 -------- 5000Nth 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 |
☛ Join to Learn from Experts: Oracle SQL Training in Chennai by TesDBAcademy