Oracle Database 12c SQL Fundamentals I Exam Guide Exam 1Z0-061 学习笔记

I. any arithmatic operator with null will result null
   concate null will not result in null, will remain the same without null
II, for ' in literal value, could use '' to escape the it.
    or could use q'<conent ''''''>' to use escape the ' in the literal value, here <> could be any other charactors or bracket paris.
--------3-----------
I.Like wildcard symbol, we could use _(for 1 letter) or %(for 0 or more letters)
  to escapse like wildcard, we could use '\%test' escape '\'; in where clause
  
II, for ampersand(&), we could double ampersand(&&) to define a session variable, so we could reuse this value in current session.
    for ampersand, we could use it in select &col_name from rcv_supply;
    for ampersand, we even could use select &rest_statment;
    if use ampersand for literal values and date, we could to use single quotes, like '&t1'
III. for double ampersand, we could define command to define a variable to let it works the same
     define t1= 'test1'      <=>    select col1 from table1 where col1='&&t1' or col2 = '&t1';
IV undefine could use to clear the variable we defined.
   verify could use to show the value of defined variable while actually running the scripts.

--------------4------------------
TRIM([trailing|leading|both] trimstring from s),
The string to be trimmed (s) is mandatory. The following points list the rules governing the use of this function:
image  TRIM(s) removes spaces from both sides of the input string. When no direction of trimming is specified, then spaces are trimmed from both sides of the string.
image  TRIM(trailing trimstring from s) removes all occurrences of trimstring from the end of the string s if it is present.
image  TRIM(leading trimstring from s) removes all occurrences of trimstring from the beginning of the string s if it is present.
image  TRIM(both trimstring from s) removes all occurrences of trimstring from the beginning and end of the string s if it is present.
SELECT TRIM('aababcca'), TRIM(leading 'a' from 'aababcca'), TRIM(trailing 'a' from 'aababcca'), TRIM(both 'a' from 'aababcca')   from dual;
aababcca babcca aababcc babcc
  
-------------------5---------------
nls_parameter always is omitted, and we could use the following command to alter it in session
alter session set nls_currency='USD';
TO_CHAR(number1, [format], [nls_parameter])
The TO_CHAR function, fm modifier, and the sp, th, and spth format models are commonly examined. 


Format Element  Description and Format Mask                         Result
fm              remove relevant space                               select to_char(sysdate, 'fmMonth')||'123' from dual; return 'May123'; If don't use fm, it returns 'May      123'; it Month by default return 9 letters.
TH              Positional or ordinal text:'DDth''of'' Month'       12th of September
SP              Spelled out number: 'MmSP Month Yyyysp'             Nine September Two Thousand Eight
THSP or SPTH    Spelled out positional or ordinal number:'hh24SPTh' Fourteenth


1.NVL(original, ifnull); -- if original is null, then return ifnull expression;
2.NVL2(original, ifnotnull, ifnull); --if orignal is not null, return ifnotnull expression; or else, return ifnull expression;
  ifnotnull and ifnull should have the same data type(or could convert ifnull to same data type as ifnotnull implicitly, nvl2(1, 'a', 4) is valid, nvl2(2, 4, 'a') is invalid.
3.NULLIF(ifunequal, comparison_term);--if ifunequal expression is equals with comparison_term, return null; or else return ifunequal.
4.COALESCE(expr1, expr2,…,exprn); --return the 1st non-null parameter; if all parameters are null, return null; it takes 2 mandatory parameters, and any number of optional parameters 
   all parameters should be the same data type, coalesce(1234, '1',4) is invalid
5.DECODE(expr1,comp1, iftrue1, [comp2,iftrue2…[compN,iftrueN]], [iffalse])
  -- decode function, at least 3 parameters are mandatory.  If the iffalse parameter does not exist and no matches are found, a null value is returned.
  -- decode(1, 1, 3, 'a') is invalid, and will throw exception; but decode(1,1,3, 'a') is valid, since 'a' will never be executed;
  -- all iftruex expression are expected the same data type as iftrue1(or cou