Write a subquery to return one row per vendor, representing the vendor’s earlies

Question

Write a subquery to return one row per vendor, representing the vendor’s earliest invoice_due_date. Each row should include ( vendor name, vendor_id, count of invoices, the earliest invoice due date. Filter the result set to only show the rows with more than 3 count of invoices.

[hints:

subquery should include a group by to find invoice with the Min(invoice_due_date), and the count( invoice_id).

The major query should join with the subquery (treat the subquery as a table).

The Sub query should have alias.

All columns inside the subquery should have aliases too

Explanation / Answer

SELECT VendorName,InvoiceNumber,InvoiceDate,InvoiceTotal FROM Vendors V JOIN Invoices I ON V.VendorID=I.VendorID
WHERE InvoiceDate <= ( SELECT Min(InvoiceDate) FROM Invoices JOIN Vendors ON V.VendorID=Vendors.VendorID )
GROUP BY VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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