1. Write a SELECT statement that returns the same result set as this SELECT stat
ID: 3546830 • Letter: 1
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
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.