Description

6393.501
Database Foundations

ASSIGNMENT 3

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 single-table SQL Select queries to answer the following questions.

1. List the products and corresponding vendor codes for products whose inventory is less than the minimum stock quantity or products that are priced less than $75 with discount greater than 5%.

2. List the product code and price for products for which the dollar value of the on hand inventory is greater than $1000.

3. List the product code, price and discount rate of all products in a descending order of their price.

4. Display all product attributes for every product with a product code starting with a ‘B’ in an ascending order of their price.

5. List the distinct codes of vendors supplying products for which minimum stock quantity is less than 10.

6. How many products are there in the product table?

7. What are the total quantity of products on hand, the average price of products, the minimum discount rate and the maximum discount rate available?

8. For each vendor, list the minimum price and the average discount rate for their products for which the quantity on hand is less than the minimum stock quantity.

9. For each available discount rate of less than 10%, show the minimum price and the maximum price of products with the same discount rate.

10. List the minimum and maximum prices of products for each vendor for vendors who supply more than 3 items.

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!