Question

Question 1: Group by and Aggregates:

Write SQL statements to answer the following questions using Assignment 4’s schema (Customer-Invoice-Line-Product-Vendor). Make sure that your SQL script runs without any errors. Submit your answers in a .SQL file.

1 (2 Points) - Find the count of distinctvendors thatsupplied products that are priced lowerthan 185?

2 (2 Points) - For each vendor, find their product that has the lowest product quantity. Your output should include vendor code, vendor name, product description and product quantity for each vendor.

Hint: Use subquery to get minimum quantity

3 (2 Points) - Find how many products are there in each invoice. The output should include invoice number and number of products in the invoice.

4 (2 Points) - Find how many invoices are made by each customer. The output should be a list of cus_code and for each cus_code, the number of invoices made by this customer.

5 (2 Points) - Find the total value for all products in the inventory. The total value in the inventory is the sum of product quantity * product price for all products listed in the product table.

6 (2 Points) - Find vendor code, vendor contact, and the number of products supplied by each vendor.

7 (2 Points) - Find product description, price, and vendor code for thecheapest (lowest price) product.

8 (3 Points) - For each invoice, find the total price. The total invoice price is the sum of product price* line units for each product purchased in the invoice.

9 (3 Points) - Find how many products are bought by each customer. The output should be a list of cus_code and for each cus_code, the number of products purchased by this customer. A more complex query (if you want to try it), would be to list the name of the customer, along with the cus_code.

this is schema

CREATE DATABASE IF NOT EXISTS donaldtrump;
USE donaldtrump;
CREATE TABLE IF NOT EXISTS Customer (
cus_code INT PRIMARY KEY,
cus_lname VARCHAR (20),
cus_fname VARCHAR (20),
cus_initial CHAR (1),
cus_areacode INT,
cus_phone INT);
CREATE TABLE IF NOT EXISTS Invoice (
   inv_number INT PRIMARY KEY,
cus_code INT,
inv_date DATE,
FOREIGN KEY (cus_code) REFERENCES Customer (cus_code) );
CREATE TABLE IF NOT EXISTS Vendor (
   vend_code INT PRIMARY KEY,
vend_name VARCHAR (30),
vend_contact VARCHAR (30),
vend_areacode INT,
vend_phone INT);
CREATE TABLE IF NOT EXISTS Product (
   prod_code INT PRIMARY KEY,
prod_desc VARCHAR (50),
prod_price INT,
prod_quant INT,
vend_code INT,
FOREIGN KEY (vend_code) REFERENCES Vendor (vend_code) );
CREATE TABLE IF NOT EXISTS Line (
   inv_number INT,
prod_code INT,
line_units INT,
PRIMARY KEY (inv_number, prod_code),
FOREIGN KEY (inv_number) REFERENCES Invoice (inv_number),
FOREIGN KEY (prod_code) REFERENCES Product (prod_code) );

INSERT INTO CUSTOMER VALUES (10010,"Johnson","Alfred","A",615,8442573);
INSERT INTO CUSTOMER VALUES (10011,"Dunne","Leona","K",713,8941238);
INSERT INTO CUSTOMER VALUES (10012,"Smith","Walter","W",615,8942285);
INSERT INTO CUSTOMER VALUES (10013,"Roberts","Paul","F",615,2221672);
INSERT INTO CUSTOMER VALUES (10014,"Orlando","Myla",NULL,615,2971228);

INSERT INTO Invoice VALUES (1001,10011,"2008-08-03");
INSERT INTO Invoice VALUES (1002,10014,"2008-08-04");
INSERT INTO Invoice VALUES (1003,10012,"2008-03-20");
INSERT INTO Invoice VALUES (1004,10014,"2008-09-23");

INSERT INTO Vendor VALUES (232, "Bryson", "Smith", 615, 2233234);
INSERT INTO Vendor VALUES (235, "Walls", "Anderson", 615, 2158995);
INSERT INTO Vendor VALUES (236, "Jason", "Schmidt", 651, 2468850);

INSERT INTO Product VALUES (12321, "hammer", 189 ,20, 232);
INSERT INTO Product VALUES (65781, "chain", 12, 45, 235);
INSERT INTO Product VALUES (34256, "tape", 35, 60, 236);
INSERT INTO Product VALUES (12333, "hanger", 200 ,10, 232);

INSERT INTO Line VALUES (1001,12321,1);
INSERT INTO Line VALUES (1001,65781,3);
INSERT INTO Line VALUES (1002,34256,6);
INSERT INTO Line VALUES (1003,12321,5);
INSERT INTO Line VALUES (1002, 12321, 6);

Answer 1

Question 1:

SQL query :

select count(distinct(vend_code)) numberOfVendors from vendor where vend_code in
(select vend_code from product where prod_price<185);

Query result :

*****************************************

Question 2:

SQL query :
select vendor.vend_code,vend_name,prod_desc,prod_quant from vendor,product
where
vendor.vend_code=product.vend_code
and prod_quant in (select min(prod_quant) from product group by vend_code);

Query result :

*****************************************

Question 3:

SQL query :

select invoice.inv_number as 'Invoice number',
count(prod_code) as 'Number of Products' from invoice,line
where invoice.inv_number=line.inv_number
group by invoice.inv_number;

Query result :

*****************************************

Question 4:

SQL query :

select customer.cus_code,count(inv_number) as 'Number of invoices'
from customer,invoice
where
customer.cus_code=invoice.cus_code
group by customer.cus_code;

Query result :

*****************************************

Question 5:

SQL query :

select sum(prod_quant*prod_price) as 'total value for all products in the inventory'
from product;

Query result :

*****************************************

Question 6:

SQL query :

select vendor.vend_code,vend_contact,count(prod_code) as 'Number of products'
from vendor,product
where
vendor.vend_code=product.vend_code
group by vendor.vend_code,vend_contact;

Query result :

*****************************************

Question 7:

SQL query :

select prod_desc,prod_price,vend_code from product where prod_price
=(select min(prod_price) from product);

Query result :

*****************************************

Question 8:

SQL query :

select invoice.inv_number,sum(line_units*prod_price) as ' total price'
from invoice,line,product
where
invoice.inv_number=line.inv_number and
line.prod_code=product.prod_code
group by invoice.inv_number;

Query result :

*****************************************

Question 9:

SQL query :

select customer.cus_code,concat(cus_lname,' ',cus_fname) as 'Customer Name',
count(prod_code) as 'Number of Products'
from customer,invoice,line
where
customer.cus_code=invoice.cus_code and
invoice.inv_number=line.inv_number
group by customer.cus_code,cus_lname,cus_fname;

Query result :

*****************************************