Question

Give the SQL statement that will find the vendor that provides that most products. Output the Vendor Name and the count of products.

categories CategorylD INT(11) ◇ CategoryDescription VARCHAR( 75) Custom erID INT(11) CustFirstName VARCHAR(25) ◇CustLastName VARCHAR(25) ◇ CusstreetAddress VARCHAR(50) ◇ CustCity VARCHAR(30) ◇ Cusstate VARCHAR(2) order_details OrderNumber INT(11) ProductNum ber INT (11) QuotedPrice DECIMAL(15,2) QuantityOrdered SMALLINT(6) OrderNumber INT(11) ◇ OrderDate DATE ShipDate DATEK CustomerID INT(11) EmployeeID INT(11) CustzipCode VARCHAR( 10) custrea Code SMALLINT(6) ◇ CustphoneN umber VARCHAR(8) ProductNum ber INT (11) ProductName VARCHAR(50) ◇ ProductDescription VARCHAR(100) EmployeeID INT (11) ◇ EmpFirstName VARCHAR(25) ◇ EmpLastName VARCHAR( 25) RetailPrice DECMAL (15,2) ◇QuantityOnHand SMALLINT(6) Category/ID INT(11) EmpSteetAddress VARCHAR(50) ◇ EmpCity VARCHAR(30) EmpState VARCHAR() EmpZipCode VARCHAR(10) EmpAreaCode SMALLINT (6) VendorID INT(11) Vend Name VARCHAR(25) VendSteetAddress VARCHAR(50) Venda ty VARCHAR(30) Vend State VARCHAR( 2) vend ZipCode VARCHAR(10) ◇ EmpPhoneNumber VARCHAR(8) product_vendors ProductNum ber INT (11) t VendorID INT(11) ◆ WholesalePrice DECHAL(15,2) DaysToDeliver SMALLINT(6) product vendors ▼ EmpBirthDate DATE vend PhoneNumber VARGAR(15) ◇ VendFaxNumber VARCHAR(15) Vend WebPage TEXT ◇ Vend EMai!Address VARCHAR(50)

Answer 1

select v.vendorname, count(o.productnumber) from product_vendors as o
inner join vendors as v on v.vendorid = o.vendorid
having max(count(o.productnumber))