Demonstrate mastery of Aggregate functions, grouping, and SQL joins. /* Use the

Question

Demonstrate mastery of Aggregate functions, grouping, and SQL joins. /* Use the Murach AP database to produce the requested result sets. 1. Produce a result set that shows which vendors are being paid from more than 1 account. 2. Write a query that returns the Count of Invoices for each vendor 3. Write a query that returns the InvoiceNumber and balance due for every invoice that has a balance and an Invoice Due Date that is less than 30 days from today. 4. Write a query that returns the total amount invoiced for each Account. 5. Write a query that calculates the number of invoices and the average invoice amount for the vendors in California. 6. Write a query that returns the Sum of the Invoice Totals for Each Vendor 7. Write a query that returns the Average of Invoice Totals for all invoices ordered by region of vendor. */

Explanation / Answer

1.
SELECT vendor_name,
COUNT(DISTINCT li.account_number) AS "Number of Accounts"
FROM vendors v JOIN invoices i
ON v.vendor_id = i.vendor_id
JOIN invoice_line_items li
ON i.invoice_id = li.invoice_id
GROUP BY vendor_name
HAVING COUNT(DISTINCT li.account_number) > 1
ORDER BY vendor_name

2.
select v.vendor_id, v.vendor_name, count(i.invoice_id)
from invoices i join vendors v
   on v.vendor_id = i.vendor_id
group by v.vendor_id, v.vendor_name

3.
select invoice_number, (invoice_total - payment_total) as balance from invoices
where (invoice_total - payment_total) > 0 and
invoice_due_date between GETDATE() and DATEADD(DAY, 30, GETDATE())

4.
select li.account_number as "account number", sum(li.line_item_amount) as "line item total"
from invoice_line_items li
group by li.account_number;

Please post only 4 subquestion in one thread as its not possible to answer more in the given time period.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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