Queries for Employees


1. Find Employees Who Have Handled Orders for BOTH 'Dairy Products' AND 'Seafood' categories, but have NOT Handled any orders shipped via 'Federal Shipping' in the last year, AND whose last name contains exactly two 'e's.

SQL Server Query 1

            
 SELECT  
e.EmployeeID,
e.FirstName,
e.LastName
FROM Employees e
WHERE
-- Condition 1: Employee handled orders for 'Dairy Products'
EXISTS (
SELECT 1
FROM Orders o_dp
JOIN [Order Details] od_dp ON o_dp.OrderID = od_dp.OrderID
JOIN Products p_dp ON od_dp.ProductID = p_dp.ProductID
JOIN Categories cat_dp ON p_dp.CategoryID = cat_dp.CategoryID
WHERE o_dp.EmployeeID = e.EmployeeID
AND cat_dp.CategoryName = 'Dairy Products'
)
AND
-- Condition 2: Employee handled orders for 'Seafood'
EXISTS (
SELECT 1
FROM Orders o_sf
JOIN [Order Details] od_sf ON o_sf.OrderID = od_sf.OrderID
JOIN Products p_sf ON od_sf.ProductID = p_sf.ProductID
JOIN Categories cat_sf ON p_sf.CategoryID = cat_sf.CategoryID
WHERE o_sf.EmployeeID = e.EmployeeID
AND cat_sf.CategoryName = 'Seafood'
)
AND
-- Condition 3: Employee has NOT handled any orders shipped via 'Federal Shipping' in the last year
NOT EXISTS (
SELECT 1
FROM Orders o_fs
JOIN Shippers s_fs ON o_fs.ShipVia = s_fs.ShipperID
WHERE o_fs.EmployeeID = e.EmployeeID
AND s_fs.CompanyName = 'Federal Shipping'
AND o_fs.OrderDate >= DATEADD(year, -1, GETDATE())
)
AND
-- Condition 4: Last name contains exactly two 'e's
(LEN(e.LastName) - LEN(REPLACE(e.LastName, 'e', ''))) = 2;

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  
.Select()
.Columns("e.EmployeeID", "e.FirstName", "e.LastName")
.From("Employees", "e")
.Where(
new Where(new EXISTS(
new SqlQueryBuilder().Select().ColumnWithValue(1, "col1")
.From("Orders","o_dp")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName("[Order Details]","od_dp")
.On(new Column("o_dp.OrderID").Equale(new Column("od_dp.OrderID"))),
new INNERJOIN().TableName("Products","p_dp")
.On(new Column("od_dp.ProductID").Equale(new Column("p_dp.ProductID"))),
new INNERJOIN().TableName("Categories","cat_dp")
.On(new Column("p_dp.CategoryID").Equale(new Column("cat_dp.CategoryID")))
})
.Where(new Where(new Column("o_dp.EmployeeID").Equale(new Column("e.EmployeeID")))
.AND(new Column("cat_dp.CategoryName").Equale("Dairy Products"))
)
)).AND(new EXISTS(
new SqlQueryBuilder().Select().ColumnWithValue(1, "col1")
.From("Orders","o_sf")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName("[Order Details]","od_sf")
.On(new Column("o_sf.OrderID").Equale(new Column("od_sf.OrderID"))),
new INNERJOIN().TableName("Products","p_sf")
.On(new Column("od_sf.ProductID").Equale(new Column("p_sf.ProductID"))),
new INNERJOIN().TableName("Categories","cat_sf")
.On(new Column("p_sf.CategoryID").Equale(new Column("cat_sf.CategoryID")))
})
.Where(new Where(new Column("o_sf.EmployeeID").Equale(new Column("e.EmployeeID")))
.AND(new Column("cat_sf.CategoryName").Equale("Seafood"))
)
)).AND(new NOT_EXISTS(
new SqlQueryBuilder().Select().ColumnWithValue(1, "col1")
.From("Orders","o_fs")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName("Shippers s_fs")
.On(new Column("o_fs.ShipVia").Equale(new Column("s_fs.ShipperID")))
})
.Where(new Where(new Column("o_fs.EmployeeID").Equale(new Column("e.EmployeeID")))
.AND(new Column("s_fs.CompanyName").Equale("Federal Shipping"))
.AND(new Column("o_fs.OrderDate").GreaterThanOrEqualeTo(new DATEADD(SqlDateInterval.year, -1, new GETDATE())))
)
)).AND(
new ColumnArithmatic().StartBracket(new LEN(new Column("e.LastName")))
.SUBTRACT(new LEN(new REPLACE(new Column("e.LastName"),"e",""))).EndBracket()
.Equale(2)
)
)
.Build();

