Queries for Customers

1. Customers who are either from 'USA' and have placed more than 5 orders, OR are from 'Canada' and have placed any order, OR are from 'Mexico' and have a Fax number.

SQL Server Query 1

            
 SELECT  

     c.CustomerID,  

     c.CompanyName,  

     c.Country,  

     c.Fax  

 FROM Customers c  

 WHERE  

     (c.Country = 'USA' AND (SELECT COUNT(o.OrderID) FROM Orders o WHERE o.CustomerID = c.CustomerID) > 5)  

     OR  

     (c.Country = 'Canada' AND EXISTS (SELECT 1 FROM Orders o2 WHERE o2.CustomerID = c.CustomerID))  

     OR  

     (c.Country = 'Mexico' AND c.Fax IS NOT NULL);  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("c.CustomerID"," c.CompanyName"," c.Country"," c.Fax")  

     .From("Customers", "c")  

     .Where(  

         new Where()  

             .StartBracket(new Column("c.Country").Equale("USA"))  

                 .AND(  

                     new SqlQueryBuilder().Select().Column(new COUNT(new Column("o.OrderID"))).From("Orders","o")  

                         .Where(new Where(new Column("o.CustomerID").Equale(new Column("c.CustomerID")))).GreaterThan(5)  

             ).EndBracket().OR()  

             .StartBracket(new Column("c.Country").Equale("Canada"))  

                 .AND(new EXISTS(new SqlQueryBuilder().Select().ColumnWithValue("1","Col1").From("Orders","o2")  

                     .Where(new Where(new Column("o2.CustomerID").Equale(new Column("c.CustomerID")))))  

             ).EndBracket().OR()  

             .StartBracket(new Column("c.Country").Equale("Mexico")).AND(new IS_NOT_NULL(new Column("c.Fax"))).EndBracket()  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.CustomerID,
       c.CompanyName,
       c.Country,
       c.Fax
FROM Customers AS c
WHERE (c.Country = @pMAIN_2507192051423347760
       AND (SELECT COUNT(o.OrderID)
            FROM Orders AS o
            WHERE o.CustomerID = c.CustomerID) > @pMAIN_2507192051423347761)
      OR (c.Country = @pMAIN_2507192051423347762
          AND EXISTS (SELECT @pMAIN_2507192051423347763 AS Col1
                      FROM Orders AS o2
                      WHERE o2.CustomerID = c.CustomerID))
      OR (c.Country = @pMAIN_2507192051423347764
          AND c.Fax IS NOT NULL);


            
        

Parameters (If used)

Query Results 1:

2. Find Customers Who Have Placed Orders BUT NOT Ordered 'Chai' in the Last 6 Months, AND have a 'PostalCode' that is purely numeric and starts with '9'

SQL Server Query 2

            
 SELECT  

     c.CustomerID,  

     c.CompanyName,  

     c.ContactName,  

     c.ContactTitle,  

     c.PostalCode  

 FROM Customers c  

 WHERE      

     -- Condition 1: Customer has NOT ordered 'Chai' in the last 6 months  

     NOT EXISTS (  

         SELECT 1  

         FROM Orders o2  

         JOIN [Order Details] od ON o2.OrderID = od.OrderID  

         JOIN Products p ON od.ProductID = p.ProductID  

         WHERE o2.CustomerID = c.CustomerID  

           AND p.ProductName = 'Chai'  

           AND o2.OrderDate >= DATEADD(month, -6, GETDATE()) -- Orders within the last 6 months  

     )  

     AND  

     -- Condition 2: PostalCode is purely numeric (using TRY_CAST for safety) AND starts with '9'  

     TRY_CAST(c.PostalCode AS INT) IS NOT NULL -- Ensures it's numeric  

     AND c.PostalCode LIKE '9%'; -- Starts with '9'  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("c.CustomerID", "c.CompanyName", "c.ContactName", "c.ContactTitle", "c.PostalCode")  

     .From("Customers", "c")  

     .Where(  

         new Where(new NOT_EXISTS(  

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

             .From("Orders","o2")  

             .Join(new List<IJoin>()  

             {  

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

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

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

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

             })  

             .Where(new Where(new Column("o2.CustomerID").Equale(new Column("c.CustomerID")))  

                 .AND(new Column("p.ProductName").Equale("Chai"))  

                 .AND(new Column("o2.OrderDate").GreaterThan(new DATEADD(SqlDateInterval.month, -6, new GETDATE()))))  

         )).AND(new IS_NOT_NULL(new TRY_CAST(new Column("c.PostalCode"), SqlDataType.INT)))  

         .AND(new LIKE(new Column("c.PostalCode"), "9%"))  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT c.CustomerID,
       c.CompanyName,
       c.ContactName,
       c.ContactTitle,
       c.PostalCode
FROM Customers AS c
WHERE NOT EXISTS (SELECT @pMAIN_2507192051423463860 AS col1
                  FROM Orders AS o2
                       INNER JOIN
                       [Order Details] AS od
                       ON o2.OrderID = od.OrderID
                       INNER JOIN
                       Products AS p
                       ON od.ProductID = p.ProductID
                  WHERE o2.CustomerID = c.CustomerID
                        AND p.ProductName = @pMAIN_2507192051423463861
                        AND o2.OrderDate > DATEADD(month, @pMAIN_2507192051423463862, GETDATE()))
      AND TRY_CAST (c.PostalCode AS INT) IS NOT NULL
      AND c.PostalCode LIKE @pMAIN_2507192051423463863;


            
        

Parameters (If used)

Query Results 2:

3. Find the Top 3 Customers by Total Order Value in Each Country, along with their Rank, and show their first and last order dates. If a customer ordered nothing, they should not appear.

SQL Server Query 3

            
 WITH CustomerOrderValue AS (  

     -- Calculate total order value per customer  

     SELECT  

         c.CustomerID,  

         c.CompanyName,  

         c.Country,  

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

         MIN(o.OrderDate) AS FirstOrderDate,  

         MAX(o.OrderDate) AS LastOrderDate  

     FROM Customers c  

     JOIN Orders o ON c.CustomerID = o.CustomerID  

     JOIN [Order Details] od ON o.OrderID = od.OrderID  

     GROUP BY c.CustomerID, c.CompanyName, c.Country  

 ),  

 RankedCustomers AS (  

     -- Rank customers by TotalOrderValue within each country  

     SELECT  

         CustomerID,  

         CompanyName,  

         Country,  

         TotalOrderValue,  

         FirstOrderDate,  

         LastOrderDate,  

         DENSE_RANK() OVER (PARTITION BY Country ORDER BY TotalOrderValue DESC) AS CountryRank  

     FROM CustomerOrderValue  

 )  

 SELECT  

     rc.Country,  

     rc.CountryRank,  

     rc.CompanyName,  

     rc.TotalOrderValue,  

     rc.FirstOrderDate,  

     rc.LastOrderDate  

 FROM RankedCustomers rc  

 WHERE rc.CountryRank <= 3  

 ORDER BY rc.Country, rc.CountryRank;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CustomerOrderValue"), new SqlQueryBuilder()  

         .Select().Columns("c.CustomerID","c.CompanyName","c.Country")  

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

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

         .Column(new MIN(new Column("o.OrderDate")), "FirstOrderDate")  

         .Column(new MAX(new Column("o.OrderDate")), "LastOrderDate")  

         .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(new Column("c.CustomerID"), new Column("c.CompanyName"), new Column("c.Country")))  

     )  

     .WithCTETable(new Table("RankedCustomers"), new SqlQueryBuilder().Select()  

         .Columns("CustomerID","CompanyName","Country","TotalOrderValue","FirstOrderDate","LastOrderDate")  

         .Column(new DENSE_RANK().PARTITION_BY(new Column("Country")).ORDER_BY(new OrderBy().SetColumnDescending("TotalOrderValue")),  

             "CountryRank")  

         .From("CustomerOrderValue")  

     )  

     .Select()  

     .Columns("rc.Country","rc.CountryRank","rc.CompanyName","rc.TotalOrderValue","rc.FirstOrderDate","rc.LastOrderDate")  

     .From("RankedCustomers", "rc")  

     .Where(  

         new Where(new Column("rc.CountryRank").LessThanOrEqualeTo(3))  

     )  

     .OrderBy(new OrderBy().SetColumnAscending("rc.Country").SetColumnAscending("rc.CountryRank"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
WITH CustomerOrderValue
AS (SELECT c.CustomerID,
           c.CompanyName,
           c.Country,
           SUM(od.Quantity * od.UnitPrice * (@pMAIN_2507192051423667390 - od.Discount)) AS TotalOrderValue,
           MIN(o.OrderDate) AS FirstOrderDate,
           MAX(o.OrderDate) AS LastOrderDate
    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.CustomerID, c.CompanyName, c.Country),
 RankedCustomers
AS (SELECT CustomerID,
           CompanyName,
           Country,
           TotalOrderValue,
           FirstOrderDate,
           LastOrderDate,
           DENSE_RANK() OVER (PARTITION BY Country ORDER BY TotalOrderValue DESC) AS CountryRank
    FROM CustomerOrderValue)
SELECT rc.Country,
       rc.CountryRank,
       rc.CompanyName,
       rc.TotalOrderValue,
       rc.FirstOrderDate,
       rc.LastOrderDate
FROM RankedCustomers AS rc
WHERE rc.CountryRank <= @pMAIN_2507192051423667391
ORDER BY rc.Country ASC, rc.CountryRank ASC;


            
        

Parameters (If used)

Query Results 3:

4. List Customers who have ordered products from at least three different suppliers in a single order, AND their ContactTitle is 'Sales Representative' or 'Marketing Manager'.

SQL Server Query 4

            
 SELECT  

     C.CustomerID,  

     C.CompanyName,  

     C.ContactTitle  

 FROM Customers C  

 WHERE  

     C.ContactTitle IN ('Sales Representative', 'Marketing Manager')  

     AND  

     EXISTS (  

         -- Find an order from this customer with products from >= 3 different suppliers  

         SELECT O.OrderID  

         FROM Orders O  

         WHERE O.CustomerID = C.CustomerID  

           AND (  

                 SELECT COUNT(DISTINCT P.SupplierID)  

                 FROM [Order Details] OD  

                 JOIN Products P ON OD.ProductID = P.ProductID  

                 WHERE OD.OrderID = O.OrderID  

           ) >= 3  

     );  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()                  

     .Select()  

     .Columns("C.CustomerID","C.CompanyName","C.ContactTitle")  

     .From("Customers", "C")  

     .Where(  

         new Where(new IN(new Column("C.ContactTitle"), "Sales Representative","Marketing Manager"))  

         .AND(new EXISTS(  

             new SqlQueryBuilder().Select().Column("O.OrderID", "OrderID")  

             .From("Orders","O")  

             .Where(new Where(new Column("O.CustomerID").Equale(new Column("C.CustomerID")))  

                 .AND(new SqlQueryBuilder().Select()  

                     .Column(new COUNT(new Column("P.SupplierID"), true), "col1")  

                     .From("[Order Details] OD")  

                     .Join(new List<IJoin>()   

                     {   

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

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

                     })  

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

                     .GreaterThanOrEqualeTo(3))  

             )  

         ))  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT C.CustomerID,
       C.CompanyName,
       C.ContactTitle
FROM Customers AS C
WHERE C.ContactTitle IN (@pMAIN_2507192051423919330, @pMAIN_2507192051423919331)
      AND EXISTS (SELECT O.OrderID AS OrderID
                  FROM Orders AS O
                  WHERE O.CustomerID = C.CustomerID
                        AND (SELECT COUNT(DISTINCT P.SupplierID) AS col1
                             FROM [Order Details] AS OD
                                  INNER JOIN
                                  Products AS P
                                  ON OD.ProductID = P.ProductID
                             WHERE OD.OrderID = O.OrderID) >= @pMAIN_2507192051423919332);


            
        

Parameters (If used)

Query Results 4:

5. Archiving Inactive Customers with Their Last Order Details.

SQL Server Query 5

            
 SELECT  

     c.CustomerID,  

     c.CompanyName,  

     c.ContactName,  

     c.ContactTitle,  

     c.Address,  

     c.City,  

     c.Region,  

     c.PostalCode,  

     c.Country,  

     c.Phone,  

     c.Fax,  

     o.OrderID AS LastOrderID,  

     o.OrderDate AS LastOrderDate,  

     o.Freight AS LastOrderFreight  

 INTO ArchivedCustomersAndLastOrder -- New table for inactive customers  

 FROM Customers c  

 OUTER APPLY (  

     -- Get the last order for each customer  

     SELECT TOP 1 OrderID, OrderDate, Freight  

     FROM Orders  

     WHERE CustomerID = c.CustomerID  

     ORDER BY OrderDate DESC  

 ) AS o  

 WHERE o.OrderDate < '1997-01-01'; -- Only include customers whose last order was before 1997  

   

 -- View the archived data  

 SELECT *  

 FROM ArchivedCustomersAndLastOrder;  

   

 -- Clean up the new table  

 DROP TABLE IF EXISTS ArchivedCustomersAndLastOrder;  

            
        

Create SQL query with SqlQueryBuilder 5

            
 var (sql5, parameters5) = new SqlQueryBuilder()  

     .Select()  

     .Columns("c.CustomerID","c.CompanyName","c.ContactName","c.ContactTitle","c.Address","c.City","c.Region","c.PostalCode","c.Country","c.Phone","c.Fax")  

     .Column("o.OrderID","LastOrderID")  

     .Column("o.OrderDate","LastOrderDate")  

     .Column("o.Freight", "LastOrderFreight")  

     .INTO(new Table("ArchivedCustomersAndLastOrder"))  

     .From("Customers", "c")  

     .OuterApply(new SqlQueryBuilder().Select()  

         .Top(1)  

         .Columns("OrderID", "OrderDate" ,"Freight")  

         .From("Orders")  

         .Where(new Where(new Column("CustomerID").Equale(new Column("c.CustomerID"))))  

         .OrderBy(new OrderBy().SetColumnDescending("OrderDate"))  

     , "o")  

     .Where(  

         new Where(new Column("o.OrderDate").LessThan("1997-01-01"))  

     )  

     .Select().Star().From("ArchivedCustomersAndLastOrder")  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT c.CustomerID,
       c.CompanyName,
       c.ContactName,
       c.ContactTitle,
       c.Address,
       c.City,
       c.Region,
       c.PostalCode,
       c.Country,
       c.Phone,
       c.Fax,
       o.OrderID AS LastOrderID,
       o.OrderDate AS LastOrderDate,
       o.Freight AS LastOrderFreight
INTO ArchivedCustomersAndLastOrder
FROM Customers AS c OUTER APPLY (SELECT TOP 1 OrderID,
                                              OrderDate,
                                              Freight
                                 FROM Orders
                                 WHERE CustomerID = c.CustomerID
                                 ORDER BY OrderDate DESC) AS o
WHERE o.OrderDate < @pMAIN_2507192051424129220;

SELECT *
FROM ArchivedCustomersAndLastOrder;

DROP TABLE IF EXISTS ArchivedCustomersAndLastOrder; 
            
        

Parameters (If used)

Query Results 5: