A brief overview with examples.

CHAPTER 1: "Group functions"
CHAPTER 2: "Popular analytical functions"
CHAPTER 3: "Hierarchical Query"


CHAPTER 1: "Group functions"
Task 1.1. Group functions and group by clause.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(ID NUMBER
                          N NUMBER
                          V VARCHAR2(100), 
                          D DATE);
        INSERT INTO TAB1 (ID, N, V, D) VALUES (1, 11,   'A', SYSDATE);
        INSERT INTO TAB1 (ID, N, V, D) VALUES (2, 21,   'B', SYSDATE);
        INSERT INTO TAB1 (ID, N, V, D) VALUES (3, 22,   'B', SYSDATE);
        INSERT INTO TAB1 (ID, N, V, D) VALUES (4, 31,   'C', SYSDATE);
        INSERT INTO TAB1 (ID, N, V, D) VALUES (5, NULL, 'C', SYSDATE);
        INSERT INTO TAB1 (ID, N, V, D) VALUES (6, 33,   'C', SYSDATE);
        COMMIT;
    Then do next:
    1. What do group function?
    2. What maximum nesting level for aggregate functions?
    3. Make result:
        A 11 11
        B 22 21
        C 33 31
    4. For which datatypes allowed using SUM and AVG.
    5. Demonstrate different COUNT for all rows and for column N (for N also
    demonstrate result equal COUNT for all rows).
Solution:
    --1
    Group functions work with sets of rows to give one result per group.
    --2
    2
    --3
    SELECT V,
           MAX(N), 
           MIN(N)
        FROM TAB1
            GROUP BY V
                ORDER BY V
    --4
    NUMBER
    --5
    SELECT COUNT(*), 
           COUNT(1),
           COUNT(NVL(N,1)),
           COUNT(N),
           COUNT(DISTINCT N)
        FROM TAB1

