Using the AP (Accounts Payable) Database in Murach\'s MySQL 5th edition: 1. Calc

Question

Using the AP (Accounts Payable) Database in Murach's MySQL 5th edition:

1. Calculate the average invoice amount by vendor_id.

2.Modify question #1 to display the vendor_id greater than 80.

3. List the highest invoice_total for each vendor_id.

4. Show the number of invoices for all vendor in California State.

5. Show the number of invoices for each vendor and sum of total_invoices by vendor_id, and show the summary row to show the sum of

total_invoices for all vendor_id.

Please use MySQL Workbench if possible to run queries.

Explanation / Answer

1. average invoice amount by vendor_id

select vendor_id, AVG(invoice_total)

from vendors

group by vendor_id;

2. for vendor_id greater than 80

select vendor_id, AVG(invoice_total)

from vendors

where vendor_id > 80

group by vendor_id;

3. highest of invoice total for each vendor_id

select vendor_id,invoice_total

from vendors

where invoice_total IN

{select MAX(invoice_total) AS highest_invoice_total

from vendors_table

group by vendor_id;}

4. No. of all invoices for all vendors in California State

select count(invoice_id) AS no_of_invoices

from vendors

where vendor_state = 'CA'

5. No. of invoices of each vendor and sum of total_invoices by vendor_id, and show the summary row to show the sum of total_invoices for all vendor_id.

select count(invoice_id) AS no_of_invoices, vendor_id

from vendors JOIN invoices

ON vendors.vendor_id = invoices.vendor_id

WHERE IN

{select vendor_id V1, vendor_id V2

from invoices

where V1.vendor_id = V2.vendor_id

IN{select vendor_id, sum(invoice_total)

from invoices

group by vendor_id;}

}

select SUM(invoice_total), vendor_id

from vendors

GROUP BY vendor_id;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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