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 a 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

SQL QUERIES:  

GROUP BY clause is used to group the results of aggregate functions according to the specified columnName.

1.

SELECT COUNT(vend_code)

FROM Product

WHERE prod_price< 182;

2.

SELECT v.vend_code, v.vend_name, p.prod_desc, p.prod_quant

FROM Vendor v, Product p

WHERE v.vend_code= p.vend_code

AND p.prod_quant= (SELECT MIN(p.prod_quant) FROM Product p WHERE p.vend_code= v.vend_code);

3.

SELECT inv_number, COUNT(prod_code)

FROM Line

GROUP BY inv_no;

4.

SELECT cus_code, COUNT(inv_number)

FROM Invoice

GROUP BY cus_code;

5.

SELECT SUM(prod_price * prod_quant)

FROM Product;

6.

SELECT v.vend_code, v.vend_contact, COUNT(p.prod_code)

FROM Vendor v, Product p

WHERE v.vend_code= p.vend_code

GROUP BY p.vend_code;

7.

SELECT prod_desc, prod_price, vend_code

FROM Product

ORDER BY prod_price

LIMIT 1;

8.

SELECT l.inv_number, SUM(l.line_units * p.prod_price)

FROM Line l, Product p

WHERE l.prod_code= p.prod_code

GROUP BY l.inv_number;

9.

SELECT i.cus_code, COUNT(li.prod_code)  

FROM Line li, Invoice i

WHERE li.inv_number= i.inv_number

GROUP BY i.cus_code;