Data Base - SQL- Programing, Query Tuning, Functions - etc.

SQL Functions :


Very good link for interactive tutorial.

https://www.skillbuilders.com/Tutorials-V2/ads-wrapper.php?content=ora9i_SQL_Decode_Case/ora9i_SQL_Decode_Case.cfm&title=Oracle%20Database%20SQL%20Decode,%20Case%20Tutorial


1)  NVL()
     NVL2


2)  Decode()
 
         The DECODE function is without doubt one of the most powerful in Oracle's SQL. It is            one of several extensions Oracle added to the standard SQL language. DECODE                   can     be used for the generation of crosstab reports. You can also use the CASE               function and the COALESCE function to execute complex logical tests within your 
        SQL statements. DECODE and CASE are often used to pivot data, that is, to turn 
          rows      of data into columns of a report.

Here is the format for DECODE:

DECODE(value, if1, then1, if2, then2, if3, then3, . . . ,else)

Here, value represents any column in a table (regardless of datatype) or any result of a computation, such as one date minus another, a SUBSTR of a character column, one number times another, and so on.
value is tested for each row.

If value equals if1, then the result of the DECODE is then1;

if value equals if2, then the result of the DECODE is then2.

This continues for virtually as many if-then pairs as you can construct. If value equals none of the ifs, then the result of the DECODE is else. Each of the ifs and thens as well as the else also can be a column or the result of a function or computation. You can have up to 255 elements within the parentheses.

      ->  The DECODE function evaluates an expression in a similar way to the IF-THEN-ELSE logic.
      ->  DECODE compares an expression to each of the search values.
     ->   The syntax of DECODE is
                       DECODE ( column1/expression, search1, result1
     ->   If the default value is omitted, a null value is returned where a search value does not match
           any of the values.

           ->  Decode vs Case
3)  abs()

4)  UNION ALL

5)  SOUNDEX

6)  TO_CHAR



PLSQL

• Triggers
• Functions
• Stored Procedures.
• Built-in Packages.
• User Packages.
• Sequences
• Synonyms

Some  basic and important faqs :

1)  How is file management system in  Oracle DB ?

2)  What is Normalization Form ?  and  what are First, Second and 3rd Normalization forms ?

3)  What is integrity ?

4)  What is Data Integrity ?

5)  What is Referential Integrity and what are examples ?

6)  What are types of Constraints ?

7)  What  are Tables ? and  operations in Tables ?  ( like create, drop, truncate, alter, merging, data manipulation, inserting, deleting and updating records in tables) ?


8)  What is  Data Query Language ( DQL) ?  How to retrieving dat,a subqueries, Nested Sub Queries ?


9)  Hierarchical Retrival ?

10)  What is  Exists, Any, All  ?


11)

References :

https://www.relationaldbdesign.com/oracle-sql-extensions/module4/oracleDecode-example.php

http://slideplayer.com/slide/7556535/

https://odba.ru/showthread.php?t=118