inline views

Hierarchical Data in a Relational Table

Flatten Hierarchical Data using a Single SQL

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 […]

Read the Full Article →
LEAD LAG Analytic Functions

Using LEAD/LAG to Place a Value within Extents

Take a table with the structure {<Category>, <Extent>}– say, a GRADE_MASTER table with two columns: {GRADE_CODE, SCORE_UPTO}. For examination scores in the range 0-100, GRADE_MASTER specifies the extent of score up to which a certain grade applies. To find the grade for a given the examination score, the SQL needs to compare the EXTENT values […]

Read the Full Article →
15 Things You Should Know about the ORDER BY Clause

15 Things You Should Know about the ORDER BY Clause

When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right. The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default. ORDER BY ASC places NULL values […]

Read the Full Article →
Inline Views

What are Inline Views in Oracle?

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 was 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:

Read the Full Article →
Correlated Subquery in SQL

Correlated Subquery

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:

Read the Full Article →
Subqueries and Their Types

Understanding Subqueries and Their Types

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:

Read the Full Article →