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
|