Query build by SqlQueryBuilder 1

            
SELECT e.EmployeeID,
       e.FirstName,
       e.LastName
FROM Employees AS e
WHERE EXISTS (SELECT @pMAIN_2507192048121126280 AS col1
              FROM Orders AS o_dp
                   INNER JOIN
                   [Order Details] AS od_dp
                   ON o_dp.OrderID = od_dp.OrderID
                   INNER JOIN
                   Products AS p_dp
                   ON od_dp.ProductID = p_dp.ProductID
                   INNER JOIN
                   Categories AS cat_dp
                   ON p_dp.CategoryID = cat_dp.CategoryID
              WHERE o_dp.EmployeeID = e.EmployeeID
                    AND cat_dp.CategoryName = @pMAIN_2507192048121126281)
      AND EXISTS (SELECT @pMAIN_2507192048121126282 AS col1
                  FROM Orders AS o_sf
                       INNER JOIN
                       [Order Details] AS od_sf
                       ON o_sf.OrderID = od_sf.OrderID
                       INNER JOIN
                       Products AS p_sf
                       ON od_sf.ProductID = p_sf.ProductID
                       INNER JOIN
                       Categories AS cat_sf
                       ON p_sf.CategoryID = cat_sf.CategoryID
                  WHERE o_sf.EmployeeID = e.EmployeeID
                        AND cat_sf.CategoryName = @pMAIN_2507192048121126283)
      AND NOT EXISTS (SELECT @pMAIN_2507192048121126284 AS col1
                      FROM Orders AS o_fs
                           INNER JOIN
                           Shippers AS s_fs
                           ON o_fs.ShipVia = s_fs.ShipperID
                      WHERE o_fs.EmployeeID = e.EmployeeID
                            AND s_fs.CompanyName = @pMAIN_2507192048121126285
                            AND o_fs.OrderDate >= DATEADD(year, @pMAIN_2507192048121126286, GETDATE()))
      AND (LEN(e.LastName) - LEN(REPLACE(e.LastName, @pMAIN_2507192048121126287, @pMAIN_2507192048121126288))) = @pMAIN_2507192048121126289;


            
        

Parameters (If used)

Name Value
@pMAIN_2507192048121126280 1
@pMAIN_2507192048121126281 Dairy Products
@pMAIN_2507192048121126282 1
@pMAIN_2507192048121126283 Seafood
@pMAIN_2507192048121126284 1
@pMAIN_2507192048121126285 Federal Shipping
@pMAIN_2507192048121126286 -1
@pMAIN_2507192048121126287 e
@pMAIN_2507192048121126288
@pMAIN_2507192048121126289 2

Query Results 1:

  EmployeeID FirstName LastName
1 3 Janet Leverling


2. Performance Summary for Employees in the Last Quarter.

SQL Server Query 2

            
 SELECT  
e.EmployeeID,
e.FirstName + ' ' + e.LastName AS EmployeeName,
e.Title,
SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalSales,
COUNT(DISTINCT o.OrderID) AS OrdersHandled,
AVG(o.Freight) AS AverageFreightHandled
INTO EmployeeQuarterlyPerformance_Q1_1998
FROM Employees e
JOIN Orders o ON e.EmployeeID = o.EmployeeID
JOIN [Order Details] od ON o.OrderID = od.OrderID
WHERE
YEAR(o.OrderDate) = 1998
AND DATEPART(quarter, o.OrderDate) = 1
GROUP BY
e.EmployeeID,
e.FirstName,
e.LastName,
e.Title;

