Computer Science and Engineering

NVL, Decode And Case Functions in SQL:

Handling NULL values:
Ø NULL: It is value which is…
Unavailable
Unassigned
Unknown

Inapplicable
Ø A NULL is not same as zero or blank space.
Ø If a row lacks the data for a particular column, than that value is said to be null or to containing null.
Select Ename, job, sal, comm from emp;
Ø If any column value in an arithmetic expression is null, the overall result is also null.
Ø The above situation is termed as null propagation and has to be handled very carefully.
Select ename, job, sal, comm, sal+comm from emp;
Select ename, job, sal, comm, 12 * (sal + comm) from emp;
NVL Function:
Ø The NVL function is used to convert a null value to an actual value.
Syntax: NVL(expr1, expr2)
Ø Expr1: it is the source value or expression that may contain null.
Ø Expr2: it is the target value for converting NULL.
Ø NVL function can be used to convert any data type; the return value is always the same as the data type of expr1.
Ø The data types of the source and destination must match.
NVL(comm,0)
NVL(hiredate,’01-JUN-99’)
NVL(job, ‘NOT ASSIGNED’)
Ex:
Select ename, sal, comm, sal + nvl(comm,0) from emp;
Select ename, sal, comm, (sal * 12) + nvl(comm,0) from emp;
Select ename, sal, comm, (sal+500) + nvl(comm,0) from emp;
DECODE Function:
Ø It is a single row function.
Ø The function works on the same principle as the if – then – else.
Ø We can pass a variable number of values into the call of the decode ()
Ø Function.
Ø The first item is always the name of the column that needs to be decoded.
Ø Once all value-substitute pairs have been defined, we can optionally specify a default value.
Syntax:
Select decode(colname, value 1, substitute1, value 2, substitute2, … returndefault) from tablename;
Ø The function has no restriction on the input and output data type.
Ø It is the most power full function in oracle.
Ø The Function can work for only an analysis that considers an equality operator in the logical comparison.
Ex:
Select Ename, job, sal,
Decode(deptno,
10,’ACCOUNTING’,
20,’RESEARCH’,
30,’SALES’,
40,’OPERATIONS’,
’OTHERS’) Departments
From Emp
Order by Departments;

Working with CASE expressions:
Ø The case expression can be used to perform if-then-else logic in SQL.
Ø Case is similar to decode but it is ANSI-compliant.
Ø It can be used even for executing conditions on range based comparison.
Ø Case expressions are of two types
Simple case expressions
Searched case expressions
Simple case expressions:
Ø These expressions are used to determine the returned value.
Ø They work with equality comparison only, almost all similar to decode.
Ø It has a selector which associates to the compared value either from the column or constant.
Ø The value in the selector is used for comparison with the expressions used in the when clause.
Syntax:
Case search_expr
When expr_1 then result 1
When expr_2 then result 2
Else default_result
End
Ex:
Select Ename, Deptno,
Case
When 10 then ’ACCOUNTS’
When 20 then ’RESEARCH’
When 30 then ’SALES’
When 40 then ’OPERATIONS’
Else ’NOT FOUND’
End
From emp;
Searched case expressions:
Ø The statement uses conditions to determine the returned value.
Ø It helps in writing multiple conditions for evaluation.
Ø Used in range analysis of values also.
Syntax:
Case
When condition1 then result1
When condition2 then result2
When condition N then result
Else default result
End
Ex:
Select ename, deptno,
Case
When deptno = 10 then ’ACCOUNTS’
When deptno = 20 then ’RESEARCH’
When deptno = 30 then ’SALES’
When deptno = 40 then ’OPERATIONS’
Else ’not specified’
End
From emp;
Select Ename, Sal,
Case
When sal >= 800 and sal =2001 and sal

Tableau Tasks:

http://www.dataplusscience.com/TableauReferenceGuide/index.html

Data + Science

< Important Mathematics Formulas >

1. (α+в)²= α²+2αв+в²
2. (α+в)²= (α-в)²+4αв b
3. (α-в)²= α²-2αв+в²
4. (α-в)²= f(α+в)²-4αв
5. α² + в²= (α+в)² - 2αв.
6. α² + в²= (α-в)² + 2αв.
7. α²-в² =(α + в)(α - в)
8. 2(α² + в²) = (α+ в)² + (α - в)²
9. 4αв = (α + в)² -(α-в)²
10. αв ={(α+в)/2}²-{(α-в)/2}²
11. (α + в + ¢)² = α² + в² + ¢² + 2(αв + в¢ + ¢α)
12. (α + в)³ = α³ + 3α²в + 3αв² + в³
13. (α + в)³ = α³ + в³ + 3αв(α + в)
14. (α-в)³=α³-3α²в+3αв²-в³
15. α³ + в³ = (α + в) (α² -αв + в²)
16. α³ + в³ = (α+ в)³ -3αв(α+ в)
17. α³ -в³ = (α -в) (α² + αв + в²)
18. α³ -в³ = (α-в)³ + 3αв(α-в)

ѕιη0° =0
ѕιη30° = 1/2
ѕιη45° = 1/√2
ѕιη60° = √3/2
ѕιη90° = 1
¢σѕ ιѕ σρρσѕιтє σƒ ѕιη
тαη0° = 0
тαη30° = 1/√3
тαη45° = 1
тαη60° = √3
тαη90° = ∞
¢σт ιѕ σρρσѕιтє σƒ тαη
ѕє¢0° = 1
ѕє¢30° = 2/√3
ѕє¢45° = √2
ѕє¢60° = 2
ѕє¢90° = ∞
¢σѕє¢ ιѕ σρρσѕιтє σƒ ѕє¢
2ѕιηα¢σѕв=ѕιη(α+в)+ѕιη(α-в)
2¢σѕαѕιηв=ѕιη(α+в)-ѕιη(α-в)
2¢σѕα¢σѕв=¢σѕ(α+в)+¢σѕ(α-в)
2ѕιηαѕιηв=¢σѕ(α-в)-¢σѕ(α+в)
ѕιη(α+в)=ѕιηα ¢σѕв+ ¢σѕα ѕιηв.
» ¢σѕ(α+в)=¢σѕα ¢σѕв - ѕιηα ѕιηв.
» ѕιη(α-в)=ѕιηα¢σѕв-¢σѕαѕιηв.
» ¢σѕ(α-в)=¢σѕα¢σѕв+ѕιηαѕιηв.
» тαη(α+в)= (тαηα + тαηв)/ (1−тαηαтαηв)
» тαη(α−в)= (тαηα − тαηв) / (1+ тαηαтαηв)
» ¢σт(α+в)= (¢σтα¢σтв −1) / (¢σтα + ¢σтв)
» ¢σт(α−в)= (¢σтα¢σтв + 1) / (¢σтв− ¢σтα)
» ѕιη(α+в)=ѕιηα ¢σѕв+ ¢σѕα ѕιηв.
» ¢σѕ(α+в)=¢σѕα ¢σѕв +ѕιηα ѕιηв.
» ѕιη(α-в)=ѕιηα¢σѕв-¢σѕαѕιηв.
» ¢σѕ(α-в)=¢σѕα¢σѕв+ѕιηαѕιηв.
» тαη(α+в)= (тαηα + тαηв)/ (1−тαηαтαηв)
» тαη(α−в)= (тαηα − тαηв) / (1+ тαηαтαηв)
» ¢σт(α+в)= (¢σтα¢σтв −1) / (¢σтα + ¢σтв)
» ¢σт(α−в)= (¢σтα¢σтв + 1) / (¢σтв− ¢σтα)

α/ѕιηα = в/ѕιηв = ¢/ѕιη¢ = 2я
» α = в ¢σѕ¢ + ¢ ¢σѕв
» в = α ¢σѕ¢ + ¢ ¢σѕα
» ¢ = α ¢σѕв + в ¢σѕα
» ¢σѕα = (в² + ¢²− α²) / 2в¢
» ¢σѕв = (¢² + α²− в²) / 2¢α
» ¢σѕ¢ = (α² + в²− ¢²) / 2¢α
» Δ = αв¢/4я
» ѕιηΘ = 0 тнєη,Θ = ηΠ
» ѕιηΘ = 1 тнєη,Θ = (4η + 1)Π/2
» ѕιηΘ =−1 тнєη,Θ = (4η− 1)Π/2
» ѕιηΘ = ѕιηα тнєη,Θ = ηΠ (−1)^ηα

1. ѕιη2α = 2ѕιηα¢σѕα
2. ¢σѕ2α = ¢σѕ²α − ѕιη²α
3. ¢σѕ2α = 2¢σѕ²α − 1
4. ¢σѕ2α = 1 − ѕιη²α
5. 2ѕιη²α = 1 − ¢σѕ2α
6. 1 + ѕιη2α = (ѕιηα + ¢σѕα)²
7. 1 − ѕιη2α = (ѕιηα − ¢σѕα)²
8. тαη2α = 2тαηα / (1 − тαη²α)
9. ѕιη2α = 2тαηα / (1 + тαη²α)
10. ¢σѕ2α = (1 − тαη²α) / (1 + тαη²α)
11. 4ѕιη³α = 3ѕιηα − ѕιη3α
12. 4¢σѕ³α = 3¢σѕα + ¢σѕ3α

🍄🍄🍄🍄🍄
» ѕιη²Θ+¢σѕ²Θ=1
» ѕє¢²Θ-тαη²Θ=1
» ¢σѕє¢²Θ-¢σт²Θ=1
» ѕιηΘ=1/¢σѕє¢Θ
» ¢σѕє¢Θ=1/ѕιηΘ
» ¢σѕΘ=1/ѕє¢Θ
» ѕє¢Θ=1/¢σѕΘ
» тαηΘ=1/¢σтΘ
» ¢σтΘ=1/тαηΘ
» тαηΘ=ѕιηΘ/¢σѕΘ