Description

Database Foundations

ASSIGNMENT 4

Vendor_T

Here are the definitions of attributes and their data types.
p_code : product code of the product (text) p_onhand : quantity available in inventory (number) p_min : minimum stock quantity (number) p_price : unit price of the product in $ (number) p_discount : product discount rate (number) v_code : vendor code of the vendor (text) v_contact : contact name of the vendor (text) v_areacode : area code of the vendor (text) v_phone : phone number of the vendor (text) v_state : state of the vendor (text)

Please write multiple-table SQL Select queries (i.e., Join and/or Subquery) to answer the following questions.

1. Which products are priced more than the average price? Display product code and product price.

2. List the products for which either the quantity on hand is minimum or the discount rate is maximum. Display all product-related attributes.

3. What are the products supplied by the vendors of products having product codes starting with a ‘B’? Display all product-related attributes.

4. List the contact person, area code and phone number for every vendor who supplies at least one product. Display the same vendor information only once.

5. What are the average price and the maximum discount available for products from vendors located in Texas?

6. Get the contact person, area code and phone number of vendors for products whose quantity on hand is less than the minimum stock quantity. Display the same vendor information only once.

7. List all products supplied by the vendor with the highest-priced product. Include vendor details together with product details.

8. Get the detail of the vendor who has supplied the product for which the dollar value of the on hand inventory is the highest.

9. For each vendor, list the product codes of products supplied along with vendor code and vendor contact name. Display vendor information even if there is no product currently supplied by the vendor. Sort the results by vendor code.

10. Display the phone number (with area code) of each vendor whose average price of supplied products is greater than the average price of the products manufactured in house.

Get in touch with your group members. It is your responsibility to contact your group members in a timely manner to work on this assignment as a group. If your group completes the assignment without you, they can choose not to include your name in the assignment and you will be required to do the assignment alone and submit your own copy. No excuses!

If you have any questions, please do not hesitate to contact me.

GOOD LUCK!