Write a SQL statement to list vendors (vend_id in Vendors) and the number of products they have available, including vendors with no products. You’ll want to use an OUTER JOIN and the COUNT() aggregate function to count the number of products for each in the Products table. Pay attention, the vend_id column appears in multiple tables so any time you refer to it you’ll need to fully qualify it.


SELECT Vendors.vend_id, COUNT(prod_id)
FROM Vendors
 LEFT OUTER JOIN Products ON Vendors.vend_id = Products.vend_id
GROUP BY Vendors.vend_id;

Comment on this FAQ