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
       }
     }
   ]);

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"
       }
     }
   ]);

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"
       }
     }
   ]);

1. Fetch only odd rows from the table:

   db.EmployeeDetails.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });

1. Fetch only even rows from the table:

   db.EmployeeDetails.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });

1. Create a new table with data and structure copied from another table:

   db.createCollection("NewTable");
   db.NewTable.insertMany(db.EmployeeSalary.find().toArray());

1. Create an empty table with the same structure as another table:

   db.createCollection("NewTable");

1. Fetch top n records (limiting documents returned):

   db.EmployeeSalary.find().sort({ Salary: -1 }).limit(N);

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] } } }
   ]);

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
         ]
       }
     }
   });

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;

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;

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;

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;

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;

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
              );