1. Write a SELECT statement that returns the same result set as this SELECT stat
ID: 3546870 • Letter: 1
Question
1. Write a SELECT statement that returns the same result set as this SELECT statement but dont use a join. Instead, use a subquery in a WHERE clause that uses in IN keyword.
SELECT DISTINCT vendor_name
FROM vendors JOIN invoices
ON vendors.vendor_id = invoices.vendor_id
ORDER BY vendor_name.
2. Write a SELECT statement that answers this question: Which invoices have a payment_total that's greater than the average payment_total for all paid invoices? Return the invoice_number and the invoice_total for each invoice.
3. Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices for each vendor (just one for each vendor). Use an inline view that returns MAX(invoice_total) grouped by vendor_id, filtering for invoices with a balance due.
Please help me Im having terrible time getting these statements to run.
Explanation / Answer
1.
SELECT DISTINCT vendor_name
FROM vendors v
where v.vendor_id in (
select i.vendor_id
from invoices i)
ORDER BY vendor_name;
2.
select i1.invoice_number,i1.invoice_total
from invoices i1,
(select invoice_number,avg(payment_total) avg_payment_total
from invoices
group by invoice_number) i2
where i1.invoice_number=i2.invoice_number
and i1.payment_total>i2.avg_payment_total;
3.
select sum(max_invoice_total)
from
(select max(invoice_total) as max_invoice_total
from invoices
where balance_due_amout>0
group by vendor_id);
Hope this is helpful
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.