Oracle Live SQL - Script: Session 01

Statement 1

create table Table_A  ( PRODUCT_ID varchar2(30), STATE varchar2(30), ZIP_CD varchar2(30), Modified_dt date)

Table created.

Statement 2

insert into table_a values ('abc',  'MN', '123', to_timestamp('3/5/2020 12:01:00 AM', 'mm/dd/yyyy hh:mi:ss AM'))

1 row(s) inserted.

Statement 3

insert into table_a values ('abc',  'MN', '123', to_timestamp('3/5/2020 6:01:13 PM', 'mm/dd/yyyy hh:mi:ss PM'))

1 row(s) inserted.

Statement 4

insert into table_a values ('abc',  'IL', '223', to_timestamp('3/5/2020 7:01:15 PM', 'mm/dd/yyyy hh:mi:ss PM'))

1 row(s) inserted.

Statement 5

insert into table_a values ('abc',  'OH', '333', to_timestamp('3/5/2020 6:01:16 PM', 'mm/dd/yyyy hh:mi:ss PM'))

1 row(s) inserted.

Statement 6

insert into table_a values ('abc',  'NY', '722', to_timestamp('3/5/2020 4:29:00 PM', 'mm/dd/yyyy hh:mi:ss PM'))

1 row(s) inserted.

Statement 7

insert into table_a values ('abc',  'KS', '444', to_timestamp('3/5/2020 4:31:41 PM', 'mm/dd/yyyy hh:mi:ss PM'))

1 row(s) inserted.

Statement 8

insert into table_a values ('bbc',  'MN', '123', to_timestamp('3/19/2020 2:47:08 PM', 'mm/dd/yyyy hh:mi:ss PM'))

1 row(s) inserted.

Statement 9

insert into table_a values ('bbc',  'IL', '223', to_timestamp('3/19/2020 2:50:37 PM', 'mm/dd/yyyy hh:mi:ss PM'))

1 row(s) inserted.

Statement 10

insert into table_a values ('ccb',  'MN', '123', to_timestamp('3/19/2020 2:56:24 PM', 'mm/dd/yyyy hh:mi:ss PM'))

1 row(s) inserted.

Statement 11

insert into table_a values ('dbd',  'KS', '444', to_timestamp('3/19/2020 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'))

1 row(s) inserted.

Statement 12

select  PRODUCT_ID, STATE, ZIP_CD, to_char(modified_dt, 'MM/DD/YYYY HH:MI:SS AM') modified_dt from table_A


PRODUCT_ID STATE ZIP_CD MODIFIED_DT
abc MN 123 03/05/2020 12:01:00 AM
abc MN 123 03/05/2020 06:01:13 PM
abc IL 223 03/05/2020 07:01:15 PM
abc OH 333 03/05/2020 06:01:16 PM
abc NY 722 03/05/2020 04:29:00 PM
abc KS 444 03/05/2020 04:31:41 PM
bbc MN 123 03/19/2020 02:47:08 PM
bbc IL 223 03/19/2020 02:50:37 PM
ccb MN 123 03/19/2020 02:56:24 PM
dbd KS 444 03/19/2020 12:00:00 AM

10 rows selected.

PRODUCT_ID	STATE	ZIP_CD	MODIFIED_DT
abc	MN	123	03/05/2020 12:01:00 AM
abc	MN	123	03/05/2020 06:01:13 PM
abc	IL	223	03/05/2020 07:01:15 PM
abc	OH	333	03/05/2020 06:01:16 PM
abc	NY	722	03/05/2020 04:29:00 PM
abc	KS	444	03/05/2020 04:31:41 PM
bbc	MN	123	03/19/2020 02:47:08 PM
bbc	IL	223	03/19/2020 02:50:37 PM
ccb	MN	123	03/19/2020 02:56:24 PM
dbd	KS	444	03/19/2020 12:00:00 AM

Statement 13

create table Table_B  ( SEQUENCE_KEY number, PRODUCT_ID varchar2(30), STATE varchar2(30), ZIP_CD varchar2(30), valid_from date, valid_to date, latest_flag varchar2(1))

Table created.

Statement 14

insert into table_b values (1,'abc',  'AR', '999', to_timestamp('3/5/2020 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),to_timestamp('3/5/2020 6:01:13 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 15

insert into table_b values (2, 'abc',  'AR', '555', to_timestamp('3/5/2020 6:01:14 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 6:01:14 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 16

insert into table_b values (3, 'abc',  'CA', '565', to_timestamp('3/5/2020 6:01:15 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 4:28:59 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 17

insert into table_b values (4, 'abc',  'CA', '777', to_timestamp('3/5/2020 4:29:00 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('12/31/2099 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),'Y')

1 row(s) inserted.

Statement 18

insert into table_b values (5, 'bbc',  'MN', '123', to_timestamp('3/5/2020 4:31:41 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 2:47:07 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 19

insert into table_b values (6, 'bbc',  'MN', '666', to_timestamp('3/5/2020 2:47:08 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 2:50:36 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 20

insert into table_b values (7, 'bbc',  'MN', '777', to_timestamp('3/19/2020 2:50:37 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('12/31/2099 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),'Y')

1 row(s) inserted.

Statement 21

insert into table_b values (8, 'ccb',  'MN', '123', to_timestamp('3/19/2020 2:56:24 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('12/31/2099 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),'Y')

1 row(s) inserted.

Statement 22

select  SEQUENCE_KEY, PRODUCT_ID, STATE, ZIP_CD, to_char(valid_from, 'MM/DD/YYYY HH:MI:SS AM') valid_from, to_char(valid_to, 'MM/DD/YYYY HH:MI:SS AM') valid_to, latest_flag from table_B


SEQUENCE_KEY PRODUCT_ID STATE ZIP_CD VALID_FROM VALID_TO LATEST_FLAG
1 abc AR 999 03/05/2020 12:00:00 AM 03/05/2020 06:01:13 PM N
2 abc AR 555 03/05/2020 06:01:14 PM 03/05/2020 06:01:14 PM N
3 abc CA 565 03/05/2020 06:01:15 PM 03/05/2020 04:28:59 PM N
4 abc CA 777 03/05/2020 04:29:00 PM 12/31/2099 12:00:00 AM Y
5 bbc MN 123 03/05/2020 04:31:41 PM 03/05/2020 02:47:07 PM N
6 bbc MN 666 03/05/2020 02:47:08 PM 03/05/2020 02:50:36 PM N
7 bbc MN 777 03/19/2020 02:50:37 PM 12/31/2099 12:00:00 AM Y
8 ccb MN 123 03/19/2020 02:56:24 PM 12/31/2099 12:00:00 AM Y

8 rows selected.

SEQUENCE_KEY	PRODUCT_ID	STATE	ZIP_CD	VALID_FROM	VALID_TO	LATEST_FLAG
1	abc	AR	999	03/05/2020 12:00:00 AM	03/05/2020 06:01:13 PM	N
2	abc	AR	555	03/05/2020 06:01:14 PM	03/05/2020 06:01:14 PM	N
3	abc	CA	565	03/05/2020 06:01:15 PM	03/05/2020 04:28:59 PM	N
4	abc	CA	777	03/05/2020 04:29:00 PM	12/31/2099 12:00:00 AM	Y
5	bbc	MN	123	03/05/2020 04:31:41 PM	03/05/2020 02:47:07 PM	N
6	bbc	MN	666	03/05/2020 02:47:08 PM	03/05/2020 02:50:36 PM	N
7	bbc	MN	777	03/19/2020 02:50:37 PM	12/31/2099 12:00:00 AM	Y
8	ccb	MN	123	03/19/2020 02:56:24 PM	12/31/2099 12:00:00 AM	Y

Statement 23

create table FINAL_Table_B  ( sequence_key number, PRODUCT_ID varchar2(30), STATE varchar2(30), ZIP_CD varchar2(30), valid_from date, valid_to date, latest_flag varchar2(1))

Table created.

Statement 24

insert into FINAL_Table_B values (1, 'abc',  'AR', '999', to_timestamp('3/5/2020 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),to_timestamp('3/5/2020 6:01:13 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 25

insert into FINAL_Table_B values (2,'abc',  'AR', '555', to_timestamp('3/5/2020 6:01:14 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 6:01:14 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 26

insert into FINAL_Table_B values (3,'abc',  'CA', '565', to_timestamp('3/5/2020 6:01:15 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 4:28:59 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 27

insert into FINAL_Table_B values (4, 'abc',  'CA', '777', to_timestamp('3/5/2020 4:29:00 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),'N')

1 row(s) inserted.

Statement 28

insert into FINAL_Table_B values (5, 'abc',  'MN', '123', to_timestamp('3/5/2020 12:01:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),to_timestamp('3/5/2020 7:01:14 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 29

insert into FINAL_Table_B values (6, 'abc',  'IL', '223', to_timestamp('3/5/2020 7:01:15 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020  6:01:15 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 30

insert into FINAL_Table_B values (7, 'abc',  'OH', '333', to_timestamp('3/5/2020  6:01:16 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 4:28:59 PM', 'mm/dd/yyyy hh:mi:ss AM'),'N')

1 row(s) inserted.

Statement 31

insert into FINAL_Table_B values (8, 'abc',  'NY', '722', to_timestamp('3/5/2020 4:29:00 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 4:31:40 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 32

insert into FINAL_Table_B values (9, 'abc',  'KS', '444', to_timestamp('3/5/2020 4:31:41 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),'Y')

1 row(s) inserted.

Statement 33

insert into FINAL_Table_B values (10, 'bbc',  'MN', '123', to_timestamp('3/5/2020 4:31:41 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 2:47:07 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 34

insert into FINAL_Table_B values (11, 'bbc',  'MN', '666', to_timestamp('3/5/2020 2:47:08 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/5/2020 2:50:36 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 35

insert into FINAL_Table_B values (12, 'bbc',  'MN', '777', to_timestamp('3/19/2020 2:50:37 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/19/2020 2:47:07 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 36

insert into FINAL_Table_B values (13, 'bbc',  'MN', '123', to_timestamp('3/19/2020 2:47:08 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('3/19/2020 2:50:36 PM', 'mm/dd/yyyy hh:mi:ss PM'),'N')

1 row(s) inserted.

Statement 37

insert into FINAL_Table_B values (14, 'bbc',  'IL', '223', to_timestamp('3/19/2020 2:50:37 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('12/31/2099 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),'Y')

1 row(s) inserted.

Statement 38

insert into FINAL_Table_B values (15, 'ccb',  'MN', '123', to_timestamp('3/19/2020 2:56:24 PM', 'mm/dd/yyyy hh:mi:ss PM'),to_timestamp('12/31/2099 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),'Y')

1 row(s) inserted.

Statement 39

insert into FINAL_Table_B values (16, 'dbd',  'KS', '444', to_timestamp('6/20/2020 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),to_timestamp('12/31/2099 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'),'Y')

1 row(s) inserted.

Statement 40

select  sequence_key, PRODUCT_ID, STATE, ZIP_CD, to_char(valid_from, 'MM/DD/YYYY HH:MI:SS AM') valid_from, to_char(valid_to, 'MM/DD/YYYY HH:MI:SS AM') valid_to, latest_flag from final_table_B order by product_id


SEQUENCE_KEY PRODUCT_ID STATE ZIP_CD VALID_FROM VALID_TO LATEST_FLAG
1 abc AR 999 03/05/2020 12:00:00 AM 03/05/2020 06:01:13 PM N
2 abc AR 555 03/05/2020 06:01:14 PM 03/05/2020 06:01:14 PM N
9 abc KS 444 03/05/2020 04:31:41 PM 03/05/2020 12:00:00 AM Y
8 abc NY 722 03/05/2020 04:29:00 PM 03/05/2020 04:31:40 PM N
7 abc OH 333 03/05/2020 06:01:16 PM 03/05/2020 04:28:59 PM N
6 abc IL 223 03/05/2020 07:01:15 PM 03/05/2020 06:01:15 PM N
5 abc MN 123 03/05/2020 12:01:00 AM 03/05/2020 07:01:14 PM N
4 abc CA 777 03/05/2020 04:29:00 PM 03/05/2020 12:00:00 AM N
3 abc CA 565 03/05/2020 06:01:15 PM 03/05/2020 04:28:59 PM N
14 bbc IL 223 03/19/2020 02:50:37 PM 12/31/2099 12:00:00 AM Y
13 bbc MN 123 03/19/2020 02:47:08 PM 03/19/2020 02:50:36 PM N
12 bbc MN 777 03/19/2020 02:50:37 PM 03/19/2020 02:47:07 PM N
11 bbc MN 666 03/05/2020 02:47:08 PM 03/05/2020 02:50:36 PM N
10 bbc MN 123 03/05/2020 04:31:41 PM 03/05/2020 02:47:07 PM N
15 ccb MN 123 03/19/2020 02:56:24 PM 12/31/2099 12:00:00 AM Y
16 dbd KS 444 06/20/2020 12:00:00 AM 12/31/2099 12:00:00 AM Y

16 rows selected.

Session 01

Additional Information