Chapter 6
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 Vendors table The sum of the invoice_total columns in the Invoices table for that vendor.
This should return 34 rows.

Answer:

SELECT vendor_id, SUM (invoice_total) AS invoice_total_sum

FROM invoices

GROUP BY vendor_id

2. a. 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 b. Sort the result set in descending sequence by the payment total sum for each vendor.

Answer:

SELECT vendor_name, SUM (payment_total) AS payment_total_sum …show more content…
Return only those rows where the count of the line items is greater than 1. This should return 10 rows.

c. Group the result set by account descriptions.

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

Answer:

SELECT account_description, COUN T(*) AS line_item_count, SUM (line_item_amt) AS line_item_amt_sum

FROM general_ledger_accounts gl JOIN invoice_line_items li

ON gl.account_number = li.account_number

GROUP BY gl.account_description

HAVING COUNT (*) > 1
ORDER BY line_item_amt_sum DESC

5. Modify the solution to exercise 4 so it returns only invoices date in the second quarter of 2014 (April 1, 2014 to June 30, 2014). 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 base on invoice_date.

Answer:

SELECT account_description, COUNT (*) As line_item_count, SUM (line_item_amt) AS line_item_amt_sum

FROM general_ledger_accounts gl JOIN invoice_line_items li

ON gl.account_number = li.account_number

JOIN invoices i

ON li.invoice_id = i.invoice_id

WHERE invoice_date BETWEEN '01-Apr-2014' AND '30-June-2014'

GROUP BY gl.account_description

HAVING COUNT (*) >