Question

(Use AP) Create a query listing all of the vendors (show only vendorid, vendorname) from the state of Wisconsin (WI) or New Jersey (NJ) or Pennsylvania (PA) who have never been invoiced (they do not have an invoice associated with them). Sort the final result set by vendorname from Z-A.

Answer 1

A SQL query is used to find vendors from WI, NJ, or PA with no invoices. LEFT JOIN ensures only vendors without invoices are listed. The list is sorted by vendorname in descending order.

Step-by-step explanation:

To create a query listing all of the vendors with the specified conditions, you would typically use a SQL SELECT statement with a LEFT JOIN to include vendors who have not been invoiced.

Assuming we have two tables: Vendors (containing vendorid, vendorname, and other vendor details) and Invoices (containing an association between vendorid and invoice details), the query would look something like this:

SELECT vendorid, vendorname

FROM Vendors AS v

LEFT JOIN Invoices AS i ON v.vendorid = i.vendorid

WHERE (v.state = 'WI' OR v.state = 'NJ' OR v.state = 'PA')

AND i.invoiceid IS NULL

ORDER BY vendorname DESC;

This SQL query selects only the vendorid and vendorname from the Vendors table, filters for vendors based in Wisconsin, New Jersey, or Pennsylvania, ensures there are no associated invoices by checking for NULL in the left-joined Invoices table, and sorts the results by vendorname in descending order (from Z-A).