Given the following Relations:
Product(pid,name,description,price,quantity)
Customer(cid,name,address,phone,email)
Invoice(id,cid,pid,quantity,state,datetime) Where state can be: paid, delivered, pending, or canceled.
Product, Customer and Invoice schema with PRIMARY and FOREIGN Key.
-----------------------------Product----------------------------
CREATE TABLE Product (
pid INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
description VARCHAR(150) NOT NULL,
price NUMBER(10,2) NOT NULL,
quantity INT NOT NULL
);
-----------------------------Customer----------------------------
CREATE TABLE Customer (
cid INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
address VARCHAR(100) NOT NULL,
phone NUMBER(10) NOT NULL,
email VARCHAR(80) NOT NULL
);
----------------------------Invoice------------------------------
CREATE TABLE Invoice (
id INT PRIMARY KEY,
cid int FOREIGN KEY REFERENCES Customer(cid),
pid int FOREIGN KEY REFERENCES Product(pid),
quantity INT NOT NULL,
state VARCHAR(20) NOT NULL,
datetime DATE NOT NULL
);
Provide SQL instructions for each of the following questions.
1.Name(s) of customer(s) who have bought the most products.
2.List name(s) of customer(s) in decreasing order of total amount spent.
3.List the total sales for each month.
