Write and SQL statement for these two queries. 1. For each part in the INVENTORY

Question

Write and SQL statement for these two queries.

1. For each part in the INVENTORYPART table, display its description, part number, and the average quantity sold on any order placed for that part. Round the average to the closest whole number, then sort the results by part description.

2. For each year in which part CF-004 was ordered at least once, display its description, part number, year, and the average quantity sold on any order placed for CF-004 during a given year. Round the average to 1 decimal place, then sort the results first by part description, then year.

COMPANY NAME: vachr (40) CONTACINAMEacha220 PARTNUMBER:h10FK REQUIREDDATE dane REVISIONLEVELarcha210) PARTNUMBER varchar2oFK SHPSTATE:2 MACHINEID: ahar FK PROCESSID: varcharFK SETUPALLOWEDaha2) EMPLOYEEIDha0FK

Explanation / Answer

Hi,
1. Here we have to deal with 2 tables INVENTORYPART and CUSTORDERLINE, to get quantity of that part
select round(avg(ORDERQUANTITY)),a.PARTDESCRIPTION,a.PARTNUMBER from INVENTORYPART a,CUSTORDERLINE b where a.PARTNUMBER=b.PARTNUMBER group by a.PARTNUMBER order by a.PARTDESCRIPTION;

here first we are joining based on part number and then taking the average of the order quantity, round() rounds the average to closest integer
2.For this we need CUSTORDER table as it has date column.
select round(avg(ORDERQUANTITY),1),a.PARTDESCRIPTION,a.PARTNUMBER from INVENTORYPART a,CUSTORDERLINE b, CUSTORDER c where a.PARTNUMBER=b.PARTNUMBER and b.ORDERID=c.ORDERID and a.PARTNUMBER='CF-004' and b.QUANTITY>=1 group by c.ORDERDATE order by a.PARTDESCRIPTION,c.ORDERDATE;
here first we join 3 tables like INVENTORYPART and CUSTORDERLINE on part number and then CUSTORDERLINE and CUSTORDER on orderid then we put the partnumber and quantity condition and then group by the date so find date aggregated results. round(x,1) rounds the results to 1 decimal places
Thumbs up if this was helpful, otherwise let me know in comments.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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