SELECT predicate WHERE and HAVING

1. Categories with Diverse Product Pricing and Specific Stock Levels

SQL Server Query 1

            
 SELECT  

     C.CategoryName,  

     MAX(P.UnitPrice) AS MaxUnitPrice,  

     MIN(P.UnitPrice) AS MinUnitPrice,  

     SUM(CASE WHEN P.Discontinued = 1 THEN P.UnitsInStock ELSE 0 END) AS TotalDiscontinuedUnits  

 FROM Categories C  

 JOIN Products P ON C.CategoryID = P.CategoryID  

 GROUP BY C.CategoryName  

 HAVING  

     (MAX(P.UnitPrice) - MIN(P.UnitPrice)) > 50 -- Price range diversity  

     AND  

     SUM(CASE WHEN P.Discontinued = 1 THEN P.UnitsInStock ELSE 0 END) < 10; -- Low stock of discontinued items  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column("c.CategoryName")  

     .Column(new MAX(new Column("P.UnitPrice")), "MaxUnitPrice")  

     .Column(new MIN(new Column("P.UnitPrice")), "MinUnitPrice")  

     .Column(new SUM(new CASE()  

         .When(new Column("P.Discontinued").Equale(1)).Then(new Column("P.UnitsInStock")).Else(0)), "TotalDiscontinuedUnits")  

     .From("Categories", "C")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Products", "P")  

             .On(new Column("C.CategoryID").Equale(new Column("P.CategoryID")))  

     })  

     .GroupBy(new GroupBy("C.CategoryName"))  

     .Having(new Having(  

         new ColumnArithmatic().StartBracket(new MAX(new Column("P.UnitPrice")))  

              .SUBTRACT(new MIN(new Column("P.UnitPrice"))).EndBracket().GreaterThan(50)  

         .AND(new SUM(new CASE()  

             .When(new Column("P.Discontinued").Equale(1)).Then(new Column("P.UnitsInStock")).Else(0)  

         ).LessThan(10))  

     ))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.CategoryName,
       MAX(P.UnitPrice) AS MaxUnitPrice,
       MIN(P.UnitPrice) AS MinUnitPrice,
       SUM(CASE WHEN P.Discontinued = @pMAIN_2507200206539624510 THEN P.UnitsInStock ELSE @pMAIN_2507200206539624511 END) AS TotalDiscontinuedUnits
FROM Categories AS C
     INNER JOIN
     Products AS P
     ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryName
HAVING (MAX(P.UnitPrice) - MIN(P.UnitPrice)) > @pMAIN_2507200206539624512
       AND SUM(CASE WHEN P.Discontinued = @pMAIN_2507200206539624513 THEN P.UnitsInStock ELSE @pMAIN_2507200206539624514 END) < @pMAIN_2507200206539624515;


            
        

Parameters (If used)

Query Results 1:

2. Customers with High-Value Orders AND Consistent Ordering Patterns

SQL Server Query 2

            
 SELECT  

     C.CompanyName AS CustomerName,  

     COUNT(DISTINCT O.OrderID) AS NumberOfOrders,  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS TotalOrderValue,  

     COUNT(DISTINCT YEAR(O.OrderDate)) AS DistinctOrderYears  

 FROM Customers C  

 JOIN Orders O ON C.CustomerID = O.CustomerID  

 JOIN [Order Details] OD ON O.OrderID = OD.OrderID  

 GROUP BY C.CompanyName  

 HAVING  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) > 100000 -- High total order value  

     AND  

     COUNT(DISTINCT YEAR(O.OrderDate)) >= 3; -- Ordered in at least 3 different years  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column("C.CompanyName", "CustomerName")  

     .Column(new COUNT(new Column("O.OrderID"), true), "NumberOfOrders")  

     .Column(new SUM(new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice")  

         .MULTIPLY().StartBracket(1).SUBTRACT("OD.Discount").EndBracket()), "TotalOrderValue")  

     .Column(new COUNT(new YEAR(new Column("O.OrderDate")), true), "DistinctOrderYears")  

     .From("Customers", "C")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Orders", "O")  

             .On(new Column("C.CustomerID").Equale(new Column("O.CustomerID"))),  

         new INNERJOIN().TableName("[Order Details]", "OD")  

             .On(new Column("O.OrderID").Equale(new Column("OD.OrderID")))  

     })  

     .GroupBy(new GroupBy("C.CompanyName"))  

     .Having(new Having(new SUM(new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice")  

         .MULTIPLY().StartBracket(1).SUBTRACT("OD.Discount").EndBracket()).GreaterThan(100000)  

         .AND(new COUNT(new YEAR(new Column("O.OrderDate")), true).GreaterThanOrEqualeTo(3))  

     ))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT C.CompanyName AS CustomerName,
       COUNT(DISTINCT O.OrderID) AS NumberOfOrders,
       SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200206539798580 - OD.Discount)) AS TotalOrderValue,
       COUNT(DISTINCT YEAR(O.OrderDate)) AS DistinctOrderYears
FROM Customers AS C
     INNER JOIN
     Orders AS O
     ON C.CustomerID = O.CustomerID
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
GROUP BY C.CompanyName
HAVING SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200206539798581 - OD.Discount)) > @pMAIN_2507200206539798582
       AND COUNT(DISTINCT YEAR(O.OrderDate)) >= @pMAIN_2507200206539798583;


            
        

