Oracle Live SQL - Script: meta table example

Statement 1

begin  
for i in ( 
    select TABLE_NAME  
    from USER_TABLES  
        where TABLE_NAME in ('META_TABLE', 'ITEM_SERVICE_TABLE', 'ITEM_RETAIL_TABLE', 'ITEM_MAINTENANCE_TABLE', 'ITEM_TABLE')  
    order by TABLE_NAME  
        ) 
    loop 
        DBMS_OUTPUT.PUT_LINE ('drop table ' || i.TABLE_NAME); 
        execute immediate 'drop table ' || i.TABLE_NAME; 
    end loop i; 
end;

Statement processed.

Statement 2

create table META_TABLE ( 
    TABLE_ITEM int constraint META_TABLE_PK primary key not null,  
    ITEM_TYPE char(30) not null, -- service, retail, maintenance  
    CREATE_DATE date not null,  
    PARTY1_ID int not null,  
    PARTY2_ID int not null,  
    DESCRIPTION char(200) not null,  
    STATUS char(30) not null,  
    SVC_TYPE char(30) null,  
    SVC_HOURS number null, 
    MILEAGE number null,  
    PRODUCT_ID int null,  
    QUANTITY int null,  
    TOTAL_SALE number(10,4) null,  
    EQUIP_ID int null,  
    MAINT_TYPE char(30) null  
    )

Table created.

Statement 3

insert into META_TABLE  
select 1, 'SERVICE', sysdate, 11, 6001, 'Service example 1', 'SCHEDULED', 'HVAC', 3.5, 16, null, null, null, null, null from dual 
union 
select 2, 'SERVICE', sysdate, 37, 1203, 'Service example 2', 'COMPLETE', 'Pool', 2, 9, null, null, null, null, null from dual 
union 
select 3, 'RETAIL', sysdate, 16, 9600, 'Retail example 1', 'COMPLETE', null, null, null, 654321, 3, 87.52, null, null from dual 
union 
select 4, 'RETAIL', sysdate, 203, 1107, 'Retail example 2', 'COMPLETE', null, null, null, 2468, 11, 23.45, null, null from dual 
union 
select 5, 'MAINTENANCE', sysdate, 102, 0, 'Maintenance example 1', 'SCHEDULED', null, null, null, null, null, null, 16, 'PM' from dual 
union 
select 6, 'MAINTENANCE', sysdate, 102, 0, 'Maintenance2', 'IN PROGESS', null, null, null, null, null, null, 17, 'CLEANING' from dual

6 row(s) inserted.

Statement 4

create table ITEM_TABLE ( 
    TABLE_ITEM int constraint ITEM_TABLE_PK primary key not null,  
    ITEM_TYPE char(30) not null,  
    CREATE_DATE date not null,  
    PARTY1_ID int not null,  
    PARTY2_ID int not null,  
    DESCRIPTION char(200) not null,  
    STATUS char(30) not null  
    )

Table created.

Statement 5

insert into ITEM_TABLE  
select 1, 'SERVICE', sysdate, 11, 6001, 'Service example 1', 'SCHEDULED' from dual 
union 
select 2, 'SERVICE', sysdate, 37, 1203, 'Service example 2', 'COMPLETE' from dual 
union 
select 3, 'RETAIL', sysdate, 16, 9600, 'Retail example 1', 'COMPLETE' from dual 
union 
select 4, 'RETAIL', sysdate, 203, 1107, 'Retail example 2', 'COMPLETE' from dual 
union 
select 5, 'MAINTENANCE', sysdate, 102, 0, 'Maintenance example 1', 'SCHEDULED' from dual 
union 
select 6, 'MAINTENANCE', sysdate, 102, 0, 'Maintenance2', 'IN PROGESS' from dual

6 row(s) inserted.

Statement 6

