Queries for Products

1. Products that are in the 'Beverages' category AND have a UnitsInStock greater than 50, OR are in the 'Confections' category AND have a UnitPrice less than 15, OR are discontinued AND have UnitsOnOrder greater than 0.

SQL Server Query 1

            
 SELECT  

     p.ProductID,  

     p.ProductName,  

     c.CategoryName,  

     p.UnitsInStock,  

     p.UnitPrice,  

     p.Discontinued,  

     p.UnitsOnOrder  

 FROM Products p  

 JOIN Categories c ON p.CategoryID = c.CategoryID  

 WHERE  

     (c.CategoryName = 'Beverages' AND p.UnitsInStock > 50)  

     OR  

     (c.CategoryName = 'Confections' AND p.UnitPrice < 15)  

     OR  

     (p.Discontinued = 1 AND p.UnitsOnOrder > 0);  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("p.ProductID"," p.ProductName"," c.CategoryName"," p.UnitsInStock"," p.UnitPrice"," p.Discontinued"," p.UnitsOnOrder")  

     .From("Products","p")  

     .Join(new List<IJoin>()  

     {  

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

             .On(new Column("p.CategoryID").Equale(new Column("c.CategoryID")))  

     })  

     .Where(  

         new Where()  

             .StartBracket(new Column("c.CategoryName").Equale("Beverages")).AND(new Column("c.UnitsInStock").GreaterThan(50)).EndBracket()  

             .OR().StartBracket(new Column("c.CategoryName").Equale("Confections")).AND(new Column("p.UnitPrice").LessThan(15)).EndBracket()  

             .OR().StartBracket(new Column("p.Discontinued").Equale(1)).AND(new Column("p.UnitsOnOrder").GreaterThan(0)).EndBracket()  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT p.ProductID,
       p.ProductName,
       c.CategoryName,
       p.UnitsInStock,
       p.UnitPrice,
       p.Discontinued,
       p.UnitsOnOrder
FROM Products AS p
     INNER JOIN
     Categories AS c
     ON p.CategoryID = c.CategoryID
WHERE (c.CategoryName = @pMAIN_2507192104275867070
       AND p.UnitsInStock > @pMAIN_2507192104275867071)
      OR (c.CategoryName = @pMAIN_2507192104275867072
          AND p.UnitPrice < @pMAIN_2507192104275867073)
      OR (p.Discontinued = @pMAIN_2507192104275867074
          AND p.UnitsOnOrder > @pMAIN_2507192104275867075);


            
        

Parameters (If used)

Query Results 1:

2. Find Products That Have Been Ordered by Customers from 'Germany' AND 'France', but have a UnitsInStock less than the average UnitsInStock for their respective category, AND have not been reordered in the last 180 days.

SQL Server Query 2

            
 SELECT  

     p.ProductID,  

     p.ProductName,  

     p.CategoryID,  

     p.UnitsInStock  

 FROM Products p  

 WHERE  

     -- Condition 1: Product ordered by a customer from 'Germany'  

     EXISTS (  

         SELECT 1  

         FROM [Order Details] od  

         JOIN Orders o ON od.OrderID = o.OrderID  

         JOIN Customers c ON o.CustomerID = c.CustomerID  

         WHERE od.ProductID = p.ProductID  

           AND c.Country = 'Germany'  

     )  

     AND  

     -- Condition 2: Product ordered by a customer from 'France'  

     EXISTS (  

         SELECT 1  

         FROM [Order Details] od2  

         JOIN Orders o2 ON od2.OrderID = o2.OrderID  

         JOIN Customers c2 ON o2.CustomerID = c2.CustomerID  

         WHERE od2.ProductID = p.ProductID  

           AND c2.Country = 'France'  

     )  

     AND  

     -- Condition 3: UnitsInStock is less than the average UnitsInStock for its category  

     p.UnitsInStock < (SELECT AVG(p2.UnitsInStock) FROM Products p2 WHERE p2.CategoryID = p.CategoryID)  

     AND  

     -- Condition 4: Product has not been reordered (no orders containing this product) in the last 180 days  

     NOT EXISTS (  

         SELECT 1  

         FROM [Order Details] od3  

         JOIN Orders o3 ON od3.OrderID = o3.OrderID  

         WHERE od3.ProductID = p.ProductID  

           AND o3.OrderDate >= DATEADD(day, -180, GETDATE())  

     );  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("p.ProductID", "p.ProductName", "p.CategoryID", "p.UnitsInStock")  

     .From("Products", "p")  

     .Where(  

         new Where(new EXISTS(new SqlQueryBuilder().Select().ColumnWithValue(1, "Col1")  

                 .From("[Order Details]","od")  

                 .Join(new List<IJoin>()   

                 {  

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

                         .On(new Column("od.OrderID").Equale(new Column("o.OrderID"))),  

                     new INNERJOIN().TableName("Customers","c")  

                         .On(new Column("o.CustomerID").Equale(new Column("c.CustomerID")))  

                 })  

                 .Where(new Where(new Column("od.ProductID").Equale(new Column("p.ProductID")))  

                     .AND(new Column("c.Country").Equale("Germany")))  

             )).AND(new EXISTS(  

                 new SqlQueryBuilder().Select().ColumnWithValue(1,"Col1")  

                     .From("[Order Details]","od2")  

                     .Join(new List<IJoin>()   

                     {  

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

                             .On(new Column("od2.OrderID").Equale(new Column("o2.OrderID"))),  

                         new INNERJOIN().TableName("Customers","c2")  

                             .On(new Column("o2.CustomerID").Equale(new Column("c2.CustomerID"))),  

                     })  

                     .Where(new Where(new Column("od2.ProductID").Equale(new Column("p.ProductID")))  

                         .AND(new Column("c2.Country").Equale("France")))  

             )).AND(  

                 new Column("p.UnitsInStock").LessThan(  

                     new SqlQueryBuilder().Select().Column(new AVG("p2.UnitsInStock"))  

                         .From("Products","p2").Where(new Where(  

                             new Column("p2.CategoryID").Equale(new Column("p.CategoryID"))      

                         ))  

                 )  

             ).AND(new NOT_EXISTS(  

                 new SqlQueryBuilder().Select().ColumnWithValue(1, "col1")  

                     .From("[Order Details]","od3")  

                     .Join(new List<IJoin>()   

                     {  

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

                             .On(new Column("od3.OrderID").Equale(new Column("o3.OrderID")))  

                     })  

                     .Where(new Where(new Column("od3.ProductID").Equale(new Column("p.ProductID")))  

                         .AND(new Column("o3.OrderDate").GreaterThanOrEqualeTo(new DATEADD(SqlDateInterval.day, -180, new GETDATE())))  

                     )  

             ))  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT p.ProductID,
       p.ProductName,
       p.CategoryID,
       p.UnitsInStock
FROM Products AS p
WHERE EXISTS (SELECT @pMAIN_2507192104276041450 AS Col1
              FROM [Order Details] AS od
                   INNER JOIN
                   Orders AS o
                   ON od.OrderID = o.OrderID
                   INNER JOIN
                   Customers AS c
                   ON o.CustomerID = c.CustomerID
              WHERE od.ProductID = p.ProductID
                    AND c.Country = @pMAIN_2507192104276041451)
      AND EXISTS (SELECT @pMAIN_2507192104276041452 AS Col1
                  FROM [Order Details] AS od2
                       INNER JOIN
                       Orders AS o2
                       ON od2.OrderID = o2.OrderID
                       INNER JOIN
                       Customers AS c2
                       ON o2.CustomerID = c2.CustomerID
                  WHERE od2.ProductID = p.ProductID
                        AND c2.Country = @pMAIN_2507192104276041453)
      AND p.UnitsInStock < (SELECT AVG(p2.UnitsInStock) AS col1
                            FROM Products AS p2
                            WHERE p2.CategoryID = p.CategoryID)
      AND NOT EXISTS (SELECT @pMAIN_2507192104276041454 AS col1
                      FROM [Order Details] AS od3
                           INNER JOIN
                           Orders AS o3
                           ON od3.OrderID = o3.OrderID
                      WHERE od3.ProductID = p.ProductID
                            AND o3.OrderDate >= DATEADD(day, @pMAIN_2507192104276041455, GETDATE()));


            
        

Parameters (If used)

Query Results 2:

3. Identify Products that have been ordered in quantities larger than 50 in at least two separate orders, AND their UnitsInStock is less than their UnitsOnOrder, AND their ProductName contains exactly two 'o' characters (case-insensitive).

SQL Server Query 3

            
 SELECT  

     P.ProductID,  

     P.ProductName,  

     P.UnitsInStock,  

     P.UnitsOnOrder  

 FROM Products P  

 WHERE  

     -- Condition 1: Has been ordered in quantities > 50 in at least two separate orders  

     (  

         SELECT COUNT(DISTINCT OD.OrderID)  

         FROM [Order Details] OD  

         WHERE OD.ProductID = P.ProductID  

           AND OD.Quantity > 50  

     ) >= 2  

     AND  

     -- Condition 2: UnitsInStock is less than UnitsOnOrder  

     P.UnitsInStock < P.UnitsOnOrder  

     AND  

     -- Condition 3: ProductName contains exactly two 'o' characters (case-insensitive)  

     (LEN(LOWER(P.ProductName)) - LEN(REPLACE(LOWER(P.ProductName), 'o', ''))) = 2;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Columns("P.ProductID","P.ProductName","P.UnitsInStock","P.UnitsOnOrder")  

     .From("Products", "P")  

     .Where(  

         new Where().StartBracket(new SqlQueryBuilder().Select()  

             .Column(new COUNT(new Column("OD.OrderID"), true), "Col1")  

             .From("[Order Details]","OD")  

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

                  .AND(new Column("OD.Quantity").GreaterThan(50))).GreaterThanOrEqualeTo(2)  

         ).EndBracket()  

         .AND(new Column("P.UnitsInStock").LessThan(new Column("P.UnitsOnOrder")))  

         .AND(new ColumnArithmatic().StartBracket(new LEN(new LOWER(new Column("P.ProductName"))))  

             .SUBTRACT(new LEN(new REPLACE(new LOWER(new Column("P.ProductName")), "o", ""))).EndBracket().Equale(2))  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT P.ProductID,
       P.ProductName,
       P.UnitsInStock,
       P.UnitsOnOrder
FROM Products AS P
WHERE ((SELECT COUNT(DISTINCT OD.OrderID) AS Col1
        FROM [Order Details] AS OD
        WHERE OD.ProductID = P.ProductID
              AND OD.Quantity > @pMAIN_2507192104276587770) >= @pMAIN_2507192104276587771)
      AND P.UnitsInStock < P.UnitsOnOrder
      AND (LEN(LOWER(P.ProductName)) - LEN(REPLACE(LOWER(P.ProductName), @pMAIN_2507192104276587772, @pMAIN_2507192104276587773))) = @pMAIN_2507192104276587774;


            
        

Parameters (If used)

Query Results 3:

4. Snapshot of Monthly Product Sales with Category Information.

SQL Server Query 4

            
 SELECT  

     p.ProductID,  

     p.ProductName,  

     c.CategoryName,  

     YEAR(o.OrderDate) AS SaleYear,  

     MONTH(o.OrderDate) AS SaleMonth,  

     SUM(od.Quantity) AS TotalQuantitySold,  

     SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue  

 INTO MonthlyProductCategorySalesSnapshot -- The new table will be created here  

 FROM Products p  

 JOIN Categories c ON p.CategoryID = c.CategoryID  

 JOIN [Order Details] od ON p.ProductID = od.ProductID  

 JOIN Orders o ON od.OrderID = o.OrderID  

 GROUP BY  

     p.ProductID,  

     p.ProductName,  

     c.CategoryName,  

     YEAR(o.OrderDate),  

     MONTH(o.OrderDate)  

 HAVING SUM(od.Quantity) >= 50; -- Filter for products ordered at least 50 times  

   

 -- Now, you can query your new table  

 SELECT *  

 FROM MonthlyProductCategorySalesSnapshot  

 ORDER BY ProductName, SaleYear, SaleMonth;  

   

 -- Clean up the new table (useful if you're running this multiple times)  

 DROP TABLE IF EXISTS MonthlyProductCategorySalesSnapshot;  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select()  

     .Columns("p.ProductID","p.ProductName","c.CategoryName")  

     .Column(new YEAR(new Column("o.OrderDate")), "SaleYear")  

     .Column(new MONTH(new Column("o.OrderDate")), "SaleMonth")  

     .Column(new SUM(new Column("od.Quantity")), "TotalQuantitySold")  

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

         .StartBracket(1).SUBTRACT(new Column("od.Discount")).EndBracket()), "TotalRevenue")  

     .INTO(new Table("MonthlyProductCategorySalesSnapshot"))  

     .From("Products", "p")  

     .Join(new List<IJoin>()   

     {  

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

             .On(new Column("p.CategoryID").Equale(new Column("c.CategoryID"))),  

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

             .On(new Column("p.ProductID").Equale(new Column("od.ProductID"))),  

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

             .On(new Column("od.OrderID").Equale(new Column("o.OrderID")))  

     })  

     .GroupBy(new GroupBy(new Column("p.ProductID"), new Column("p.ProductName"), new Column("c.CategoryName"),  

         new YEAR(new Column("o.OrderDate")), new MONTH(new Column("o.OrderDate"))))  

     .Having(new Having(new SUM(new Column("od.Quantity")).GreaterThanOrEqualeTo(50)))  

 	.Select().Star().From("MonthlyProductCategorySalesSnapshot")  

 	.OrderBy(new OrderBy().SetColumnAscending("ProductName").SetColumnAscending("SaleYear").SetColumnAscending("SaleMonth"))  

 	.DropTable(new Table("MonthlyProductCategorySalesSnapshot"), true)  

 	.Build();  

            
        

Query build by SqlQueryBuilder 4

            
 SELECT p.ProductID,         p.ProductName,         c.CategoryName,         YEAR(o.OrderDate) AS SaleYear,         MONTH(o.OrderDate) AS SaleMonth,         SUM(od.Quantity) AS TotalQuantitySold,         SUM(od.Quantity * od.UnitPrice * (@pMAIN_2505262233481230440 - od.Discount)) AS TotalRevenue  INTO MonthlyProductCategorySalesSnapshot  FROM Products AS p       INNER JOIN       Categories AS c       ON p.CategoryID = c.CategoryID       INNER JOIN       [Order Details] AS od       ON p.ProductID = od.ProductID       INNER JOIN       Orders AS o       ON od.OrderID = o.OrderID  GROUP BY p.ProductID, p.ProductName, c.CategoryName, YEAR(o.OrderDate), MONTH(o.OrderDate)  HAVING SUM(od.Quantity) >= @pMAIN_2505262233481230441;  SELECT *  FROM MonthlyProductCategorySalesSnapshot  ORDER BY ProductName ASC, SaleYear ASC, SaleMonth ASC;SELECT p.ProductID,         p.ProductName,         c.CategoryName,         YEAR(o.OrderDate) AS SaleYear,         MONTH(o.OrderDate) AS SaleMonth,         SUM(od.Quantity) AS TotalQuantitySold,         SUM(od.Quantity * od.UnitPrice * (@pMAIN_2505262233481230440 - od.Discount)) AS TotalRevenue  INTO MonthlyProductCategorySalesSnapshot  FROM Products AS p       INNER JOIN       Categories AS c       ON p.CategoryID = c.CategoryID       INNER JOIN       [Order Details] AS od       ON p.ProductID = od.ProductID       INNER JOIN       Orders AS o       ON od.OrderID = o.OrderID  GROUP BY p.ProductID, p.ProductName, c.CategoryName, YEAR(o.OrderDate), MONTH(o.OrderDate)  HAVING SUM(od.Quantity) >= @pMAIN_2505262233481230441;    SELECT *  FROM MonthlyProductCategorySalesSnapshot  ORDER BY ProductName ASC, SaleYear ASC, SaleMonth ASC;  DROP TABLE IF EXISTS MonthlyProductCategorySalesSnapshot   
            
        

Parameters (If used)

Query Results 4:

5. Specific Product Information with Supplier Contacts for Reporting.

SQL Server Query 5

            
 SELECT  

     p.ProductID,  

     p.ProductName,  

     p.UnitPrice,  

     p.UnitsInStock,  

     p.UnitsOnOrder,  

     p.ReorderLevel,  

     s.CompanyName AS SupplierName,  

     s.ContactName AS SupplierContact,  

     s.Phone AS SupplierPhone,  

     s.HomePage AS SupplierHomePage  

 INTO HighStockProductsWithSupplierContacts  

 FROM Products p  

 JOIN Suppliers s ON p.SupplierID = s.SupplierID  

 WHERE  

     p.UnitsInStock > 50 -- Products with high stock  

     AND p.ReorderLevel >= 10; -- And a decent reorder level  

   

 -- Query the specific product info  

 SELECT *  

 FROM HighStockProductsWithSupplierContacts  

 ORDER BY ProductName;  

   

 -- Clean up  

 DROP TABLE IF EXISTS HighStockProductsWithSupplierContacts;  

            
        

Create SQL query with SqlQueryBuilder 5

            
 var (sql5, parameters5) = new SqlQueryBuilder()  

     .Select()  

     .Columns("p.ProductID","p.ProductName","p.UnitPrice","p.UnitsInStock","p.UnitsOnOrder","p.ReorderLevel")  

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

     .Column("s.ContactName", "SupplierContact")  

     .Column("s.Phone", "SupplierPhone")  

     .Column("s.HomePage", "SupplierHomePage")  

     .INTO(new Table("HighStockProductsWithSupplierContacts"))  

     .From("Products", "p")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Suppliers", "s")  

             .On(new Column("p.SupplierID").Equale(new Column("s.SupplierID")))  

     })  

     .Where(new Where(new Column("p.UnitsInStock").GreaterThan(50))  

         .AND(new Column("p.ReorderLevel").GreaterThanOrEqualeTo(10))  

     )  

     .Select().Star().From("HighStockProductsWithSupplierContacts")  

     .OrderBy(new OrderBy().SetColumnAscending("ProductName"))  

     .DropTable(new Table("HighStockProductsWithSupplierContacts"), true)  

     .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT p.ProductID,
       p.ProductName,
       p.UnitPrice,
       p.UnitsInStock,
       p.UnitsOnOrder,
       p.ReorderLevel,
       s.CompanyName AS SupplierName,
       s.ContactName AS SupplierContact,
       s.Phone AS SupplierPhone,
       s.HomePage AS SupplierHomePage
INTO HighStockProductsWithSupplierContacts
FROM Products AS p
     INNER JOIN
     Suppliers AS s
     ON p.SupplierID = s.SupplierID
WHERE p.UnitsInStock > @pMAIN_2507192104276993680
      AND p.ReorderLevel >= @pMAIN_2507192104276993681;

SELECT *
FROM HighStockProductsWithSupplierContacts
ORDER BY ProductName ASC;

DROP TABLE IF EXISTS HighStockProductsWithSupplierContacts
            
        

Parameters (If used)

Query Results 5: