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 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

SQL Query 1:

select count(Prod_name) as 'number of table product' from product
where Prod_name like '%Table%';

query result :

*************************

SQL Query 2:

select avg(prod_price) from product
where Prod_name like '%Table%';

query result :

*************************

SQL Query 3:

select vend_name , count(prod_id) as 'Number of products'
from vendor,product
where
vendor.vend_id=product.PROD_VENDOR
group by vend_name;

query result :

*************************

SQL Query 4:

select vend_name , avg(prod_price) as 'Average price by vendor'
from vendor,product
where
vendor.vend_id=product.PROD_VENDOR
group by vend_name;

query result :

*************************

SQL query 5:

select VEND_ST , avg(prod_price) as 'Average price by state'
from vendor,product
where
vendor.vend_id=product.PROD_VENDOR
group by VEND_ST;

Query result :

****************************