SQL for database of a Store

create database shopee_improvisation;
CREATE TABLE shopee_improvisation.Product_Category (
  Product_ID nvarchar(255) not null,
  Product_Price int,
  Product_Type varchar(255),
  primary key(Product_ID)
);

CREATE TABLE shopee_improvisation.Customer (
  Customer_ID nvarchar(255) not null,
  Customer_Name varchar(255),
  Email nvarchar(255),
  Telephone_No int,
  Address nvarchar(255),
  City varchar(255),
  Postal_Code int,
  primary key(Customer_ID)
);

CREATE TABLE shopee_improvisation.Shipment_Information (
  Product_ID nvarchar(255) not null,
  Shipment_ID nvarchar(255) not null,
  Shipment_Price int,
  primary key(Shipment_ID)
);

CREATE TABLE shopee_improvisation.Payment (
  Transaction_ID nvarchar(255) not null,
  Payment_Type char(255),
  Payment_Date date,
  Total_Price int,
  Shipment_ID nvarchar(255) not null,
  Product_ID nvarchar(255) not null,
  primary key(Transaction_ID)
);

CREATE TABLE shopee_improvisation.Staff(
  Staff_ID nvarchar(255) not null,
  Staff_Name varchar(255),
  Position varchar(255),
  Email nvarchar(255),
  primary key(Staff_ID)
);

drop table shopee_improvisation.Defective_Product;

CREATE TABLE shopee_improvisation.Defective_Product(
  Product_ID nvarchar(255) not null,
  Customer_ID nvarchar(255) not null,
  Recieve_Date date,
  primary key(Product_ID)
);
CREATE TABLE shopee_improvisation.Supplier (
  Supplier_ID nvarchar(255) not null,
  Supplier_Name varchar(255),
  Email nvarchar(255),
  Telephone_No int,
  primary key(Supplier_ID)
);

CREATE TABLE shopee_improvisation.Product_Information (
  Product_ID nvarchar(255) not null,
  Product_Name varchar(255),
  Product_Category varchar(255),
  Product_Price int not null,
  Order_Quantity int not null,
  primary key(Product_ID)
);

CREATE TABLE shopee_improvisation.Store(
  Product_ID nvarchar(255) not null,
  Product_Status varchar(255),
  Shipment_ID nvarchar(255) not null,
  Payment_Status varchar(255),
  Product_Type varchar(255),
  primary key(Product_ID)
);


drop table shopee_improvisation.Store;



CREATE TABLE shopee_improvisation.Delivery_and_Supply_System (
  Shipment_ID nvarchar(255) not null,
  Shipment_Type varchar(255),
  Delivery_Company_Name varchar(255),
  Recieve_Date date,
  Delivery_Status varchar(255),
  primary key(Shipment_ID)
);

CREATE TABLE shopee_improvisation.Order_Information (
  Customer_ID nvarchar(255) not null,
  Total_Ordered_Product_Price int,
  Required_Date date,
  Order_Date date,
  Order_Id nvarchar(255) not null,
  Product_ID nvarchar(255) not null,
  primary key(order_id)
);

alter table shopee_improvisation.Order_Information
    add  Customer_ID nvarchar(20)
        after Trace_ID;
alter table shopee_improvisation.Order_Information
    add Trace_ID nvarchar(255) not null
        after Product_ID;


#add column Trace id in order_information which is the join of customer_id and order_id

create table shopee_improvisation.Order_trace
as( select OI.Customer_ID, Order_ID,Trace_id
    from shopee_improvisation.Order_Information OI
    join shopee_improvisation.customer
    where customer.Customer_ID=OI.Customer_ID);

alter table shopee_improvisation.payment
    add TRX_ID nvarchar(20)
    after Shipment_ID;
select p.Product_ID,Customer_ID,TRX_ID
from shopee_improvisation.payment p
join shopee_improvisation.order_information;

alter table shopee_improvisation.delivery_and_supply_system
    add verfication_id nvarchar(20)
    after shipment_id ;

select oi.Product_ID, Customer_ID,verfication_id
from shopee_improvisation.delivery_and_supply_system ds
inner join shopee_improvisation.shipment_information si
    on ds.Shipment_ID=si.Shipment_id
inner join shopee_improvisation.order_information oi
    on si.Product_ID=oi.Product_ID;

#order of a customer for test. Suppose the customer's name is David Beckham

----- sign up(customer)----------
insert into shopee_improvisation.customer
values('C107','David Beckham','david@gmail.com','372937','21 Nasirabad', 'Kualalampur','4377');
#Now the customer identity as tamim shah for not doing lengthy process
#Tamim Shah wants to buy a laptop. The whole process will be coded below.
#Query for products

select Product_Type,Product_Name,PC.Product_Price,PC.Product_ID
from shopee_improvisation.product_category PC
join shopee_improvisation.product_information PI
where PC.Product_ID=PI.Product_ID and Product_Type='Electronics' and Product_Name='Laptop';