-- Check the summary
SELECT *
FROM EmployeeQuarterlyPerformance_Q1_1998
ORDER BY TotalSales DESC;
-- Clean up
DROP TABLE IF EXISTS EmployeeQuarterlyPerformance_Q1_1998;

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  
.Select()
.Column("e.EmployeeID", "EmployeeID")
.Column(new CONCAT(new Column("e.FirstName"), " ", new Column("e.LastName")), "EmployeeName")
.Column("e.Title", "Title")
.Column(new SUM(new ColumnArithmatic(new Column("od.Quantity")).MULTIPLY(new Column("od.UnitPrice"))
.MULTIPLY().StartBracket(1).SUBTRACT(new Column("od.Discount")).EndBracket()), "TotalSales")
.Column(new COUNT(new Column("o.OrderID"), true), "OrdersHandled")
.Column(new AVG(new Column("o.Freight")), "AverageFreightHandled")
.INTO(new Table("EmployeeQuarterlyPerformance_Q1_1998"))
.From("Employees", "e")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName("Orders","o")
.On(new Column("e.EmployeeID").Equale(new Column("o.EmployeeID"))),
new INNERJOIN().TableName("[Order Details]","od")
.On(new Column("o.OrderID").Equale(new Column("od.OrderID"))),
})
.Where(
new Where(new YEAR(new Column("o.OrderDate")).Equale(1998))
.AND(new DATEPART(SqlDateInterval.quarter, new Column("o.OrderDate")).Equale(1))
)
.GroupBy(new GroupBy(new Column("e.EmployeeID"), new Column("e.FirstName"),
new Column("e.LastName"), new Column("e.Title")))
.Select().Star().From("EmployeeQuarterlyPerformance_Q1_1998")
.OrderBy(new OrderBy().SetColumnDescending("TotalSales"))
.DropTable(new Table("EmployeeQuarterlyPerformance_Q1_1998"), true)
.Build();

Query build by SqlQueryBuilder 2

            
SELECT e.EmployeeID AS EmployeeID,
       CONCAT(e.FirstName, @pMAIN_2507192048121421020, e.LastName) AS EmployeeName,
       e.Title AS Title,
       SUM(od.Quantity * od.UnitPrice * (@pMAIN_2507192048121421021 - od.Discount)) AS TotalSales,
       COUNT(DISTINCT o.OrderID) AS OrdersHandled,
       AVG(o.Freight) AS AverageFreightHandled
INTO EmployeeQuarterlyPerformance_Q1_1998
FROM Employees AS e
     INNER JOIN
     Orders AS o
     ON e.EmployeeID = o.EmployeeID
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
WHERE YEAR(o.OrderDate) = @pMAIN_2507192048121421022
      AND DATEPART(quarter, o.OrderDate) = @pMAIN_2507192048121421023
GROUP BY e.EmployeeID, e.FirstName, e.LastName, e.Title;

SELECT *
FROM EmployeeQuarterlyPerformance_Q1_1998
ORDER BY TotalSales DESC;

DROP TABLE IF EXISTS EmployeeQuarterlyPerformance_Q1_1998
            
        

Parameters (If used)

Name Value
@pMAIN_2507192048121421020
@pMAIN_2507192048121421021 1
@pMAIN_2507192048121421022 1998
@pMAIN_2507192048121421023 1

Query Results 2:

  EmployeeID EmployeeName Title TotalSales OrdersHandled AverageFreightHandled
1 3 Janet Leverling Sales Representative 63605.372549057 28 100.3125
2 1 Nancy Davolio Sales Representative 44090.316488266 29 61.8155
3 2 Andrew Fuller Vice President, Sales 41416.2998876572 19 106.4842
4 4 Margaret Peacock Sales Representative 38187.4799957275 32 82.8964
5 8 Laura Callahan Inside Sales Coordinator 32097.8425865173 19 168.1601
6 9 Anne Dodsworth Sales Representative 31601.6590003967 15 69.9316
7 5 Steven Buchanan Sales Manager 19481.8949356079 12 140.5094
8 7 Robert King Sales Representative 19113.4824829102 14 150.9350
9 6 Michael Suyama Sales Representative 8897.20499420166 14 33.1730