We continue from the two tables in lab 3 part 2, and practice with functions and...

70.2K

Verified Solution

Question

Programming

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;

Answer & Explanation Solved by verified expert

3.9 Ratings (528 Votes)

HiThere should be no such need to group the tables by both thefields vendor name as well as vendor Id from product table Iwould recommend you to check the underlying schema of See Answer

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

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

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