Question

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 total number of table products
2. Find the average price of all tables
3. Find the total number of products from each vendor
4. Find the average prices by vendors (please include vendor names in the query result)
5. Find the average prices by states

Answer 1

Answer:

   /*find total nnumber of table products*/

   Select count(*) as TOTAL_TABLE_PROD from PRODUCT where PROD_NAME like '%table%';

   /*Find the average price of all tables */

   select avg(PROD_PRICE) as TABLE_AVG_PRICE from PRODUCT where PROD_NAME like '%table%' ;

   /*Find the total number of products from each vendor */

   select PROD_VENDOR, count(*) as TOTAL_PRODUCT from PRODUCT group by PROD_VENDOR;

   /*Find the average prices by vendors */

   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 P.PROD_VENDOR;

   /*Find the average prices by states*/

   select V.VEND_ST, P.PROD_VENDOR , avg(P.PROD_PRICE) as AVG_PRICE from
       PRODUCT P join Vendor V on P.PROD_VENDOR = V.VEND_ID group by V.VEND_ST;

Complete SQL QUERIES Screenshot: