NVL vs Coalesce

• NVL is an Oracle Function. Hence works perfect in Oracle, may or may not work on other RDBMS softwares.
  
• Coalesce is an ASCII function. Hence works perfect on any RDBMS software.
  
• NVL can implicitly convert datatypes. But Coalesce cannot.
  
  
  Example:
  
  
  
  
  
  
   
• Syntaxes:    NVL (expr1, expr2 )
  
  Coalesce( expr1, expr2 )
  
• Coalesce executes faster than NVL. (~ 0.5 times ). This is because, NVL evaluates both the arguments and Coalesce evaluates only first argument.

NVL vs NVL2

• NVL , NVL2 are special functions of Oracle.
  
• NVL is similar to if-else.
  
  NVL2 is similar to conditional operator. { a>b? a:b }
  
• NVL:
  
  It is used to replace NULL value with Not Null value.
  
  Syntax:    NVL ( expr1, expr2 );
  
      If expr1 is NULL, expr2 value is returned.
  
      If expr1 is Not Null, expr1 is only returned.
  
  Example:
  
      Query: Display employee name and total salary.
  
     
  
      Select ename as 'Employee Name', sal+NVL(comm,0) as 'Total Salary' from emp;
  
  
  
  
  
  
  
  
  
  
  
  NVL(comm,0) = If comm is NULL, then return 0. Else return comm.
  
  This is used because 10+NULL = NULL;
  
  
  
  Example:
  
      Select comm, sal, NVL(comm,sal) from emp;
  
  
  
  
  
  
  Here where ever comm is null, sal is printed.
  
• NVL2:
  
  It is used to convert NULL values and NOT NULL values.
  
  It is same as conditional operator.    { a>b?a:b }
  
  Syntax:
  
      NVL2( expr1, expr2, expr3 );
  
  If expr1 = NOT NULL, returns expr2.
  
  If expr1 = NULL, returns expr3.
  
  
  
  Example:
  
      Query:    Update the employee comm as follows.
  
              Comm is NULL, update to 500.
  
              Comm is NOT NULL, update
  
     
  
  Initial Query:
  
     
  
  
  
     
  
  
  
     
  
  
  
      Updated Query:
  
  
  
  
  
  
  
  
   

Decode vs Coalesce

Decode:

• Works similarly to 'switch' in c, c++.
  
• Syntax:
  

  Decode ( Expression    , value1, return expr1
  

              , value2, return expr2
  

              ------
  

              [ default expr ] );
  

• If default expr is not provided, Oracle returns NULL.
  
• Decode must be used only if the comparison is '='.
  
• Example
  

  Query: Display employee names, salaries, job from emp.
  

          If Job = clerk, then display 'worker'
  

          If Job = manager, then display 'Boss'
  

          If Job = president, then display 'Big Boss'
  

  Select ename, sal, decode(lower(job), 'clerk', 'worker', 'manager', 'boss', 'president', 'big boss') from emp;
  

  
  

  Here, few of the employee's job is null. This is because, we didn't provided default value.
  

  Select ename, sal, decode(lower(job), 'clerk', 'worker', 'manager', 'boss', 'president', 'big boss', 'employee') from emp;
  

  
  

 

Coalesce:

• Works similar to Decode.
  
• It returns the first not null value from the list of fields.
  
• Syntax:
  

  Coalesce (expr1,expr2, ---);
  

• Example:
  

  Select comm, sal, coalesce(comm,sal) as 'coalesce' from emp;
  

  
  

  Here if we observe,
  

  First row, comm= null. Hence sal is printed.
  

  Second row, comm=300. Hence Comm is printed.

Rank vs Dense_Rank

1. Rank(), Dense_Rank() are OLAP Functions
   
2. They are used to find ranks for a table based on certain business logic.
   
3. Syntaxes:
   
   1. Rank() over ( expr );
      
   2. Dense-Rank() over ( expr );
4. The difference between rank and dense rank is that,
   
   
   
   Rank : Generate gap when there are same ranks.
   
   Dense_Rank: Doesn't generate a gap when there are same ranks.
5. These functions must not be used in where clause.
   
   
   
   
   

OLAP vs OLTP

OLTP	OLAP
OLAP stands for Online Transaction Processing.	OLTP stands for Online Analytical Processing.
It is  a  Database.	It is a Data ware House.
It stores Operational data.	It stores consolidated data.
It maintains data particular to a location or a branch.	It maintains information about the complete Organization.
They store current data. May be 1 month, 6 months or max 1 year.	They store historical data. May be 5 years, 10 years, 20 years.
They are mostly used for        -          Fast online transactions.         -          Maintain data integrity in multiple  environments.        -          Effective no of transactions per second etc..	They are mostly used for          -          Less volume of transactions.          -          Complex queries involving aggregations.          -          Response time is the effective measure.
Its main purpose is to run the business tasks.	Its main purpose is to plan, problem solving, decision support.
It is highly normalized with many tables.	It is Typically de-normalized with fewer tables, uses start or snow flake schema.
It can be relatively small if historical data is achieved.	It must be large due to historical data, requires more indexes than OLTP.
Short and Fast insert / updates by the end user.	Periodically running batch jobs will refresh the Data.

Differences in Oracle

OLAP    vs   OLTP	RANK   vs   Dense_Rank	Sysdate   vs  Timestamp	Substr   vs   Instr	ER Model   vs   RDBMS
&    vs    &&	Union  vs   Union ALL	Decode   vs    Coalesce	Alter     vs    Update	Replace   vs   Translate
NVL       vs    NVL2	Distinct   vs   Distinct ALL	Where   vs   Having	Mod      vs    Remainder	DML    vs     DDL
Months_between vs    Add_months	Column Constraints   vs     Table Constraints	Unique     vs                      Primary Key	Composite Key    vs      Primary Key	DBTimeZone vs SessionTimeZone
Primary Key    vs                 Cadidate Key	Primary Key    vs                                 Alternate Key	CurrentTimeStamp                 vs   SysTimeStamp   vs LocalTimeStamp	Drop    vs Delete   vs    Truncate	Foreign Key       vs Self Referential Integrity