You will demonstrate an update that includes two tables and usesa subquery. Make sure to prove that your update executed correctlyby showing data in the tables before and after the update.
CREATE TABLE PRODUCTS (
Product_ID INT PRIMARY KEY,
Product_Type VARCHAR(50),
Product_Name VARCHAR(50),
Product_Desc VARCHAR(50),
Product_Details VARCHAR(50)
);
CREATE TABLE CUSTOMER_ORDER_PRODUCTS (
order_ID INT,
product_ID VARCHAR(50),
quantity INT,
product_cost INT,
PRIMARY KEY(order_ID, product_ID)
);
CREATE TABLE CUSTOMERS (
Customer_ID INT PRIMARY KEY,
Customer_Name VARCHAR(20),
Customer_Phone VARCHAR(20),
Customer_Email VARCHAR(20)
);
CREATE TABLE CUSTOMER_ORDERS (
order_ID INT PRIMARY KEY,
customer_ID INT,
customer_payment_method VARCHAR(50),
order_status VARCHAR(50),
FOREIGN KEY (order_ID) REFERENCES CUSTOMER_ORDER_PRODUCTS(order_ID),
FOREIGN KEY (customer_ID) REFERENCES CUSTOMERS(customer_ID),
FOREIGN KEY (customer_payment_method) REFERENCES PAYMENT_METHOD(Payment_Method)
);
CREATE TABLE CUSTOMER_PAYMENT_METHOD (
customer_ID INT,
payment_method VARCHAR(50),
card_number VARCHAR(50),
Date DATE,
order_details VARCHAR(50),
FOREIGN KEY (customer_ID) REFERENCES CUSTOMERS(customer_ID),
FOREIGN KEY (payment_method) REFERENCES PAYMENT_METHOD(Payment_Method)
);
CREATE TABLE PAYMENT_METHOD (
Payment_Method VARCHAR(50)