Question

SQL statement needed:

List the CompanyName of the suppliers and the number of products they supply if they supply more 3 or more products to only 1 product category.

For the Order Details table in the northwind database, you will need to enclose the name of the table in quotes like this `Order Details` or double click the name of the table in the left hand list of schemas to get the table name to show up appropriately.

Answer 1

Solution:

There are 3 tables from the Northwind Database that will be required to display the corresponding output for the query in question.

List the CompanyName of the suppliers and the number of products they supply if they supply more 3 or more products to only 1 product category.

Namely,

1. Products

2. Categories

3. Suppliers

Note:

I don’t think Order Details table is required for this instance.

SQL STATEMENT

-- List of Suppliers who supply 3 or more Products in the Same Category –

SELECT Suppliers.CompanyName as "Supplier", Categories.CategoryName as "Category", COUNT(Products.ProductID) AS "Products"

FROM Products, Categories, Suppliers

WHERE products.CategoryID = Categories.CategoryID

AND Products.SupplierID = Suppliers.SupplierID

GROUP BY Categories.CategoryName, Suppliers.CompanyName

HAVING COUNT(Products.ProductID)>=3;

-- Display Data without CatergoryName -(as per Question)--

SELECT Suppliers.CompanyName as "Supplier", COUNT(Products.ProductID) AS "Products"

FROM Products, Categories, Suppliers

WHERE products.CategoryID = Categories.CategoryID

AND Products.SupplierID = Suppliers.SupplierID

GROUP BY Categories.CategoryName, Suppliers.CompanyName

HAVING COUNT(Products.ProductID)>=3;

OUTPUT SCREENSHOT

Quick Launch (Ctrl+Q) File SQLQuery2.sql - LAPTOP-5818E4A/\SQLEXPRESS.Northwind (LAPTOP-5818E4AJ\arunv (56))* - Microsoft SQL Server Management Studio Edit View Query Project Tools Window Help New Query OR MR MAMA constraint Northwind Execute ✓ O gru 29 23 1 LAPTOP-5818E4AJ\S....hwind - Diagram_0* SQLQuery2.sql - LA...818E4AJ\arunv (56))* + x List of Suppliers who supply 3 or more Products in the Same Category SELECT Suppliers. Company Name as "Supplier", Categories. CategoryName as "Category", COUNT (Products ProductID AS "Products" FROM Products, Categories, Suppliers WHERE products. CategoryID = Categories CategoryID AND Products. SupplierID = Suppliers. SupplierID GROUP BY Categories CategoryName, Suppliers. Company Name HAVING COUNT(Products. ProductID) >=3; + + Display Data without CatergoryName -(as per Question) -- SELECT Suppliers. Company Name as "Supplier", COUNT (Products ProductID AS "Products" FROM Products, Categories, Suppliers WHERE products. CategoryID = Categories CategoryID AND Products. SupplierID = Suppliers. SupplierID GROUP BY Categories CategoryName, Suppliers.CompanyName HAVING COUNT(Products ProductID) >=3;| Object Explorer 1 x Connect i Tyth LAPTOP-5818E4AJ\SQLEXPRESS (SA Databases # System Databases Database Snapshots Covid19_GH_DB # Exam hospital HR Northwind Database Diagrams Tables + System Tables File Tables # External Tables Graph Tables dbo.Categories #dbo.CustomerCustome + dbo.CustomerDemogr # B dbo.Customers + dbo. Employees # B dbo.Employee Territori dbo.Order Details #dbo.Orders #dbo.Products # ! dbo.Region # 3 dbo.Shippers #dbo.Suppliers dbo. Territories Views # External Resources + Synonyms # Programmability Service Broker #Storage Security < Products 3 1 3 100 % Results Bi Messages Supplier Bigfoot Breweries 2 Formaggi Fortini s.r.l. 3 Heli Süßwaren GmbH & Co. KG 4 New Orleans Cajun Delights 5 Norske Meierier 6 Specialty Biscuits, Ltd. 7 Svensk Sjöföda AB 3 Category Beverages Dairy Products Confections Condiments Dairy Products Confections Seafood 4 3 4 3 Products 3 1 2 3 3 3 Supplier Bigfoot Breweries Formaggi Fortini s.r.l. Heli Süßwaren GmbH & Co. KG New Orleans Cajun Delights Norske Meierier Specialty Biscuits, Ltd. Svensk Sjöföda AB 4 4 5 3 6 4 7 3 Query executed successfully. LAPTOP-5818E4A/\SQLEXPRESS ... LAPTOP-5818E4AJ\arunv ... Northwind 00:00:00 14 rows O Ready Ln 15 Col 37 Ch 37 INS O Type here to search ; ב (1) ENG 9:07 AM 8/1/2020

***Hope this is what you were looking for.

***Kindly get back to me for clarifications if any.