Home

from: oracle sql practice exercises with solutions - YouTube

# Select alternate records

1
2
3
4
5
6
7
select *
  from (select empno, sal, rownum as rn from scott.emp order by empno)
 where mod(rn, 2) = 0;
 
select *
  from (select empno, sal, rownum as rn from scott.emp order by empno)
 where mod(rn, 2) = 1;

# Find unique route cost

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table edi_route_details(
  src varchar2(100),
  dest varchar2(100),
  cost number
);

insert into edi_route_details values ('CHN', 'BNG', 500);
insert into edi_route_details values ('BNG', 'CHN', 500);
insert into edi_route_details values ('HYB', 'MUMB',2500);
insert into edi_route_details values ('MUMB', 'HYB',2500);
insert into edi_route_details values ('PUNE', 'UP',4500);

select distinct least(src, dest), greatest(src, dest), cost
  from edi_route_details;
  
--drop table edi_route_details;
LEAST(SRC,DEST) GREATEST(SRC,DEST) COST
HYB MUMB 2500
BNG CHN 500
PUNE UP 4500

# Find similar name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
create table edi_emp(
  name varchar2(100)
);

insert into edi_emp values ('ravi');
insert into edi_emp values ('(RAVI)');
insert into edi_emp values ('(**RAVI**)');
insert into edi_emp values ('SCOTT');
insert into edi_emp values ('King');
insert into edi_emp values ('SMITH');
insert into edi_emp values ('SMITH***');
insert into edi_emp values ('Ravi.');

/*select *
  from edi_emp
 where lower(regexp_replace(name, '[^a-zA-Z]')) in
       (select lower(regexp_replace(name, '[^a-zA-Z]'))
          from edi_emp
         group by lower(regexp_replace(name, '[^a-zA-Z]'))
        having count(*) > 1);*/
        
with t as
 (select name, lower(regexp_replace(name, '[^a-zA-Z]')) compare_name
    from edi_emp)
select name
  from t
 where compare_name in
       (select compare_name 
          from t 
         group by compare_name 
        having count(*) > 1);
        

select *
  from (select name,
               count(*) over(partition by lower(regexp_replace(name, '[^a-zA-Z]'))) as c
          from edi_emp)
 where c > 1;
NAME C
ravi 4
(RAVI) 4
(**RAVI**) 4
Ravi. 4
SMITH*** 2
SMITH 2

# Get diagonal data

1
2
3
4
5
with t as
 (select level a, level * 10 b, level * 100 c
    from dual
  connect by level <= 9)
select * from t;
A B C
1 10 100
2 20 200
3 30 300
4 40 400
5 50 500
6 60 600
7 70 700
8 80 800
9 90 900 
1
2
3
4
5
6
7
8
9
10
11
with t as
 (select level a, level * 10 b, level * 100 c
    from dual
  connect by level <= 9)
select a,
       b,
       c,
       rownum,
       mod(rownum, 3),
       decode(mod(rownum, 3), 1, a, 2, b, 0, c) as diagonal
  from t;
A B C ROWNUM MOD(ROWNUM,3) DIAGONAL
1 10 100 1 1 1
2 20 200 2 2 20
3 30 300 3 0 300
4 40 400 4 1 4
5 50 500 5 2 50
6 60 600 6 0 600
7 70 700 7 1 7
8 80 800 8 2 80
9 90 900 9 0 900

# Multi rows into single row

1
2
3
4
5
6
7
8
9
10
with t as
 (select chr(ascii('A') + level - 1) || 1 as c1,
         chr(ascii('A') + level - 1) || 2 as c2,
         chr(ascii('A') + level - 1) || 3 as c3,
         chr(ascii('A') + level - 1) || 4 as c4,
         chr(ascii('A') + level - 1) || 5 as c5,
         chr(ascii('A') + level - 1) || 6 as c6
    from dual
  connect by level <= 9)
select * from t;
C1 C2 C3 C4 C5 C6
A1 A2 A3 A4 A5 A6
B1 B2 B3 B4 B5 B6
C1 C2 C3 C4 C5 C6
D1 D2 D3 D4 D5 D6
E1 E2 E3 E4 E5 E6
F1 F2 F3 F4 F5 F6
G1 G2 G3 G4 G5 G6
H1 H2 H3 H4 H5 H6
I1 I2 I3 I4 I5 I6 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
with t as (
  select chr(ascii('A') + level - 1) || 1 as c1, 
         chr(ascii('A') + level - 1) || 2 as c2, 
         chr(ascii('A') + level - 1) || 3 as c3, 
         chr(ascii('A') + level - 1) || 4 as c4, 
         chr(ascii('A') + level - 1) || 5 as c5, 
         chr(ascii('A') + level - 1) || 6 as c6
  from dual
  connect by level <= 9
)
select max(decode(mod(rownum, 3), 1, c1)) c1,
       max(decode(mod(rownum, 3), 1, c2)) c2, 
       max(decode(mod(rownum, 3), 2, c3)) c3, 
       max(decode(mod(rownum, 3), 2, c4)) c4,
       max(decode(mod(rownum, 3), 0, c5)) c5, 
       max(decode(mod(rownum, 3), 0, c6)) c6
  from t
 group by ceil(rownum / 3);
C1 C2 C3 C4 C5 C6
A1 A2 B3 B4 C5 C6
D1 D2 E3 E4 F5 F6
G1 G2 H3 H4 I5 I6 
1
2
3
4
5
6
7
8
with t as (
  select 1 as c from dual
  union
  select 2 from dual
)
select * from t
union all
select * from t;
1
2
3
4
5
6
7
8
9
with t as (
  select 1 as c from dual
  union
  select 2 from dual
)
select t.* 
  from t, 
       (select 1 from dual union all select 1 from dual)
order by 1;
1
2
3
4
5
6
7
8
9
with t as (
  select 1 as c from dual
  union
  select 2 from dual
)
select t.* 
  from t, 
       (select 1 from dual connect by level <= 2) 
 order by 1;
C
1
1
2
2

# Find max value of columns and rows

1
2
3
4
5
6
7
8
9
10
11
12
create table edi_t (
 c1 number,
 c2 number,
 c3 number
);

insert into edi_t
  select (rownum - 1) * 3 + 1, (rownum - 1) * 3 + 2, (rownum - 1) * 3 + 3
    from dual
  connect by level <= 3;
  
select * from edi_t;
C1 C2 C3
1 2 3
4 5 6
7 8 9 
1
select greatest(max(c1), max(c2), max(c3)) from edi_t;
GREATEST
9 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with d as (
  select 'C1' column_name, max(c1) val from edi_t
  union all
  select 'C2', max(c2) from edi_t
  union all
  select 'C3', max(c3) from edi_t
)
select column_name, val
  from d
 where val = (select max(val) from d);
 

with d as (
  select * 
    from edi_t
  unpivot (val for column_name in (c1, c2, c3))
) 
select column_name, val 
 from d 
 where val = (select max(val) from d);
COLUMN_NAME VAL
C3 9 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with d as (
  select 'C1' column_name, c1 val from edi_t
  union all
  select 'C2', c2 from edi_t
  union all
  select 'C3', c3 from edi_t
)
select *
  from (select column_name,
               case when val = max(val) over() then val end val
          from d)
 where val is not null;
 
with d as (
   select * 
     from edi_t
  unpivot (val for column_name in (c1, c2, c3))
) 
select * 
  from (
  select column_name, case when val = max(val) over() then val end val
    from d
) where val is not null;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create table edi_t (
  item_no varchar2(30),
  address varchar2(30),
  item    varchar2(30)
);

insert into edi_t values ('5', 'Bala', 'Pen');
insert into edi_t values ('5', 'Bala', 'Pencil');
insert into edi_t values ('5', 'Bala', 'Notepad');
insert into edi_t values ('6', 'Ram', 'Laptop');
insert into edi_t values ('6', 'Ram', 'Notebook');
insert into edi_t values ('6', 'Ram', 'PC');

with d as
 (select item_no,
         address,
         item,
         row_number() over(partition by item_no, address order by rownum) r
    from edi_t)
select case when r = 1 then item_no end item_no,
       case when r = 1 then address end address,
       item
  from d;
ITEM_NO ADDRESS ITEM
5 Bala Pen
Pencil
Notepad
6 Ram Laptop
Notebook
PC 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with d as (
  select rownum as c from dual connect by level <= 5
   union all
  select 5 from dual
   union all 
  select 5 from dual
   union all 
  select 6 from dual
   union all
  select rownum + 5
    from dual
  connect by level <= 5
)
select case when c = lag(c, 1) over(order by rownum) then null else c end as r
  from d;
R
1
2
3
4
5
6
7
8
9
10

# Generate alphanumeric sequence

1
2
3
4
5
6
7
with d as
 (select 'ABCD' as str, 4 as seq from dual)
select a || n
  from (select level as n from d connect by level <= seq),
       (select substr(str, level, 1) as a
          from d
        connect by level <= length(str));
A||N
A1
B1
C1
D1
A2
B2
C2
D2
A3
B3
C3
D3
A4
B4
C4
D4 
1
2
3
4
5
6
7
8
9
with d as
 (select 'ABCD' as str, 4 as seq from dual)
select str as c1,
       ceil(level / length(str)) as c2,
       mod(level, length(str)) + 1 as c3,
       substr(str, mod(level, length(str)) + 1, 1) || ceil(level / length(str))
  from d
connect by level <= length(str) * seq
 order by ceil(level / length(str)), mod(level, length(str)) + 1;
C1 C2 C3 AN
ABCD 1 1 A1
ABCD 1 2 B1
ABCD 1 3 C1
ABCD 1 4 D1
ABCD 2 1 A2
ABCD 2 2 B2
ABCD 2 3 C2
ABCD 2 4 D2
ABCD 3 1 A3
ABCD 3 2 B3
ABCD 3 3 C3
ABCD 3 4 D3
ABCD 4 1 A4
ABCD 4 2 B4
ABCD 4 3 C4
ABCD 4 4 D4

# Cumulative sum

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table edi_t (
  id number,
  type varchar2(1),
  amount number
);

insert into edi_t values (1, 'C', 10000);
insert into edi_t values (2, 'D', 2000);
insert into edi_t values (3, 'C', 10000);
insert into edi_t values (4, 'D', 5000);
insert into edi_t values (5, 'D', 4000);

select id,
       type,
       amount,
       sum(decode(type, 'C', amount, -amount)) over(order by id)
  from edi_t;
ID TYPE AMOUNT CUMULATIVE_SUM
1 C 10000 10000
2 D 2000 8000
3 C 10000 18000
4 D 5000 13000
5 D 4000 9000

# Character occurence two times only

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with t as
 (select 'MUSHROOM' as s from dual 
   union all 
  select 'HELLO' as s from dual)
select distinct s, c
  from (select s,
               column_value,
               substr(s, column_value, 1) as c,
               regexp_count(s, substr(s, column_value, 1)) cnt
          from t,
               table(cast(multiset (select level l
                                      from dual
                                   connect by level <= length(s)) as
                          sys.odcinumberlist)))
 where cnt = 2;
S C
HELLO L
MUSHROOM O
MUSHROOM M

# Find the number of month and days in given year

1
2
3
4
5
6
7
with t as
 (select 2024 as y from dual)
select to_char(level, '00'),
       to_char(to_date(to_char(level, '00') || y, 'mmyyyy'), 'MONTH') month,
       to_char(last_day(to_date(to_char(level, '00') || y, 'mmyyyy')), 'DD') days
  from t
connect by level <= 12;
TO_CHAR(LEVEL,‘00’) MONTH DAYS
01 JANUARY 31
02 FEBRUARY 29
03 MARCH 31
04 APRIL 30
05 MAY 31
06 JUNE 30
07 JULY 31
08 AUGUST 31
09 SEPTEMBER 30
10 OCTOBER 31
11 NOVEMBER 30
12 DECEMBER 31

# Insert space between chars

1
2
3
4
5
6
7
8
9
10
11
12
13
select 'HELLO',
       trim(regexp_replace('HELLO', '(.)', '\1 ')) as space_seperated_chars
  from dual;
  
with t as
 (select 'HELLO' as s from dual)
select s,
       listagg(substr(s, column_value, 1), ' ') within group(order by column_value) as space_seperated_chars
  from t,
       table(cast(multiset (select level 
                              from dual 
                            connect by level <= length(s)) as
                  sys.odcinumberlist));
S SPACE_SEPERATED_CHARS
HELLO H E L L O

# Re-arrange digits

1
2
3
4
5
6
7
8
9
10
with t as
 (select '12453' as d from dual union all select '5684' from dual)
select d,
       listagg(substr(d, column_value, 1), '') within group(order by substr(d, column_value, 1)) as rearranged_digits
  from t,
       table(cast(multiset (select level 
                              from dual 
                           connect by level <= length(d)) as
                  sys.odcinumberlist))
 group by d;
D REARRANGED_DIGITS
12453 12345
5684 4568

# Mask user data with random value

1
2
3
4
5
6
7
8
with t as
 (select 'KA33456' d from dual)
select dbms_random.string('U', 1) upper_char,
       dbms_random.value random_value,
       to_char(dbms_random.value) as random_value_str,
       substr(d, 1, 2) || dbms_random.string('U', 1) ||
       substr(substr(dbms_random.value, 2), 1, length(d) - 3) as masked_result --substr(dbms_random.value, 2) transfer to char firstly, so here use 2 not 3
  from t;
UPPER_CHAR RANDOM_VALUE RANDOM_VALUE_STR MASKED_RESULT
P **0.**143166111 **.**143166111 KAN8258

# Find symmetric set in given x, y coordinates

(x1, y1) and (x2, y2) are symmetric if x1=y2 and y1=x2.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
create table edi_t(
  set_name varchar2(10),
  x number,
  y number
);

insert into edi_t values ('P1', 1, 2);
insert into edi_t values ('P2', 2, 1);
insert into edi_t values ('P3', 6, 6);
insert into edi_t values ('P4', 6, 6);
insert into edi_t values ('P5', 7, 8);
insert into edi_t values ('P6', 8, 9);
insert into edi_t values ('P7', 8, 10);
insert into edi_t values ('P8', 10, 8);

select *
  from edi_t 
 where (x, y) in (
   select y, x from edi_t
 ) order by set_name;
 
select *
  from edi_t t1
 where exists (select 1
                 from edi_t t2
                where t1.x = t2.y
                  and t1.y = t2.x)
 order by set_name;
 
select distinct t1.*
  from edi_t t1, edi_t t2
 where t1.x = t2.y
   and t1.y = t2.x
 order by t1.set_name;
 
 select distinct t1.*
  from edi_t t1,
       table(cast(multiset (select 1
                     		  from edi_t t2
                             where t1.x = t2.y
                               and t1.y = t2.x) as 
                  sys.odcinumberlist));
SET_NAME X Y
P1 1 2
P2 2 1
P3 6 6
P4 6 6
P7 8 10
P8 10 8

# Remove duplicate values within row of data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create table edi_t(
  id number, 
  c1 varchar2(10),
  c2 varchar2(10),
  c3 varchar2(10)
);

insert into edi_t values (1, 'A', 'B', 'C');
insert into edi_t values (2, 'A', 'B', 'A');
insert into edi_t values (3, 'A', 'A', 'A');

select id, 
       listagg(c, ',') within group(order by c) 
 from (
  select id, c1 as c from edi_t
  union 
  select id, c2 from edi_t
  union
  select id, c3 from edi_t
) group by id;

select id, 
       listagg(c, ',') within group(order by c) as result
  from (
    select distinct id, c
      from edi_t 
    unpivot (c for column_value in (c1, c2, c3))
) group by id;
ID RESULT
1 A,B,C
2 A,B
3 A

# Find consecutive number

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
create table edi_t (
  n number
);
insert into edi_t values (1);
insert into edi_t values (3);
insert into edi_t values (4);
insert into edi_t values (5);
insert into edi_t values (10);
insert into edi_t values (13);
insert into edi_t values (14);


select n
  from (
    select n,
           lag(n, 1) over (order by n) prev,
           lead(n, 1) over (order by n) next
      from edi_t
  ) 
where n = prev + 1 or n = next - 1;
  
select n 
  from (
  select n,
         count(*) over (partition by (n - rownum)) c
    from edi_t) 
where c > 1
order by n;


select t.n
  from edi_t t, edi_t prev
 where t.n = prev.n + 1
union
select t.n
  from edi_t t, edi_t next
 where t.n = next.n - 1;
 
--drop table edi_t;
N
3
4
5
13
14

# Find data from One table that are not exists in Another table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
create table edi_t1(
  c varchar2(1)
);
insert into edi_t1 values ('A');
insert into edi_t1 values ('B');
insert into edi_t1 values ('C');
insert into edi_t1 values ('D');
insert into edi_t1 values ('E');

create table edi_t2(
  c varchar2(1)
);
insert into edi_t2 values ('A');
insert into edi_t2 values ('C');
insert into edi_t2 values ('E');
insert into edi_t2 values ('G');

--Method 1
select * 
  from edi_t1 
 where c not in (
   select * from edi_t2
 );
 
--Method 2
select * 
  from edi_t1 t1
 where not exists (
   select * 
     from edi_t2 t2 
    where t2.c = t1.c 
 );

--Method 3
select * from edi_t1
minus
select * from edi_t2;

--Method 4
select t1.* 
  from edi_t1 t1 left outer join edi_t2 t2 on t1.c = t2.c
 where t2.c is null;

--Method 5
select * 
  from edi_t1 t1
where (select count(1) from edi_t2 t2 where t2.c = t1.c) = 0;

--drop table edi_t1;
--drop table edi_t2;
C
B
D

# Generate Symbol

1
2
3
4
5
6
7
8
select rpad('*', rownum, '*') output1, 
       rpad('*', 11 - rownum, '*') output2,
       rpad(' ', rownum, ' ') || rpad('*', 11 - rownum, '*') output3,
       rpad(' ', 10 - rownum, ' ') || rpad('*', rownum, '*') output4,
       rpad(' ', rownum, ' ') || rpad('*', rownum, '*') output5,
       rpad(' ', 11 - rownum, ' ') ||  rpad(' ', 11 - rownum, ' ') || rpad('*', rownum, '*') output6
  from dual
connect by level <= 10;
OUTPUT1 OUTPUT2 OUTPUT3 OUTPUT4 OUTPUT5 OUTPUT6
* ********** ********** * * *
** ********* ********* ** ** **
*** ******** ******** *** *** ***
**** ******* ******* **** **** ****
***** ****** ****** ***** ***** *****
****** ***** ***** ****** ****** ******
******* **** **** ******* ******* *******
******** *** *** ******** ******** ********
********* ** ** ********* ********* *********
********** * * ********** ********** **********

