OCP prepare 20140628

1. null if nvl nvl2

Nullif Function

The syntax of the Oracle nullif function is nullif (expression 1, expression 2). If expression 1 and expression 2 are equal, a null value is returned. If expression 1 and expression 2 are not equal, the result of expression 1 is returned.

Note: expression 1 and expression 2 should be of the same data type or can be implicitly converted to the same data type. Expression 1 cannot use the character null. Www.2cto.com

Example 1: If Sal is 888, null is returned.

[SQL]

SQL> select a. ename, A. Sal, nullif (SAL, 8888) value from EMP;

Ename Sal Value

----------------------------------------

Smith 99 99

Allen 8888

Ward 8888

 

Nvl2 (expr1, expr2, expr3)

Function: if the value of the parameter expression expr1 is null, The nvl2 () function returns the value of the parameter expression expr3. If the value of the parameter expression expr1 is not null, The nvl2 () the function returns the value of the parameter expression expr2.
  
   Nvl (string1, replace_with)

Function: If string1 is null, The nvl function returns the value of replace_with; otherwise, the value of string1 is returned. If both parameters are null, null is returned.

Coalesce Function

The Oracle coalesce function syntax is coalesce (expression 1, expression 2 ,..., expression N), N> = 2. The function of this expression is to return the first non-null expression. If both are null, a null value is returned.

Note: All expressions must be of the same type or can be converted to the same type. Www.2cto.com

Example 1: In the EMP table, set the person whose comm column is empty to 200.

[SQL] www.2cto.com

SQL> select a. empno, A. ename, comm, coalesce (Comm, 200) new_comm from EMP;

Empno ename comm new_comm

--------------------------------------------------

7369 Smith 200

7499 Allen 300 300

2.

Min () and max () are Aggregate functions.
Group by is followed by all the fields in select that are not Aggregate functions.
Ex1: Select count (*) from EMP; // only queries the total number of EMP records in the table.
Ex2: Select count (*), deptno from EMP group by deptno;
// Based on the deptno group, the retrieved data is the total number of records in different departments.
Select count (*), deptno, comm from EMP group by deptno, comm;
// Group by deptno and comm, and so on
Group by is followed by all the fields in select that are not Aggregate functions. Otherwise, an error is reported.

Having is equivalent to where

The only difference from where is that

When an aggregate function exists in a query statement, where cannot be used. Having can only be used.

3. Join

Http://www.cnblogs.com/lovemoon714/archive/2012/03/02/2376782.html