In MongoDB, the equivalent queries for the scenarios provided are as follows:
- Fetch all Employees who are also managers (Self-Join):
db.EmployeeDetails.aggregate([
{
$lookup: {
from: "EmployeeDetails",
localField: "EmpID",
foreignField: "ManagerID",
as: "ManagerInfo"
}
},
{
$match: {
"ManagerInfo": { $ne: [] }
}
},
{
$project: {
"FullName": 1
}
}
]);
- Fetch duplicate records from EmployeeDetails (excluding primary key – EmpId):
db.EmployeeDetails.aggregate([
{
$group: {
_id: {
FullName: "$FullName",
ManagerId: "$ManagerId",
DateOfJoining: "$DateOfJoining",
City: "$City"
},
count: { $sum: 1 },
duplicates: { $push: "$_id" }
}
},
{
$match: {
count: { $gt: 1 }
}
},
{
$project: {
FullName: "$_id.FullName",
ManagerId: "$_id.ManagerId",
DateOfJoining: "$_id.DateOfJoining",
City: "$_id.City",
Count: "$count",
_id: "$duplicates"
}
}
]);
- Remove duplicates from a table without using a temporary table:
db.EmployeeDetails.aggregate([
{
$lookup: {
from: "EmployeeDetails",
let: { EmpId: "$EmpId", FullName: "$FullName", ManagerId: "$ManagerId", DateOfJoining: "$DateOfJoining", City: "$City" },
pipeline: [
{
$match: {
$expr: {
$and: [
{ $gt: ["$EmpId", "$$EmpId"] },
{ $eq: ["$FullName", "$$FullName"] },
{ $eq: ["$ManagerId", "$$ManagerId"] },
{ $eq: ["$DateOfJoining", "$$DateOfJoining"] },
{ $eq: ["$City", "$$City"] }
]
}
}
}
],
as: "duplicates"
}
},
{
$unwind: "$duplicates"
},
{
$replaceRoot: { newRoot: "$duplicates" }
},
{
$delete: {
delete: "EmployeeDetails",
deletes: "$_id"
}
}
]);
- Fetch only odd rows from the table:
db.EmployeeDetails.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });
- Fetch only even rows from the table:
db.EmployeeDetails.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });
- Create a new table with data and structure copied from another table:
db.createCollection("NewTable");
db.NewTable.insertMany(db.EmployeeSalary.find().toArray());
- Create an empty table with the same structure as another table:
db.createCollection("NewTable");
- Fetch top n records (limiting documents returned):
db.EmployeeSalary.find().sort({ Salary: -1 }).limit(N);
- Find the nth highest salary from a table (using aggregation):
db.EmployeeSalary.aggregate([
{ $group: { _id: null, salaries: { $push: "$Salary" } } },
{ $project: { thirdHighestSalary: { $arrayElemAt: ["$salaries", N - 1] } } }
]);
- Find the 3rd highest salary without using TOP/LIMIT keyword:
db.EmployeeSalary.find({
Salary: {
$eq: {
$arrayElemAt: [
{
$setUnion: [
{ $map: { input: "$Salary", as: "s", in: "$$s" } },
[]
]
},
N - 1
]
}
}
});
- SQL query to fetch all Employees who are also managers (Self-Join):
SELECT DISTINCT E.FullName
FROM EmployeeDetails E
INNER JOIN EmployeeDetails M ON E.EmpID = M.ManagerID;
- SQL query to fetch duplicate records from EmployeeDetails (excluding primary key – EmpId):
SELECT FullName, ManagerId, DateOfJoining, City, COUNT(*)
FROM EmployeeDetails
GROUP BY FullName, ManagerId, DateOfJoining, City
HAVING COUNT(*) > 1;
- SQL query to remove duplicates from a table without using a temporary table:
DELETE E1
FROM EmployeeDetails E1
INNER JOIN EmployeeDetails E2
WHERE E1.EmpId > E2.EmpId
AND E1.FullName = E2.FullName
AND E1.ManagerId = E2.ManagerId
AND E1.DateOfJoining = E2.DateOfJoining
AND E1.City = E2.City;
-
SQL query to fetch only odd rows from the table:
- Using Row_number in SQL Server:
SELECT E.EmpId, E.Project, E.Salary FROM ( SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber FROM EmployeeSalary ) E WHERE E.RowNumber % 2 = 1;
-
Using user-defined variable in MySQL:
SELECT * FROM ( SELECT *, @rowNumber := @rowNumber+ 1 rn FROM EmployeeSalary JOIN (SELECT @rowNumber:= 0) r ) t WHERE rn % 2 = 1;
-
SQL query to fetch only even rows from the table:
- Using Row_number in SQL Server:
SELECT E.EmpId, E.Project, E.Salary FROM ( SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber FROM EmployeeSalary ) E WHERE E.RowNumber % 2 = 0;
-
Using user-defined variable in MySQL:
SELECT * FROM ( SELECT *, @rowNumber := @rowNumber+ 1 rn FROM EmployeeSalary JOIN (SELECT @rowNumber:= 0) r ) t WHERE rn % 2 = 0;
- SQL query to create a new table with data and structure copied from another table:
CREATE TABLE NewTable AS
SELECT * FROM EmployeeSalary;
- SQL query to create an empty table with the same structure as another table:
CREATE TABLE NewTable AS
SELECT * FROM EmployeeSalary WHERE 1 = 0;
-
SQL query to fetch top n records (MySQL and SQL Server):
- MySQL using LIMIT:
SELECT * FROM EmployeeSalary ORDER BY Salary DESC LIMIT N;
-
SQL Server using TOP:
SELECT TOP N * FROM EmployeeSalary ORDER BY Salary DESC;
-
SQL query to find the nth highest salary from a table (MySQL and SQL Server):
- Using Top keyword (SQL Server):
SELECT TOP 1 Salary FROM ( SELECT DISTINCT TOP N Salary FROM Employee ORDER BY Salary DESC ) ORDER BY Salary ASC;
-
Using LIMIT clause (MySQL):
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1, 1;
-
SQL query to find the 3rd highest salary without using TOP/LIMIT keyword:
SELECT Salary FROM EmployeeSalary Emp1 WHERE 2 = ( SELECT COUNT(DISTINCT Emp2.Salary) FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary );
Top comments (0)