# Extract names from mail

1
2
3
4
5
6
7
8
9
10
with t as (
  select 'Babb.K.Chen@gmail.com' as name from dual
)
select name,
       --regexp_substr(name, '[^.]+') first_name,
       regexp_substr(name, '[^.]+', 1) first_name,
       regexp_substr(name, '[^.]+', 1, 2) middle_name,
       regexp_substr(name, '[^.@]+', 1, 3) last_name,
       regexp_substr(name, '[^@]+$') domain_name
  from t;
NAME FIRST_NAME MIDDLE_NAME LAST_NAME DOMAIN_NAME
Babb.K.Chen@gmail.com Babb K Chen gmail.com 
1
2
3
4
5
6
7
with t as (
  select 'Babb.K.Chen@gmail.com' as name from dual
)
select name,
       regexp_substr(name, '[^.]+', 1, level) first_name
  from t
connect by level <= regexp_count(name, '[^.]+');
NAME FIRST_NAME
Babb.K.Chen@gmail.com Babb
Babb.K.Chen@gmail.com K
Babb.K.Chen@gmail.com Chen@gmail
Babb.K.Chen@gmail.com com

# Generate date range

1
2
3
4
5
6
7
8
9
10
11
12
13
14
with t as (
  select to_date('10-JAN-19', 'DD-MON-YY') as t_date from dual
  union all
  select to_date('20-JAN-19', 'DD-MON-YY') as t_date from dual
  union all  
  select to_date('11-JAN-19', 'DD-MON-YY') as t_date from dual
), d as (
  select trunc(min(t_date), 'MONTH') first_day,
         last_day(max(t_date)) - trunc(min(t_date), 'MONTH') + 1 as n_of_days 
  from t
)
select to_char(first_day + level - 1, 'DD-MON-YYYY') as date_range
  from d
connect by level <= n_of_days;
DATE_RANGE
01-JAN-2019
02-JAN-2019
03-JAN-2019
04-JAN-2019
05-JAN-2019
06-JAN-2019
07-JAN-2019
08-JAN-2019
09-JAN-2019
10-JAN-2019
11-JAN-2019
12-JAN-2019
13-JAN-2019
14-JAN-2019
15-JAN-2019
16-JAN-2019
17-JAN-2019
18-JAN-2019
19-JAN-2019
20-JAN-2019
21-JAN-2019
22-JAN-2019
23-JAN-2019
24-JAN-2019
25-JAN-2019
26-JAN-2019
27-JAN-2019
28-JAN-2019
29-JAN-2019
30-JAN-2019
31-JAN-2019

# Find Saturday and Sunday

1
2
3
4
5
6
7
8
with t as (
  select trunc(sysdate, 'MONTH') + level - 1 as t_date
    from dual
   connect by level <= last_day(sysdate) - trunc(sysdate, 'MONTH') + 1
 )
select t_date, to_char(t_date, 'DAY') t_day, to_char(t_date, 'DY') t_dy, to_char(t_date, 'D') as t_d
  from t
 where to_char(t_date, 'DY') in ('SAT', 'SUN');
T_DATE T_DAY T_DY T_D
2024-01-06 SATURDAY SAT 7
2024-01-07 SUNDAY SUN 1
2024-01-13 SATURDAY SAT 7
2024-01-14 SUNDAY SUN 1
2024-01-20 SATURDAY SAT 7
2024-01-21 SUNDAY SUN 1
2024-01-27 SATURDAY SAT 7
2024-01-28 SUNDAY SUN 1

# Find Match Played, Won, Lost count

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table edi_t(
  match_no number,
  team_a varchar2(10),
  team_b varchar2(10),
  winner varchar2(10)
);

insert into edi_t values (1, 'ABC', 'SLP', 'ABC');
insert into edi_t values (2, 'ABC', 'BUS', 'ABC');
insert into edi_t values (3, 'ABC', 'XYZ', 'XYZ');
insert into edi_t values (4, 'AHI', 'XYZ', 'XYZ');
insert into edi_t values (5, 'SNI', 'XYZ', 'XYZ');
insert into edi_t values (6, 'WDU', 'ZZC', 'ZZC');

select * from  edi_t;
MATCH_NO TEAM_A TEAM_B WINNER
1 ABC SLP ABC
2 ABC BUS ABC
3 ABC XYZ XYZ
4 AHI XYZ XYZ
5 SNI XYZ XYZ
6 WDU ZZC ZZC 
1
2
3
4
5
6
7
with t as (
  select team_a, team_b,
         case when team_a = winner then 1 else 0 end t_a,
         case when team_b = winner then 1 else 0 end t_b
    from edi_t
) 
select * from t;
TEAM_A TEAM_B T_A T_B
ABC SLP 1 0
ABC BUS 1 0
ABC XYZ 0 1
AHI XYZ 0 1
SNI XYZ 0 1
WDU ZZC 0 1 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with t as (
  select team_a, team_b,
         case when team_a = winner then 1 else 0 end t_a,
         case when team_b = winner then 1 else 0 end t_b
    from edi_t
) 
select team,
       count(*) as played,
       sum(t_r) as won,
       count(*) - sum(t_r) as lost
  from (select decode(r, 1, team_a, team_b) as team,
               decode(r, 1, t_a, t_b) as t_r
          from t, (select rownum r from dual connect by level <= 2))
 group by team
 order by won desc;
TEAM PLAYED WON LOST
XYZ 3 3 0
ABC 3 2 1
ZZC 1 1 0
WDU 1 0 1
BUS 1 0 1
AHI 1 0 1
SLP 1 0 1
SNI 1 0 1

# Find dice rolled combinations and the third rolled dice is equal to sum of first and second value

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
with t as (
   select level r 
     from dual
   connect by level <= 6
)
select t1.r, t2.r, t3.r
  from t t1, t t2, t t3
 where t3.r = t1.r + t2.r;
 

with t as (
   select level r 
     from dual
   connect by level <= 6
)
select t1.r, t2.r, t1.r + t2.r
  from t t1, t t2
 where t1.r + t2.r in (2, 3, 4, 5, 6)
order by t1.r + t2.r;
R R T1.R+T2.R
1 1 2
1 2 3
2 1 3
2 2 4
1 3 4
3 1 4
1 4 5
2 3 5
4 1 5
3 2 5
4 2 6
3 3 6
1 5 6
2 4 6
5 1 6

# Sort the numbers in the commas seperated list

1
2
3
4
5
6
7
8
9
with t as (
  select '3,10,5,1,7,100,12' s from dual
)
select s, listagg(n, ',') within group (order by to_number(n)) as sorted_result
from(
  select s, regexp_substr(s, '[^,]+', 1, level) n
    from t
  connect by level <= regexp_count(s, ',') + 1
) group by s;
S SORTED_RESULT
3,10,5,1,7,100,12 1,3,5,7,10,12,100 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with t as (
  select '3   10 5 1     7   100   12' s from dual
)
select s, listagg(n, ' ') within group (order by to_number(n)) as sorted_result
from(
  select s, regexp_substr(regexp_replace(s, '\D+', ','), '[^,]+', 1, level) n
    from t
  connect by level <= regexp_count(regexp_replace(s, '\D+', ','), ',') + 1
) group by s; 


with t as (
  select '3   10 5 1     7   100   12' s from dual
)
select s, listagg(n, ' ') within group (order by to_number(n)) as sorted_result
from(
  select s, regexp_substr(s, '\d+', 1, level) n  --\d+ <=> [0-9]+
    from t
  connect by level <= regexp_count(s, '\D+') + 1 --\D+ <=> [^0-9]+
) group by s;
S SORTED_RESULT
3 10 5 1 7 100 12 1 3 5 7 10 12 100

# Reverse string

1
2
3
4
5
6
7
8
9
10
11
select reverse('WELCOME') from dual;

with t as (
   select 'WELCOME' as s from dual
)
select listagg(ss, '') within group (order by l desc) as reversed_result
  from (
    select substr(s, level, 1) ss, level l
      from t
    connect by level <= length(s)
);
REVERSED_RESULT
EMOCLEW

# Consolidate first row and last row, second row and second from last row … as single row

1
2
3
4
5
6
with t as (
   select level no, chr(ascii('A') + level - 1) name
     from dual
   connect by level <= 11
)
select * from t;
NO NAME
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
with t as (
   select level no, chr(ascii('A') + level - 1) name
     from dual
   connect by level <= 11
),
t1 as (
  select no,
         name,
   		 count(1) over() / 2,
         row_number() over (order by no),
         round(count(1) over() / 2) - row_number() over (order by no) as c
    from t
)
select * from t1;
NO NAME COUNT(1)OVER()/2 ROW_NUMBER()OVER(ORDERBYNO) C
1 A 5.5 1 5
2 B 5.5 2 4
3 C 5.5 3 3
4 D 5.5 4 2
5 E 5.5 5 1
6 F 5.5 6 0
7 G 5.5 7 -1
8 H 5.5 8 -2
9 I 5.5 9 -3
10 J 5.5 10 -4
11 K 5.5 11 -5 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
with t as (
   select level no, chr(ascii('A') + level - 1) name
     from dual
   connect by level <= 11
),
t1 as (
  select no,
         name,
         round(count(1) over() / 2) - row_number() over (order by no) as c
    from t
)
d1 as (select no, name, c from t1 where c >= 0),
d2 as (select no, name, abs(c) c from t1 where c < 0)
select * 
  from d1 left outer join d2 on d1.c = d2.c
