Write SQL statements to answer the following questions using Assignment 2s schema (Customer-Invoice-Line-Product-Vendor).

(Please see attached images from assignment 2)

1 - Find the count of unique vendors that supplied products that are priced lower than 185?

2 - 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.

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

4 - 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 - 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 - Find vendor code, vendor contact and the number of products supplied by each vendor.

7 - Find product description, price, and vendor code for the cheapest (lowest price) product.

8 - Create a view Alfred that contains customer code, customer last name, and number of invoices made by each customer who has a first name of Alfred.

9- 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.

10 - 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.

MySQL Workbench Tima lica311sp190129x File Edit View Query Database r Tools Scripting Help Assignment 2 Tim Fadeyev SCHEMAS Limit to 1000 rowa - objects 1.Table Name :Customer* Foreign Keys Triggers 2create table Custoner 3 cus code integer primary key 4 Cus_lname varchar (20) not null, line product vendor cus_fname varchar (20) not null, cus_initial chars cus areacode integer, Columns vend code vend name vend contact vend areacode vend-phone 8 cus phone integer V1 /*Inserting records into customer table*/ Information Name: Tima Host: 127.0.0.1 Port: 3306 Server: (Ubuntu) Version: 5.6.33-0ubuntu0.14.04.1 Connector: C+8.0.15 Login User: ics311sp190129 Current User: ics311sp190129@localhost SSL: Disabled 12nsert into custoner values (1001 RamsAlfred, "A', 615, 8442573)i 13insert into customer values (1001 "Dunne Leona, K' 713, 8941238)j 14insert into customer values (19012, Smith'KathyW' 615, 8942285)3 15 nsert into customer values (10013, .olowski', .Paul.,.F., 615, 2221672); 16nsert into customer values (10814, "Orlando, Myron', NULL, 615, 2971228)5 17 18 19elect from customer 28 21 22create table Invoice( /selecting records*/ /2. Table Name Invoice Action Ourput Object Info Session Query interrupted 5:22 PM O Type here to search ENG 2/23/2019 MySQL Workbench Tima lica311sp190129x File Edit View Query Database r Tools Scripting Help Assignment 2 Tim Fadeyev SCHEMAS Limit to 1000 rowa - objects 21 /2. Table Name Invoice Foreign Keys Triggers 22 create table Invoice 23 inv_number integer primary key, 24 25 26 foreign key (cus_code) references customer(cus_code) 27 28 29 3insert into invoice values (1001, 10011, 2008-08-03 31 insert into invoice values (1002, 10e14, .2008-08-04); 32insert into invoice values (1803, 10012, 2008-03-28') 33insert into invoice values (1084, 18014, 2888-89-23 line product vendor cus_code integer inv_date date, Columns vend code vend name vend contact vend areacode /Inserting data into invoice table/ vend phone Information Name: Tima Host: 127.0.0.1 Port: 3306 Server: (Ubuntu) Version: 5.6.33-0ubuntu0.14.04.1 Connector: C+8.0.15 Login User: ics311sp190129 Current User: ics311sp190129@localhost SSL: Disabled /selecting record/ 35 36selectfrom invoice; 37 38 9create table vendor( 40 41 vend_name varchar (30) 42 vend_contact varchar(30), 3.Table Name vendor end_code integer primary key Action Ourput Object Info Session Query interrupted 5:22 PM O Type here to search 4, ENG 2/23/2019 MySQL Workbench Tima lica311sp190129x File Edit View Query Database r Tools Scripting Help Assignment 2 Tim Fadeyev SCHEMAS Limit to 1000 rowa - objects 3.Table Name :vendor 38 Foreign Keys Triggers 39 create table vendor 48 vend code integer primary key, 41 vend name varchar (3), 42 43 44vend_phone integer 45 46 line product vendor Columns vend_contact varchar(30) vend areacode integer vend code vend name vend contact vend areacode vend phone /insert data into vendor table/ 48 insert into vendor values(232, .Bryson . , . Smith', 615, 2233234); 49insert into vendor values (235, SuperLoo' Anderson', 615, 2158995)j 50 insert into vendor values(236, "Jason', 'Schmidt', 651, 2468850); 51 52 53select from vendor; 54 Information Name: Tima Host: 127.0.0.1 Port: 3306 Server: (Ubuntu) Version: 5.6.33-0ubuntu0.14.04.1 Connector: C+8.0.15 Login User: ics311sp190129 Current User: ics311sp190129@localhost SSL: Disabled /selecting records from vendor table/ *4. Table Name : Product 6create table product( 57 prod_code integer primary key, 58 prod_desc varchar (58), 59 prod_price integer Output Action Ourput Object Info Session Query interrupted 5:22 PM O Type here to search ENG 2/23/2019 MySQL Workbench Tima lica311sp190129x File Edit View Query Database r Tools Scripting Help Assignment 2 Tim Fadeyev SCHEMAS Limit to 1000 rowa - objects *4. Table Name Product Foreign Keys Triggers 56 create table product( 57 prod code integer primary key, 58 prod_desc varchar(58) 59 60 prod _quant integer 61 62 foreign key (vend code) references Vendor(vend code) 63 64 65 Inserting data into product 66 . insert into product values (12321, 'harmer', 189 ,20, 232); 67insert into product values (65781, chain', 12, 45, 235)j 68insert into product values (34256, tape 35, 6, 236)3 69 nsert into product values (12333, .hanger., 200 ,10, 232); 70 71 72select from productj 73 74 75create table Line ( 76 line product vendor prod_price integer, Columns vend code vend name end code integer vend contact vend areacode vend-phone Information Name: Tima Host: 127.0.0.1 Port: 3306 Server: (Ubuntu) Version: 5.6.33-0ubuntu0.14.04.1 Connector: C+8.0.15 Login User: ics311sp190129 Current User: ics311sp190129@localhost SSL: Disabled /selecting records from product table"/ *5.Table Name Line inv number integer Output Action Ourput Object Info Session Query interrupted O Type here to search 5:22 PM 2/23/2019 MySQL Workbench Tima lica311sp190129x File Edit View Query Database r Tools Scripting Help Assignment 2 Tim Fadeyev" x SCHEMAS Limit to 1000 rowa - objects *5.Table Name Line 74 Foreign Keys Triggers 75 create table Line ( inv number integer, 76 77 prod_code integer 78 79 primary key (inv_numbersprod_code), 88 foreign key (inv_number) references Invoice(inv number), 81 82 83 84 85 insert into Line values (1001, 12321, 1); 86insert into Line values (1001, 65781, 3); 87 . insert into Line values (1002, 34256, 6); 88 insert into Line values (1003, 12321, 5); 89 insert into Line values (1002, 1232, 6); 90 91 92select from Linej 93 94 . insert into CUSTOMER values (10012,' Juan, . Rodriguez', 'J', 612, 7788776); 95 line product vendor Columns line_units integer vend code vend name vend contact foreign key (prod code) references Product (prod_code) vend areacode vend phone /*Inserting records into Line table Information Name: Tima Host: 127.0.0.1 Port: 3306 Server: (Ubuntu) Version: 5.6.33-0ubuntu0.14.04.1 Connector: C+8.0.15 Login User: ics311sp190129 Current User: ics311sp190129@localhost SSL: Disabled /selecting records from Line Output Action Ourput Object Info Session Query interrupted 5:23 PM O Type here to search 4, ENG 2/23/2019 MySQL Workbench Tima lica311sp190129x File Edit View Query Database r Tools Scripting Help Assignment 2 Tim Fadeyev" x SCHEMAS Limit to 1000 rowa - objects 94insert into CUSTOMER values (10012, Juan'Rodriguez', ', 612, 7788776) Foreign Keys Triggers 95 96 insert into INVOICE values (1005, 10017, . 2008-11-30'); 97 98insert into PRODUCT values (12322, "hanmer, 189, 28, 231) line product vendor Columns vend code vend-name lee insert into VENDOR values (231,'Adan', 'Eric', 615, 2158995); 101 182 insert into PRODUCT values (12322, "coil' 189, 20, 231) 103 184 /*Query 1 185select cus_code, cus_1name, cus fname fron customerj 186 107 188select inv number , inv date from invoice where cus code-10014; 189 118 vend contact vend areacode vend phone Information Query 2 Name: Tima Host: 127.0.0.1 Port: 3306 Server: (Ubuntu) Version: 5.6.33-0ubuntu0.14.04.1 Connector: C+8.0.15 Login User: ics311sp190129 Current User: ics311sp190129@localhost SSL: Disabled /Query 3 11 List the product code and product quantity for products in invoice number 1801./ 112 113select product.prod code,prod_quant from product,line,invoice 114 115 where product.prod code-line.prod code and line.inv_number-invoice.inv_number and invoice.inv_number-1e01j Output Action Ourput Object Info Session Query interrupted O Type here to search 5:24 PM 2/23/2019 MySQL Workbench Tima lica311sp190129x File Edit View Query Database r Tools Scripting Help Assignment 2 Tim Fadeyev" x SCHEMAS Limit to 1000 rowa - objects 182 insert into PRODUCT values (12322, "coil' 189, 20, 231) Foreign Keys Triggers A 103 184 /*Query 1*/ 105select cus_code, cus_lname, cusfname fron customerj 186 107 188select inv_number, inv date from invoice where cus code-100145 109 118 line product vendor Columns Query 2* vend code vend-name vend contact vend areacode vend-phone /Query 3 List the product code and product quantity for products in invoice number 1001."7 112 113select product.prod code,prod_quant from product,line,invoice 114 115 116 Information where product.prod code-line.prod_code and line.inv_number-invoice.inv_number and invoice.inv_number-18ee1j Name: Tima Host: 127.0.0.1 Port: 3306 Server: (Ubuntu) Version: 5.6.33-0ubuntu0.14.04.1 Connector: C+8.0.15 Login User: ics311sp190129 Current User: ics311sp190129@localhost SSL: Disabled 117 Query 118 119 and vendor phone for all products with quantity less than or equal to 60. 120 select prod-desc , vend-name ,vend-phone from product,vendor 121 where product.vend code-vendor.vend-code and prod-quant60; 122 123 Produce a list of product description, vendor name Output Action Ourput Object Info Session Query interrupted O Type here to search 5:24 PM 2/23/2019