Write SQL query to create a view payed_vendors, containing the vendor name, the

Question

Write SQL query to create a view payed_vendors, containing the vendor name, the count of invoices, the sum of the total invoices, and the sum of the total payments. For those that have 2 or more invoices.

COLUMN ID COMMENTS COLUMN_NAMIE 1 VENDOR_ID 2 VENDOR NAME 3 VENDOR_ADDRESS1 4 VENDOR_ADDRESS2 5 VENDOR_CITY 6 VENDOR STATE 7 VENDOR_ZIP_CODE 8 VENDOR_PHONE 9 VENDOR CONTACT LAST NAME VARCHAR2 (50 BYTE) Yes DATA TYPE NULLABLE DATA DEFAULT NUMBER VARCHAR2 (50 BYTE) No VARCHAR2 (50 BYTE) Yes VARCHAR2 (50 BYTE) Yes VARCHAR2 (50 BYTE) No CHAR (2 BYTE) VARCHAR2 (20 BYTE) No VARCHAR2 (50 BYTE) Yes (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) 1 (null) 2 (null) 3 (null) 4 (null) 5 (null) 6 (null) 7 (null) 8 (null) 9 (null) 10 (null) 11 (null) 12 (null) 0 No 10 VENDOR_CONTACT_FIRST_NAME VARCHAR2 (50 BYTE) Yes 11 DEFAULTTERMS_ID 12 DEFAULT_ACCOUNT_NUMBER NUMBER NUMBER 0 0 0 COLUMN NAME| DATA TYPE | NU LLABLE DATA_DEFAULTCOLUMN ID COMMENTS (null) (null) (null) (null) (null) 1 (null) 2 (null) 3 (null) 4 (null) 5 (null) 6 (null) 7 (null) 8 (null) 9 (null) 10 (null) 1 INVOICE ID NUMBER No 0 3 INVOICE _NUMBER VARCHAR2 (50 BYTE) No No No VENDOR ID NUMBER DATE 4 INVOICE_DATE 5 INVOICE_TOTAL NUMBER (9,2) 6 PAYMENT TOTALNUMBER (9,2) 7 CREDIT_TOTAL 8 TERMS_ID 9 INVOICE_DUE_DATE DATE Yes NUMBER (9,2) NUMBER Yes (null) (null) (null) No No 10 PAYMENT_DATE DATE Yes

Explanation / Answer

ANSWER:

Create View payed_vendors as
Select v.VENDOR_NAME ,COUNT(i.INVOICE_ID) as "Count of Invoices", SUM(i.INVOICE_TOTAL) as "Sum of the Total Invoices", SUM(i.PAYMENT_TOTAL) as "Sum of the Total Payments"
FROM VENDOR v JOIN INVOICE i ON i.VENDOR_ID = v.VENDOR_ID
WHERE i.INVOICE_TOTAL > 2
GROUP BY v.VENDOR_NAME;/*create a view payed_vendors, containing the vendor name, the count of invoices, the sum of the total invoices, and the sum of the total payments*/

SELECT * FROM payed_vendors;/*This query will display all the posible values of the view payed_vendors*/

Sample output of the selection query:

Let me know if you have any doubts in the answer.

VENDOR_NAME Count of Invoices Sum of the Total Invoices Sum of the Total Payments Ambrose 10 165400 143663 Brock 13 146399 126553 Reigns 12 156824 154432 …….. ……. …. ….

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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