write the SQL queries, and screenshot the results of the following 

 

1. List customers (in alphabetical order) and their invoice numbers.

 

 

2. How many invoices does each customer have? List the output in increasing order of invoices (ignore order of cus_l

 

 

3. List the distinct products bought by each customer (in alphabetical order)

 

 

4. Generate a listing of all purchases made by the customers:

 

 

5. Summarize the value of products currently in inventory:

 

 

6. Write SQL query to duplicate the results outlined below:

 

7. Compute the average purchase amount per product made by each customer:

 

 

8. List customers who have purchased hammers or saws *hint: make sure to not have repeating CUS_CODEs

 

9. List the difference between product price and average product price. *Notice that AVGPRICE has the same value for all rows. Subqueries are often used when you need to return a single value (aggregation).

 

 

10. Find the product that has sold the most quantity

 

 

the following screenshots include the tables and their content from SALESCO

and this is the schema:

  

 
 
 
 
 

Transcribed Image Text:

Administration SCHEMAS QFilter objects ITP249 MySQL Server MySQL Model EER Diagram largeco rimbhard Tables customer Schemas Query 1 customer customer invoice line invoice customer invoice line Context Help Snippets Limit to 1000 rows 1 = SELECT * FROM rimbhard.customer; > invoice > line > product > vendor 100% 1:1 Views Result Grid Filter Rows: Search Edit: Export/Import: Stored Procedures Functions CUS_CODE CUS_LNAME CUS_FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS_BALANCE Result Grid salesco 10010 Ramas Alfred A 615 844-2573 0.00 10011 Dunne Leona K 713 894-1238 0.00 10012 Smith Kathy W 615 894-2285 345.85 10013 Olowski Paul F 615 894-2180 536.75 Form Editor 10014 Orlando Myron 615 222-1672 0.00 10015 O'Brian Amy B 713 442-3381 0.00 10016 Brown James G 615 297-1228 221.19 10017 Williams George 615 290-2556 768.92 Field 10018 Farriss Anne G 713 10019 Smith Olette K 615 HULL HULL HULL HULL HULL 382-7185 297-3809 HULL 216.55 Types 0.00 HULL Object Info Session Table: customer Columns: CUS_CODE CUS_LNAME Int PK customer 1 varchar(15) CUS_FNAME varchar(15) Action Output CUS_INITIAL char(1) CUS AREACODE char(3) Time Action Response CUS_PHONE char(8) CUS BALANCE decimal(9.2) 66 04:44:02 Administration SCHEMAS QFilter objects ITP249 MySQL Server MySQL Model EER Diagram largeco rimbhard Tables customer Schemas Query 1 customer customer invoice line invoice customer invoice line Context Help Snippets Limit to 1000 rows 1 = SELECT * FROM rimbhard.customer; > invoice > line > product > vendor 100% 1:1 Views Result Grid Filter Rows: Search Edit: Export/Import: Stored Procedures Functions CUS_CODE CUS_LNAME CUS_FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS_BALANCE Result Grid salesco 10010 Ramas Alfred A 615 844-2573 0.00 10011 Dunne Leona K 713 894-1238 0.00 10012 Smith Kathy W 615 894-2285 345.85 10013 Olowski Paul F 615 894-2180 536.75 Form Editor 10014 Orlando Myron 615 222-1672 0.00 10015 O'Brian Amy B 713 442-3381 0.00 10016 Brown James G 615 297-1228 221.19 10017 Williams George 615 290-2556 768.92 Field 10018 Farriss Anne G 713 10019 Smith Olette K 615 HULL HULL HULL HULL HULL 382-7185 297-3809 HULL 216.55 Types 0.00 HULL Object Info Session Table: customer Columns: CUS_CODE CUS_LNAME Int PK customer 1 varchar(15) CUS_FNAME varchar(15) Action Output CUS_INITIAL char(1) CUS AREACODE char(3) Time Action Response CUS_PHONE char(8) CUS BALANCE decimal(9.2) 66 04:44:02