-- create tables create table ch17_employee ( id number generated by default on null as identity constraint ch17_employee_id_pk primary key, apex_un varchar2(30 char) not null, firstname varchar2(30 char) not null, lastname varchar2(30 char) not null ) ; create table ch17_customer ( id number generated by default on null as identity constraint ch17_customer_id_pk primary key, comp_name varchar2(80 char) not null, comp_taxid varchar2(20 char) not null, comp_bic varchar2(20 char) not null, comp_iban varchar2(20 char) not null, comp_rating varchar2(1 char) constraint ch17_customer_comp_rating_ck check (comp_rating in ('A','B','C','D')) not null ) ; create table ch17_doc_class ( id number generated by default on null as identity constraint ch17_doc_class_id_pk primary key, doc_class_desc varchar2(512 char) not null, doc_class_short varchar2(13 char) constraint ch17_doc_clas_doc_class_sho_ck check (doc_class_short in ('MANUFACTURING','FINANCIAL','BUSINESS','OTHER')) not null ) ; create table ch17_inquiry ( id number generated by default on null as identity constraint ch17_inquiry_id_pk primary key, customer_id number constraint ch17_inquiry_customer_id_fk references ch17_customer on delete cascade not null, cust_price number not null, cust_quantity number not null, cust_delivery_date date not null, cust_product_item varchar2(50) not null, sale_man_id number constraint ch17_inquiry_sale_man_id_fk references ch17_employee on delete cascade not null, sale_eval_date date, prod_capability varchar2(11 char) constraint ch17_inquiry_manf_ck check (prod_capability in ('YES','NO','CONDITIONAL','N.A.')) not null, prod_delivery_days number, prod_justification varchar2(1024 char), prod_man_id number constraint ch17_inquiry_prod_man_id_fk references ch17_employee on delete cascade not null, prod_eval_date date, fina_capability varchar2(11 char) constraint ch17_inquiry_fin_ck check (fina_capability in ('YES','NO','CONDITIONAL','N.A.')) not null, fina_exp_profit number, fina_justification varchar2(1024 char), fina_man_id number constraint ch17_inquiry_fina_man_id_fk references ch17_employee on delete cascade not null, fina_eval_date date, bus_capability varchar2(11 char) constraint ch17_inquiry_bus_ck check (bus_capability in ('YES','NO','CONDITIONAL','N.A.')) not null, bus_bid_price number, bus_bid_quant number, bus_bid_deliver date, bus_man_id number constraint ch17_inquiry_bus_man_id_fk references ch17_employee on delete cascade not null, bus_eval_date date ) ; create table ch17_inquiry_doc ( id number generated by default on null as identity constraint ch17_inquiry_doc_id_pk primary key, inquiry_id number constraint ch17_inquiry_do_inquiry_id_fk references ch17_inquiry on delete cascade not null, doc_class_id number constraint ch17_inquiry_do_doc_class_i_fk references ch17_doc_class on delete cascade not null, contblb blob, fmime varchar2(100 char), fname varchar2(200 char), fdcr date, frefer varchar2(256 char) ) ; -- load data -- Generated by Quick SQL Sunday February 05, 2023 15:24:01 /* ch17_employee apex_un vc30 /nn, firstname vc30 /nn, lastname vc30 /nn ch17_customer comp_name vc80 /nn, comp_taxid vc20 /nn, comp_bic vc20 /nn, comp_iban vc20 /nn, comp_rating vc1 /check 'A','B','C','D' /nn ch17_doc_class doc_class_desc vc512 /nn, doc_class_short vc13 /check 'MANUFACTURING','FINANCIAL','BUSINESS','OTHER' /nn ch17_inquiry customer_id num /fk ch17_customer /nn, cust_price num /nn, cust_quantity num /nn, cust_delivery_date date /nn, cust_product_item vc50 /nn, sale_man_id num /fk ch17_employee /nn, sale_eval_date date, prod_capability vc11 /check 'YES','NO','CONDITIONAL','N.A.', prod_delivery_days num, prod_justification vc1024, prod_man_id num /fk ch17_employee /nn, prod_eval_date date, fina_capability vc11 /check 'YES','NO','CONDITIONAL','N.A.', fina_exp_profit num, fina_justification vc1024, fina_man_id num /fk ch17_employee /nn, fina_eval_date date, bus_capability vc11 /check 'YES','NO','CONDITIONAL','N.A.', bus_bid_price num, bus_bid_quant num, bus_bid_deliver date, bus_man_id num /fk ch17_employee /nn, bus_eval_date date ch17_inquiry_doc inquiry_id /fk ch17_inquiry /nn, doc_class_id /fk ch17_doc_class /nn, contblb blob, fmime vc100, fname vc200, fdcr date, frefer vc256 # settings = { semantics: "CHAR", language: "EN", APEX: true } */