Task 1.2. ROLLUP.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. Demonstrate (ROLLUP and non-ROLLUPSUM(N) for V and add grand total to the 
    end of result. Grand total must have name, do not display NULL. Write result.
    2. Demonstrate (ROLLUP and non-ROLLUPSUM(N) for D, V and add grand total to 
    the end of result. Grand total must have name, do not display NULL. Write result.
Solution:
    --1
    SELECT V, 
           SUM(N) 
        FROM TAB1
            GROUP BY V
    UNION ALL
    SELECT 'Grand total', 
           SUM(N) 
        FROM TAB1
    --
    SELECT NVL(V, 'GRAND TOTAL'),
           SUM(N)
        FROM TAB1
            GROUP BY ROLLUP (V)
    --Result
    A           80
    B           20
    C           170
    GRAND TOTAL 270
    --2
    SELECT TO_CHAR(D), 
           V, 
           SUM(N) 
        FROM TAB1
            GROUP BY D, V
    UNION ALL
    SELECT TO_CHAR(D), 
           NULL--OR '{Sub total}'
           SUM(N) 
        FROM TAB1
            GROUP BY D
    UNION ALL
    SELECT 'Grand total', 
           NULL,
           SUM(N) 
        FROM TAB1
    ORDER BY 1, 2
    --Rollup produce n + 1 totals.
    SELECT NVL(TO_CHAR(D), 'GRAND TOTAL'),
           V,
           SUM(N)
        FROM TAB1
            GROUP BY ROLLUP (D, V)
                ORDER BY 1, 2
    --Result
    1_SEPTEMBER A   10
    1_SEPTEMBER B   20
    1_SEPTEMBER     30
    2_OCTOBER   C   90
    2_OCTOBER       90
    3_NOVEMBER  A   70
    3_NOVEMBER  C   80
    3_NOVEMBER      150
    GRAND TOTAL     270
    
Task 1.3. CUBE.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. Using CUBE demonstrate SUM(N) for V. Total must have name 'Total', do not 
    display NULL. Which difference between ROLLUP and CUBE. Write result.
    2. Using CUBE demonstrate SUM(N) for D, V. All totals must have name 'Total', do 
    not display NULL. Write result.
Solution:
    --1
    --CUBE have result at the upper of table without order.
    SELECT NVL(V, 'TOTAL'),
           SUM(N)
        FROM TAB1
            GROUP BY CUBE (V)
    --result
    TOTAL    270
    A        80
    B        20
    C        170           
    --2
    --CUBE instead ROLLUP produces subtotals for all possible combinations of
    --groupings specified in the GROUP BY CUBE clause, and a grand total.
    --CUBE totals = 2^(count of columns from group by)
    SELECT NVL(TO_CHAR(D), 'Total'),
           NVL(V, 'Total'),
           SUM(N)
        FROM TAB1
            GROUP BY CUBE (D, V)
                ORDER BY 1, 2
    --result
    1_SEPTEMBER A       10
    1_SEPTEMBER B       20
    1_SEPTEMBER Total   30
    2_OCTOBER   C       90
    2_OCTOBER   Total   90
    3_NOVEMBER  A       70
    3_NOVEMBER  C       80
    3_NOVEMBER  Total   150
    Total       A       80
    Total       B       20
    Total       C       170
    Total       Total   270  
    
Task 1.4. GROUPING.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. Explain GROUPING.
    2. Using COUNT function and ROLLUP (for D and N colummns) demonstrate GROUPING 
    for TAB1. Totals must have name 'Total'. Write result.
    3. Using COUNT function and CUBE (for D and N colummns) demonstrate GROUPING for 
    TAB1. Totals must have name 'Total'. Write result.
Solution:
    --1
    GROUPING - is the function returns 1 (for subtotals from ROLLUP or CUBE), 
    otherwise return 0, also when return 1 this mean that in current row this column
    not participate in GROUP BY clause for calculate subtotal. Using for make visual 
    difference between NULL values and NULL-total from ROLLUP and CUBE.
    GROUPING argument or expression must match with argument or expression from GROUP
    BY.
    --2
    SELECT DECODE(GROUPING(D), 1, 'Total', D),
           DECODE(GROUPING(N), 1, 'Total', N),
           COUNT(V),
           GROUPING(D),
           GROUPING(N)
        FROM TAB1
            GROUP BY ROLLUP (D, N)
                ORDER BY 1, 2
    1_SEPTEMBER 10      1   0   0
    1_SEPTEMBER 20      1   0   0
    1_SEPTEMBER Total   2   0   1
    2_OCTOBER   30      1   0   0
    2_OCTOBER   60      1   0   0
    2_OCTOBER   Total   3   0   1
    2_OCTOBER           1   0   0
    3_NOVEMBER  70      1   0   0
    3_NOVEMBER  80      1   0   0
    3_NOVEMBER  Total   2   0   1
    Total       Total   7   1   1
    --3
    SELECT DECODE(GROUPING(D), 1, 'Total', D),
           DECODE(GROUPING(N), 1, 'Total', N),
           COUNT(V),
           GROUPING(D),
           GROUPING(N)
        FROM TAB1
            GROUP BY CUBE (D, N)
                ORDER BY 1, 2    
    1_SEPTEMBER 10      1   0   0
    1_SEPTEMBER 20      1   0   0
    1_SEPTEMBER Total   2   0   1
    2_OCTOBER   30      1   0   0
    2_OCTOBER   60      1   0   0
    2_OCTOBER   Total   3   0   1
    2_OCTOBER           1   0   0
    3_NOVEMBER  70      1   0   0
    3_NOVEMBER  80      1   0   0
    3_NOVEMBER  Total   2   0   1
    Total       10      1   1   0
    Total       20      1   1   0
    Total       30      1   1   0
    Total       60      1   1   0
    Total       70      1   1   0
    Total       80      1   1   0
    Total       Total   7   1   1
    Total               1   1   0

Task 1.5. GROUPING_ID.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. Explain GROUPING_ID.
    2. Using COUNT function and ROLLUP (for D and N colummns) demonstrate GROUPING_ID
    for TAB1. Totals must have name 'Total'. Write result.
    3. Using COUNT function and CUBE (for D and N colummns) demonstrate GROUPING_ID 
    for TAB1. Totals must have name 'Total'. Write result.
Solution:
    --1
    GROUPING_ID - is the function, that return a number for subtotal rows (from 
    ROLLUP or CUBE) and this number indicate the GROUP BY level. In this function 
    need write all columns from GROUP BY clause.
    --2
    --GROUPIND_ID displays level for each totals from ROLLUP, where number 0 is first
    --detailed total, then 1 is total for first argument from GROUPIND_ID and etc.
    SELECT DECODE(GROUPING_ID(D, N), 3, 'Total',
                                     D) D,
           DECODE(GROUPING_ID(D, N), 1, 'Total',
                                     N) N,
           COUNT(V),
           GROUPING_ID(D, N)
        FROM TAB1
            GROUP BY ROLLUP (D, N)
                ORDER BY 1, 2
    1_SEPTEMBER 10      1   0
    1_SEPTEMBER 20      1   0
    1_SEPTEMBER Total   2   1
    2_OCTOBER   30      1   0
    2_OCTOBER   60      1   0
    2_OCTOBER   Total   3   1
    2_OCTOBER           1   0
    3_NOVEMBER  70      1   0
    3_NOVEMBER  80      1   0
    3_NOVEMBER  Total   2   1
    Total               7   3
    --3
    --GROUPIND_ID displays level for each totals from CUBE, where number 0 is first
    --detailed total, then 1 is total for first argument from GROUPIND_ID and etc.
    SELECT DECODE(GROUPING_ID(D, N), 2, 'Total',
                                     3, 'Total',
                                     D) D,
           DECODE(GROUPING_ID(D, N), 1, 'Total',
                                     3, 'Total',
                                     N) N,
           COUNT(V),
           GROUPING_ID(D, N)
        FROM TAB1
            GROUP BY CUBE (D, N)
                ORDER BY 1, 2
    1_SEPTEMBER 10      1   0
    1_SEPTEMBER 20      1   0
    1_SEPTEMBER Total   2   1
    2_OCTOBER   30      1   0
    2_OCTOBER   60      1   0
    2_OCTOBER   Total   3   1
    2_OCTOBER           1   0
    3_NOVEMBER  70      1   0
    3_NOVEMBER  80      1   0
    3_NOVEMBER  Total   2   1
    Total       10      1   2
    Total       20      1   2
    Total       30      1   2
    Total       60      1   2
    Total       70      1   2
    Total       80      1   2
    Total       Total   7   3
    Total               1   2  
    
Task 1.6. GROUPING SETS.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. Explain GROUPING SETS.
    2. Write a query (order by D) displaying SUM(N) for D, V and demonstrating 
    GROUPING SETS where also must be grand total. Write result.
    3. Remake query from point 2 and demonstrating the use of GROUPING SETS instead 
    of ROLLUP, indicates totals by GROUPING_ID(V, D). Write result.
    4. Remake query from point 2 and demonstrating the use of GROUPING SETS instead 
    of CUBE, indicates totals by GROUPING_ID(V, D). Write result.
    5. What happens if at a query from point 2 add in GROUPING SETS clause CUBE(D,V)?
Solution:
    --1
    GROUPING SETS allows create totals for certain columns, column`s sets or for all 
    columns. All grouping columns from SELECT clause must be listed at the GROUPING 
    SETS.
    --2
    SELECT D, 
           V, 
           SUM(N)
        FROM TAB1
            GROUP BY GROUPING SETS (
                                    (D, V), ()
                                   )
                ORDER BY D
    --
    D            V  SUM(N)
    1_SEPTEMBER  A  10
    1_SEPTEMBER  B  20
    2_OCTOBER    C  90
    3_NOVEMBER   A  70
    3_NOVEMBER   C  80
                    270
    --3
    SELECT D,
           V,
           SUM(N),
           GROUPING_ID(V, D)
        FROM TAB1
            GROUP BY GROUPING SETS (
                                    (D, V), D, ()
                                   )
                ORDER BY D
    --
    D            V  SUM(N)  GROUPING_ID(V,D)
    1_SEPTEMBER  A  10      0
    1_SEPTEMBER  B  20      0
    1_SEPTEMBER     30      2
    2_OCTOBER    C  90      0
    2_OCTOBER       90      2
    3_NOVEMBER   A  70      0
    3_NOVEMBER   C  80      0
    3_NOVEMBER      150     2
                    270     3
    --4
    SELECT D,
           V,
           SUM(N),
           GROUPING_ID(V, D)
        FROM TAB1
            GROUP BY GROUPING SETS (
                                    (D, V), (D), (V), ()
                                   )
                ORDER BY D    
    --
    D            V  SUM(N)  GROUPING_ID(V,D)
    1_SEPTEMBER  A  10      0
    1_SEPTEMBER     30      2
    1_SEPTEMBER  B  20      0
    2_OCTOBER       90      2
    2_OCTOBER    C  90      0
    3_NOVEMBER      150     2
    3_NOVEMBER   A  70      0
    3_NOVEMBER   C  80      0
                 B  20      1
                 A  80      1
                 C  170     1
                    270     3 
    --5
    --will be added additional rows with totals as at using the CUBE
    SELECT D, 
           V, 
           SUM(N)
        FROM TAB1
            GROUP BY GROUPING SETS (
                                    (D, V), (), CUBE(D, V)
                                   )
                ORDER BY D

Task 1.7. GROUPING SETS. Part 2.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(YEAR VARCHAR2(100), 
                          SHOP VARCHAR2(100), 
                          PRODUCT VARCHAR2(100),
                          COLOR VARCHAR2(100),
                          QUANTITY NUMBER);
        INSERT INTO TAB1 (YEAR, SHOP, PRODUCT, COLOR, QUANTITY) 
            VALUES (2000, 'shop1', 'a', 'black', 1);
        INSERT INTO TAB1 (YEAR, SHOP, PRODUCT, COLOR, QUANTITY) 
            VALUES (2000, 'shop2', 'a', 'white', 2);
        INSERT INTO TAB1 (YEAR, SHOP, PRODUCT, COLOR, QUANTITY) 
            VALUES (2001, 'shop1', 'a', 'white', 4);
        COMMIT;
    Then do next:
    1. Using several GROUPING SETS write a query displaying all columns + 
    SUM(QUANTITY) (also display and sort by GROUPING ID), for next groups:
        YEAR + PRODUCT,
        YEAR + COLOR, 
        SHOP + PRODUCT,
        SHOP + COLOR.
    Write result.
Solution:
    --1
    SELECT NVL(YEAR, '-'),
           NVL(SHOP, '-'),
           NVL(PRODUCT, '-'),
           NVL(COLOR, '-'),
           SUM(QUANTITY),
           GROUPING_ID(YEAR, SHOP, PRODUCT, COLOR) GID
        FROM TAB1
            GROUP BY GROUPING SETS (YEAR, SHOP), 
                     GROUPING SETS (PRODUCT, COLOR)
                ORDER BY GID;
    --Result = 4 groups = quantity of columns from GROUPING SETS (YEAR, SHOP) * 
    --quantity of columns from GROUPING SETS (PRODUCT, COLOR)
    "YEAR"    "SHOP"    "PRODUCT"    "COLOR"    "QUANTITY"  "GID"
    2001    -       a           -       4           5
    2000    -       a           -       3           5
    2000    -       -           white   2           6
    2000    -       -           black   1           6
    2001    -       -           white   4           6
    -       shop1   a           -       5           9
    -       shop2   a           -       2           9
    -       shop1   -           black   1           10
    -       shop2   -           white   2           10
    -       shop1   -           white   4           10      
                    
Task 1.8. ROLLUP and CUBE with composite columns.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. Write a query (order by D) displaying SUM(N) for D, V and demonstrating total 
    for (D, V), total for D and also grand total using composite columns. Write result.
    2. Write a query (order by D) displaying SUM(N) for D, V and demonstrating total 
    for (D, V) and grand total using composite columns. Write result.    
Solution:
    --1
    --will be 3 totals: for D, for D, V and grand total
    SELECT D, 
           V, 
           SUM(N)
        FROM TAB1
            GROUP BY ROLLUP(
                            D, (D, V)
                           )
                ORDER BY D
    1_SEPTEMBER A   10
    1_SEPTEMBER B   20
    1_SEPTEMBER     30
    2_OCTOBER   C   90
    2_OCTOBER       90
    3_NOVEMBER  A   70
    3_NOVEMBER  C   80
    3_NOVEMBER      150
                    270                
    --2
    --will be 2 totals: D, V and grand total
    SELECT D, 
           V, 
           SUM(N)
        FROM TAB1
            GROUP BY CUBE(
                         (D, V)
                         )
                ORDER BY D
    1_SEPTEMBER A   10
    1_SEPTEMBER B   20
    2_OCTOBER   C   90
    3_NOVEMBER  A   70
    3_NOVEMBER  C   80
                    270    


CHAPTER 2: "Popular analytical functions"
Task 2.1. Analytic functions.
    1. What difference between analytical and aggregate functions?
    2. How to processes analytical function in query? Where is it can be appears?
    3. What does "window" mean and where is it defined? What determines a "window"?
    4. Write structure of analytic function with clauses.
Solution:
    --1
    Analytic function return multiple rows for each group.
    --2
    --analytic functions can appear only in the select list or ORDER BY clause
    Process:
    1. FROM, JOINS, WHEREGROUP BY, and HAVING clauses are completed first.
    2. Analytic function operates on a query result set.
    3. Execute ORDER BY (if exists) for additional sorting.
    --3
    The group of rows is called a window and is defined by the analytic_clause. For 
    each row, a sliding window of rows is defined.
    The window determines the range of rows used to perform the calculations for the 
    current row. Window is extension of ORDER_BY_CLAUSE in analytic function.
    --4
    Analytic_function(arguments) -> OVER (analytic_clause)
    analytic_clause = query_partition_clause -> order_by_clause -> windowing_clause 

Task 2.2. RANKDENSE_RANKROWNUMROW_NUMBER.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    In any places with ORDER BY use ASC ordering and then do next:
    1. What does RANK calculates?
    2. Demonstrate RANK for row with N = 60, V = 'Z' and write type of syntax. Write 
    result.
    3. Using different syntax from point 2 write query with:
    - RANK for column V;
    - DENSE_RANK for column V;
    - ROWNUM;
    - ROW_NUMBER for column V.
    Write result.
    4. With partition for V using different syntax from point 2 write query with:
    - RANK for column V;
    - DENSE_RANK for column V;
    - ROWNUM;
    - ROW_NUMBER for column V.
    Write result.
Solution:
    --1
    RANK calculates the rank of a value in a group of values.
    --2
    --Aggregate Syntax
    --result = 5
    SELECT RANK(60, 'Z') WITHIN GROUP (ORDER BY N, V)
        FROM TAB1
    --3
    --Analytic Syntax
    SELECT V,
           RANK() OVER (ORDER BY V) "RANK",
           DENSE_RANK() OVER (ORDER BY V) "DENSE_RANK",
           ROWNUM "ROWNUM",
           ROW_NUMBER() OVER (ORDER BY V) "ROW_NUMBER"
        FROM TAB1
    --result
    V    RANK    DENSE_RANK    ROWNUM    ROW_NUMBER
    A    1        1            6        1
    A    1        1            1        2
    B    3        2            2        3
    C    4        3            5        4
    C    4        3            7        5
    C    4        3            3        6
    C    4        3            4        7
    --4
    SELECT V,
           RANK() OVER (PARTITION BY V ORDER BY V) "RANK",
           DENSE_RANK() OVER (PARTITION BY V ORDER BY V) "DENSE_RANK",
           ROWNUM "ROWNUM",
           ROW_NUMBER() OVER (PARTITION BY V ORDER BY V) "ROW_NUMBER"
        FROM TAB1
    --result
    V    RANK    DENSE_RANK    ROWNUM    ROW_NUMBER
    A    1        1            6        1
    A    1        1            1        2
    B    1        1            2        1
    C    1        1            5        1
    C    1        1            7        2
    C    1        1            3        3
    C    1        1            4        4
    
Task 2.3. Sorting and RANKDENSE_RANKROWNUMROW_NUMBER.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. Using analytical function for partition = V and sorting N by ascending 
    display in result all columns with only first rows from each partition. Write 
    result.
    2. Make 1 query where summarize of N and sort sum by DESC. Result must have 3
    rows. Do not write ORDER BY clause at the end of query. Write result.
    3. Make 1 query where display N, RANK for partition N + order by any column.
    But sorting in column N must be descending. Do not write ORDER BY clause at the 
    end of query. Write result.
Solution:
    --1
    SELECT t.*
        FROM 
        (
        SELECT t1.*,
                ROW_NUMBER() OVER (PARTITION BY V ORDER BY N ASC) FIRST_ROW
            FROM TAB1 t1
        ) t
            WHERE t.FIRST_ROW = 1
    --result
    N   V   D           FIRST_ROW
    10  A   1_SEPTEMBER 1
    20  B   1_SEPTEMBER 1
    30  C   2_OCTOBER   1
    --2
    SELECT V,
           SUM(N) SUMN,
           DENSE_RANK() OVER(ORDER BY SUM(N) DESC) ORDERING
        FROM TAB1
            GROUP BY V
    --result
    V    SUMN    ORDERING
    C   170     1
    A   80      2
    B   20      3
    --3
    SELECT NVL(TO_CHAR(N), 'NULL') N,
           RANK() OVER (PARTITION BY N * -1 ORDER BY V ASC) ORDERING
        FROM TAB1
    --result
    N       ORDERING
    80      1
    70      1
    60      1
    30      1
    20      1
    10      1
    NULL    1

Task 2.4. Using analytical functions.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. Write query using analytical functions where display all columns and:
    - SUM of N;
    - SUM of N with order by D ASC;
    - SUM of N with order by D, N ASC;
    - SUM of N with partition by D and order by D ASC;
    - SUM of N with partition of D and order by N ASC;
Solution:
    --1
    SELECT N,
            V,
            D,
            SUM(N) OVER() S1,
            SUM(N) OVER (ORDER BY D) S2,
            SUM(N) OVER (ORDER BY D, N) S3,
            SUM(N) OVER (PARTITION BY D ORDER BY D) S4,
            SUM(N) OVER (PARTITION BY D ORDER BY N) S5
        FROM TAB1
    --
    N   V   D           S1  S2  S3  S4  S5
    10  A   1_SEPTEMBER 270 30  10  30  10
    20  B   1_SEPTEMBER 270 30  30  30  30
    30  C   2_OCTOBER   270 120 60  90  30
    60  C   2_OCTOBER   270 120 120 90  90
        C   2_OCTOBER   270 120 120 90  90
    70  A   3_NOVEMBER  270 270 190 150 70
    80  C   3_NOVEMBER  270 270 270 150 150
    
Task 2.5. LAGLEAD.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    In any places with ORDER BY use ASC ordering and then do next:
    1. Make query where display:
    - N;
    - using short syntax (is mean use default values and do not write them in query) 
    display for NVL(N, 99) (order by D) previous and next values. Write result.
    2. Make query where display:
    - N;
    - using full syntax display for N partition by D order by V previous (eliminate 
    NULL rows in N column) and next (do not eliminate NULL rows in N column) values 
    with step = 1 (if value is out of scope then display 0). Write result.
Solution:
    --1
    SELECT N,
           LAG(NVL(N, 99)) OVER (ORDER BY D) LG,
           LEAD(NVL(N, 99)) OVER (ORDER BY D) LD           
        FROM TAB1
    --
    N   LG  LD
    10      20
    20  10  30
    30  20  99
        30  60
    60  99  70
    70  60  80
    80  70  
    --2
    SELECT N,
           LAG(N, 1, 0) IGNORE NULLS OVER (PARTITION BY D ORDER BY V) LAG,
           LEAD(N RESPECT NULLS, 1, 0) OVER (PARTITION BY D ORDER BY V) LEAD
        FROM TAB1
    --
    N   LAG LEAD
    10  0   20
    20  10  0
    30  0   
        30  60
    60  30  0
    70  0   80
    80  70  0


CHAPTER 3: "Hierarchical Query"
Task 3.1. Hierarchical query pseudocolumns.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(N NUMBER
                          V VARCHAR2(100), 
                          D VARCHAR2(100));
        INSERT INTO TAB1 (N, V, D) VALUES (10,   'A', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (20,   'B', '1_SEPTEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (30,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (60,   'C', '2_OCTOBER');
        INSERT INTO TAB1 (N, V, D) VALUES (70,   'A', '3_NOVEMBER');
        INSERT INTO TAB1 (N, V, D) VALUES (80,   'C', '3_NOVEMBER');
        COMMIT;
    Then do next:
    1. List the hierarchical query pseudocolumns and explain what they returns.
    2. Write hierarchical_query_clause and explain clauses.
    3. What are two operators valid only in hierarchical queries?
Solution:
    --1
    --CONNECT_BY_ISCYCLE
    CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which 
    is also its ancestor. Otherwise it returns 0. You can specify CONNECT_BY_ISCYCLE 
    only if you have specified the NOCYCLE parameter of the CONNECT BY clause.
    --CONNECT_BY_ISLEAF
    CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the 
    tree defined by the CONNECT BY condition. Otherwise it returns 0. This 
    information indicates whether a given row can be further expanded to show more of 
    the hierarchy.
    --LEVEL
    For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 
    for a root row, 2 for a child of a root, and so on. A 'root row' is the highest 
    row within an inverted tree. A 'child row' is any nonroot row. A 'parent row' is 
    any row that has children. A 'leaf row' is any row without children.
    --2
               => NOCYCLE =>           => START WITH => condition =>
    CONNECT BY ============> condition ============================>
    --or
                                          => NOCYCLE =>
    START WITH => condition => CONNECT BY ============> condition
    --
    'START WITH' specifies the root row(s) of the hierarchy.
    'CONDITION' can be any Oracle condition. In a hierarchical query, one expression 
    in condition must be qualified with PRIOR operator to refer to the parent row.
    'CONNECT BY' specifies the relationship between parent rows and child rows.
    'NOCYCLE' parameter instructs Oracle to return rows from a query even if a 
    CONNECT BY loop exists in the data. Use with CONNECT_BY_ISCYCLE pseudocolumn 
    to see which rows contain the loop.
    --3
    PRIOR and CONNECT_BY_ROOT
    
Task 3.2. Hierarchical query. Part 1.
    1. Which objects PRIOR can`t be refer?
    2. Which precedence between PRIOR and unary arithmetic operators?
    3. What PRIOR causes?
    4. What is CONNECT_BY_ROOT returns? Where can we use it? Which is restriction for
    CONNECT_BY_ROOT?
    5. How to Oracle processes hierarchical queries?
    6. How to preserve ordering within the hierarchy?
Solution:
    --1
    Sequence: CURRVALNEXTVAL
    --2
    PRIOR is a unary operator and has the same precedence as the unary + and - 
    (positive or negative) arithmetic operators.
    --3
    PRIOR causes Oracle to use the value of the parent row in the column.
    --4
    Returns the column value using data from the root row. In SELECT clause when you 
    qualify a column with this operator. You cannot specify this operator in the 
    START WITH condition or the CONNECT BY condition.
    --5
    - A joins in FROMWHERE clauses, if present, is evaluated first;
    - The CONNECT BY condition is evaluated;
    - Any remaining WHERE clause predicates are evaluated.
    --6
    ORDER SIBLINGS BY my_column
    
Task 3.3. Hierarchical query. Part 2.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(ID NUMBER,
                          PID NUMBER,
                          NAME VARCHAR2(4000));
        INSERT INTO TAB1(ID, PID, NAME) VALUES(0,    NULL,  'A');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(1,    0,     'B');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(2,    0,     'C');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(NULL, 0,     'D');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(4,    2,     'E');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(5,    NULL,  'F');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(6,    5,     'G');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(7,    3,     'H');
        COMMIT;
    Suppose that ID is child column, PID is parent. Then do next:
    1. Write hierarchical query without specifies root rows and also indicate level 
    of each row. Explain how much rows will be in result.
    2. Remake query from point 1, but:
    - root row must contain NULL in PID and sort by NAME within hierarchy;
    - instead NAME use function that returns the path of a NAME from root to node;
    - for each row display NAME from root row.
    Write result.
    3. Remake query from point 1, but:
    - display only leaf rows;
    - root row must contain ID = 0 and sort by NAME within hierarchy.
    Write result.
    4. Write query where display column NAME in one row as H,G,F,E,D,C,B,A.
Solution:
    --1
    --Will be retrieved 14 rows: 8 rows for 1 level, 5 rows for 2 level, 1 row for 3 
    --level. Rows A and F not will be shown as 2 level for parent row D, because NULL
    SELECT ID,
           PID,
           NAME,
           LEVEL
        FROM TAB1
            CONNECT BY PRIOR ID = PID --If PID from current row <> ID from another
                                      --rows, that is current row is root, otherwise
                                      --is child.
                ORDER BY LEVEL,
                         NAME
    --2
    SELECT ID,
           PID,
           SYS_CONNECT_BY_PATH(NAME, '/') SCBP, --valid only in hierarchical queries
           CONNECT_BY_ROOT(NAME) CBR,
           LEVEL
        FROM TAB1
            CONNECT BY PRIOR ID = PID
                START WITH PID IS NULL --it is mean that rows with PID = NILL is root
                    ORDER SIBLINGS BY NAME
    --will be two root rows: A and F
    ID  PID SCBP    CBR LEVEL
    0       /A      A   1
    1   0   /A/B    A   2
    2   0   /A/C    A   2
    4   2   /A/C/E  A   3
        0   /A/D    A   2
    5       /F      F   1
    6   5   /F/G    F   2
    --3
    SELECT ID,
           PID,
           NAME,
           LEVEL,
           CONNECT_BY_ISLEAF
        FROM TAB1
            WHERE CONNECT_BY_ISLEAF = 1
                CONNECT BY PID = PRIOR ID
                    START WITH ID = 0
                        ORDER SIBLINGS BY NAME
    --
    ID  PID NAME    LEVEL   CONNECT_BY_ISLEAF
    1   0   B       2       1
    4   2   E       3       1
        0   D       2       1
    --4
    SELECT LTRIM(SYS_CONNECT_BY_PATH(NAME, ','), ',') NAME_ROW
        FROM
            (
            SELECT ROWNUM r, 
                   NAME 
                FROM TAB1
            )
            WHERE CONNECT_BY_ISLEAF = 1
                START WITH r = 8
                    CONNECT BY r = PRIOR r - 1

Task 3.4. Hierarchical query. Part 3.
    Do next operations:
        DROP TABLE TAB1;
        CREATE TABLE TAB1(ID NUMBER,
                          PID NUMBER,
                          NAME VARCHAR2(4000));
        INSERT INTO TAB1(ID, PID, NAME) VALUES(0,    NULL,  'A');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(1,    0,     'B');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(2,    0,     'C');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(2,    2,     'D');
        INSERT INTO TAB1(ID, PID, NAME) VALUES(4,    2,     'E');
        COMMIT;
    Suppose that ID is child column, PID is parent. Then do next:
    1. Create hierarchical query where root row must contain ID = 0. Indicate
    row that has a child which is also its ancestor. Write result.
    2. Create hierarchical query where root row must contain ID = 0 and calculate
    count ID for PID, NAME. In result display only row that has a child which is 
    also its ancestor. Write result.
    3. Make query that return dates for year (365 or 366 rows, since 1 JAN to 31 DEC)
    depends on leap year.
    4. Split string 'a,b,c,,d' into one column with values 'a', 'b', 'c', 'd' using 
    hierarchical query and REGEXP functions.
Solution:
    --1
    SELECT ID,
           PID,
           NAME,
           CONNECT_BY_ISCYCLE
        FROM TAB1
            CONNECT BY NOCYCLE PRIOR ID = PID
                START WITH ID = 0
    --
    ID  PID NAME    CONNECT_BY_ISCYCLE
    0       A       0
    1   0   B       0
    2   0   C       1
    4   2   E       0
    --2
    SELECT COUNT(ID) CNT,
           PID,
           NAME
        FROM TAB1
            WHERE CONNECT_BY_ISCYCLE = 1
                CONNECT BY NOCYCLE PRIOR ID = PID
                    START WITH ID = 0
                        GROUP BY PID, NAME
    --
    CNT PID NAME
    1   0   C
    --3
    SELECT TO_DATE('01.01.'||EXTRACT(YEAR FROM SYSDATE)||'','DD.MM.YYYY') + LEVEL - 1     
        FROM DUAL 
            CONNECT BY LEVEL <= 337 + 
                  TO_DATE('01.03.'||EXTRACT(YEAR FROM SYSDATE)||'','DD.MM.YYYY') -   
                  TO_DATE('01.02.'||EXTRACT(YEAR FROM SYSDATE)||'','DD.MM.YYYY')
    --4
    SELECT REGEXP_SUBSTR(t.STR, '[^,]+', 1, LEVEL) COL
        FROM
            (
            SELECT 'a,b,c,,d' STR
                FROM DUAL
            ) t
            CONNECT BY NVL(REGEXP_INSTR(t.STR, '[^,]+', 1, LEVEL),
                           0) <> 0