#see the price of electronics product
select product_category.Product_Price,Product_Name,product_information.Product_ID
from shopee_improvisation.product_information
join shopee_improvisation.product_category
on product_information.Product_ID=product_information.Product_ID and Product_Category='Electronics';

# Selected the desire product
select*
from shopee_improvisation.order_information
join shopee_improvisation.product_information
where order_information.Product_ID=product_information.Product_ID and product_information.Product_ID='P111';
#Then he will get shipment information
select*
from shopee_improvisation.shipment_information
where Product_ID="P111";

#Then he has to make the payment and will keep the TRX_ID

select TRX_ID
from shopee_improvisation.payment
where Product_ID='P111' and Shipment_ID='SP101';

#Now the staff will check the order database and then check the store

select s.Product_ID,Payment_Status,Product_Status,Product_Type
from shopee_improvisation.order_information oi
join shopee_improvisation.store s
where oi.Product_ID=s.Product_ID and s.Product_ID='P111';

#So a staff is informing the supplier who deliver the selected product
#for improvising the experience overall we have to specify the seller with selected product

alter table shopee_improvisation.supplier
    add Product_ID nvarchar(255)
    after Supplier_ID ;
select*
from shopee_improvisation.supplier
where supplier.Product_ID='P111';

#will provide in the store
#One staff have to inform the delivery company
create table shopee_improvisation.Customer_Product_Verification
as(select order_information.Customer_ID,Product_ID
    from shopee_improvisation.order_information
    join shopee_improvisation.customer
    where order_information.Customer_ID=customer.Customer_ID);
alter table shopee_improvisation.customer_product_verification
    add verfication_id nvarchar(255) not null
    after Product_ID;

#Delivery company have to find the customer location
select order_information.Customer_ID,customer.City,Customer_Name,customer.Customer_Name,Postal_Code,customer.Telephone_No,Address,Email
from shopee_improvisation.delivery_and_supply_system
inner join shopee_improvisation.customer_product_verification
    on delivery_and_supply_system.verfication_id=customer_product_verification.verfication_id
inner join shopee_improvisation.customer
    on customer_product_verification.Customer_ID=customer.Customer_ID
inner join shopee_improvisation.order_information
    on customer.Customer_ID=order_information.Customer_ID
inner join shopee_improvisation.store
    on order_information.Product_ID=store.Product_ID
    where order_information.Product_ID='P111';

#then customer will notify about the product and delivery status
#customer will use his shipment id to locate his product

select*
from shopee_improvisation.delivery_and_supply_system
where delivery_and_supply_system.Shipment_ID='SP101';

#after receiving the product,if the customer find the product defective, will contact with a staff
#add his product on defective product database
#staff will notify
#staff will check

select*
from shopee_improvisation.defective_product
where Product_ID='P111';

#Then either he will again check the store and deliver the product or will refund into customer's account
#suppose the staff has to refund

select*
from shopee_improvisation.payment
where payment.Product_ID="P111";

#now the staff wants to see the amount of order from his store

select count(Order_Quantity)
from shopee_improvisation.product_information;

#display the number of the order based on customers username

select customer.Customer_ID,Customer_Name,Order_Quantity
from shopee_improvisation.product_information
inner join shopee_improvisation.customer_product_verification
    on product_information.Product_ID=customer_product_verification.Product_ID
inner join shopee_improvisation.customer
    on customer_product_verification.Customer_ID=customer.Customer_ID;


# suppose the tax is included. We want to know the tax price. For accessories there is 10% tax,
#for electronics it is 15%,for grocceries it is 5% and for skin product it is 7%

alter table shopee_improvisation.product_information
    add Tax_Price int
    after Product_Price;
alter table shopee_improvisation.product_information
    drop column Tax_Price;



select Product_Price,Product_ID,Product_Name,
(case
    when Product_Category="Accessories" then Product_Price*.10
    when Product_Category='Electronics' then Product_Price*.15
    when Product_Category='Grocceries' then Product_Price*.05
    else Product_Price*0.07
end) as Tax_Price
from shopee_improvisation.product_information;

#see the category

select Product_Name,Product_Category
from shopee_improvisation.product_category
join shopee_improvisation.product_information
where product_category.Product_ID=product_information.Product_ID;

select Delivery_Status,Delivery_Company_Name,Shipment_Type
from shopee_improvisation.delivery_and_supply_system
join shopee_improvisation.payment
where delivery_and_supply_system.Shipment_ID=payment.Shipment_ID and Product_ID="P111";




#practice for exam

select Customer_Name,Customer_Address,Product_Name,Product_Price,Payment_Type
from customer
inner join payment
    on customer.Customer_ID=payment.Customer_ID
inner join product
    on payment.Product_ID=product.Product_ID
where Product_Category="Stationary"and "Furniture";


 select sum(Payment_totPrice),Payment_date
 from Paymnet
 where extract(Month from Payment_Date)=01;