order by d1.no;
NO NAME C NO NAME C
1 A 5 11 K 5
2 B 4 10 J 4
3 C 3 9 I 3
4 D 2 8 H 2
5 E 1 7 G 1
6 F 0

# Fill the missing dates with last not null value

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
with t as (
  select to_date('20-JUL-19', 'DD-MON-YY') as dt, 100 as amount from dual 
  union all
  select to_date('25-JUL-19', 'DD-MON-YY'), 200 from dual 
  union all
  select to_date('30-JUL-19', 'DD-MON-YY'), 300 from dual 
), d as (
  select min(dt) min_dt, max(dt) max_dt, max(dt) - min(dt) + 1 num
   from t
), d1 as (
  select min_dt + level - 1 as dt
    from d
  connect by level <= num
) 
select d1.dt, amount
  from d1 left outer join t
    on d1.dt = t.dt
 order by d1.dt;
DT AMOUNT
2019-07-20 100
2019-07-21
2019-07-22
2019-07-23
2019-07-24
2019-07-25 200
2019-07-26
2019-07-27
2019-07-28
2019-07-29
2019-07-30 300 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with t as (
  select to_date('20-JUL-19', 'DD-MON-YY') as dt, 100 as amount from dual 
  union all
  select to_date('25-JUL-19', 'DD-MON-YY'), 200 from dual 
  union all
  select to_date('30-JUL-19', 'DD-MON-YY'), 300 from dual 
), d as (
  select min(dt) min_dt, max(dt) max_dt, max(dt) - min(dt) + 1 num
   from t
), d1 as (
  select min_dt + level - 1 as dt
    from d
  connect by level <= num
) 
select d1.dt, 
       last_value(amount ignore nulls) over (order by d1.dt /*RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW*/) amount,
       last_value(amount ignore nulls) over (order by d1.dt rows between unbounded preceding and unbounded following) amount2
  from d1 left outer join t
    on d1.dt = t.dt
 order by d1.dt;
DT AMOUNT AMOUNT2
2019-07-20 100 300
2019-07-21 100 300
2019-07-22 100 300
2019-07-23 100 300
2019-07-24 100 300
2019-07-25 200 300
2019-07-26 200 300
2019-07-27 200 300
2019-07-28 200 300
2019-07-29 200 300
2019-07-30 300 300

# Compute the start and end value of group in given seq

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create type edi_number_list is table of number;

with t as (
  select COLUMN_VALUE as d 
    from table(edi_number_list(1, 2, 3, 5, 7, 8, 9, 11, 12, 15, 16, 20))
) 
select min(d) as range_start, max(d) as range_end, d-rownum, count(*)  
  from t
 group by d-rownum
 order by 1;
 
with t as
 (select column_value as d
    from table(sys.odcinumberlist(1, 2, 3, 5, 7, 8, 9, 11, 12, 15, 16, 20)))
select min(d) as range_start, max(d) as range_end, d - rownum, count(*)
  from t
 group by d - rownum
 order by 1;
RANGE_START RANGE_END D-ROWNUM COUNT(*)
1 3 0 3
5 5 1 1
7 9 2 3
11 12 3 2
15 16 5 2
20 20 8 1

# Expand the range to sequence numbers

1
2
3
4
5
6
7
8
9
10
11
12
13
with t as
 (select 1  as start_range, 3  as end_range from dual union all
  select 5  as start_range, 5  as end_range from dual union all
  select 7  as start_range, 9  as end_range from dual union all
  select 11 as start_range, 12 as end_range from dual union all
  select 15 as start_range, 16 as end_range from dual union all
  select 20 as start_range, 20 as end_range from dual)
select *
  from t,
       (select rownum r
          from dual
        connect by level <= (select max(end_range) from t)) t2
 where t2.r between t.start_range and t.end_range;
START_RANGE END_RANGE R
1 3 1
1 3 2
1 3 3
5 5 5
7 9 7
7 9 8
7 9 9
11 12 11
11 12 12
15 16 15
15 16 16
20 20 20

# Grouping the range data

1
2
3
4
5
6
7
8
9
10
11
12
13
create table edi_t(
  name varchar2(100),
  start_year number,
  end_year number
);

insert into edi_t values ('Swift', 2007, 2009);
insert into edi_t values ('Swift', 2009, 2011);
insert into edi_t values ('Swift', 2011, 2013);
insert into edi_t values ('BMW', 2009, 2011);
insert into edi_t values ('BMW', 2013, 2015);

select * from edi_t;
NAME START_YEAR END_YEAR
Swift 2007 2009
Swift 2009 2011
Swift 2011 2013
BMW 2009 2011
BMW 2013 2015 
1
2
3
4
5
6
7
8
9
with r as
 (select distinct name, start_year + column_value as year
    from edi_t,
         table(cast(multiset (select rownum - 1
                                from dual
                             connect by level <= end_year - start_year + 1) as 
                    sys.odcinumberlist))
   order by 1, 2)
select * from r;
NAME YEAR
BMW 2009
BMW 2010
BMW 2011
BMW 2013
BMW 2014
BMW 2015
Swift 2007
Swift 2008
Swift 2009
Swift 2010
Swift 2011
Swift 2012
Swift 2013 
1
2
3
4
5
6
7
8
9
10
11
12
with r as
 (select distinct name, start_year + column_value as year
    from edi_t,
         table(cast(multiset (select rownum - 1
                                from dual
                             connect by level <= end_year - start_year + 1) as 
                    sys.odcinumberlist))
   order by 1, 2)
select name, min(year), max(year) 
  from r
group by name, year - rownum
order by 1;
NAME MIN(YEAR) MAX(YEAR)
BMW 2009 2011
BMW 2013 2015
Swift 2007 2013

# Convert list of numbers to table format

1
2
3
4
5
6
7
8
with t as (
 select level as d 
   from dual 
 connect by level <= 25)
select d,
       ceil(d / 5) as x,
       row_number() over (partition by ceil(d / 5) order by d) as y
  from t;
D X Y
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 1
7 2 2
8 2 3
9 2 4
10 2 5
11 3 1
12 3 2
13 3 3
14 3 4
15 3 5
16 4 1
17 4 2
18 4 3
19 4 4
20 4 5
21 5 1
22 5 2
23 5 3
24 5 4
25 5 5 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
with t as (
  select level as d 
    from dual 
 connect by level <= 25
)
select  max(case when mod(d, 5) = 1 then d end) as c1,  
        max(case when mod(d, 5) = 2 then d end) as c2,  
        max(case when mod(d, 5) = 3 then d end) as c3,  
        max(case when mod(d, 5) = 4 then d end) as c4,  
        max(case when mod(d, 5) = 0 then d end) as c5
  from t
 group by ceil(d/5)
 order by 1;

with t as
 (select level as d 
    from dual 
  connect by level <= 25)
select *
  from (select d,
               ceil(d / 5) as x,
               row_number() over (partition by ceil(d / 5) order by d) as y
          from t) pivot(sum(d) for y in (1 c1, 2 c2, 3 c3, 4 c4, 5 c5))
 order by x;
X C1 C2 C3 C4 C5
1 1 2 3 4 5
2 6 7 8 9 10
3 11 12 13 14 15
4 16 17 18 19 20
5 21 22 23 24 25 
1
2
3
4
5
6
7
8
9
10
with t as (
  select level as d 
    from dual 
 connect by level <= 25)
select *
  from (select d,
               ceil(d / 5) as x,
               row_number() over (partition by ceil(d / 5) order by d) as y
          from t) pivot(sum(d) for x in (1 c1, 2 c2, 3 c3, 4 c4, 5 c5))
 order by 1;
Y C1 C2 C3 C4 C5
1 1 6 11 16 21
2 2 7 12 17 22
3 3 8 13 18 23
4 4 9 14 19 24
5 5 10 15 20 25

# Fetch emp hierarchical data in Flat format

1
2
3
4
5
6
select trim(',' from sys_connect_by_path(empno, ',')) empno_list, 
       trim(',' from sys_connect_by_path(ename, ',')) ename_list, 
       connect_by_isleaf isleaf
  from scott.emp
  start with mgr is null
