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,lead(sal,2)
	over(order by sal asc) leadresult from emp 


		  ENAME	       DEPTNO	     SAL     LEADRESULT
		  ---------- ---------- ---------- ----------
		  SMITH		        20	     800       1100
		  JAMES		        30	     950       1250
		  ADAMS		        20	    1100       1250
		  WARD		        30	    1250       1300
		  MARTIN		30	    1250       1500
		  MILLER		10	    1300       1600
		  TURNER		30	    1500       2450
		  ALLEN		        30	    1600       2850
		  CLARK		        10	    2450       2975
		  BLAKE		        30	    2850       3000
		  JONES		        20	    2975       3000
		  SCOTT		        20	    3000       5000
		  FORD		        20	    3000
		  KING		        10	    5000

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

« Previous

Next Topic »

(SQL - Constraints)