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(COALESCE(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-ROLLUP) SUM(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-ROLLUP) SUM(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 COALESCE(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 COALESCE(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 COALESCE(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 COALESCE(TO_CHAR(D), 'Total'),
COALESCE(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
--GROUPING_ID displays level for each totals from ROLLUP, where number 0 is first
--detailed total, then 1 is total for first argument from GROUPING_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
--GROUPING_ID displays level for each totals from CUBE, where number 0 is first
--detailed total, then 1 is total for first argument from GROUPING_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 COALESCE(YEAR, '-'),
COALESCE(SHOP, '-'),
COALESCE(PRODUCT, '-'),
COALESCE(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"?
What is a default value of the "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
Process:
1. FROM, JOINS, WHERE, GROUP BY, and HAVING clauses are completed first.
2. Analytic function operates on a query result set. DISTINCT applies after
analytic function to exclude duplicates and additional sorting!
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.
Default value = RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
--4
Analytic_function(arguments) -> OVER (analytic_clause)
analytic_clause = query_partition_clause -> order_by_clause -> windowing_clause
Task 2.2. RANK, DENSE_RANK, ROWNUM, ROW_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 RANK, DENSE_RANK, ROWNUM, ROW_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 the 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 COALESCE(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. LAG, LEAD.
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 COALESCE(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(COALESCE(N, 99)) OVER (ORDER BY D) LG,
LEAD(COALESCE(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
Task 2.6. FIRST. LAST.
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;
1. What are aggregate functions can use with the FIRST or LAST?
2. Using aggregate syntax display V, minimum of N for first and last row in group
using ASC sorting for N. Do not use ORDER BY at the end of query. Write result.
3. Remake point 2, but display maximum of N. Also use ASC sorting for V. Do not
use ORDER BY at the end of the query. Write result.
4. Rewrite queries from points 2 and 3 together, but get SUM(N) instead minimum,
maximum and also use analytical syntax. Result must be sorted by V DESC, but do
not use ORDER BY at the end of the query. Write result.
Solution:
--1
MIN, MAX, SUM, AVG, COUNT, VARIANCE or STDDEV
--2
SELECT V,
MIN(N) KEEP(DENSE_RANK FIRST ORDER BY N) MN_FIRST,
MIN(N) KEEP(DENSE_RANK LAST ORDER BY N) MN_LAST
FROM TAB1
GROUP BY V
--result
V MN_FIRST MN_LAST
A 10 70
B 20 20
C 30
--3
SELECT V,
MAX(N) KEEP(DENSE_RANK FIRST ORDER BY V) MX_FIRST,
MAX(N) KEEP(DENSE_RANK LAST ORDER BY V) MX_LAST
FROM TAB1
GROUP BY V
--result
V MX_FIRST MX_LAST
A 70 70
B 20 20
C 80 80
--4
--in this analytical clause allowed only PARTITION BY
SELECT V,
SUM(N) KEEP(DENSE_RANK FIRST ORDER BY N)
OVER(PARTITION BY DECODE(V, 'A', 3,
'B', 2,
'C', 1)) SUM_FIRST_N,
SUM(N) KEEP(DENSE_RANK LAST ORDER BY N) OVER(PARTITION BY V) SUM_LAST_N,
SUM(N) KEEP(DENSE_RANK FIRST ORDER BY V) OVER(PARTITION BY V) SUM_FIRST_V,
SUM(N) KEEP(DENSE_RANK LAST ORDER BY V) OVER(PARTITION BY V) SUM_LAST_V
FROM TAB1
--result
V SUM_FIRST_N SUM_LAST_N SUM_FIRST_V SUM_LAST_V
C 30 170 170
C 30 170 170
C 30 170 170
C 30 170 170
B 20 20 20 20
A 10 70 80 80
A 10 70 80 80
Task 2.7. FIRST_VALUE. LAST_VALUE.
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 (70, 'A', '3_NOVEMBER');
INSERT INTO TAB1 (N, V, D) VALUES (80, 'C', '3_NOVEMBER');
INSERT INTO TAB1 (N, V, D) VALUES (30, 'C', '2_OCTOBER');
INSERT INTO TAB1 (N, V, D) VALUES (60, 'C', '2_OCTOBER');
INSERT INTO TAB1 (N, V, D) VALUES (NULL, 'C', '2_OCTOBER');
COMMIT;
In any places with ORDER BY use ASC ordering and then do next:
1. Display N, first (do not eliminate NULL rows of N) and last (eliminate NULL
rows of N) values of N for whole table with ORDER BY N ASC at the end of query.
Write result.
2. Do not eliminate NULL rows display N, first value and last value for partition
D and order by V. Write result.
Solution:
--1
--if do not specify analytic_clause in OVER, then FIRST_VALUE and LAST_VALUE
--returns value after ORDER BY
SELECT N,
FIRST_VALUE(N RESPECT NULLS) OVER() FV,
LAST_VALUE(N) IGNORE NULLS OVER() LV
FROM TAB1
ORDER BY N
--result
N FV LV
10 10 80
20 10 80
30 10 80
60 10 80
70 10 80
80 10 80
10 80
--2
SELECT N,
FIRST_VALUE(N) OVER(PARTITION BY D ORDER BY V) FV,
LAST_VALUE(N) OVER(PARTITION BY D ORDER BY V) LV
FROM TAB1
--result
N FV LV
10 10 10
20 10 20
30 30
60 30
30
70 70 70
80 70 80
Task 2.8. NTILE.
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 where display D column and divide TAB1 by 0.5, 1, 2, 5.5, 10
buckets sorting by column D ASC. Write result.
2. Remake point 1, but use partition for column D.
Solution:
--1
SELECT D,
--NTILE(0.5) OVER(ORDER BY D) N05, --will be error, because noninteger
--value truncates to integer and it
--must be positive integer, not a zero
NTILE(1) OVER(ORDER BY D) N1,
NTILE(2) OVER(ORDER BY D) N2,
NTILE(5.5) OVER(ORDER BY D) N55,
NTILE(10) OVER(ORDER BY D) N10--if number of rows <= NTILE argument then
--number of buckets = the number of rows
FROM TAB1
--result
D N1 N2 N55 N10
1_SEPTEMBER 1 1 1 1
1_SEPTEMBER 1 1 1 2
2_OCTOBER 1 1 2 3
2_OCTOBER 1 1 2 4
2_OCTOBER 1 2 3 5
3_NOVEMBER 1 2 4 6
3_NOVEMBER 1 2 5 7
--2
SELECT D,
--NTILE(0.5) OVER(PARTITION BY D ORDER BY D) N05, --will be error
NTILE(1) OVER(PARTITION BY D ORDER BY D) N1,
NTILE(2) OVER(PARTITION BY D ORDER BY D) N2,
NTILE(5.5) OVER(PARTITION BY D ORDER BY D) N55,
NTILE(10) OVER(PARTITION BY D ORDER BY D) N10
FROM TAB1
--result
D N1 N2 N55 N10
1_SEPTEMBER 1 1 1 1
1_SEPTEMBER 1 2 2 2
2_OCTOBER 1 1 1 1
2_OCTOBER 1 1 2 2
2_OCTOBER 1 2 3 3
3_NOVEMBER 1 1 1 1
3_NOVEMBER 1 2 2 2
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 not be refer?
2. Which precedence between PRIOR and unary arithmetic operators?
3. What is 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: CURRVAL, NEXTVAL
--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 FROM, WHERE 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 = NULL 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 ROW_NUMBER() OVER(ORDER BY NAME) 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 REGEXP_INSTR(t.STR, '[^,]+', 1, LEVEL) <> 0