connect by prior empno = mgr;
EMPNO_LIST ENAME_LIST ISLEAF
7839 KING 0
7839,7566 KING,JONES 0
7839,7566,7788 KING,JONES,SCOTT 0
7839,7566,7788,7876 KING,JONES,SCOTT,ADAMS 1
7839,7566,7902 KING,JONES,FORD 0
7839,7566,7902,7369 KING,JONES,FORD,SMITH 1
7839,7698 KING,BLAKE 0
7839,7698,7499 KING,BLAKE,ALLEN 1
7839,7698,7521 KING,BLAKE,WARD 1
7839,7698,7654 KING,BLAKE,MARTIN 1
7839,7698,7844 KING,BLAKE,TURNER 1
7839,7698,7900 KING,BLAKE,JAMES 1
7839,7782 KING,CLARK 0
7839,7782,7934 KING,CLARK,MILLER 1 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select regexp_substr(empno_list, '\w+', 1, 1) lv1_empno, regexp_substr(ename_list, '\w+', 1, 1) lv1_ename, 1 lv1,
       regexp_substr(empno_list, '\w+', 1, 2) lv2_empno, regexp_substr(ename_list, '\w+', 1, 2) lv2_ename, 2 lv2,
       regexp_substr(empno_list, '\w+', 1, 3) lv3_empno, regexp_substr(ename_list, '\w+', 1, 3) lv3_ename, 3 lv3,
       regexp_substr(empno_list, '\w+', 1, 4) lv4_empno, regexp_substr(ename_list, '\w+', 1, 4) lv4_ename, 
       case when regexp_substr(empno_list, '\w+', 1, 4) is not null then 4 end lv4,
       regexp_substr(empno_list, '\w+', 1, 5) lv5_empno, regexp_substr(ename_list, '\w+', 1, 5) lv5_ename, 
       case when regexp_substr(empno_list, '\w+', 1, 5) is not null then 5 end lv5
  from (
    select trim(',' from sys_connect_by_path(empno, ',')) empno_list, 
           trim(',' from sys_connect_by_path(ename, ',')) ename_list, 
           connect_by_isleaf isleaf
      from scott.emp
      start with mgr is null
      connect by prior empno = mgr
  ) where isleaf = 1;
LV1_EMPNO LV1_ENAME LV1 LV2_EMPNO LV2_ENAME LV2 LV3_EMPNO LV3_ENAME LV3 LV4_EMPNO LV4_ENAME LV4 LV5_EMPNO LV5_ENAME
7839 KING 1 7566 JONES 2 7788 SCOTT 3 7876 ADAMS 4
7839 KING 1 7566 JONES 2 7902 FORD 3 7369 SMITH 4
7839 KING 1 7698 BLAKE 2 7499 ALLEN 3
7839 KING 1 7698 BLAKE 2 7521 WARD 3
7839 KING 1 7698 BLAKE 2 7654 MARTIN 3
7839 KING 1 7698 BLAKE 2 7844 TURNER 3
7839 KING 1 7698 BLAKE 2 7900 JAMES 3
7839 KING 1 7782 CLARK 2 7934 MILLER 3

# Group the emplyees into multiple team

1
2
3
4
5
6
7
8
9
10
11
12
13
create table edi_t(
  ename varchar2(100),
  city varchar2(100)
);

insert into edi_t values ('Babb1', 'A');
insert into edi_t values ('Babb2', 'A');
insert into edi_t values ('Babb3', 'A');
insert into edi_t values ('Babb4', 'A');
insert into edi_t values ('Babb5', 'A');
insert into edi_t values ('Babb11', 'B');
insert into edi_t values ('Babb22', 'B');
insert into edi_t values ('Babb111', 'C');
1
2
3
4
select ename,
       city,
       ceil(row_number() over(partition by city order by rownum) / 2) r
  from edi_t
ENAME CITY R
Babb1 A 1
Babb2 A 1
Babb3 A 2
Babb4 A 2
Babb5 A 3
Babb11 B 1
Babb22 B 1
Babb111 C 1 
1
2
3
4
5
6
7
8
9
--max team members = 2
select city, listagg(ename, ',') within group(order by ename) as team_members, 'Team' || r as team_name
  from (
    select ename, 
      	   city, 
           ceil(row_number() over (partition by city order by rownum) / 2) r
      from edi_t
  )
group by city, r;
CITY TEAM_MEMBERS TEAM_NAME
A Babb1,Babb2 Team1
A Babb3,Babb4 Team2
A Babb5 Team3
B Babb11,Babb22 Team1
C Babb111 Team1 
1
2
3
4
5
6
7
8
9
--max team members = 3
select city, listagg(ename, ',') within group(order by ename) as team_members, 'Team' || r as team_name
  from (
    select ename, 
           city, 
           ceil(row_number() over (partition by city order by rownum) / 3) r
      from edi_t
  )
group by city, r;
CITY TEAM_MEMBERS TEAM_NAME
A Babb1,Babb2,Babb3 Team1
A Babb4,Babb5 Team2
B Babb11,Babb22 Team1
C Babb111 Team1

# Fill the missing months with previous month salary

1
2
3
4
5
6
7
8
9
10
11
create table edi_t(
  ename varchar2(100),
  month_year varchar2(100),
  salary number
);

insert into edi_t values ('Peter', 'May-19', 5000);
insert into edi_t values ('Peter', 'Aug-19', 10000);
insert into edi_t values ('Peter', 'Mar-20', 15000);
insert into edi_t values ('Solomon', 'Oct-19', 20000);
insert into edi_t values ('Solomon', 'Feb-20', 30000);
1
2
3
4
5
6
7
8
9
10
11
select ename,
       salary,
       to_date(month_year, 'Mon-YY') d,
       lead(to_date(month_year, 'Mon-YY')) over(partition by ename order by to_date(month_year, 'Mon-YY')) next_d,
       nvl(
          months_between(
            lead(to_date(month_year, 'Mon-YY')) over(partition by ename order by to_date(month_year, 'Mon-YY')),
            to_date(month_year, 'Mon-YY')
          ),
       1) n
  from edi_t;
ENAME SALARY D NEXT_D N
Peter 5000 2019-05-01 2019-08-01 3
Peter 10000 2019-08-01 2020-03-01 7
Peter 15000 2020-03-01 1
Solomon 20000 2019-10-01 2020-02-01 4
Solomon 30000 2020-02-01 1 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
with t as (
  select ename,
         salary,
         to_date(month_year, 'Mon-YY') d,
         lead(to_date(month_year, 'Mon-YY')) over(partition by ename order by to_date(month_year, 'Mon-YY')) next_d,
         nvl(
            months_between(
              lead(to_date(month_year, 'Mon-YY')) over(partition by ename order by to_date(month_year, 'Mon-YY')),
              to_date(month_year, 'Mon-YY')
            ),
         1) n
    from edi_t
)
select ename, to_char(add_months(d, column_value), 'Mon-YY') month_year, salary
  from t,
       table(cast(multiset (select rownum - 1
                                    from dual
                                  connect by level <= t.n) as sys.odcinumberlist)
            );
ENAME MONTH_YEAR SALARY
Peter May-19 5000
Peter Jun-19 5000
Peter Jul-19 5000
Peter Aug-19 10000
Peter Sep-19 10000
Peter Oct-19 10000
Peter Nov-19 10000
Peter Dec-19 10000
Peter Jan-20 10000
Peter Feb-20 10000
Peter Mar-20 15000
Solomon Oct-19 20000
Solomon Nov-19 20000
Solomon Dec-19 20000
Solomon Jan-20 20000
Solomon Feb-20 30000

# Count number of occurences of vowels in a string

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
with t as
 (select 'SUBHASIS ORACLE' name from dual
  union all
  select 'BABB CHEN' from dual),
d as
 (select name, substr(name, column_value, 1) w
    from t,
         table(cast(multiset (select level l
                       from dual
                     connect by level <= length(name)) as sys.odcinumberlist)))
select *
  from (select name, w, count(*) cnt
          from d
         where d.w in ('A', 'E', 'I', 'O', 'U')
         group by name, w) pivot (max(cnt) for w in ('A', 'E', 'I', 'O', 'U'));

--
with t as (
  select 'SUBHASIS ORACLE' name from dual
  union all
  select 'BABB CHEN' from dual
)
select name, 
       length(name) - length(replace(name, 'A')) A,
       regexp_count(name, 'E') E,
       regexp_count(name, 'I') I,
       regexp_count(name, 'O') O,
       regexp_count(name, 'U') U
  from t;
NAME A E I O U
SUBHASIS ORACLE 2 1 1 1 1
BABB CHEN 1 1 0 0 0

# Get all dates for the given input month and year

1
2
3
4
5
6
7
8
9
10
with t as
 (select 'DEC' m, 2023 y from dual),
t1 as
 (select m || y as my from t),
t2 as
 (select to_date(my, 'MONYYYY') first_day,
         last_day(to_date(my, 'MONYYYY')) - to_date(my, 'MONYYYY') + 1 n_of_days
    from t1)
select first_day + level - 1 
  from t2 connect by level <= n_of_days;
FIRST_DAY+LEVEL-1
2023-12-01
2023-12-02
2023-12-03
2023-12-04
2023-12-05
2023-12-06
2023-12-07
2023-12-08
2023-12-09
2023-12-10
2023-12-11
2023-12-12
2023-12-13
2023-12-14
2023-12-15
2023-12-16
2023-12-17
2023-12-18
2023-12-19
2023-12-20
2023-12-21
2023-12-22
2023-12-23
2023-12-24
2023-12-25
2023-12-26
2023-12-27
2023-12-28
2023-12-29
2023-12-30
2023-12-31

# Comma separated string into row of data

1
2
3
4
5
6
7
8
9
10
with t as (
  select 10 as deptno, 'A,B,C,D'  as ename from dual union all
  select 20 as deptno, 'AA,BB,CC' as ename from dual union all
  select 30 as deptno, 'AAA,BBB'  as ename from dual)
select deptno, ename, regexp_substr(ename, '[^,]+', 1, column_value) as sub
  from t,
       table(cast(multiset (select level l
                     		  from dual
                   		   connect by level <= regexp_count(ename, ',') + 1) as
                  sys.odcinumberlist));
