Hello I had some oracle/sql questions. I\'ve tried to solve them but dont know w

Question

Hello I had some oracle/sql questions. I've tried to solve them but dont know where to begin. Create a PL/SQL (must have BEGIN ... END) block listing all invoices where the total is more than twice the average amount for that vendor. Format and align the output. Invoke the function to obtain the percentage. Order the output by the vendor name. Include the vendor name, total amount, percentage, average amount, and due date (full month name, no extra spaces). The output must be generated from within the PL/SQL block. the other question : Recreatethe above problem only listing vendors that have more than 5 invoices. PL/SQL is optional.

Explanation / Answer

DECLARE

percent number(3,2);

rawNum number(3,2);

FUNCTION percentage()

RETURN number

Is

per number;

BEGIN

Select count(*) into num from Invoice, Vendor where Invoice.VendorId=Vendor.VendorId and sum(Invoice.InvoiceTotal)>(2*avg(Vendor.Amont) )group by Invoice.VendorId;   

Select count(*) into total from Invoice;

percent:= num/total*100;

RETURN rawNum;

END

BEGIN

rawNum:=percentage();

Select vendor name, total amount, rawNum as percentage,avg(Invoice.amount) as average amount, and due date from Invoice, Vendor where Invoice.VendorId=Vendor.VendorId and sum(Invoice.InvoiceTotal)>avg(Vendor.Amont) group by Invoice.VendorName

END

PART 2:

DECLARE

percent number(3,2);

rawNum number(3,2);

FUNCTION percentage()

RETURN number

Is

per number;

BEGIN

Select count(*) into num from Invoice, Vendor where Invoice.VendorId=Vendor.VendorId and sum(Invoice.InvoiceTotal)>avg(Vendor.Amont) group by Invoice.VendorId;  

Select count(*) into total from Invoice;

percent:= num/total*100;

RETURN rawNum;

END

BEGIN

rawNum:=percentage();

Select vendor name, total amount, rawNum as percentage,avg(Invoice.amount) as average amount, and due date from Invoice, Vendor where Invoice.VendorId=Vendor.VendorId and sum(Invoice.InvoiceTotal)>avg(Vendor.Amont) and Count(Invoice.VendorId)>5 group by Invoice.VendorName

END

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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