Question

We continue from the two tables in lab 3 part 2, and practice with functions and the GROUP BY statement. The data in each table should be as below

Table Product:

PROD_ID	PROD_NAME	PROD_PRICE	PROD_VENDOR
1101	Table	100	2
1102	Chair	80	3
1103	Armchair	90	2
1104	Nightstand	110	1
1105	Bed	200	3
1106	Dresser	150	3
1107	Daybed	190	2
1108	Ash Table	120	2
1109	Cherry Table	130	2
1110	Table - High	100	2
1111	Office Chair	110	3

Table Vendor:

VEND_ID	VEND_NAME	VEND_ST
1	Green Way Inc	GA
2	Forrest LLC	NC
3	AmeriMart	NC

Please write the SQL script and provide screenshots of results for these below queries. Please include the SQL in text format, and all screenshots in one single document.

1. Find the average prices by vendors (please include vendor names in the query result)
2. Find the average prices by states

Help:

I do it the following way, but it gives me an error " NO ROWS SELECTED." Also, I had to  group by V.VEND_NAME, P.PROD_VENDOR, otherwise I'll  get an error message. Please help me figure this out. Same for # 5. Thanks.

select P.PROD_VENDOR, V.VEND_NAME, avg(P.PROD_PRICE) as AVG_PRICE from

PRODUCT P join Vendor V on P.PROD_VENDOR = V.VEND_ID group by V.VEND_NAME, P.PROD_VENDOR;

Answer 1

Hi,

There should be no such need to group the tables by both the fields, vendor name as well as vendor Id from product table. I would recommend you to check the underlying schema of your tables.

I didn't encounter any such error, below are my queries-

mysql> Select vendor.VEND_NAME, Avg(PROD_PRICE) from vendor, product
-> where vendor.VEND_ID = product.PROD_VENDOR
-> group by vendor.VEND_ID;
+---------------+-----------------+
| VEND_NAME | Avg(PROD_PRICE) |
+---------------+-----------------+
| Forrest LLC | 121.6667 |
| AmeriMart | 135.0000 |
| Green Way Inc | 110.0000 |
+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> Select vendor.VEND_ST, Avg(PROD_PRICE) from vendor, product
-> where vendor.VEND_ID = product.PROD_VENDOR
-> group by VEND_ST;
+---------+-----------------+
| VEND_ST | Avg(PROD_PRICE) |
+---------+-----------------+
| NC | 127.0000 |
| GA | 110.0000 |
+---------+-----------------+
2 rows in set (0.00 sec)

Therer might be slight variations in our queries due to the cases of table names and field names (upper case/lower case) . Kindly match up the logic of your query with the ones above.

If that still doesn't work out, kindly share your schema and the error screenshot. Will be happy to help you.. thanks!