DEPTNO ENAME SUB
10 A,B,C,D A
10 A,B,C,D B
10 A,B,C,D C
10 A,B,C,D D
20 AA,BB,CC AA
20 AA,BB,CC BB
20 AA,BB,CC CC
30 AAA,BBB AAA
30 AAA,BBB BBB

# Find the set of rows in hierarchical relational data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table edi_t (
  id number,
  old_id number
);

insert into edi_t values (1, null);
insert into edi_t values (2, 1);
insert into edi_t values (3, 2);
insert into edi_t values (4, 3);
insert into edi_t values (5, 4);
insert into edi_t values (6, null);
insert into edi_t values (7, null);
insert into edi_t values (8, 7);
insert into edi_t values (9, null);
insert into edi_t values (10, 9);
insert into edi_t values (11, 10);

select * from edi_t;
ID OLD_ID
1
2 1
3 2
4 3
5 4
6
7
8 7
9
10 9
11 10 
1
2
3
4
select id, old_id, connect_by_isleaf l
  from edi_t
connect by prior old_id = id
 start with id = 3;
ID OLD_ID L
3 2 0
2 1 0
1 1 
1
2
3
4
5
6
7
8
9
10
11
with t as
 (select id
    from (select id, old_id, connect_by_isleaf l
            from edi_t
          connect by prior old_id = id
           start with id = 3)
   where l = 1)
select id, old_id
  from edi_t
connect by prior id = old_id
 start with id = (select id from t);
ID OLD_ID
1
2 1
3 2
4 3
5 4

# Convert data representation in different format

Source

ACTIVATION_DT DEACTIVATION_DT RATE
2018-01-01 2018-01-31 10
2018-02-01 2018-02-27 15
2018-03-01 2018-04-30 12 
1
2
3
4
5
6
7
8
9
10
11
create table edi_t(
  activation_dt date,
  deactivation_dt date,
  rate number
);

insert into edi_t values (to_date('01-JAN-18', 'DD-MON-YY'), to_date('31-JAN-18', 'DD-MON-YY'), 10);
insert into edi_t values (to_date('01-FEB-18', 'DD-MON-YY'), to_date('27-FEB-18', 'DD-MON-YY'), 15);
insert into edi_t values (to_date('01-MAR-18', 'DD-MON-YY'), to_date('30-APR-18', 'DD-MON-YY'), 12);

select * from edi_t;

Result

DT RATE CHANGE_LOG
2018-01-01 10 Added
2018-02-01 15 Modified
2018-02-27 15 Removed
2018-03-01 12 Added
2018-04-30 12 Removed 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
with t as (
  select activation_dt, deactivation_dt, rate, 
         lag(activation_dt) over (order by activation_dt) p_activation_dt, 
         lag(deactivation_dt) over (order by activation_dt) p_deactivation_dt,
         lag(rate) over (order by activation_dt) p_rate,
         lead(activation_dt) over (order by activation_dt) n_activation_dt, 
         lead(deactivation_dt) over (order by activation_dt) n_deactivation_dt,
         lead(rate) over (order by activation_dt)  n_rate
    from edi_t
),
t2 as (
select activation_dt, deactivation_dt, rate,
       case 
         when p_activation_dt is null or activation_dt <> p_deactivation_dt + 1 then 'Added'
         when activation_dt = p_deactivation_dt + 1 and rate <> p_rate then 'Modified'
       end add_or_mod,
       case 
         when n_deactivation_dt is null or deactivation_dt + 1 <> n_activation_dt then 'Removed'
       end rem
  from t
)
select decode(l, 1, activation_dt, deactivation_dt) as dt,
       rate,
       decode(l, 1, add_or_mod, rem) as change_log
  from t2, (select level as l  from dual connect by level <= 2)
where decode(l, 1, add_or_mod, rem) is not null
order by activation_dt, l;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with t as (
  select case 
           when p.activation_dt is null then 'Added'
           when p.rate <> c.rate then 'Modified'
         end change1, 
         c.activation_dt, c.deactivation_dt, c.rate,
         case
           when n.activation_dt is null then 'Removed'
         end change2
    from edi_t c 
    left outer join edi_t p on (c.activation_dt = p.deactivation_dt + 1)
    left outer join edi_t n on (c.deactivation_dt + 1 = n.activation_dt)
)
select * 
  from (
  select activation_dt, rate, change1 as change_log from t
  union all
  select deactivation_dt, rate, change2 as change_log from t
) where change_log is not null
order by 1;

# Determine whether two strings are anagram

ASCII

Use this method, MAN and TAG also are anagram. This is not right.

1
2
3
4
5
6
7
8
9
10
with t as (
  select 'HEART' c1, 'EARTH' c2 from dual
)
select c1, c2, sum(a1), sum(a2), case when sum(a1) = sum(a2) then 'Valid Anagram' else 'No Anagram' end output
 from (
  select c1, c2, ascii(substr(c1, level, 1)) a1, ascii(substr(c2, level, 1)) a2
    from t
  connect by level <= length(c1)
)
group by c1, c2;

ORA_HASH

1
2
3
4
5
6
7
8
9
10
with t as (
  select 'MAN' c1, 'TAG' c2 from dual
)
select c1, c2, sum(a1), sum(a2), case when sum(a1) = sum(a2) then 'Valid Anagram' else 'No Anagram' end output
 from (
  select c1, c2, ora_hash(substr(c1, level, 1)) a1, ora_hash(substr(c2, level, 1)) a2
    from t
  connect by level <= length(c1)
)
group by c1, c2;
C1 C2 SUM(A1) SUM(A2) OUTPUT
MAN TAG 4199279673 5736920293 No Anagram

LISTAGG

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with t as (
  select 'HEART' c1, 'EARTH' c2 from dual
  union all 
  select 'MAN' c1, 'TAG' c2 from dual
  union all 
  select 'HELLO' c1, 'HELLOO' c2 from dual
)
select c1, c2, 
       listagg(substr(c1, column_value, 1), '') within group (order by substr(c1, column_value, 1)) w1, 
       listagg(substr(c2, column_value, 1), '') within group (order by substr(c2, column_value, 1)) w2,
       case 
         when listagg(substr(c1, column_value, 1), '') within group (order by substr(c1, column_value, 1))
              = listagg(substr(c2, column_value, 1), '') within group (order by substr(c2, column_value, 1))
         then 'Valid Anagram' else 'No Anagram' 
       end output
  from t, table(
    cast (
      multiset(
        select level from dual connect by level <= greatest(length(c1), length(c2)) 
      ) as sys.odcinumberlist
    )
  )
group by c1, c2;
C1 C2 W1 W2 OUTPUT
MAN TAG AMN AGT No Anagram
HEART EARTH AEHRT AEHRT Valid Anagram
HELLO HELLOO EHLLO EHLLOO No Anagram

# Divide the rows into groups

1
2
3
4
5
6
7
8
9
10
11
12
13
14
with t as
 (select level as no, chr(ascii('A') + level - 1) as name
    from dual
  connect by level <= 10),
t2 as
 (select no, name, ntile(3) over(order by no) as group_no from t),
t3 as
 (select group_no, no, name, null as min_no, null as max_no
    from t2
  union all
  select group_no, null, null, min(no) as min_no, max(no) as max_no
    from t2
   group by group_no)
select * from t3 order by group_no, no nulls last;
GROUP_NO NO NAME MIN_NO MAX_NO
1 1 A
1 2 B
1 3 C
1 4 D
1 1 4
2 5 E
2 6 F
2 7 G
2 5 7
3 8 H
3 9 I
3 10 J
3 8 10

# Compute group number for repeating numbers

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create table edi_t (
  c1 number,
  c2 number
);

insert into edi_t values (1, 100);
insert into edi_t values (2, 100);
insert into edi_t values (3, 100);
insert into edi_t values (4, 200);
insert into edi_t values (5, 200);
insert into edi_t values (6, 300);
insert into edi_t values (7, 300);
insert into edi_t values (8, 100);
insert into edi_t values (9, 300);
insert into edi_t values (10, 400);
insert into edi_t values (11, 400);
insert into edi_t values (12, 100);

select c1, c2,
       case 
         when c2 <> nvl(lag(c2) over (order by c1), 0) then 1
         else 0
       end r
  from edi_t;
C1 C2 R
1 100 1
2 100 0
3 100 0
4 200 1
5 200 0
6 300 1
7 300 0
8 100 1
9 300 1
10 400 1
11 400 0
12 100 1 
1
2
3
4
5
6
7
8
9
select c1, c2, sum(r) over (order by c1) as group_number
  from (
  select c1, c2,
         case 
           when c2 <> nvl(lag(c2) over (order by c1), 0) then 1
           else 0
         end r
    from edi_t
);
C1 C2 GROUP_NUMBER
1 100 1
2 100 1
3 100 1
4 200 2
5 200 2
6 300 3
7 300 3
8 100 4
9 300 5
10 400 6
11 400 6
12 100 7

# Compute count of group of repeating values

1
2
3
4
5
6
with t as (
  select level as no,  substr('AAAABCCCAAACA', level, 1) as c
    from dual
    connect by level <= length('AAAABCCCAAACA')
)
select * from t;
NO C
1 A
2 A
3 A
4 A
5 B
6 C
7 C
8 C
9 A
10 A
11 A
12 C
13 A 
1
2
3
4
5
6
7
8
9
with t as
 (select level as no, substr('AAAABCCCAAACA', level, 1) as c
    from dual
  connect by level <= length('AAAABCCCAAACA'))