create table ITEM_SERVICE_TABLE ( 
    SERVICE_TABLE_ITEM int constraint ITEM_SERVICE_TABLE_PK primary key not null,  
    TABLE_ITEM int not null,  
    SVC_TYPE char(30) not null,  
    SVC_HOURS number not null, 
    MILEAGE number not null,  
    constraint TABLE_ITEM_FK1 foreign key (TABLE_ITEM) references ITEM_TABLE (TABLE_ITEM)  
    )

Table created.

Statement 7

insert into ITEM_SERVICE_TABLE  
select 1, 1, 'HVAC', 3.5, 16 from dual 
union 
select 2, 2, 'Pool', 2, 9 from dual

2 row(s) inserted.

Statement 8

create table ITEM_RETAIL_TABLE ( 
    RETAIL_TABLE_ITEM int constraint ITEM_RETAIL_TABLE_PK primary key not null,  
    TABLE_ITEM int not null,  
    PRODUCT_ID int not null,  
    QUANTITY int not null,  
    TOTAL_SALE number(10,4) not null,  
    constraint TABLE_ITEM_FK2 foreign key (TABLE_ITEM) references ITEM_TABLE (TABLE_ITEM)  
    )

Table created.

Statement 9

insert into ITEM_RETAIL_TABLE  
    ( 
    RETAIL_TABLE_ITEM, TABLE_ITEM,  
    PRODUCT_ID, QUANTITY, TOTAL_SALE  
    ) 
select 1, 3, 654321, 3, 87.52 from dual 
union 
select 2, 4, 2468, 11, 23.45 from dual

2 row(s) inserted.

Statement 10

create table ITEM_MAINTENANCE_TABLE ( 
    MAINTENANCE_TABLE_ITEM int constraint ITEM_MAINTENANCE_TABLE_PK primary key not null,  
    TABLE_ITEM int not null,  
    EQUIP_ID int not null,  
    MAINT_TYPE char(30) not null,  
    constraint TABLE_ITEM_FK3 foreign key (TABLE_ITEM) references ITEM_TABLE (TABLE_ITEM)  
    )

Table created.

Statement 11

insert into ITEM_MAINTENANCE_TABLE  
    ( 
    MAINTENANCE_TABLE_ITEM, TABLE_ITEM,  
    EQUIP_ID, MAINT_TYPE 
    ) 
select 1, 5, 16, 'PM' from dual 
union 
select 2, 6, 17, 'CLEANING' from dual

2 row(s) inserted.

Statement 12

commit

Statement processed.

Statement 13

select * from META_TABLE


TABLE_ITEM ITEM_TYPE CREATE_DATE PARTY1_ID PARTY2_ID DESCRIPTION STATUS SVC_TYPE SVC_HOURS MILEAGE PRODUCT_ID QUANTITY TOTAL_SALE EQUIP_ID MAINT_TYPE
1 SERVICE                       14-AUG-17 11 6001 Service example 1                                                                                                                                                                                       SCHEDULED                     HVAC                          3.5 16  -  -  -  -  - 
2 SERVICE                       14-AUG-17 37 1203 Service example 2                                                                                                                                                                                       COMPLETE                      Pool                          2 9  -  -  -  -  - 
3 RETAIL                        14-AUG-17 16 9600 Retail example 1                                                                                                                                                                                        COMPLETE                       -  -  - 654321 3 87.52  -  - 
4 RETAIL                        14-AUG-17 203 1107 Retail example 2                                                                                                                                                                                        COMPLETE                       -  -  - 2468 11 23.45  -  - 
5 MAINTENANCE                   14-AUG-17 102 0 Maintenance example 1                                                                                                                                                                                   SCHEDULED                      -  -  -  -  -  - 16 PM                            
6 MAINTENANCE                   14-AUG-17 102 0 Maintenance2                                                                                                                                                                                            IN PROGESS                     -  -  -  -  -  - 17 CLEANING                      

6 rows selected.

