Exercise 1

Write a SELECT statement that returns one row for each vendor in the Invoices table that contains these columns:

• The vendor_id column from the Invoices table
• The sum of the invoice_total columns in the Invoices table for that vendor

This should return 34 rows.

Exercise 2

Write a SELECT statement that returns one row for each vendor that contains these columns:

• The vendor_name column from the Vendors table
• The sum of the payment_total columns in the Invoices table for that vendor

Sort the result set in descending sequence by the payment total sum for each vendor.

Exercise 3

Write a SELECT statement that returns one row for each vendor that contains three columns:

• The vendor_name column from the Vendors table
• The count of the invoices in the Invoices table for each vendor
• The sum of the invoice_total columns in the Invoices table for each vendor

Sort the result set so the vendor with the most invoices appears first.

Exercise 4

Write a SELECT statement that returns one row for each general ledger account number that contains three columns:

• The account_description column from the General_Ledger_Accounts table
• The count of the items in the Invoice_Line_Items table that have the same account_number
• The sum of the line_item_amount columns in the Invoice_Line_Items table that have the same account_number

Return only those rows where the count of line items is greater than 1. This should return 10 rows.

Group the result set by the account_description column.

Sort the result set in descending sequence by the sum of the line item amounts.

Exercise 5

Modify the solution to exercise 4 so it returns only invoices dated in the second quarter of 2018 (April 1, 2018 to June 30, 2018).

This should still return 10 rows but with some different line item counts for each vendor.

Hint: Join to the Invoices table to code a search condition based on invoice_date.

Exercise 6

Write a SELECT statement that answers this question:

What is the total amount invoiced for each general ledger account number?

Return these columns:

• The account_number column from the Invoice_Line_Items table
• The sum of the line_item_amount columns from the Invoice_Line_Items table

Use the WITH ROLLUP operator to include a row that gives the grand total.

This should return 22 rows.

Exercise 7

Write a SELECT statement that answers this question: Which vendors are being paid from more than one account?

Return these columns:

• The vendor_name column from the Vendors table
• The count of distinct general ledger accounts that apply to that vendor’s invoices

This should return 2 rows.

Exercise 8

Write a SELECT statement that answers this question: What are the last payment date and total amount due for each vendor with each terms id? Return these columns:

• The terms_id column from the Invoices table The vendor_id column from the Invoices table
• The last payment date for each combination of terms id and vendor id in the Invoices table - The sum of the balance due (invoice_total - payment_total - credit_total) for each combination of terms id and vendor id in the Invoices table

Use the WITH ROLLUP operator to include rows that give a summary for each terms id as well as a row that gives the grand total. This should return 40 rows.

Use the IF and GROUPING functions to replace the null values in the terms_id and vendor_id columns with literal values if they’re for summary rows.

Exercise 9

Write a SELECT statement that uses aggregate window functions to calculate the total due for all vendors and the total due for each vendor.

Return these columns:

• The vendor id from the Invoices table The balance due (invoice_total - payment_total - credit_total) for each invoice in the Invoices table with a balance due greater than 0
• The total balance due for all vendors in the Invoices table The total balance due for each vendor in the Invoices table

Modify the column that contains the balance due for each vendor so it contains a cumulative total by balance due. This should return 11 rows.