select c,
       row_number() over(order by no) - row_number() over(partition by c order by no) as r,
       row_number() over(order by no),
       row_number() over(partition by c order by no)
  from t;
C R ROW_NUMBER()OVER(ORDERBYNO) ROW_NUMBER()OVER(PARTITIONBYCO
A 0 1 1
A 0 2 2
A 0 3 3
A 0 4 4
B 4 5 1
C 5 6 1
C 5 7 2
C 5 8 3
A 4 9 5
A 4 10 6
A 4 11 7 
1
2
3
4
5
6
7
8
9
10
with t as (
  select level as no,  substr('AAAABCCCAAACA', level, 1) as c
    from dual
    connect by level <= length('AAAABCCCAAACA')
)
select c, count(*)
  from (
    select distinct c, row_number() over (order by no) - row_number() over (partition by c order by no) as r
      from t
) group by c;
C COUNT(*)
A 3
C 2
B 1

# Concate previous row value continuously

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with t as (
  select 'A' as col1, 1 as col2 from dual union all
  select 'A',  2 from dual union all
  select 'A',  3 from dual union all
  select 'B', 100 from dual union all 
  select 'B', 200 from dual union all
  select 'B', 300 from dual union all
  select 'B', 400 from dual union all
  select 'B', 500 from dual
),
t1 as (
  select col1,
         col2,
         listagg(col2, ',') within group(order by col2) over (partition by col1) agg,
         row_number() over (partition by col1 order by col2) r
    from t
)
COL1 COL2 AGG R
A 1 1,2,3 1
A 2 1,2,3 2
A 3 1,2,3 3
B 100 100,200,300,400,500 1
B 200 100,200,300,400,500 2
B 300 100,200,300,400,500 3
B 400 100,200,300,400,500 4
B 500 100,200,300,400,500 5 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with t as (
  select 'A' as col1, 1 as col2 from dual union all
  select 'A',  2 from dual union all
  select 'A',  3 from dual union all
  select 'B', 100 from dual union all 
  select 'B', 200 from dual union all
  select 'B', 300 from dual union all
  select 'B', 400 from dual union all
  select 'B', 500 from dual
),
t1 as (
  select col1,
         col2,
         listagg(col2, ',') within group(order by col2) over (partition by col1) agg,
         row_number() over (partition by col1 order by col2) r
    from t
)
select col1, col2, 
       substr(agg, 0, instr(agg || ',', ',', 1, r) - 1) output
  from t1;
COL1 COL2 OUTPUT
A 1 1
A 2 1,2
A 3 1,2,3
B 100 100
B 200 100,200
B 300 100,200,300
B 400 100,200,300,400
B 500 100,200,300,400,500

# Generate integer series 1…n

# Integer Table Method

1
2
3
4
5
6
7
8
9
10
11
12
13
create table integers
  ( integer_value integer primary key )
  organization index;

begin
  for i in -5 .. 5 loop
    insert into integers values ( i );
  end loop;
  commit;
end;
/

select * from integers ;
INTEGER_VALUE
-5
-4
-3
-2
-1
0
1
2
3
4
5

# MODEL Method

1
2
3
4
5
6
7
8
select integer_value
  from dual 
  model 
  dimension by(0 as key) 
  measures(0 as integer_value) 
  rules iterate(5)
    (integer_value[ iteration_number ] = iteration_number + 1)
 order by 1;
1
2
3
4
5
6
7
select integer_value
  from dual
 where 1 = 2 
 model 
 dimension by (0 as key) 
 measures (0 as integer_value)
 rules upsert(integer_value[ for key from -5 to 5 increment 1 ] = cv(key))
1
2
3
4
5
6
7
select integer_value
  from dual
 where 1 = 2 
 model 
 dimension by (0 as key) 
 measures (0 as integer_value)
 rules upsert (integer_value[ for key from -5 to 5 increment 2 ] = cv(key));
INTEGER_VALUE
-5
-3
-1
1
3
5 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select integer_value
  from dual
 where 1 = 2 
 model 
 dimension by (0 as key) 
 measures (0 as integer_value)
 rules upsert(integer_value [ for key from 3 to 1 DECREMENT 1 ] = cv(key))
 order by integer_value desc;

--or

select integer_value
  from dual
 where 1 = 2 
 model 
 dimension by (0 as key) 
 measures (0 as integer_value)
 rules upsert(integer_value [ for key from 1 to 3 INCREMENT 1 ] = cv(key))
 order by integer_value desc;
INTEGER_VALUE
3
2
1

# ROWNUM + a Big Table Method

1
2
3
select rownum
  from all_objects
 where rownum <= 5;

# CONNECT BY LEVEL Method

1
2
3
select level 
  from dual 
connect by level <= 5;

# CUBE Method

1
2
3
4
5
select rownum
  from (select 1 
          from dual 
         group by cube(1, 2, 3, 4))
 where rownum <= 5;

# Type Constructor Expression Method

1
create type integer_table_type as table of integer;
1
2
select column_value
  from table(integer_table_type(1, 2, 3, 4, 5));
COLUMN_VALUE
1
2
3
4
5 
1
2
select column_value 
  from table(integer_table_type(1, 1, 4, 4, 4, 8, 10));
COLUMN_VALUE
1
1
4
4
4
8
10

# Type Constructor + Cartesian Product Method

1
2
3
4
5
6
create type integer_table_type as table of integer;

with i as
 (select * 
    from table(integer_table_type(1, 2, 3)))
select rownum from i, i, i, i where rownum <= 5;

# Pipelined Function Method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create type integer_table_type as table of integer;

create function integer_series(p_lower_bound in number,
                               p_upper_bound in number)
  return integer_table_type
  pipelined as
begin

  for i in p_lower_bound .. p_upper_bound loop
    pipe row(i);
  end loop;

  return;
end;
/

select *
  from table(integer_series(-5, 5)) ;

# Table Function Method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create or replace function integer_series(p_lower_bound in number,
                                          p_upper_bound in number)
  return sys.odcinumberlist is
  l_num_tab sys.odcinumberlist := sys.odcinumberlist();
begin
  for i in p_lower_bound .. p_upper_bound loop
    l_num_tab.extend;
    l_num_tab(l_num_tab.last) := i;
  end loop;

  return l_num_tab;
end;
/

select * from table(integer_series(-5, 5));

# XML Method

1
2
3
select rownum from (
  select * from xmltable('1 to 10')
);

# WITH CTE Method

1
2
3
4
5
6
with t(n) as (
   select 1 from dual
   union all
   select n + 1 from t where n < 10  
)
select * from t;

# Split string to rows

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create table edi_t
(
  id  number,
  data varchar2(10),
  note varchar2(25)
);
/

insert into edi_t values ( 1 ,  'v1'       , 'One value'               );
insert into edi_t values ( 2 ,  'v1,'      , 'Two values, one empty'   );
insert into edi_t values ( 3 ,  'v1,,'     , 'Three values, two empty' );
insert into edi_t values ( 4 ,  ',v2,'     , 'Three values, two empty' );
insert into edi_t values ( 5 ,  ',,v3'     , 'Three values, two empty' );
insert into edi_t values ( 6 ,  'v1,v2'    , 'Two values'              );
insert into edi_t values ( 7 ,  'v1,v2,'   , 'Three values, one empty' );
insert into edi_t values ( 8 ,  'v1,v2,v3' , 'Three values, none empty');
insert into edi_t values ( 9 ,  null       , 'One empty value'         );
insert into edi_t values ( 10 , ','        , 'Two values, two empty'   );
insert into edi_t values ( 11 , ',,'       , 'Three values, all empty' );

select * from edi_t;
ID DATA NOTE
1 v1 One value
2 v1, Two values, one empty
3 v1, Three values, two empty
4 ,v2, Three values, two empty
5 ,v3 Three values, two empty
6 v1,v2 Two values
7 v1,v2, Three values, one empty
8 v1,v2,v3 Three values, none empty
9 One empty value
10 , Two values, two empty
11 , Three values, all empty

# Collection Method

1
2
3
4
5
6
7
8
9
10
11
select id,
       data,
       regexp_substr(data, '[^,]+', 1, column_value) as val,
       column_value as position,
       note
  from edi_t,
       table(cast(multiset
                  (select level l
                     from dual
                   connect by level <= regexp_count(data, ',') + 1) as
                  sys.odcinumberlist));
ID DATA VAL POSITION NOTE
1 v1 v1 1 One value
2 v1, v1 1 Two values, one empty
2 v1, 2 Two values, one empty
3 v1, v1 1 Three values, two empty
3 v1, 2 Three values, two empty
3 v1, 3 Three values, two empty
4 ,v2, v2 1 Three values, two empty
4 ,v2, 2 Three values, two empty
4 ,v2, 3 Three values, two empty
5 ,v3 v3 1 Three values, two empty
5 ,v3 2 Three values, two empty
5 ,v3 3 Three values, two empty
6 v1,v2 v1 1 Two values
6 v1,v2 v2 2 Two values
7 v1,v2, v1 1 Three values, one empty
7 v1,v2, v2 2 Three values, one empty
7 v1,v2, 3 Three values, one empty
8 v1,v2,v3 v1 1 Three values, none empty
8 v1,v2,v3 v2 2 Three values, none empty
8 v1,v2,v3 v3 3 Three values, none empty
9 1 One empty value
10 , 1 Two values, two empty
10 , 2 Two values, two empty
11 , 1 Three values, all empty
11 , 2 Three values, all empty
11 , 3 Three values, all empty

