mongdb queris part2

MongoDB uses a different syntax from SQL due to its NoSQL nature. Here are the equivalent queries in MongoDB:

1. Fetch records present in one collection but not in another:

   db.collection1.find({ field1: { $nin: db.collection2.distinct("field1") } });

1. Fetch employees not working on any project:

   db.employee.find({ "_id": { $nin: db.projectAssignment.distinct("employeeId") } });

1. Fetch employees from EmployeeDetails who joined in the Year 2020:

   db.employeeDetails.find({ "joinDate": { $gte: ISODate("2020-01-01"), $lt: ISODate("2021-01-01") } });

1. Fetch employees from EmployeeDetails with salary records in EmployeeSalary:

   db.employeeDetails.find({ "_id": { $in: db.employeeSalary.distinct("employeeId") } });

1. Fetch project-wise count of employees:

   db.projectAssignment.aggregate([
     { $group: { _id: "$projectId", employeeCount: { $sum: 1 } } }
   ]);

1. Fetch employee names and salaries with NULLs for missing salary records:

   db.employeeDetails.aggregate([
     {
       $lookup: {
         from: "employeeSalary",
         localField: "_id",
         foreignField: "employeeId",
         as: "salaryInfo"
       }
     },
     {
       $project: {
         _id: 1,
         employeeName: 1,
         salary: { $ifNull: [{ $arrayElemAt: ["$salaryInfo.salary", 0] }, "No Salary Record"] }
       }
     }
   ]);

1. Fetch all Employees who are also managers:

   db.employeeDetails.find({ "_id": { $in: db.employeeDetails.distinct("managerId", { managerId: { $ne: null } }) } });

1. Fetch duplicate records from EmployeeDetails:

   db.employeeDetails.aggregate([
     { $group: { _id: { employeeId: "$employeeId", employeeName: "$employeeName" }, count: { $sum: 1 } } },
     { $match: { count: { $gt: 1 } } }
   ]);

1. Fetch only odd documents from the collection:

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

1. Find the 3rd highest salary without using top or limit keyword:
   

   db.employeeSalary.distinct("salary").sort().reverse()[2];
   

2. Fetch records present in one table but not in another:
   

   SELECT * 
   FROM Table1
   WHERE Column1 NOT IN (SELECT Column1 FROM Table2);

1. Fetch employees not working on any project:

   SELECT *
   FROM Employee
   WHERE EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM ProjectAssignment);

1. Fetch employees from EmployeeDetails who joined in the Year 2020:

   SELECT *
   FROM EmployeeDetails
   WHERE YEAR(JoinDate) = 2020;

1. Fetch employees from EmployeeDetails with salary records in EmployeeSalary:

   SELECT *
   FROM EmployeeDetails
   WHERE EmployeeID IN (SELECT DISTINCT EmployeeID FROM EmployeeSalary);

1. Fetch project-wise count of employees:

   SELECT ProjectID, COUNT(EmployeeID) AS EmployeeCount
   FROM ProjectAssignment
   GROUP BY ProjectID;

1. Fetch employee names and salaries with NULLs for missing salary records:

   SELECT E.EmployeeID, E.EmployeeName, ISNULL(S.Salary, 'No Salary Record') AS Salary
   FROM EmployeeDetails E
   LEFT JOIN EmployeeSalary S ON E.EmployeeID = S.EmployeeID;

1. Fetch all Employees who are also managers:

   SELECT *
   FROM EmployeeDetails
   WHERE EmployeeID IN (SELECT DISTINCT ManagerID FROM EmployeeDetails WHERE ManagerID IS NOT NULL);

1. Fetch duplicate records from EmployeeDetails:

   SELECT EmployeeID, EmployeeName, COUNT(*)
   FROM EmployeeDetails
   GROUP BY EmployeeID, EmployeeName
   HAVING COUNT(*) > 1;

1. Fetch only odd rows from the table:

   SELECT *
   FROM TableName
   WHERE ROWID % 2 <> 0;

1. Find the 3rd highest salary without using top or limit keyword:
   

   SELECT DISTINCT Salary
   FROM EmployeeSalary E1
   WHERE 3 = (SELECT COUNT(DISTINCT Salary) FROM EmployeeSalary E2 WHERE E1.Salary <= E2.Salary);
   

The provided MongoDB queries and commands focus on the manipulation and retrieval of data from the "staff" collection. Below is an analysis of each command:

1. Inserting Data:

   db.staff.insertMany([{
  "employees": [
    {
      "id": 1,
      "name": "John Doe",
      "position": "Software Engineer",
      "salary": 80000
    },
    {
      "id": 2,
      "name": "Jane Smith",
      "position": "Data Analyst",
      "salary": 65000
    },
    {
      "id": 3,
      "name": "Robert Johnson",
      "position": "Project Manager",
      "salary": 90000
    }
  ],
  "contractors": [
    {
      "id": 101,
      "name": "Emily Brown",
      "project": "Data Migration",
      "dailyRate": 400
    },
    {
      "id": 102,
      "name": "Michael Davis",
      "project": "Web Development",
      "dailyRate": 450
    }
  ],
  "freelancers": [
    {
      "id": 201,
      "name": "Sophia Wilson",
      "service": "Graphic Design",
      "hourlyRate": 50
    },
    {
      "id": 202,
      "name": "Daniel White",
      "service": "Content Writing",
      "hourlyRate": 40
    }
  ]
}
]);

• Inserts a document containing information about employees, contractors, and freelancers.

1. Query for Freelancers in Graphic Design:

   db.staff.find(
     { "freelancers": { $elemMatch: { "service": "Graphic Design" } } },
     { "freelancers.$": 1, "_id": 0 }
   );

• Retrieves freelancers specializing in Graphic Design.

1. Query for All Employees:

   db.staff.find({}, { "employees": 1, "_id": 0 });

• Retrieves information about all employees.

1. Query for Employees with Salary > 80000:

   db.staff.find(
     { "employees.salary": { $gt: 80000 } },
     { "employees.$": 1, "_id": 0 }
   );

• Retrieves employees with a salary greater than 80000.

1. Query for Employees with Salary > 80000 using $elemMatch:

   db.staff.find(
     { "employees": { $elemMatch: { "salary": { $gt: 80000 } } } },
     { "_id": 0, "employees.$": 1 }
   );

• Another approach using $elemMatch to retrieve employees with a salary greater than 80000.

1. Aggregate to Retrieve Employees with Salary > 80000:

   db.staff.aggregate([
     { $match: { "employees": { $elemMatch: { "salary": { $gt: 80000 } } } } },
     { $project: { "_id": 0, "empwithsalary": "$employees" } }
   ]);

• Uses aggregation to match and project employees with a salary greater than 80000.

1. Query for Freelancers:

   db.staff.find({}, { "freelancers": 1, "_id": 0 });

• Retrieves information about all freelancers.

1. Query for Freelancers in Graphic Design (Alternative):

   db.staff.find({"freelancers.service": "Graphic Design" });
   db.staff.find({ "freelancers.service": 'Graphic Design' });

• Alternative queries to retrieve freelancers in Graphic Design.

1. Aggregate to Calculate Total Salary of Employees:

   db.staff.aggregate([
     { $unwind: "$employees" },
     { $group: { _id: null, totalSalary: { $sum: "$employees.salary" } } }
   ]);

• Uses aggregation to unwind and group employees, calculating the total salary.

1. Aggregate to Group and Reorganize Data:
   

   db.staff.aggregate([
     { $unwind: "$employees" },
     { $match: { "employees.position": { $exists: true } } },
     {
       $group: {
         _id: null,
         employees: { $push: "$employees" },
         contractors: { $first: "$contractors" },
         freelancers: { $first: "$freelancers" }
       }
     },
     { $project: { _id: 0 } }
   ]);
   

- Uses aggregation to group, match, and reorganize data into distinct categories.

1. Aggregate to Calculate Total Salary and Unwind Contractors/Freelancers:
   

   db.staff.aggregate([
     { $unwind: "$employees" },
     { $match: { "employees.salary": { $gt: 80000 } } },
     { $group: { _id: null, totalSalary: { $sum: "$employees.salary" } } },
     { $unwind: "$contractors" },
     { $unwind: "$freelancers" }
   ]);
   

- Aggregation to calculate total salary of employees with salary > 80000 and unwind contractors and freelancers.

Please note that these queries assume a specific structure of the "staff" collection and may need adjustments based on the actual data model.