Given the following Relations: Product(pid,name,description,price,quantity) Cust
ID: 3867804 • 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.
Provide SQL instructions for each of the following questions.
DO NOT USE NESTED SUBQUERIES UNLESS IT IS STRICTLY NECESSARY.
1.Create the schema. Include keys and foreign keys.
2.List invoice id and date of all paid invoices.
3.For invoice id=234567 list the customer's name as well as the product description for each product in the invoice.
4.Assuming a 6% sales tax, produce a list of all delivered invoices including the total amount (with and without tax).
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
Answer: => 1
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
);
-------------------------------------------------------------------
Answer: 2
Select id, CONVERT(varchar, datetime,101) as date from Invoice where state = 'Paid';
(or)
Select id, CAST(datetime AS DATE) from Invoice where state = 'Paid';
Note: 101 is US Datetime Format
Answer: 3
a) select C.name,P.description from Customer C , Product P , Invoice I where C.cid = I.cid AND P.pid = I.pid AND I.id in (2,3,4,5,6,7);
b) select C.name,P.description from Customer C , Product P , Invoice I where C.cid = I.cid AND P.pid = I.pid AND I.id = 234567;
Note: I am not sure that 234567 is a single invoice id or 2,3,4,5,6,7 is separate. If 234567 is separate invoice id use (b) query otherwise use (a) query.
Answer: 4
Select I.id, (I.quantity * P.price) as "Total Without Tax", (I.quantity * P.price * 1.06) as "Total With Tax"
from Invoice I, Product P where I.pid = P.pid AND I.state = 'delivered';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.