Question

Which of the following statements is valid?SELECT InvoiceNumber, VendorNameFROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorIDWHERE InvoiceTotal = MAX(InvoiceTotal)SELECT InvoiceNumber, VendorNameFROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorIDWHERE InvoiceTotal = (SELECT MAX(InvoiceTotal))SELECT InvoiceNumber, VendorNameFROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorIDWHERE InvoiceTotal IN (SELECT MAX(InvoiceTotal) FROM Invoices)All of the above

Answer 1

Correct Answer:

c.

SELECT InvoiceNumber, VendorName

FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID

WHERE InvoiceTotal IN (SELECT MAX(InvoiceTotal) FROM Invoices)

Explanation:

All options only differ on the WHERE clause:

a: WHERE InvoiceTotal = MAX(InvoiceTotal)

Fails because aggregate functions (like MAX, COUNT, etc) have to be used on the SELECT clause.

b: WHERE InvoiceTotal = (SELECT MAX(InvoiceTotal))

Fails because the SELECT clause is incomplete.

c: WHERE InvoiceTotal IN (SELECT MAX(InvoiceTotal) FROM Invoices)

This one is correct, and returns the InvoiceNumber and VendorName register with the largest value on the InvoiceTotal field.