1. Write a SELECT statement that returns the same result set as this SELECT stat

Question

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 joint.

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 invoices.

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 PERCENTAGE PaymentTotal" in the subquery.

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

9. Rewrite exercise 6 so it uses a common table expression

(CTE) instead of a derived table.

Explanation / Answer

the answers are

1.
SELECT distinct VendorName FROM Vendors
WHERE VendorID IN (
SELECT VendorID FROM Invoices
)

2

SELECT InvoiceID, InvoiceTotal FROM INVOICES
WHERE PaymentTotaL>
(SELECT Avg(PaymentTotal) FROM INVOICES)

3

SELECT InvoiceNumber, InvoiceTotal FROM INVOICES

WHERE PaymentTotaL > ALL
(SELECT TOP 50 PERCENT PaymentTotal
FROM INVOICES
ORDER BY PaymentTotal asc)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.