1.decode function

SELECT ID,NAME,DECODE(LEVER,1,'professor',2,'associate professor',3,'tutor','lecturer') POSITION,
(SELECT CLASSNUM FROM CLASS T WHERE T.NUM = S.NUM) NUM FROM TEACHER S WHERE T.LEVER IN (1,2,3);

The usage logic of the decode function in the previous SQL is as follows:

IF(LEVER==1) THEN
          RETURN 'professor'(Translation value 1)
ELSE IF(LEVER==2) THEN
          RETURN 'associate professor'(Translation value 2)
ELSE IF(LEVER==3) THEN
          RETURN 'tutor'
ELSE 
          RETURN 'lecturer'(Default value)

Where, the default value is optional

2.NVL function

SELECT ID,PERSONNAME,NVL(BOOKNUM,0) AS BOOKNUM,NVL(PERSONNUM,0) AS PERSONNUM FROM BOOKSTORE

The NVL() function of SQL can be understood as follows:

IF (BOOKNUM !=NULL) THEN 
          RETURN BOOKNUM
ELSE
          RETURN 0

3.NVL2 function

SELECT ID,PERSONNAME,NVL2(BOOKNUM,BOOKNUM,0) AS BOOKNUM,NVL2(PERSONNUM,PERSONNUM,0) AS PERSONNUM FROM BOOKSTORE

The NVL2() function of SQL can be understood as follows

IF (BOOKNUM !=NULL) THEN 
          RETURN BOOKNAME
ELSE IF(BOOKNUM == NULL)
          RETURN 0

It is worth noting that if the type of BOOKNAME is different from that of 0, 0 will be converted to the type of BOOKNAME, otherwise, an error will be reported

4.NULLIF function

SELECT E.NAME,E.JOB_ID,J.JOB_ID,NULLIF(E.JOB_ID,J.JOB_ID) AS SAME FROM EMPLOYEES E,JOB_HISTORY J 
   WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID ORDER BY NAME;

The NULLIF() function of SQL in the previous sentence can be understood in this way

IF(E.JOB_ID == J.JOB_ID) THEN 
           RETURN NULL
ELSE
           RETURN E.JOB_ID

5.SUM function

SELECT ID ,CLASSNAME,SUM(NVL(RESULTS,0)) AS RESULTS,SUM(DECODE(TYPE,'50',0,1)) AS CLASSTYPE FROM CLASS

The SUM() function of SQL in the previous sentence is mainly used to sum the value returned by the expression in brackets or the sum of a field in the table

6.SUBSTR function

SELECT SUBSTR(CLASSNAME,0) AS CLASSNAME ,SUBSTR("It's sunny today!",3,2) FROM CLASS;

The SUBSTR() function of SQL in the previous sentence is mainly used to intercept a part of the string. For example, SUBSTR(CLASSNAME,0) means to intercept the CLASSNAME string from the 0th byte. Substr (today's weather is sunny, 3,2) means to intercept two bytes of "today's weather is sunny" from the third byte, and the result is "weather"

7. To char function

SELECT S.ID,S.NAME,TO_CHAR(S.TIME,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(S.MONEY,'999') AS MONEY
    FROM STUDENT S

The SUBSTR() function of SQL in the previous sentence is mainly to convert numeric or date fields into character fields, which is convenient for operations similar to fuzzy queries