I need the solution of the diagram, please write the SQL for the part (f), (g),

Question

I need the solution of the diagram,
please write the SQL for the part (f), (g), (h),(i)

(f) List the names of the parts which have been bought by at least two different customers..

(g) Find the names of the customers who have bought a Dryer but have not bought any other part.

(h) Get a count of the number of warehouses which supply at least 4 parts.

(i) From among the customers who placed at least 2 orders, and the name of the customer with          

     the largest balance.

f) List the names of the parts which have been hought by at leas two different cussomers (g) Find the names of the customers who have bought Dryer but have not bought any other part. h) Get a count of the number of warehouses which supply at least 4 parts From among the customers who placed at least 2 orders, and the name of the customer with he lgst balace Customer Galore ,000.00 0 ndApplience artNu DescriptionOntar clar warehout Price BVHome Gym 45 SG C052 Microwave Ove DL Cordless Deil z1 HW3 DR33 Gas Rang F02L KTOB Dishwasher 399 99 158.95 Stand Mxer 12 AP KV2STreadmill 956 2 2161020/10/2007 356 21613 21/10/2007 408 1614 21/10/2007 282 17 23/10/2007 608 23/10/2007 148 23/10/2007 608 2 216 21619 21623 OrderNu PartNu NumOrder QuotedPric 21610 21610 E399.99 E329.95 ESS5.00 E794.95 21617 21617 21619 KV29 2E1,290.00

Explanation / Answer

f : Names of the parts bought by atleast 2 different customers.

For this we need to count the number of orders for each part.

SQL: select Description from Part where PartNu = (select PartNu from OrderLine groupby(PartNu) having count(OrderNu) >= 2)

g: Name of customers who bought only dryers.

we only need to check from specific part and we need to refrence 3 tables to get the customer name.

SQL: select CustomerName from Customer, Order, OrderLine where Customer.CustomerNu = ( select CustomerNu from Orders where OrderNu = ( select OrderNu from OrderLine where PartNu like "KL62" ))

h: We need to find the number of warehouses supplying atleast 4 parts.

For this we need only Part table.

SQL: select count( Warehous ) from Part groupby( Warehous ) having count( PartNu ) >= 4

i: We need to find the customer with largest balance among the customers who placed atleast two orders.

For this we need to reference two tables i.e. Customer table and Orders table.

SQL: select CustomerName from Customer where CustomerNu = ( select CustomerNu from Orders groupby( CustomerNu ) having count( OrderNu ) >= 2 )

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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