3. Based on the relational schema in Question 2 (reproduced below, with one more

Question

3. Based on the relational schema in Question 2 (reproduced below, with one more attribute S_NAME in SUPPLIER), write SQL commands for the following problems. (Write SQL statements only). PRODUCT ( P#, P_NAME,SELL_PRICE, QTY) SUPPLY(S#^*, P#^8, SUP_PRICE) SUPPLIER(S#,S_NAME, CITY) (a) Find all the product names whose selling prices are at least 50% greater than their supplying price. (5) (b) Find the product name, selling-price, and supplier-price offered by suppliers in the city of ^'New York^'. Sort the output by product name (5) (c) Find the supplier number, supplier name and their minimum, maximum, and average supplying price for all the products each supplier offers (5)

Explanation / Answer

(a) select a.p_name from product a, suppy b where a.p# = b.p#* and a.sell_price > (b.sup_price*50/100)+b,sup_price;

(b) select a.p_name, a.sell_price, b.sup_price from product a, supply b, supplier c where c.city = 'New York' and b.s#*=c.s# and b.p#*=a.p# order by a.p_name;

(c) select b.s#, b.s_name, min(a.sup_price), max(a.sup_price), avg(a.sup_price) from supply a, supplier b where a.s#* = b.s# group by a.p#*;

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.