Oracle SQL Scripts:
1a. Use of Self-Join to get corporate hierarchy (typically Employee - Manager relationship). The basic table structure should be as shown below, for this query to work.
 
Employee_Master
Employee_IDNUMBER
Manager_IDNUMBER
First_NameVARCHAR2(some value)
Last_NameVARCHAR2(some value)

 
1b. Another Self-Join query to display corporate hierarchy using START WITH and CONNECT BY PRIOR syntax that shows employees staggered per their Level.
Note: CONNECT BY NOCYCLE PRIOR can be used in Oracle 10g to avoid looping.
 
 
2. Analysis of Customer data that is not in the Sales Table (using row count for simplicity).
 
 
The same results (of query shown above) can be obtained by changing the query to use an Outer Join to get much faster output in large tables (over million rows of data typically in data warehouses)
 



For corrections or feedback, other useful links please contact webmaster

  1476