DEV Community

avinash-repo
avinash-repo

Posted on

mongo db query3

In MongoDB, the equivalent queries for the scenarios provided are as follows:

  1. 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
       }
     }
   ]);
Enter fullscreen mode Exit fullscreen mode
  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"
       }
     }
   ]);
Enter fullscreen mode Exit fullscreen mode
  1. 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"
       }
     }
   ]);
Enter fullscreen mode Exit fullscreen mode
  1. Fetch only odd rows from the table:
   db.EmployeeDetails.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });
Enter fullscreen mode Exit fullscreen mode
  1. Fetch only even rows from the table:
   db.EmployeeDetails.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });
Enter fullscreen mode Exit fullscreen mode
  1. Create a new table with data and structure copied from another table:
   db.createCollection("NewTable");
   db.NewTable.insertMany(db.EmployeeSalary.find().toArray());
Enter fullscreen mode Exit fullscreen mode
  1. Create an empty table with the same structure as another table:
   db.createCollection("NewTable");
Enter fullscreen mode Exit fullscreen mode
  1. Fetch top n records (limiting documents returned):
   db.EmployeeSalary.find().sort({ Salary: -1 }).limit(N);
Enter fullscreen mode Exit fullscreen mode
  1. 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] } } }
   ]);
Enter fullscreen mode Exit fullscreen mode
  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
         ]
       }
     }
   });
Enter fullscreen mode Exit fullscreen mode
  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;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
Enter fullscreen mode Exit fullscreen mode
  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;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
    
  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;
    
  1. SQL query to create a new table with data and structure copied from another table:
   CREATE TABLE NewTable AS
   SELECT * FROM EmployeeSalary;
Enter fullscreen mode Exit fullscreen mode
  1. SQL query to create an empty table with the same structure as another table:
   CREATE TABLE NewTable AS
   SELECT * FROM EmployeeSalary WHERE 1 = 0;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
    
  1. 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;
    
  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)