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

Lab Price = $8
Please feel free to send us your queries at: support@iqrajavaid.com
Payment methods

Add to Cart

Buy Now

View Cart


Problem Statement

1. Write a SELECT statement that returns the same result set as this SELECT
statement. Substitute a subquery in a WHERE clause for the inner join.
SELECT DISTINCT VendorName
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
ORDER BY VendorName

2. Write a SELECT statement that answers this question: Which invoices have a
PaymentTotal that’s greater than the average PaymentTotal for all paid invoices?
Return the InvoiceNumber and InvoiceTotal for each invoice.

3. Write a SELECT statement that answers this question: Which invoices have a
PaymentTotal that’s greater than the median PaymentTotal for all paid invoices?
(The median marks the midpoint in a set of values; an equal number of values lie
above and below it.) Return the InvoiceNumber and InvoiceTotal for each invoice.
Hint: Begin with the solution to exercise 2, then use the ALL keyword in the
WHERE clause and code “TOP 50 PERCENT PaymentTotal” in the subquery.

4. Write a SELECT statement that returns two columns from the GLAccounts table:
AccountNo and AccountDescription. The result set should have one row for each
account number that has never been used. Use a correlated subquery introduced with
the NOT EXISTS operator. Sort the final result set by AccountNo.

5. Write a SELECT statement that returns four columns: VendorName, InvoiceID,
InvoiceSequence, and InvoiceLineItemAmount for each invoice that has more than
one line item in the InvoiceLineItems table.
Hint: Use a subquery that tests for InvoiceSequence > 1.

6. Write a SELECT statement that returns a single value that represents the sum of the
largest unpaid invoices submitted by each vendor. Use a derived table that returns
MAX(InvoiceTotal) grouped by VendorID, filtering for invoices with a balance due.

7. Write a SELECT statement that returns the name, city, and state of each vendor
that’s located in a unique city and state. In other words, don’t include vendors that
have a city and state in common with another vendor.

8. Write a SELECT statement that returns four columns: VendorName,
InvoiceNumber, InvoiceDate, and InvoiceTotal. Return one row per vendor,
representing the vendor’s invoice with the earliest date.

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 support@iqrajavaid.com.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* 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 support@iqrajavaid.com 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.
* In case of any query, please donot hesitate to contact us at support@iqrajavaid.com.
* 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 = $8
Please feel free to send us your queries at: support@iqrajavaid.com
Payment methods

Add to Cart

Buy Now

View Cart