1.Write a SELECT statement that returns one row for each vendor in the Invoices
ID: 3561982 • Letter: 1
Question
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 Vendor table
The sum of the invoice_total columns in the Invoices table for that vendor.
2.write a select statement that returns one row for each vendor in the invoice table 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.
3.write a select statement that returns one row for each vendor in the invoice table that contains these columns:
The vendor_name column from the Vendors table
TThe count of the invoices in the Invoices table for each vendor
The sum of the invoice_total columns in the Invoices ttable for each vendor. sort the result set so the vendor with the most invoices appears first.
4. write a SELECT statement that returns one row for each genneral 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 result set by account description. sort the result set in descending sequence by the sum of the line item amounts.
5. Modify the solution to exercise 4 so it returns only invoices dated in the second quarter of 2011(april 1, 2011 to june 30, 2011). this should still return 10 rows but with some different line item counts each vendor.
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 from the invoice_line_items table, the sum of the line item amounts from the invoice_line_items table. use the WITH ROOLUP operator to include a row that gives the grand total.
7. Write a Select statement that answers this question: which vendors are being paid from more than one account? return these: the vendor name from vendors table, the count of distinct general ledger accounts that apply to that vendor's invoices.
Explanation / Answer
1) SELECT Vendor.vendor_id,sum(Invoices.invoice_total)
FROM Vendor
INNER JOIN Invoices
WHERE Vendor.vendor_id=Invoices.vendor_id
GROUP BY Vendor.vendor_id;
2) SELECT Vendor.vendor_name,sum(Invoices.payment_total)
FROM Vendor
INNER JOIN Invoices
WHERE Vendor.vendor_name=Invoices.vendor_name
GROUP BY Vendor.vendor_name
ORDER BY sum(Invoices.payment_total) DESC;
3)SELECT Vendor.vendor_name,count(Invoices.invoices),sum(Invoices.invoice_total)
FROM Vendor
INNER JOIN Invoices
WHERE Vendor.vendor_name=Invoices.vendor_name
GROUP BY Vendor.vendor_name
ORDER BY count(Invoices.invoices) DESC
LIMIT 10;
4)SELECT general_ledger_accounts.account_description,count(Invoice_Line_Items.items),sum(Invoice_Line_Items.line_item_amount)
FROM general_ledger_accounts
INNER JOIN Invoice_Line_Items
WHERE general_ledger_accounts.account number=Invoice_Line_Items.account number
GROUP BY general_ledger_accounts.account_description
HAVING count(Invoice_Line_Items.items)>1
ORDER BY sum(Invoice_Line_Items.line_item_amount) DESC;
5) SELECT general_ledger_accounts.account_description,count(Invoice_Line_Items.items),sum(Invoice_Line_Items.line_item_amount)
FROM general_ledger_accounts
INNER JOIN Invoice_Line_Items
WHERE (general_ledger_accounts.account number=Invoice_Line_Items.account number) AND
(Invoice_Line_Items.invoices_date BETWEEN {april 1, 2011} AND {june 30, 2011})
GROUP BY general_ledger_accounts.account_description
HAVING count(Invoice_Line_Items.items)>1
ORDER BY sum(Invoice_Line_Items.line_item_amount) DESC;
6) SELECT account_number,sum(line_item_amount)
FROM invoice_line_items
GROUP BY account_number WITH ROLLUP;
7) SELECT vendorname,count(DISTINCT accounts_number)
FROM vendors
GROUP BY vendorname;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.