Given the following Relations: Product(pid,name,description,price,quantity) Cust
ID: 3868049 • Letter: G
Question
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.
5.Name(s) of customer(s) who have bought the most products.
6.List name(s) of customer(s) in decreasing order of total amount spent.
7.List the total sales for each month.
Explanation / Answer
Solution:
5)
SELECT name, MAX(quantity) FROM Customer INNER JOIN Invoice ON Customer.cid= Invoice.cid INNER JOIN Product ON Invoice.pid= Product.pid
The above query will produce the name of the customers who have purchased maximum quantity from the lot.
6)
SELECT name, quantity*price AS Total FROM Customer INNER JOIN Invoice ON Customer.cid= Invoice.cid INNER JOIN Product ON Invoice.pid= Product.pid ORDER BY Total DESC
Above query will show the result in decreasing order of Total order place by a customer
7)
SELECT name, sum(quantity*price) AS TotalsSale, MONTH(DATE) as SalesMonth FROM Customer INNER JOIN Invoice ON Customer.cid= Invoice.cid INNER JOIN Product ON Invoice.pid= Product.pid GROUP BY MONTH(DATE) ORDER BY MONTH(DATE)
The above query will display the Total sales of each month.
I hope this helps, if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.