SQL General Functions

Today, we are going to know about some new functions present in the Structured Query Language(SQL). The few functions which we are going to learn are:

NVL()
COALESCE()
NVL2()
NANVL()
DECODE()yb
LNNVL()

NVL()

This is one of the SQL features that Structured Query Language makes considerable use of(SQL).

This function only accepts two input values. An error is returned if the supplied values exceed 2. When searched within the function, this function returns the first NOT NULL value.

There is no output from this function if any or both of the arguments are NULL.

The blank area indicates that there is a NULL value in the developer-created SQL Table.

Input data types include integers, floats, strings, character input, and many more.

Syntax

Example Queries

Query 1

Query Output:

NVL(15,20)
_ _ _ _ _ _
      15

Query 2

Query Output:

NVL(NULL,115)
_ _ _ _ _ _ _ _
    115

Query 3

Query Output:

NVL(1.027584 ,1.0275384)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ 

       1.027584

Query 4

Query Output:

NVL(NULL,7.0124375)
_ _ _ _ _ _ _ _ _ _ _ _
     7.0214375

Query 5

Query Output:

NVL
_ _ _
JAMES

Query 6

Query Output:

NVL(
_ _ _ _
ANDERSON

Query 7

Query Output:

COMMISSION_PERCENT
_ _ _ _ _ _ _ _ _ _ _ _ _ _
    .16
    .14
    .05


    .385

8 rows selected.

Query 8

Query Output:

NVL(COMMISSION_PERCENT,0)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
      .16
      .14
      .05
        0
        0
     .385
        0
        0

8 rows selected.

Query 9

SQL>	 select ID_NO , NAME , SALARY , NVL(COMMISSION_PERCENT , 0) ,
   	SALARY +(SALARY * NVL(COMMISSION_PERCENT, 0))
    	TOTAL FROM SALARY;

Query Output:

ID_NO	       NAME               SALARY		NVL(COMMISSION_PERCENT,0)            TOTAL
_ _ _ _    _ _ _ _ _ _ _       _ _ _ _ _ _ 	_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _    _ _ _ _ _ _ _
1	    Joe Burns               52600                       .16      			61016
2 	    Ross Boss               93700                       .14     			106818
3 	    Steven Smith            75000                       .05      			78750
4 	    Chris Lynn              35000                         0      			35000
5 	    Mitch Starc             39900                         0     			39900
6 	    Ricky Ponting           230000                      .385     			318550
7 	    Pat Cummins             23000                         0      			23000
8 	    Scott Boland            10000                         0      			10000
8 rows selected.

Query 10

SQL> select ID_NO , NAME , SALARY , NVL(COMMISSION_PERCENT , 0) ,
   	(SALARY * 12) +(SALARY * NVL(COMMISSION_PERCENT, 0))
    	YEAR_SAL FROM SALARY;

Query Output:

ID_NO	       NAME                SALARY		NVL(COMMISSION_PERCENT,0)     YEAR_SAL
_ _ _ _    _ _ _ _ _ _ _       _ _ _ _ _ _ 	_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _       _ _ _ _ _ _ _
1	      Joe Burns               52600                       .16      				  639616
2 	      Ross Boss               93700                       .14     				  1137518
3 	      Steven Smith            75000                       .05      				  903750
4 	      Chris Lynn              35000                         0      				  420000
5 	      Mitch Starc             39900                         0     				  478800
6 	      Ricky Ponting           230000                      .385     				  2848550
7         Pat Cummins             23000                         0      				  276000
8 	      Scott Boland            10000                         0      				  120000
8 rows selected.

COALESCE()

This is also one of the SQL expressions as well. This expression functions similarly to NVL(). The only distinction is that it now accepts inputs larger than two. The first NOT NULL input element is returned.

Any form of input data can be used. Inputs may take the form of ints, floats, strings, characters, numbers, etc.

Syntax

COALESCE(input value 1 , input value 2 , input value 3 , . . . . . . . . . . , input value n)

Example Queries

Query 1

Query Output:

COALESCE(NULL,1)
_ _ _ _ _ _ _ _ _ _ _
     15

Query 2

Query Output:

COALESCE(16,29,32)
_ _ _ _ _ _ _ _ _ _ _
      16

Query 3

Query Output:

COALESCE(NULL,29,32)
_ _ _ _ _ _ _ _ _ _ _ _ 
        29

Query 4

Query Output:

COALESCE(NULL,NULL,32)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
        32

Query 5

Query Output:

C
_

Query 6

SQL> select COALESCE(NULL , NULL , NULL , 41 , NULL , 36 , 77 , NULL , 64 , NULL) from dual;

Query Output:

COALESCE(NULL,NULL,NULL,41,NULL,36,77,NULL,64,NULL)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
                     41

Query 7

SQL> select COALESCE(NULL , 'NULL' , 'James Anderson' , 'Ricky Ponting' , 'NULLED') from dual;

Query Output:

COAL
_ _ _ _
NULL

Query 7 Explanation

Here, the output of the query is NULL.
This is because NULL is a word specified under the quotes.
So, because of this factor NULL is considered as String but not as a NULL value.
So, that was the reason why NULL was printed as an output.

Query 8

SQL> select COALESCE(NULL , ' ' , 'James Anderson' , 'Ricky Ponting' , 'NULLED') from dual;

Query Output:

C
_

Query 8 Explanation

NVL2()

This is one of the SQL features that Structured Query Language makes considerable use of(SQL).

Only three input values can be stored in this function. An error is returned if the supplied values exceed three. When searching within the function, this function returns the first value that is NOT NULL.

The second value is returned if the first value is NOT NULL.
Third value is returned if first value is NULL and second value is NOT NULL.
The value that is returned may potentially be NULL.
This functionality operates in the same way as NVL() in SQL.
There is no output from this function if any or both of the arguments are NULL.
Input data types include integers, floats, strings, character input, and many more.

Syntax

Example

Query 1

Query Output:

NVL2(11,21,13)
_ _ _ _ _ _ _ _
    21

Query 2

Query Output:

NVL2(62,62,63)
_ _ _ _ _ _ _ _ _
    62

Query 3

Query Output:

NVL2(4,2,63)
_ _ _ _ _ _ _ _ 
    2

Query 4

Query Output:

NVL2(4,NULL,63)
_ _ _ _ _ _ _ _ _

Query 5

Query Output:

NVL2(NULL,NULL,63)
_ _ _ _ _ _ _ _ _ _ _ _
       63

Query 6

Query Output:

NVL2(NULL,89,63)
_ _ _ _ _ _ _ _ _ _ _
     63

Query 7

Query Output:

NVL2('JA
_ _ _ _ _ _
ANDERSON

Query 8

Query Output:

NVL
_ _ _
179

Query 9

Query Output:

NVL2('NU
--------
ANDERSON

Query 10

Query Output:

NVL2(56.2,35.6,23.4)
--------------------
     35.6

Query 11

Query Output:

NVL2(NULL,35.6,23.4)
--------------------
       23.4

Query 12

Query Output:

NVL2(NULL,NULL,23.459)
----------------------
        23.459

Query 13

Query Output:

N
-

Table to perform Operations

ID_NO	       NAME                SALARY		COMMISSION_PERCENT      
_ _ _ _    _ _ _ _ _ _ _       _ _ _ _ _ _ 	_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _       
1	    Joe Burns                 52600                       .16      				  
2 	    Ross Boss                 93700                       .14     				  
3 	    Steven Smith              75000                       .05      				
4 	    Chris Lynn                35000                               				  
5 	    Mitch Starc               39900                              				  
6 	    Ricky Ponting             230000                      .385     				
7 	    Pat Cummins               23000                               				  
8 	    Scott Boland              10000

Query 14

SQL> 	select ID_NO , NAME , SALARY , NVL2(COMMISSION_PERCENT , SALARY , 0) ,
    	SALARY +(SALARY * NVL2(COMMISSION_PERCENT , 0.2 , 0.1))
    	WierdSAL FROM SALARY;

Query Output:

ID_NO     NAME          SALARY     NVL2(COMMISSION_PERCENT,SALARY,0)   WIERDSAL

_ _ _   _ _ _ _ _ _ _   _ _ _ _ _     _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _    _ _ _ _ _ _ _
1       Joe Burns       	52600           			52600                             	63120

2       Ross Boss       	93700           			93700                             	112440

3       Steven Smith        75000           			75000                             	90000

4       Chris Lynn      	35000          			    0                                   38500

5       Mitch Starc     	39900           			0                                 	43890

6       Ricky Ponting   	230000          			230000                           	276000

7       Pat Cummins         23000           			0                                 	25300

8       Scott Boland        10000           			0                                 	11000


8 rows selected.

NANVL()

This method NANVL() returns n1 in the alternate case where the input value n2 is NaN(not a number), and n2 in the case where n2 is not NaN. With this function, only floating-point numbers of the kinds BINARY FLOAT or BINARY DOUBLE may be utilised.

The function can implicitly transform any input data type, whether it be nonnumeric or numeric, into a numeric data type.

If the parameter is a BINARY FLOAT, the method delivers a BINARY DOUBLE value; otherwise, it returns a numeric data type that matches the parameter.

Syntax

Example Queries

Query 1

Query Output:

   DEC_NUM 	BIN_DOUBLE  	      BIN_FLOAT
_ _ _ _ _ _ _     _ _ _ _ _ _ _ _       _ _ _ _ _ _ _  
 9273.420	9.273E+003 	       9.273E+003

Insertion Query

SQL> INSERT INTO FPT VALUES(0 , 'NaN' , 'NaN');
SQL> INSERT INTO FPT VALUES(10 , 'NaN' , 'NaN');
SQL> INSERT INTO FPT VALUES(100 , 'NaN' , 'NaN');

Insertion Query Output

1 row created.
1 row created.
1 row created.

Query 2(Printing the Table Contents)

Query Output:

   DEC_NUM 	BIN_DOUBLE  	      BIN_FLOAT
_ _ _ _ _ _ _     _ _ _ _ _ _ _ _       _ _ _ _ _ _ _  
 3563.971	3.564E+003 	       3.564E+003
       0        Nan       		 Nan
       10       Nan       		 Nan
      100       Nan       		 Nan

Query 3

SQL> 	SELECT DEC_NUM, BIN_DOUBLE, BIN_FLOAT,
	NANVL(DEC_NUM, 0), NANVL(BIN_DOUBLE, 0), NANVL(BIN_FLOAT, 0)
	from NUMBER;

Query Output:

   DEC_NUM 	BIN_DOUBLE  	      BIN_FLOAT
_ _ _ _ _ _ _     _ _ _ _ _ _ _ _       _ _ _ _ _ _ _  
 3563.971	3.564E+003 	       3.564E+003
       0       	 Nan       		 Nan
       10        Nan       		 Nan
      100        Nan       		 Nan
NANVL(DEC_NUM,0) NANVL(BIN_DOUBLE,0)  NANVL(BIN_DOUBLE,0)
_ _ _ _ _ _ _ _  _ _ _ _ _ _ _ _ _ _ _ _ _  	   _ _ _ _ _ _ _ _ _ _ _ _ _
3563.971	 3.564E+003 	       		3.564E+003
0			0				0
10			0				0
100			0				0

DECODE

This is also one of the expression which we can see in Structured Query Language(SQL). The IF, ELSE, IF, ELSE Ladder style is utilized with this decode phrase. Based on the conditions mentioned, this decoder operates.

Any procedure that is listed will function in this situation.

The input types must be chosen depending on the provided data types.

Syntax

DECODE(column  name ,  number 1 to be searched , result 1 to be updated 
			         , number 2 to be searched , result 2 to be updated
			          , number 3 to be searched , result 3 to be updated   
			 . . . . . . . . . 
			          number n to be searched , result n to be updated  , default)

Example Queries

Query 1

Query Output:

      SID 	  SNAME  SAL
_ _ _   _ _ _ _ _     _ _ _ _ _
1 	Dhoni           12
2 	DK              6.25
3 	Sir             14
4 	KL              16
5	Pant            16
6 	Yuzi            6.25
7 	SKY             12

Query 2

SQL > SELECT Sname, sid, sal,
             DECODE(sid, 4, 1.5*sal,
                          7, 3*sal,
                          6, 7*sal,
                          1, 12*sal,
                                    sal)
            "FAKE SALARY"
    from iplb ;

Query Output:

SNAME             SID        SAL    FAKE SALARY
_ _ _ _         _ _ _ _    _ _ _ _  _ _ _ _ _ _ _
Dhoni              1         12         144
DK                 2       6.25        6.25
Sir                3         14          14
KL                 4         16          24
Pant               5         16          16
Yuzi               6       6.25       43.75
SKY                7         12          36

7 rows selected.

Query 3

SQL > SELECT Sid, Sname, Sal  ,
               DECODE(sid ,1 , 1 * sid * sal ,
                            2 , 4 * sid * sal ,
                            3 , 9 * sid * sal ,
                            4 , 16 * sid * sal ,
                            5 , 25 * sid * sal ,
                            6 , 36 * sid * sal ,
                            7 , 49 * sid * sal ,
                           sal/2)
               "F A K E SALARY"
       from iplb ;

Query Output:

       SID      SNAME       SAL      F A K E SALARY
_ _ _ _ _ _  _  _ _ _ _   _ _ _ _  _ _ _ _ _ _ _ _ 
         1        Dhoni       12          12
         2        DK          6.25        50
         3        Sir         14          378
         4        KL          16          1024
         5        Pant        16          2000
         6        Yuzi        6.25        1350
         7        SKY         12          4116

LNNVL()

This is one of the SQL functions that is employed in SQL. This is used to change the values of True and False.

A condition may be held by the LNNVL() function. This causes the situation to change.

If SID = 3is the condition, then. Consequently, SID! =3is identical to LNNVL(SID = 3).

Syntax

Table Query 1

Table Query Output:

 SID 	 SNAME      SAL
_ _ _   _ _ _ _ _     _ _ _ _ _
 1 	Dhoni           12
2 	DK              6.25
3 	Sir             14
4 	KL              16
5	Pant            16
6 	Yuzi            6.25
7 	SKY              12

Example Queries

Query 1

Query Output:

SID 	 SNAME             SAL
_ _ _   _ _ _ _ _     _ _ _ _ _
3 	Sir                14

Query 2

Query Output:

SID 	 SNAME             SAL
_ _ _   _ _ _ _ _     _ _ _ _ _
 1 	Dhoni           12
2 	DK              6.25
4 	KL              16
5	Pant            16
6 	Yuzi            6.25
7 	SKY             12

Table Query

Example Table Query Output:

ID_NO	       NAME                SALARY		 COMMISSION_PERCENT      
_ _ _ _    _ _ _ _ _ _ _       _ _ _ _ _ _ 	_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _       
1	    Joe Burns                52600                       .16      				  
2 	   Ross Boss                 93700                       .14     				  
3 	   Steven Smith              75000                       .05      				
4 	  Chris Lynn                 35000                               				  
5 	  Mitch Starc                39900                              				  
6 	  Ricky Ponting              230000                      .385     				  
7 	  Pat Cummins                23000                               				  
8 	   Scott Boland              10000                               				  
8 rows selected.

Query 3

Query Output:

ID_NO	       NAME                SALARY		 COMMISSION_PERCENT      
_ _ _ _    _ _ _ _ _ _ _       _ _ _ _ _ _ 	_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _       		
4 	  Chris Lynn                 35000                               0				  
5 	  Mitch Starc                39900                               0				  
7 	  Pat Cummins                23000                               0				  
8 	  Scott Boland               10000                               0

Query 4

Query Output:

ID_NO	       NAME                SALARY		 COMMISSION_PERCENT      
_ _ _ _    _ _ _ _ _ _ _       _ _ _ _ _ _ 	_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _       
1	    Joe Burns                52600                       .16      				  
2 	   Ross Boss                 93700                       .14     				  
3 	   Steven Smith              75000                       .05      				
6 	  Ricky Ponting              230000                      .385

Next Topic7 Reasons Why to Learn SQL in 2023

← prev next →