create database super_store;
use super_store;

create table customer(Customer_ID varchar(30) primary key,	
						Customer_Name varchar(50),
                        Segment varchar(20),
                        Country varchar(20),
                        City varchar(20),
                        State varchar(20),
                        Postal_Code int(10),
                        Region varchar(20));
select * from customer;

CREATE TABLE super_store.order(
    Order_ID VARCHAR(30) PRIMARY KEY,
    Order_Date DATE,
    Ship_Date DATE,
    Ship_Mode VARCHAR(50)
);

select * from super_store.order;


CREATE TABLE Product (
    Product_ID VARCHAR(30) PRIMARY KEY,
    Category VARCHAR(50),
    Sub_Category VARCHAR(50),
    Product_Name VARCHAR(150)
);
select * from Product;


-- Create Returned table with foreign key constraint
CREATE TABLE Returned (
    Order_ID VARCHAR(30),
    Returned varchar(10),
    PRIMARY KEY (Order_ID),
    FOREIGN KEY (Order_ID) REFERENCES super_store.order(Order_ID)
);
select * from returned;


CREATE TABLE Sales (
  Order_ID varchar(30) NOT NULL,
  Customer_ID varchar(30) NOT NULL,
  Product_ID varchar(30) NOT NULL,
  Sales DECIMAL(10,2) NOT NULL,
  Quantity INT NOT NULL,
  Discount DECIMAL(5,2),
  Profit DECIMAL(10,2),
  PRIMARY KEY (Order_ID, Customer_ID, Product_ID),
  FOREIGN KEY (Order_ID) REFERENCES super_store.order(Order_ID),
  FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID),
  FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);
select * from sales;