With reference to the AP database, you will answer the following questions You a

Question

With reference to the AP database, you will answer the following questions You are to upload a document of a script file with the scripts for the following questions Write a SELECT statement that returns three columns Account Description. Line Item Count. and Line Sum LineItcmCount is the number of entries in the Invoice Lineltems table that have that Account No LineltemSum is the sum of the InvoiceLineltemAmount column for that Account No. Filter the result set to include only those rows with LineItemCount greater than I Group the result set by account description, and sort it by descending LineItemCount Modify the solution to exercise I to filter for invokes dated in the second quarter of 2008 (April I, 2008 to June 30.2008). Write a SELECT statement that answers this question Which vendors arc being paid from more than one account'' Return two columns: the vendor name and the total number of accounts that apply to that vendor 's invoices

Explanation / Answer

Hi below i have written the solution for your reference,

1.

SELECT GLAccounts.AccountDescription, COUNT(*) AS LineItemCount,
SUM(InvoiceLineItemAmount) AS LineItemSum
FROM GLAccounts JOIN InvoiceLineItems
ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo
GROUP BY GLAccounts.AccountDescription
HAVING COUNT(*) > 1
ORDER BY LineItemCount DESC;

2.

SELECT GLAccounts.AccountDescription, COUNT(*) AS LineItemCount,
SUM(InvoiceLineItemAmount) AS LineItemSum
FROM GLAccounts JOIN InvoiceLineItems
ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo
JOIN Invoices
ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
WHERE InvoiceDate BETWEEN '2008-04-01' AND '2004-06-30'
GROUP BY GLAccounts.AccountDescription
HAVING COUNT(*) > 1
ORDER BY LineItemCount DESC;

3.

SELECT VendorName,
COUNT(DISTINCT InvoiceLineItems.AccountNo) AS [# of Accounts]
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
JOIN InvoiceLineItems
ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
GROUP BY VendorName
HAVING COUNT(DISTINCT InvoiceLineItems.AccountNo) > 1
ORDER BY VendorName;

4.

SELECT AccountNo, SUM(InvoiceLineItemAmount) AS LineItemSum
FROM InvoiceLineItems
GROUP BY AccountNo WITH ROLLUP;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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