TABLE_ITEM	ITEM_TYPE	CREATE_DATE	PARTY1_ID	PARTY2_ID	DESCRIPTION	STATUS	SVC_TYPE	SVC_HOURS	MILEAGE	PRODUCT_ID	QUANTITY	TOTAL_SALE	EQUIP_ID	MAINT_TYPE
1	SERVICE	14-AUG-17	11	6001	Service example 1	SCHEDULED	HVAC	3.5	16	-	-	-	-	-
2	SERVICE	14-AUG-17	37	1203	Service example 2	COMPLETE	Pool	2	9	-	-	-	-	-
3	RETAIL	14-AUG-17	16	9600	Retail example 1	COMPLETE	-	-	-	654321	3	87.52	-	-
4	RETAIL	14-AUG-17	203	1107	Retail example 2	COMPLETE	-	-	-	2468	11	23.45	-	-
5	MAINTENANCE	14-AUG-17	102	0	Maintenance example 1	SCHEDULED	-	-	-	-	-	-	16	PM
6	MAINTENANCE	14-AUG-17	102	0	Maintenance2	IN PROGESS	-	-	-	-	-	-	17	CLEANING

Statement 14

select * from ITEM_TABLE


TABLE_ITEM ITEM_TYPE CREATE_DATE PARTY1_ID PARTY2_ID DESCRIPTION STATUS
1 SERVICE                       14-AUG-17 11 6001 Service example 1                                                                                                                                                                                       SCHEDULED                     
2 SERVICE                       14-AUG-17 37 1203 Service example 2                                                                                                                                                                                       COMPLETE                      
3 RETAIL                        14-AUG-17 16 9600 Retail example 1                                                                                                                                                                                        COMPLETE                      
4 RETAIL                        14-AUG-17 203 1107 Retail example 2                                                                                                                                                                                        COMPLETE                      
5 MAINTENANCE                   14-AUG-17 102 0 Maintenance example 1                                                                                                                                                                                   SCHEDULED                     
6 MAINTENANCE                   14-AUG-17 102 0 Maintenance2                                                                                                                                                                                            IN PROGESS                    

6 rows selected.

TABLE_ITEM	ITEM_TYPE	CREATE_DATE	PARTY1_ID	PARTY2_ID	DESCRIPTION	STATUS
1	SERVICE	14-AUG-17	11	6001	Service example 1	SCHEDULED
2	SERVICE	14-AUG-17	37	1203	Service example 2	COMPLETE
3	RETAIL	14-AUG-17	16	9600	Retail example 1	COMPLETE
4	RETAIL	14-AUG-17	203	1107	Retail example 2	COMPLETE
5	MAINTENANCE	14-AUG-17	102	0	Maintenance example 1	SCHEDULED
6	MAINTENANCE	14-AUG-17	102	0	Maintenance2	IN PROGESS

Statement 15

select * from ITEM_SERVICE_TABLE


SERVICE_TABLE_ITEM TABLE_ITEM SVC_TYPE SVC_HOURS MILEAGE
1 1 HVAC                          3.5 16
2 2 Pool                          2 9

2 rows selected.

SERVICE_TABLE_ITEM	TABLE_ITEM	SVC_TYPE	SVC_HOURS	MILEAGE
1	1	HVAC	3.5	16
2	2	Pool	2	9

Statement 16

select * from ITEM_RETAIL_TABLE


RETAIL_TABLE_ITEM TABLE_ITEM PRODUCT_ID QUANTITY TOTAL_SALE
1 3 654321 3 87.52
2 4 2468 11 23.45

2 rows selected.

RETAIL_TABLE_ITEM	TABLE_ITEM	PRODUCT_ID	QUANTITY	TOTAL_SALE
1	3	654321	3	87.52
2	4	2468	11	23.45

Statement 17

select * from ITEM_MAINTENANCE_TABLE


MAINTENANCE_TABLE_ITEM TABLE_ITEM EQUIP_ID MAINT_TYPE
1 5 16 PM                            
2 6 17 CLEANING                      

2 rows selected.

meta table example

Additional Information