select  *
from    my_table_1 a
where   exists
        (
        select  'x'
        from    my_table_1 x
        where   x.column_1 = a.column_1
        and     x.column_2 = a.column_2
        and     x.column_2 <= 30
        )
;

select  *
from    my_table_1 a
where   not exists
        (
        select  'x'
        from    my_table_1 x
        where   x.column_1 = a.column_1
        and     x.column_2 = a.column_2
        and     x.column_2 <= 30
        )
;

select  round(hong,2) hong,round(kim,2) kim,round(lee,2) lee,round(park,2) park,round(shin,2) shin,round(choi,2) choi
from    (
        select  substr(column_1,1,1) fnm,avg(column_2) avg_age
        from    my_table_1 a
        group   by substr(column_1,1,1)
        )
pivot   (avg(avg_age) for fnm in ('홍' as hong,'김' as kim,'이' as lee,'박' as park,'신' as shin,'최' as choi))
;

with    fmnm_list as
(
select  round(hong,2) hong,round(kim,2) kim,round(lee,2) lee,round(park,2) park,round(shin,2) shin,round(choi,2) choi
from    (
        select  substr(column_1,1,1) fnm,avg(column_2) avg_age
        from    my_table_1 a
        group   by substr(column_1,1,1)
        )
pivot   (avg(avg_age) for fnm in ('홍' as hong,'김' as kim,'이' as lee,'박' as park,'신' as shin,'최' as choi))
)
select  *
from    fmnm_list a
unpivot (age for fnm in (hong as 'H',kim as 'K',lee as 'L',park as 'P',shin as 'S',choi as 'C'))
;

select  column_1,column_2,
        lead(column_1,1,'-')  over (partition by 1 order by column_2) lead_col,
        lag(column_1,1,'-')  over (partition by 1 order by column_2) lag_col
from    my_table_1
order   by column_2
;

select  listagg(column_1,',') within group (order by column_2) name_list
from    my_table_1
;

select  column_1,column_2,
        sum(column_2) over (partition by 1 order by column_1 rows between 2 preceding and 1 preceding) before_two_rows,
        sum(column_2) over (partition by 1 order by column_1 rows between 1 following and 2 following) after_two_rows,
        sum(column_2) over (partition by 1 order by column_1 rows between unbounded preceding and current row) before_all_rows,
        avg(column_2) over (partition by 1 order by column_1 rows between unbounded preceding and current row) row_sample,
        avg(column_2) over (partition by 1 order by column_1 range between unbounded preceding and current row) range_sample
from    my_table_1
order   by column_1
;

with    data_set as
(
select  'A' frid, 'D' toid from dual union all
select  'D' frid, 'B' toid from dual union all
select  'B' frid, 'C' toid from dual union all
select  'C' frid, 'G' toid from dual union all
select  'C' frid, 'H' toid from dual union all
select  'G' frid, 'E' toid from dual union all
select  'H' frid, 'I' toid from dual
)
select  level lvl,
        connect_by_root(a.frid) root,
        a.frid,a.toid,
        sys_connect_by_path(frid,'>')||'>'||toid conn_str
from    data_set a
start   with frid = 'A'
connect by prior toid = frid
order   by level
;

select  column_1,column_2,
        sum(column_2) over (partition by 1) age_sum,
        column_2/sum(column_2) over (partition by 1) age_rto_1,
        ratio_to_report(column_2) over (partition by 1) age_rto_2
from    my_table_1
;

create  table my_table_2 as
select  *
from    my_table_1
;

create  table my_table_3 as
select  column_1 as co1,column_2 as co2
from    my_table_1
;

merge   into my_table_1 a
using   (
        select  column_1,column_2+10 column_2,column_3
        from    my_table_1
        where   substr(column_1,1,1) in ('홍','박','신')
        union all
        select  '유소영' column_1,27 column_2,sysdate column_3
        from    dual
        ) b
on      (
        a.column_1 = b.column_1
        )
when matched then update set
        a.column_2 = column_2
when not matched then insert (a.column_1,a.column_2,a.column_3)
        values (b.column_1,b.column_2,b.column_3)
;