Parameters (If used)

Query Results 2:

3. Suppliers with Products in Multiple Categories AND High Reorder Activity

SQL Server Query 3

            
 SELECT  

     S.CompanyName AS SupplierName,  

     COUNT(DISTINCT C.CategoryID) AS DistinctCategoriesSupplied,  

     SUM(CASE WHEN P.ReorderLevel > 5 THEN P.UnitsOnOrder ELSE 0 END) AS TotalHighReorderUnitsOnOrder  

 FROM Suppliers S  

 JOIN Products P ON S.SupplierID = P.SupplierID  

 JOIN Categories C ON P.CategoryID = C.CategoryID  

 GROUP BY S.CompanyName  

 HAVING  

     COUNT(DISTINCT C.CategoryID) >= 2 -- Products in at least 2 categories  

     AND  

     SUM(CASE WHEN P.ReorderLevel > 5 THEN P.UnitsOnOrder ELSE 0 END) >= 100; -- High units on order for reorderable items  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Column("S.CompanyName", "SupplierName")  

     .Column(new COUNT(new Column("C.CategoryID"), true), "DistinctCategoriesSupplied")  

     .Column(new SUM(new CASE()  

         .When(new Column("P.ReorderLevel").GreaterThan(5)).Then(new Column("P.UnitsOnOrder")).Else(0)), "TotalHighReorderUnitsOnOrder")  

     .From("Suppliers", "S")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Products", "P")  

             .On(new Column("S.SupplierID").Equale(new Column("P.SupplierID"))),  

         new INNERJOIN().TableName("Categories", "C")  

             .On(new Column("P.CategoryID").Equale(new Column("C.CategoryID")))  

     })  

     .GroupBy(new GroupBy("S.CompanyName"))  

     .Having(new Having(new COUNT(new Column("C.CategoryID"), true).GreaterThanOrEqualeTo(2))  

         .AND(new SUM(  

             new CASE()  

             .When(new Column("P.ReorderLevel").GreaterThan(5)).Then(new Column("P.UnitsOnOrder")).Else(0)  

         ).GreaterThanOrEqualeTo(100)))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT S.CompanyName AS SupplierName,
       COUNT(DISTINCT C.CategoryID) AS DistinctCategoriesSupplied,
       SUM(CASE WHEN P.ReorderLevel > @pMAIN_2507200206540274900 THEN P.UnitsOnOrder ELSE @pMAIN_2507200206540274901 END) AS TotalHighReorderUnitsOnOrder
FROM Suppliers AS S
     INNER JOIN
     Products AS P
     ON S.SupplierID = P.SupplierID
     INNER JOIN
     Categories AS C
     ON P.CategoryID = C.CategoryID
GROUP BY S.CompanyName
HAVING COUNT(DISTINCT C.CategoryID) >= @pMAIN_2507200206540274902
       AND SUM(CASE WHEN P.ReorderLevel > @pMAIN_2507200206540274903 THEN P.UnitsOnOrder ELSE @pMAIN_2507200206540274904 END) >= @pMAIN_2507200206540274905;


            
        

Parameters (If used)

Query Results 3:

4. Products with Significant Order Volume and Average Discount Above a Threshold

SQL Server Query 4

            
 SELECT  

     P.ProductName,  

     COUNT(OD.OrderID) AS TotalOrderDetailsEntries, -- How many times this product appeared in an order  

     AVG(OD.Discount) AS AverageDiscountForProduct  

 FROM Products P  

 JOIN [Order Details] OD ON P.ProductID = OD.ProductID  

 GROUP BY P.ProductName  

 HAVING  

     COUNT(OD.OrderID) >= 50 -- Product has high order volume  

     AND  

     AVG(OD.Discount) > (SELECT AVG(Discount) FROM [Order Details]); -- Product's average discount is higher than overall average  

   

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select()  

     .Column("P.ProductName")  

     .Column(new COUNT(new Column("OD.OrderID")),"TotalOrderDetailsEntries")  

     .Column(new AVG(new Column("OD.Discount")), "AverageDiscountForProduct")  

     .From("Products", "P")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("[Order Details]","OD")  

             .On(new Column("P.ProductID").Equale(new Column("OD.ProductID")))  

     })  

     .GroupBy(new GroupBy("P.ProductName"))  

     .Having(new Having(new COUNT(new Column("OD.OrderID")).GreaterThanOrEqualeTo(50))  

         .AND(new AVG(new Column("OD.Discount")).GreaterThan(new SqlQueryBuilder().Select()  

             .Column(new AVG(new Column("Discount"))).From("[Order Details]"))))  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT P.ProductName,
       COUNT(OD.OrderID) AS TotalOrderDetailsEntries,
       AVG(OD.Discount) AS AverageDiscountForProduct
FROM Products AS P
     INNER JOIN
     [Order Details] AS OD
     ON P.ProductID = OD.ProductID
GROUP BY P.ProductName
HAVING COUNT(OD.OrderID) >= @pMAIN_2507200206540378590
       AND AVG(OD.Discount) > (SELECT AVG(Discount)
                               FROM [Order Details]);


            
        

Parameters (If used)

Query Results 4: