mongo db query3
Published: 2024-02-03 03:12:53

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

31. **Fetch all Employees who are also managers (Self-Join):**
   ```javascript
   db.EmployeeDetails.aggregate([
     {
       $lookup: {
         from: "EmployeeDetails",
         localField: "EmpID",
         foreignField: "ManagerID",
         as: "ManagerInfo"
       }
     },
     {
       $match: {
         "ManagerInfo": { $ne: [] }
       }
     },
     {
       $project: {
         "FullName": 1
       }
     }
   ]);
   ```

32. **Fetch duplicate records from EmployeeDetails (excluding primary key – EmpId):**
   ```javascript
   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"
       }
     }
   ]);
   ```

33. **Remove duplicates from a table without using a temporary table:**
   ```javascript
   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"
       }
     }
   ]);
   ```

34. **Fetch only odd rows from the table:**
   ```javascript
   db.EmployeeDetails.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });
   ```

35. **Fetch only even rows from the table:**
   ```javascript
   db.EmployeeDetails.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });
   ```

36. **Create a new table with data and structure copied from another table:**
   ```javascript
   db.createCollection("NewTable");
   db.NewTable.insertMany(db.EmployeeSalary.find().toArray());
   ```

37. **Create an empty table with the same structure as another table:**
   ```javascript
   db.createCollection("NewTable");
   ```

38. **Fetch top n records (limiting documents returned):**
   ```javascript
   db.EmployeeSalary.find().sort({ Salary: -1 }).limit(N);
   ```

39. **Find the nth highest salary from a table (using aggregation):**
   ```javascript
   db.EmployeeSalary.aggregate([
     { $group: { _id: null, salaries: { $push: "$Salary" } } },
     { $project: { thirdHighestSalary: { $arrayElemAt: ["$salaries", N - 1] } } }
   ]);
   ```

40. **Find the 3rd highest salary without using TOP/LIMIT keyword:**
   ```javascript
   db.EmployeeSalary.find({
     Salary: {
       $eq: {
         $arrayElemAt: [
           {
             $setUnion: [
               { $map: { input: "$Salary", as: "s", in: "$$s" } },
               []
             ]
           },
           N - 1
         ]
       }
     }
   });
   ```

31. **SQL query to fetch all Employees who are also managers (Self-Join):**
   ```sql
   SELECT DISTINCT E.FullName
   FROM EmployeeDetails E
   INNER JOIN EmployeeDetails M ON E.EmpID = M.ManagerID;
   ```

32. **SQL query to fetch duplicate records from EmployeeDetails (excluding primary key – EmpId):**
   ```sql
   SELECT FullName, ManagerId, DateOfJoining, City, COUNT(*)
   FROM EmployeeDetails
   GROUP BY FullName, ManagerId, DateOfJoining, City
   HAVING COUNT(*) > 1;
   ```

33. **SQL query to remove duplicates from a table without using a temporary table:**
   ```sql
   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;
   ```

34. **SQL query to fetch only odd rows from the table:**
   - Using Row_number in SQL Server:
     ```sql
     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:
     ```sql
     SELECT *
     FROM (
           SELECT *, @rowNumber := @rowNumber+ 1 rn
           FROM EmployeeSalary
           JOIN (SELECT @rowNumber:= 0) r
          ) t 
     WHERE rn % 2 = 1;
     ```

35. **SQL query to fetch only even rows from the table:**
   - Using Row_number in SQL Server:
     ```sql
     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:
     ```sql
     SELECT *
     FROM (
           SELECT *, @rowNumber := @rowNumber+ 1 rn
           FROM EmployeeSalary
           JOIN (SELECT @rowNumber:= 0) r
          ) t 
     WHERE rn % 2 = 0;
     ```

36. **SQL query to create a new table with data and structure copied from another table:**
   ```sql
   CREATE TABLE NewTable AS
   SELECT * FROM EmployeeSalary;
   ```

37. **SQL query to create an empty table with the same structure as another table:**
   ```sql
   CREATE TABLE NewTable AS
   SELECT * FROM EmployeeSalary WHERE 1 = 0;
   ```

38. **SQL query to fetch top n records (MySQL and SQL Server):**
   - MySQL using LIMIT:
     ```sql
     SELECT *
     FROM EmployeeSalary
     ORDER BY Salary DESC LIMIT N;
     ```
   - SQL Server using TOP:
     ```sql
     SELECT TOP N *
     FROM EmployeeSalary
     ORDER BY Salary DESC;
     ```

39. **SQL query to find the nth highest salary from a table (MySQL and SQL Server):**
   - Using Top keyword (SQL Server):
     ```sql
     SELECT TOP 1 Salary
     FROM (
           SELECT DISTINCT TOP N Salary
           FROM Employee
           ORDER BY Salary DESC
           )
     ORDER BY Salary ASC;
     ```
   - Using LIMIT clause (MySQL):
     ```sql
     SELECT Salary
     FROM Employee
     ORDER BY Salary DESC LIMIT N-1, 1;
     ```

40. **SQL query to find the 3rd highest salary without using TOP/LIMIT keyword:**
     ```sql
     SELECT Salary
     FROM EmployeeSalary Emp1
     WHERE 2 = (
                 SELECT COUNT(DISTINCT Emp2.Salary)
                 FROM EmployeeSalary Emp2
                 WHERE Emp2.Salary > Emp1.Salary
               );
     ```