USA: +1 725-224-8795 
Sign in
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.
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.
Expert Answer
create table Product(pid varchar(10) primary key, name varchar(15), description varchar(15), price number,quantity number);
You have made changes to the database.
insert into Product values(‘Product X’, ‘Bluetooth’, ‘Hearing ability’, 1500, 5);
You have made changes to the database. Rows affected: 1
insert into Product values(‘Product Y’, ‘Mobile Charger’, ‘Charging device’, 1000, 10);
You have made changes to the database. Rows affected: 1
insert into Product values(‘Product Z’, ‘Fountain Pen’, ‘Writing device’, 500, 2);
You have made changes to the database. Rows affected: 1
insert into Product values(‘Product A’, ‘Laser Printer’, ‘Printer’, 4500, 5);
You have made changes to the database. Rows affected: 1
create table Customer(cid varchar(10) primary key, name varchar(15), address varchar(20), phone number,email varchar(20));
You have made changes to the database.
insert into Customer values(‘Cust001’, ‘Sanjay’, ’56, Delhi’, 965874123, ‘sanju@domain.com’);
You have made changes to the database. Rows affected: 1
insert into Customer values(‘Cust002’, ‘Prakash’, ’34, Berlin’, 925674123, ‘praks@domain.com’);
You have made changes to the database. Rows affected: 1
insert into Customer values(‘Cust003’, ‘Rita’, ‘101A, Beijing’, 925677896, ‘rita@domain.com’);
You have made changes to the database. Rows affected: 1
insert into Customer values(‘Cust004’, ‘Mithra’, ‘1G, Korea’, 895677896, ‘mithu@domain.com’);
You have made changes to the database. Rows affected: 1
create table Invoice(id number primary key, cid varchar(10), pid varchar(10), quantity number, state varchar(10), datetime date, foreign key (cid) references Customer(cid), foreign key (pid) references Product(pid));
You have made changes to the database.
insert into Invoice values(123456, ‘Cust001’, ‘Product X’, 5, ‘paid’, ’12/08/2017′);
You have made changes to the database. Rows affected: 1
insert into Invoice values(234567, ‘Cust002’, ‘Product Y’, 10, ‘delivered’, ’13/08/2017′);
You have made changes to the database. Rows affected: 1
insert into Invoice values(345678, ‘Cust003’, ‘Product Z’, 1, ‘pending’, ’10/08/2017′);
You have made changes to the database. Rows affected: 1
insert into Invoice values(456789, ‘Cust004’, ‘Product A’, 2, ‘Canceled’, ’10/08/2017′);
You have made changes to the database. Rows affected: 1
insert into Invoice values(567890, ‘Cust003’, ‘Product A’, 2, ‘Paid’, ’10/08/2017′);
You have made changes to the database. Rows affected: 1
