IS$276$DB MODULE $5$

Correct Table Joins and Join Fields & Summary Queries

USE AP

Vendors to Invoices $($on VendorID$)$

Invoices to InvoiceLineItems $($on InvoiceID$)$

InvoiceLineItems to GLAccounts $($on AccountNo$)$

$1.$ Write a select statement that returns $2$ columns from the Invoices table:

InvoiceCount and AvgInvAmount. InvoiceCount is the count of the number of

invoices and AvgInvAmount is the average of the InvoiceTotal column.

$2.$ Write a select statement that returns $2$ columns from the Invoices table:

VendorID and BalanceDue. BalanceDue is the sum of invoices with a balance

due. Filter the results to only return rows where a balance is due. Group the

results by VendorID.

$3.$ Write a select statement that returns $3$ columns from the Invoices table:

VendorID, InvoiceCount, and TotalPaid. InvoiceCount is the count of the

number of invoices, and TotalPaid is the sum of the PaymentTotal and

CreditTotal columns added together. Filter the results to only return rows

where the balance due is equal to zero. Group the results by VendorID.

$4.$ Write a select statement that returns $6$ columns: VendorName, InvCount,

LargestInv, SmallestInv, AverageInv and TotalInv:

InvCount: Count of the number of invoices

LargestInv: Highest value in the InvoiceTotal column

SmallestInv: Lowest value in the InvoiceTotal column

AverageInv: Average value in the InvoiceTotal column TotalInv:

Sum of the InvoiceTotal column

Group the results by VendorName.

$5.$ Write a select statement that returns $4$ columns: VendorState, VendorCity,

TotalInvoices, and LargestInvoice:

TotalInvoices: Count of the number of invoices

LargestInvoice: Highest value in the InvoiceTotal column

Filter the results $($using a HAVING clause$)$ to only return rows where the

LargestInvoice is greater than $$500.$ Group and sort the results by VendorState

and VendorCity.

$6.$ Write a select statement that returns $4$ columns: VendorCity, InvoiceDate,

InvoiceCount and InvoiceSum:

InvoiceCount: Count of the number of invoices InvoiceSum:

Sum of the InvoiceTotal column

Add a compound condition in a HAVING clause that filters the results to only

return rows that match these conditions:

$$ The InvoiceDate is in the range of $5/1/2015$ to $8/1/2016$

$$ The InvoiceSum is greater than $$300$

$$ The InvoiceCount is greater than $1$

$7.$ Write a select statement that returns $6$ columns: VendorName, InvoiceCount,

InvoiceTotal, TotalPayments, TotalCredits, and BalanceDue:

InvoiceCount: Count of the number of invoices

InvoiceTotal: Sum of the InvoiceTotal column

TotalPayments: Sum of the PaymentTotal column

TotalCredits: Sum of the CreditTotal column

BalanceDue: Sum of Invoices with a balance due

Group the results by VendorName and include the operator necessary to add

a summary row for each of the columns.

$8.$ Write a select statement that returns $4$ columns: VendorName, TotalInvAmt,

LineItemTotal and LineItemCount:

TotalInvAmt: Sum of the InvoiceTotal column

LineItemTotal: Sum of the InvoiceLineItemAmount column

LineItemCount: Count of entries in InvoiceLineItemAmount column

Filter the results to only return rows where VendorName starts with the letters

D through U$.$ Group the results by VendorName and using a HAVING clause,

further filter the results to only return rows where the TotalInvAmt is greater

than $$400$