Oracle uses SQL to make reports with subtotal totals commonly used functions

The DECODE function is Oracle PL/SQL is one of the most powerful functions, and only Oracle SQL provides this function, and the SQL implementations of other database vendors do not yet have this function. What is the use of decode? Let's first construct an example, assuming that we want to raise wages for the smart-star staff, the standard is: wages under 8000 yuan will be added 20%, wages in the 8000 yuan plus 15%, the usual practice is to first select the record of the wage field value? Select Salary to Var-salary from employee, and then the variable var-salary is judged by a flow control statement such as If-then-else or choose case. If we use the Decode function, then we can omit these flow control statements, which can be done directly through the SQL statement. The following: Select Decode (salary-8000), 1,salary*1.15,-1,salary*1.2,salary from employee is not very concise? DECODE syntax: DECODE (value,if1,then1,if2,then2,if3,then3,..., else), indicating that if value equals IF1, the result of the DECODE function is returned THEN1,..., Returns else if it is not equal to any of the if values. At first glance, DECODE can only do equals test, but just see, we can use some function or calculation instead of value, it is possible to make the DECODE function more than, less than or equal to the function.

2--Compare size function sign
Signs (x) or sign (x) are called symbolic functions, and their function is to take a number of symbols (positive or negative): when x>0,sign (x) = 1; when x=0,sign (x) = 0; When X<0,sign (x) =-1;x can be a function or a calculated expression

3--groupping is an aggregate function that produces an additional column that, when added with the cube or ROLLUP operator, has an additional column output value of 1 and an additional column value of 0 when the row being added is not produced by cube or ROLLUP.
Syntax: GROUPING (column_name)
Parameter: column_name is a column in the GROUP by clause that checks for a CUBE or ROLLUP null value.
return type: int
Note: grouping is used to differentiate between null values returned by CUBE and ROLLUP and standard null values. The null that is returned as a result of the cube or ROLLUP operation is a special application of NULL.

The 4--ROLLUP operator generates aggregate summaries, which are useful when you need to summarize information. The result set generated by the operator is similar to the result set generated by the CUBE operator. But there are some differences between them, and the result set generated by cube shows aggregations for all the combinations of values in the selected column. The result set generated by rollup shows the aggregation of a hierarchy of values in the selected column.
Syntax: ROLLUP (column_name1[,column_name2 ... ] )
Usage: Used in the GROUP BY clause. Add the rollup operator to the columns that you want to group and summarize aggregated data for grouping.

5--nvl
Grammar
NVL (EExpression1, eExpression2) parameter eExpression1, EExpression2
If the eExpression1 evaluates to a null value, NVL () returns EEXPRESSION2. If the EExpression1 evaluates to a value other than NULL, the EExpression1 is returned. EExpression1 and EExpression2 can be any data type. If the result of both EExpression1 and EExpression2 is null, then NVL () returns. Null.. return value type character, date, datetime, numeric, currency, logical, or null value
Description
You can use NVL () to remove null values from a calculation or operation in cases where null values are not supported or if NULL values do not matter.
Select NVL (a.name, ' empty ') as name from student a joins school B on A.id=b.id

Note: Two parameter types to match

Oracle uses SQL to make reports with subtotal totals commonly used functions