# Murach’s SQL for SQL Server – Exercise 5 – Guaranteed 100% score

Lab Price = \$7
Payment methods

Problem Statement
1. Write a SELECT statement that returns two columns from the Invoices table:
VendorID and PaymentSum, where PaymentSum is the sum of the PaymentTotal
column. Group the result set by VendorID.

2. Write a SELECT statement that returns two columns: VendorName and
PaymentSum, where PaymentSum is the sum of the PaymentTotal column. Group
the result set by VendorName. Return only 10 rows, corresponding to the 10 vendors
who’ve been paid the most.
Hint: Use the TOP clause and join Vendors to Invoices.

3. Write a SELECT statement that returns three columns: VendorName, InvoiceCount,
and InvoiceSum. InvoiceCount is the count of the number of invoices, and
InvoiceSum is the sum of the InvoiceTotal column. Group the result set by vendor.
Sort the result set so that the vendor with the highest number of invoices appears
first.

4. Write a SELECT statement that returns three columns: AccountDescription,
LineItemCount, and LineItemSum. LineItemCount is the number of entries in the
InvoiceLineItems table that have that AccountNo. LineItemSum is the sum of the
InvoiceLineItemAmount column for that AccountNo. Filter the result set to include
only those rows with LineItemCount greater than 1. Group the result set by account
description, and sort it by descending LineItemCount.
Hint: Join the GLAccounts table to the InvoiceLineItems table.

5. Modify the solution to exercise 4 to filter for invoices dated in the first quarter of
2002 (January 1, 2002 to March 31, 2002).
Hint: Join to the Invoices table to code a search condition based on InvoiceDate.

6. Write a SELECT statement that answers the following question: What is the total
amount invoiced for each AccountNo? Use the WITH ROLLUP operator to include
a row that gives the grand total.
Hint: Use the InvoiceLineItemAmount column of the InvoiceLineItems table.

7. Write a SELECT statement that returns four columns: VendorName,
AccountDescription, LineItemCount, and LineItemSum. LineItemCount is the row
count, and LineItemSum is the sum of the InvoiceLineItemAmount column. For each
vendor and account, return the number and sum of line items, sorted first by vendor,
then by account description.
Hint: Use a four-way join.

8. Write a SELECT statement that answers this question: Which vendors are being paid
from more than one account? Return two columns: the vendor name and the total
number of accounts that apply to that vendor’s invoices.

Relevant Material
Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at [email protected].
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at [email protected] and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Payment Details
Lab Price = \$7