We continue from the two tables in lab3 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 providescreenshots of results for thesebelow queries. Please include the SQL in textformat, and all screenshots in one singledocument.
- Find the average prices by vendors (please include vendor namesin the query result)
- Find the average prices by states
Help:
I do it the following way, but it gives me an error \" NO ROWSSELECTED.\" 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 onP.PROD_VENDOR = V.VEND_ID group by V.VEND_NAME, P.PROD_VENDOR;
