Oracle has some neat ways of handling hierarchical data.
A single Oracle SQL can find the entire tree of data above or below a node in a hierarchy using the CONNECT BY clause.
What if the requirement is to flatten hierarchical data?
For example, given a table containing the employee reporting hierarchy of an organization (image alongside), get a single SQL to return the 4 types of employee roles as columns:
- PROJECT_MANAGER (Level 1)
- DBA (Level 2)
- TEAM_LEAD (Level 2)
This is trickier than transposing rows as columns in a non-hierarchical table, and needs a little extra to get the result. But it is nowhere near as tough as it appears. No multiple self-joins, no recursive CTE.
This post will show you how to flatten hierarchical data using a single SQL in Oracle.
[click to continue…]
LEAD/LAG are analytic functions that provide access to more than one row of a table at the same time, without a self join. Let’s see how.
Take a table that stores the master list of exam grades, mapped to the upper limit up to which the grade applies. The table (GRADE_MASTER) has two columns: {GRADE_CODE, SCORE_UPTO}. For exam scores in the range 0-100, GRADE_MASTER specifies the A-F.
SQL> desc grade_master
Name Null? Type
----------------- -------- -----------
GRADE_CODE NOT NULL VARCHAR2(2)
SCORE_UPTO NOT NULL NUMBER(3)
SQL> select * from grade_master;
GR SCORE_UPTO
-- ----------
F 59
D 69
C 79
B 89
A 100
The above data means that grade F applies to scores 0-59, D applies to scores 60-69, C to scores 70-79, and so on.
To find the grade for a given the examination score, the SQL needs to compare the EXTENT values across *two* rows. Comparing a column’s value across more than one row can be tricky to implement – unless you turn to Oracle functions LEAD/LAG.
[click to continue…]
An inline view is a subquery with an alias that you can use within a SQL statement. An inline view behaves just as if the subquery were a table name.
A classic use of inline views is in queries for Top-N analysis. See the one used for finding Nth row from a table:
[click to continue…]
A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause.
For example, a query to list employees whose salary is more than their department’s average:
[click to continue…]
A subquery is – to put it simply – a query within a query.
What purpose does a subquery serve?
A subquery may be needed when it takes more than a single step to reach the answer.
Suppose we need to find all employees who work in the same department as KING. We need to:
[click to continue…]
