Question

3.Describe the use of subquery factoring clauses.

4.Describe the use of a hierarchical query.

(in 200 to 300 words)

Answer 1

`Hey,

Note: If you have any queries related the answer please do comment. I would be very happy to resolve all your queries.

3)

Subquery factoring, also known as the WITH clause, provides a convenient and flexible way for us to define subqueries and in-line views in Oracle 9i. The primary purpose of subquery factoring is to reduce repeated table accesses by generating temporary datasets during query execution.

The syntax for subquery factoring is as follows.

WITH subquery_name AS (
        SELECT ...
        )
,    another_subquery_name AS (
        SELECT ...
        )
SELECT ...
FROM   subquery_name          sq1
,      another_subquery_name  sq2
WHERE  sq1... = sq2...

4)

In its simplest form a hierarchical query needs a definition of how each child relates to its parent. This is defined using the CONNECT BY .. PRIOR clause, which defines how the current row (child) relates to a prior row (parent). In addition, the START WITH clause can be used to define the root node(s) of the hierarchy. Hierarchical queries come with operators, pseudo columns and functions to help make sense of the hierarchy.

The following query gives an example of these items based on the previously defined test table.

SET PAGESIZE 20 LINESIZE 110
COLUMN tree FORMAT A20
COLUMN path FORMAT A20

SELECT id,
       parent_id,
       RPAD('.', (level-1)*2, '.') || id AS tree,
       level,
       CONNECT_BY_ROOT id AS root_id,
       LTRIM(SYS_CONNECT_BY_PATH(id, '-'), '-') AS path,
       CONNECT_BY_ISLEAF AS leaf
FROM   tab1
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR id
ORDER SIBLINGS BY id;

Kindly revert for any queries

Thanks.