CREATE TABLE CUSTOMERS
(
CUSTOMER_ID NUMBER NOT NULL
, CUST_FIRST_NAME VARCHAR2(20)
, CUST_LAST_NAME VARCHAR2(20)
, CUST_ADDRESS VARCHAR2(100)
, PHONE_NUMBERS VARCHAR2(50)
, NLS_LANGUAGE VARCHAR2(20)
, NLS_TERRITORY VARCHAR2(100)
, CREDIT_LIMIT NUMBER
, CUST_EMAIL VARCHAR2(50)
, ACCOUNT_MGR_ID NUMBER
, CUST_GEO_LOCATION VARCHAR2(200)
, DATE_OF_BIRTH DATE
, MARITAL_STATUS VARCHAR2(20)
, GENDER CHAR(1) NOT NULL
, INCOME_LEVEL INTEGER
, CONSTRAINT CUSTOMERS_PK PRIMARY KEY
(
CUSTOMER_ID
)
ENABLE
);
--INSERT DATA
INSERT INTO "OE"."CUSTOMERS" (CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_ADDRESS, PHONE_NUMBERS, NLS_LANGUAGE,
NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID, CUST_GEO_LOCATION, DATE_OF_BIRTH, MARITAL_STATUS, GENDER, INCOME_LEVEL)
VALUES ('1', 'Abiola', 'Amzat', 'old kent', '07919 191791', 'en', 'uk', '2', 'amzat@gmail.com', '2',
'London', TO_DATE('2003-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Married', 'f', '200000')
INSERT INTO "OE"."CUSTOMERS" (CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_ADDRESS, PHONE_NUMBERS, NLS_LANGUAGE,
CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID, CUST_GEO_LOCATION, DATE_OF_BIRTH, MARITAL_STATUS, GENDER, INCOME_LEVEL)
VALUES ('2', 'Felix', 'Combs', '3rd Avenue', '23478970975', 'Ohio', '1', 'felixcombs@gmail.com', '3',
'USA', TO_DATE('2004-12-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Divorce', 'M', '1000000')
INSERT INTO "OE"."CUSTOMERS" (CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_ADDRESS, PHONE_NUMBERS, NLS_LANGUAGE,
CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID, CUST_GEO_LOCATION, DATE_OF_BIRTH, MARITAL_STATUS, GENDER, INCOME_LEVEL)
VALUES ('3', 'Adio', 'Fela', 'Hall street', '23490970975', 'lagos', '0', 'adiofela@gmail.com', '3',
'USA', TO_DATE('2001-02-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Single', 'M', '100')
--orders table
CREATE TABLE "ORDERS" (
"ORDER_ID" NUMBER NOT NULL ENABLE,
"ORDER_DATE" TIMESTAMP with local time zone,
"ORDER_MODE" VARCHAR2(100),
"CUSTOMER_ID" NUMBER NOT NULL ENABLE,
"ORDER_Status" VARCHAR2(100),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID" NUMBER NOT NULL ENABLE,
"PROMOTION_ID" NUMBER NOT NULL ENABLE,
CONSTRAINT "ORDER_TOTAL" CHECK (ORDER_TOTAL >= 0) ENABLE,
CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID") ENABLE,
CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
REFERENCES "CUSTOMERS" ("CUSTOMER_ID") ON DELETE CASCADE ENABLE
)
--INSERT INTO ORDERS TABLE
INSERT INTO "OE"."ORDERS" (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, "ORDER_Status", ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES ('1', TO_TIMESTAMP('2022-12-12 18:49:19.304818200', 'YYYY-MM-DD HH24:MI:SS.FF'), 'Bike', '5', 'Delivered', '4500', '4', '5');
INSERT INTO "OE"."ORDERS" (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, "ORDER_Status", ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES ('2', systimestamp-51, 'Bus', '2', 'Failed', '5500', '2', '1');
INSERT INTO "OE"."ORDERS" (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, "ORDER_Status", ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES ('3', systimestamp-41, 'Bike', '2', 'Delivered', '500', '2', '1');
INSERT INTO "OE"."ORDERS" (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, "ORDER_Status", ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES ('4', systimestamp-21, 'Bike', '2', 'Delivered', '500', '3', '2');
INSERT INTO "OE"."ORDERS" (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, "ORDER_Status", ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES ('5', systimestamp-65, 'Bike', '1', 'Delivered', '20500', '3', '2');
INSERT INTO "OE"."ORDERS" (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, "ORDER_Status", ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES ('6', systimestamp-64, 'Bus', '1', 'Failed', '10500', '2', '2');
INSERT INTO "OE"."ORDERS" (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, "ORDER_Status", ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES ('7', systimestamp-6, 'Bus', '8', 'Failed', '100000', '1', '1');
INSERT INTO "OE"."ORDERS" (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, "ORDER_Status", ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)
VALUES ('8', systimestamp-67, 'Bus', '6', 'Failed', '100000', '1', '1');
