Write an SQL query to list all product line product line names and, for each product line the product line ID, product line name the number of products and the average product price. Make sure to include all product lines separately

Assignment 4
Pine Valley Furniture Corporation

Answer the following questions are based on Problems and Exercises at the end of Chapter 6. (These questions refer to PVFC database that you used in Assignment 3):

1. Write an SQL query to list all product line product line names and, for each product line the product line ID, product line name the number of products and the average product price. Make sure to include all product lines separately.
2. Modify the query in Problem 1 to include only those product lines the average price of which is higher than $100.
3. List the employee names and number of employees he/she supervises (label this value No_of_Reportees) for each supervisor who supervises more than two employees.
4. Write an SQL query to list each customer ( customer ID and name) who bought at least one product that belongs to product line Basic in March 2018. List each customer only once.
5. Modify the query in Problem 4 so that you include the number of products in product line Basic that the customer ordered in March 2018.
6. Modify the query in Problem 5 so that the list includes the number of products in each customer bought in each product line in March 2018.
7. Display the salesperson name, product finish, and total quantity sold (label as Units_Sold) for each finish by each salesperson.
8. Display the customer ID, name, and order ID for all customer orders. For those customers who do not have any orders, include them in the display once.
9. Show the customer ID and name for all the customers who have ordered both products with IDs 3 and 4 on the same order.
10. List the IDs and names of all products that cost less than the average product price in their respective product lines.

======
If you have “dirtied” tables in PVFC database by making changes to table definitions or by adding, deleting or updating the rows, use the script below to deleted ALL tables from the schema.
The script is available as a .SQL file PVFC_Drop_All_Tables.sql under the tab Workshops & Databases

begin
for i in (select ‘drop table ‘||table_name||’
cascade constraints’ tbl from user_tables)
loop
execute immediate i.tbl;
end loop;
end;

With a clean schema run the script(s) to create tables in PVFC and populate them.