# Model Method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select id, data, val, position, note
  from edi_t
model
  return updated rows
  partition by ( id, data, note )
  dimension by ( 0 as position )
  measures     ( data || ',' as val )
  rules upsert
  ( val
    [ for position
        from 1
        to   regexp_count(val[0], ',')
        increment 1
    ] = rtrim( regexp_substr( val[0], '[^,]+', 1, cv(position) ), ',' )
  )
order by id, position;
ID DATA VAL POSITION NOTE
1 v1 v1 1 One value
2 v1, v1 1 Two values, one empty
2 v1, 2 Two values, one empty
3 v1, v1 1 Three values, two empty
3 v1, 2 Three values, two empty
3 v1, 3 Three values, two empty
4 ,v2, v2 1 Three values, two empty
4 ,v2, 2 Three values, two empty
4 ,v2, 3 Three values, two empty
5 ,v3 v3 1 Three values, two empty
5 ,v3 2 Three values, two empty
5 ,v3 3 Three values, two empty
6 v1,v2 v1 1 Two values
6 v1,v2 v2 2 Two values
7 v1,v2, v1 1 Three values, one empty
7 v1,v2, v2 2 Three values, one empty
7 v1,v2, 3 Three values, one empty
8 v1,v2,v3 v1 1 Three values, none empty
8 v1,v2,v3 v2 2 Three values, none empty
8 v1,v2,v3 v3 3 Three values, none empty
9 1 One empty value
10 , 1 Two values, two empty
10 , 2 Two values, two empty
11 , 1 Three values, all empty
11 , 2 Three values, all empty
11 , 3 Three values, all empty

# Pipelined Function Method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
create type table_str is table of varchar2(4000);

create or replace function split_str(p_str       in varchar2,
                                     p_separator in varchar2 default ',')
  return table_str
  pipelined as

  l_index pls_integer;
  l_start pls_integer;
begin
  l_start := 1;

  loop
    l_index := instr(p_str, p_separator, l_start);
    if nvl(l_index, 0) = 0 then
      pipe row(substr(p_str, l_start));
      exit;
    else
      pipe row(substr(p_str, l_start, l_index - l_start));
      l_start := l_index + length(p_separator);
    end if;
  end loop;

  return;
end split_str;
/


select id,
       data,
       column_value as val,
       row_number() over(partition by id order by rownum) as position,
       note
  from edi_t, table(split_str(data));
ID DATA VAL POSITION NOTE
1 v1 v1 1 One value
2 v1, v1 1 Two values, one empty
2 v1, 2 Two values, one empty
3 v1, v1 1 Three values, two empty
3 v1, 2 Three values, two empty
3 v1, 3 Three values, two empty
4 ,v2, 1 Three values, two empty
4 ,v2, v2 2 Three values, two empty
4 ,v2, 3 Three values, two empty
5 ,v3 1 Three values, two empty
5 ,v3 2 Three values, two empty
5 ,v3 v3 3 Three values, two empty
6 v1,v2 v1 1 Two values
6 v1,v2 v2 2 Two values
7 v1,v2, v1 1 Three values, one empty
7 v1,v2, v2 2 Three values, one empty
7 v1,v2, 3 Three values, one empty
8 v1,v2,v3 v1 1 Three values, none empty
8 v1,v2,v3 v2 2 Three values, none empty
8 v1,v2,v3 v3 3 Three values, none empty
9 1 One empty value
10 , 1 Two values, two empty
10 , 2 Two values, two empty
11 , 1 Three values, all empty
11 , 2 Three values, all empty
11 , 3 Three values, all empty

# Java Function Method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create type edi_varchar_list_type is table of varchar2(32767);
/

create or replace and compile java source named edi_java_test as
import java.lang.*;
public class edi_java_test {
    public static java.sql.Array split_str(String str, String separator) throws java.sql.SQLException {
        String[] elements = str.split(separator);
        
        oracle.jdbc.OracleDriver driver = new oracle.jdbc.OracleDriver();
        java.sql.Connection conn = driver.defaultConnection();
        oracle.jdbc.OracleConnection oracleConn = (oracle.jdbc.OracleConnection)conn;
        
        String typeName = "EDI_VARCHAR_LIST_TYPE"; /* typeName have to be UPPERCASE */
        java.sql.Array sqlArr = oracleConn.createARRAY(typeName, elements);
        return sqlArr;
    }
};
/

create or replace function edi_split_str(p_str varchar2, p_separator varchar2) return edi_varchar_list_type as
language java name 'edi_java_test.split_str(java.lang.String, java.lang.String) return java.sql.Array';
/

select id,
       data,
       column_value as val,
       row_number() over(partition by id order by rownum) as position,
       note
  from edi_t, table(edi_split_str(data, ','))
 where data is not null;
ID DATA VAL POSITION NOTE
1 v1 v1 1 One value
2 v1, v1 1 Two values, one empty
3 v1, v1 1 Three values, two empty
4 ,v2, 1 Three values, two empty
4 ,v2, v2 2 Three values, two empty
5 ,v3 1 Three values, two empty
5 ,v3 2 Three values, two empty
5 ,v3 v3 3 Three values, two empty
6 v1,v2 v1 1 Two values
6 v1,v2 v2 2 Two values
7 v1,v2, v1 1 Three values, one empty
7 v1,v2, v2 2 Three values, one empty
8 v1,v2,v3 v1 1 Three values, none empty
8 v1,v2,v3 v2 2 Three values, none empty
8 v1,v2,v3 v3 3 Three values, none empty

# XML Method

1
2
3
4
5
6
7
8
9
10
11
12
with t as
 (select id,
         data,
         note,
         '<val>' || replace(data, ',', '</val><val>') || '</val>' as data_xml
    from edi_t)
select id,
       data,
       EXTRACTVALUE(t2.column_value, '/*/text()') val,
       row_number() over(partition by id order by rownum) as position,
       note
  from t, table(XMLSEQUENCE(XMLPARSE(content t.data_xml wellformed))) t2;
ID DATA VAL POSITION NOTE
1 v1 v1 1 One value
2 v1, v1 1 Two values, one empty
2 v1, 2 Two values, one empty
3 v1, v1 1 Three values, two empty
3 v1, 2 Three values, two empty
3 v1, 3 Three values, two empty
4 ,v2, 1 Three values, two empty
4 ,v2, v2 2 Three values, two empty
4 ,v2, 3 Three values, two empty
5 ,v3 1 Three values, two empty
5 ,v3 2 Three values, two empty
5 ,v3 v3 3 Three values, two empty
6 v1,v2 v1 1 Two values
6 v1,v2 v2 2 Two values
7 v1,v2, v1 1 Three values, one empty
7 v1,v2, v2 2 Three values, one empty
7 v1,v2, 3 Three values, one empty
8 v1,v2,v3 v1 1 Three values, none empty
8 v1,v2,v3 v2 2 Three values, none empty
8 v1,v2,v3 v3 3 Three values, none empty
9 1 One empty value
10 , 1 Two values, two empty
10 , 2 Two values, two empty
11 , 1 Three values, all empty
11 , 2 Three values, all empty
11 , 3 Three values, all empty

# Calculate the median

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table movie_ratings (
  rating number
);

insert into movie_ratings(rating) values (11);
insert into movie_ratings(rating) values (12);
insert into movie_ratings(rating) values (15);
insert into movie_ratings(rating) values (19);
insert into movie_ratings(rating) values (12);
insert into movie_ratings(rating) values (13);
insert into movie_ratings(rating) values (16);
insert into movie_ratings(rating) values (20);

select * from movie_ratings;
RATING
11
12
15
19
12
13
16
20

# ROW_NUMBER() Window Function Method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select avg(m.rating)
  from (select m1.rating,
               row_number() over(order by m1.rating) as row_num,
               count(*) over() as cnt
          from movie_ratings m1) m
 where m.row_num in
       (floor(cnt / 2) + 1,
        case when mod(cnt, 2) = 0 then cnt / 2 else floor(cnt / 2) + 1 end);
        
        
select avg(m.rating)
  from (select m1.rating,
               row_number() over(order by m1.rating) as row_num1, --11, 12, 12, 13, 15, 16, 19, 20
               row_number() over(order by m1.rating desc) as row_num2, --20, 19, 16, 15, 13, 12, 12, 11
               count(*) over() as cnt
          from movie_ratings m1) m
 where row_num1 = round(cnt / 2) --13
    or row_num2 = round(cnt / 2); --15
    
    
 select avg(m.rating)
  from (select m1.rating,
               row_number() over(order by m1.rating) as row_num,
               count(*) over() as cnt
          from movie_ratings m1) m
 where m.row_num between cnt / 2 and cnt / 2 + 1;
MEDIAN
14

# Having Clause Method

1
2
3
4
5
6
7
select avg(m.rating)
  from (select m1.rating
          from movie_ratings m1, movie_ratings m2
         group by m1.rating
        having sum(case when m1.rating <= m2.rating then 1 else 0 end) >= count(*) / 2 --11, 12, 12, 13, 15
           and sum(case when m1.rating >= m2.rating then 1 else 0 end) >= count(*) / 2 --13, 15, 16, 19, 20
        ) m;

# Median Function Method

1
select median(rating) from movie_ratings;
Edited on