Demonstrate mastery of Aggregate functions, grouping, and SQL joins. Need whole

Question

Demonstrate mastery of Aggregate functions, grouping, and SQL joins.

Need whole code written out! Please let me know if table names are missing!

l Columns TermaitikPk, int,not null) Cri not nul eDa not hull igger i Statisti EE dbo Vendofs bo.Vendors Columns e VendortD ipK int noth s1 warc VendorAddress1 (varchar(50x n vendorAddress2 (varchar(50), null) ull) VendorState (char(2), not null) vendorZipCode (varchar(20), not null) vendorphone (varchar(50), null) VendorContactLName (varchar(50), null) vendorContactF Name (varchar(50), null) em DefaultTermsID (FK, int, not null) om DefaultAccountNo (FK, int, not null) eys , ntt 5 e e e

Explanation / Answer

1.   Query to display vendors name:
/*Select name*/
select Vendors.VendorName,
/*Call count() and set column name as “Total Accounts”*/
count(DISTINCT InvoiceLineItems.AccountNo) AS "Total Accounts" from Vendors
/*Inner join with */
inner join Invoices on
/*based on condition*/
Invoices.VendorID = Vendors.VendorID
/*inner join with*/
inner join InvoiceLineItems on
/*based on condition*/
InvoiceLineItems.InvoiceID = Invoices.InvoiceID
/*gather by*/
group by Vendors.VendorName
/*with count */
having count(DISTINCT InvoiceLineItems.AccountNo)>1 order by Vendors.VendorName;

2.   Query to display the Invoices count for each vendor:
/*select name*/
select Vendors.VendorName,
/*count invoices*/
count(Invoices.InvoiceID)AS "Invoices Total"
/*from table Vendors joining with Invoices */
from Vendors inner join Invoices on
/*based on the condition*/
Invoices.VendorName = Vendors.VendorName
/*gather by*/
group by Vendors.VendorName
/*Arrange by count*/
order by count(Invoices.InvoiceID);

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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