TheLazy.Dev
Portfolio GitHub

Blog

javascript |mongodb |sql |mysql

Top Asking SQL vs Mongo Queries

akmaurya31

Avinash Maurya

Posted on January 30, 2024

Top Asking SQL vs Mongo Queries

To find the second-highest salary in a MySQL database, you can use a query that utilizes the ORDER BY and LIMIT clauses. Assuming you have a table named employees with a column named salary, here is an example query:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  1. SELECT DISTINCT salary: This selects all distinct salary values from the employees table.

  2. ORDER BY salary DESC: This orders the salary values in descending order, so the highest salary comes first.

  3. LIMIT 1 OFFSET 1: This limits the result set to one row starting from the second row. In other words, it skips the first row (highest salary) and retrieves the second row, which is the second-highest salary.

Make sure to replace employees and salary with the actual table and column names in your database.

In MongoDB, you can use the aggregate pipeline to find the second-highest salary. Assuming you have a collection named employees with a field named salary, here is an example query:

db.employees.aggregate([
    { $group: { _id: null, salaries: { $addToSet: "$salary" } } },
    { $unwind: "$salaries" },
    { $sort: { "salaries": -1 } },
    { $skip: 1 },
    { $limit: 1 }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  1. $group: Groups the documents in the collection and creates an array salaries containing all unique salary values.

  2. $unwind: Deconstructs the array salaries to transform each element of the array into a separate document.

  3. $sort: Sorts the documents based on the salaries field in descending order.

  4. $skip: Skips the first document, which corresponds to the highest salary.

  5. $limit: Limits the result set to one document, which represents the second-highest salary.

This query assumes that the salary field in the employees collection contains numerical values. Adjust the field names accordingly if your actual field names are different.

Certainly! One of the most commonly asked SQL query-based questions involves finding the second-highest (or nth-highest) value in a particular column. Here's an example of a query to find the second-highest salary in a table:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  1. The inner subquery (SELECT MAX(salary) FROM employees) finds the highest salary in the employees table.

  2. The outer query then selects the maximum salary from the employees table where the salary is less than the highest salary found in the subquery. This effectively retrieves the second-highest salary.

This approach is often considered a classic and is widely used in interviews to assess a candidate's understanding of subqueries and logical thinking. Remember to replace employees and salary with your actual table and column names.

Keep in mind that there are multiple ways to achieve the same result, and interviewers might be interested in discussing alternative approaches or optimizations.

db.employees.aggregate([
    { $sort: { salary: -1 } },
    { $group: { _id: null, salaries: { $push: "$salary" } } },
    { $project: { second_highest_salary: { $arrayElemAt: ["$salaries", 1] } } }
])
Enter fullscreen mode Exit fullscreen mode

Question 13 Write a query to fetch the EmpFname from the EmployeeInfo table in upper case and use the ALIAS name as EmpName.?

Certainly! Below are examples of queries in both SQL and MongoDB for fetching the EmpFname from the EmployeeInfo table or collection in upper case and using the alias name EmpName.

SQL Query: 

SELECT UPPER(EmpFname) AS EmpName
FROM EmployeeInfo;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • UPPER(EmpFname): Converts the EmpFname column values to uppercase.
  • AS EmpName: Assigns the alias name EmpName to the result set.

MongoDB Query: 

db.EmployeeInfo.aggregate([
    {
        $project: {
            EmpName: { $toUpper: "$EmpFname" }
        }
    }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $project: Reshapes each document in the collection.
  • EmpName: { $toUpper: "$EmpFname" }: Uses the $toUpper operator to convert the value of EmpFname to uppercase and assigns it to the field EmpName.

Make sure to adjust the collection and field names according to your actual MongoDB setup. If you have any more questions or if there's anything else I can help you with, feel free to ask!

Question 14 Write a query to fetch the number of employees working in the department ‘HR’.?

Certainly! Here are the queries for both MySQL and MongoDB to fetch the number of employees working in the department 'HR':

MySQL Query: 

SELECT COUNT(*) AS NumEmployeesInHR
FROM EmployeeInfo
WHERE Department = 'HR';
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • COUNT(*): Counts the number of rows that satisfy the condition.
  • WHERE Department = 'HR': Filters the rows to include only those where the department is 'HR'.

MongoDB Query: 

db.EmployeeInfo.count({ Department: 'HR' })
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • db.EmployeeInfo.count({ Department: 'HR' }): Counts the number of documents in the EmployeeInfo collection where the department is 'HR'.

Adjust the collection and field names according to your actual MongoDB setup.

Question 15 Write a query to get the current date.?

Certainly! Below are the queries for both MySQL and MongoDB to get the current date:

MySQL Query: 

SELECT CURDATE() AS CurrentDate;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • CURDATE(): Returns the current date in MySQL.

MongoDB Query: 

db.runCommand({ date: 1 })
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • In MongoDB, you can use the db.runCommand() method with the { date: 1 } command to retrieve the current date.

These queries will provide you with the current date in both MySQL and MongoDB. If you have any more questions or if there's anything else I can help you with, feel free to ask!

Question 16 Write a query to retrieve the first four characters of EmpLname from the EmployeeInfo table.

Certainly! Below are the queries for both MySQL and MongoDB to retrieve the first four characters of EmpLname from the EmployeeInfo table or collection:

MySQL Query: 

SELECT SUBSTRING(EmpLname, 1, 4) AS FirstFourCharacters
FROM EmployeeInfo;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • SUBSTRING(EmpLname, 1, 4): Retrieves the substring of EmpLname starting from position 1 (the first character) and with a length of 4 characters.

MongoDB Query: 

db.EmployeeInfo.aggregate([
    {
        $project: {
            FirstFourCharacters: { $substr: ["$EmpLname", 0, 4] }
        }
    }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $project: Reshapes each document in the collection.
  • FirstFourCharacters: { $substr: ["$EmpLname", 0, 4] }: Uses the $substr operator to retrieve the substring of EmpLname starting from position 0 (the first character) and with a length of 4 characters.

Adjust the collection and field names according to your actual MongoDB setup.

Question 17 Write q query to find all the employees whose salary is between 50000 to 100000.

Certainly! Here are the queries for both MySQL and MongoDB to find all employees whose salary is between 50000 and 100000:

MySQL Query: 

SELECT *
FROM EmployeeInfo
WHERE Salary BETWEEN 50000 AND 100000;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • BETWEEN 50000 AND 100000: Filters the rows to include only those where the salary falls within the specified range.

MongoDB Query: 

db.EmployeeInfo.find({
    Salary: { $gte: 50000, $lte: 100000 }
})
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $gte: Matches values that are greater than or equal to the specified value (50000).
  • $lte: Matches values that are less than or equal to the specified value (100000).

Adjust the collection and field names according to your actual MongoDB setup.

Question 18 Write a query to find the names of employees that begin with ‘S’

Certainly! Here are the queries for both MySQL and MongoDB to find the names of employees that begin with 'S':

MySQL Query: 

SELECT *
FROM EmployeeInfo
WHERE EmpName LIKE 'S%';
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • EmpName LIKE 'S%': Filters the rows to include only those where the EmpName starts with the letter 'S'.

MongoDB Query: 

db.EmployeeInfo.find({
    EmpName: /^S/
})
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • EmpName: /^S/: Uses a regular expression to match names that start with the letter 'S'.

Adjust the collection and field names according to your actual MongoDB setup.

Question 19 Write a query to fetch top N records.
Certainly! To fetch the top N records in both MySQL and MongoDB, you can use the LIMIT clause in MySQL and the limit() method in MongoDB. Here are the queries for both:

MySQL Query: 

SELECT *
FROM YourTableName
LIMIT N;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • LIMIT N: Limits the result set to the first N records.

Replace YourTableName with the actual name of your table, and replace N with the desired number of records.

MongoDB Query: 

db.YourCollectionName.find().limit(N)
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • .limit(N): Limits the result set to the first N records.

Replace YourCollectionName with the actual name of your collection, and replace N with the desired number of records.

Question 20 Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”. The first name and the last name must be separated with space.

Certainly! Here are the queries for both MySQL and MongoDB to retrieve EmpFname and EmpLname in a single column as "FullName":

MySQL Query: 

SELECT CONCAT(EmpFname, ' ', EmpLname) AS FullName
FROM YourTableName;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • CONCAT(EmpFname, ' ', EmpLname): Concatenates EmpFname and EmpLname with a space in between.

Replace YourTableName with the actual name of your table.

MongoDB Query: 

db.YourCollectionName.aggregate([
    {
        $project: {
            FullName: { $concat: ["$EmpFname", " ", "$EmpLname"] }
        }
    }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $project: Reshapes each document in the collection.
  • FullName: { $concat: ["$EmpFname", " ", "$EmpLname"] }: Concatenates EmpFname and EmpLname with a space in between.

Replace YourCollectionName with the actual name of your collection.

Question 21 Write a query find number of employees whose DOB is between 02/05/1970 to 31/12/1975 and are grouped according to gender
Certainly! Here are the queries for both MySQL and MongoDB to find the number of employees whose DOB is between 02/05/1970 to 31/12/1975 and group them according to gender:

MySQL Query: 

SELECT Gender, COUNT(*) AS NumEmployees
FROM YourTableName
WHERE DOB BETWEEN '1970-05-02' AND '1975-12-31'
GROUP BY Gender;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • COUNT(*): Counts the number of rows for each gender group.
  • GROUP BY Gender: Groups the result set by the Gender column.

Replace YourTableName with the actual name of your table.

MongoDB Query: 

db.YourCollectionName.aggregate([
    {
        $match: {
            DOB: {
                $gte: ISODate("1970-05-02T00:00:00.000Z"),
                $lte: ISODate("1975-12-31T23:59:59.999Z")
            }
        }
    },
    {
        $group: {
            _id: "$Gender",
            NumEmployees: { $sum: 1 }
        }
    }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $match: Filters the documents to include only those with DOB between the specified date range.
  • $group: Groups the documents by the Gender field.
  • NumEmployees: { $sum: 1 }: Calculates the count of employees for each gender group.

Replace YourCollectionName with the actual name of your collection.

Adjust the table/collection names according to your actual MySQL/MongoDB setup.

Question 22 Write a query to fetch details of employees whose EmpLname ends with an alphabet ‘A’ and contains five alphabets.

Your SQL query is almost correct. The query you provided will select all records from the EmployeeInfo table where the EmpLname ends with an 'a' and contains four characters before the 'a'. Here's the corrected version:

SELECT * FROM EmployeeInfo WHERE EmpLname LIKE '____a';
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • LIKE '____a': The underscore (_) is a wildcard in SQL, representing a single character. So, ____a will match any four characters followed by the letter 'a'.

This query will fetch details of employees whose last names end with 'a' and have exactly four characters before the 'a'.

In MongoDB, the $regex operator is commonly used to perform regular expression-based queries, which is analogous to the use of wildcards in SQL LIKE queries. If you want to perform a wildcard-like query in MongoDB, you can use a regular expression with the $regex operator.

Here's an example of how you might achieve a similar effect to your SQL query using a wildcard in MongoDB:

db.EmployeeInfo.find({
  EmpLname: { $regex: /^.{4}a$/i }
})
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • ^: Anchors the regex to the start of the string.
  • .{4}: Matches any four characters.
  • a$: Matches the letter 'a' at the end of the string.
  • i: Makes the regex case-insensitive.

This query will find documents in the EmployeeInfo collection where the EmpLname field contains exactly four characters before the letter 'a'. Adjust the collection and field names according to your actual MongoDB setup.

Your SQL query is almost correct. The query you provided will select all records from the EmployeeInfo table where the EmpLname ends with an 'a' and contains four characters before the 'a'. Here's the corrected version:

SELECT * FROM EmployeeInfo WHERE EmpLname LIKE '____a';
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • LIKE '____a': The underscore (_) is a wildcard in SQL, representing a single character. So, ____a will match any four characters followed by the letter 'a'.

This query will fetch details of employees whose last names end with 'a' and have exactly four characters before the 'a'.

Question 23 Write a query to fetch details of all employees excluding the employees with first names, “Radha” and Krishna from the EmployeeInfo table.

Certainly! Here are the queries for both MySQL and MongoDB to fetch details of all employees excluding those with the first names "Radha" and "Krishna":

MySQL Query: 

SELECT *
FROM EmployeeInfo
WHERE EmpFname NOT IN ('Radha', 'Krishna');
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • NOT IN ('Radha', 'Krishna'): Excludes rows where the EmpFname is either "Radha" or "Krishna".

MongoDB Query: 

db.EmployeeInfo.find({
    EmpFname: { $nin: ['Radha', 'Krishna'] }
})
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $nin: Matches values that are not in the specified array.

Adjust the table/collection and field names according to your actual MySQL/MongoDB setup.

Question 24
SELECT * FROM EmployeeInfo WHERE Address LIKE 'DELHI(DEL)%';

In MongoDB, you can use the $regex operator to perform regular expression-based queries. Here's the equivalent MongoDB query for your given SQL query:

MongoDB Query: 

db.EmployeeInfo.find({
    Address: { $regex: /^DELHI\(DEL\)/ }
})
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $regex: Allows you to use regular expressions in the query.
  • /^DELHI\(DEL\)/: Represents a regular expression that matches strings starting with "DELHI(DEL)".

Adjust the collection and field names according to your actual MongoDB setup.

Keep in mind that MongoDB uses a different syntax for regular expressions compared to SQL, and you need to escape special characters like parentheses.

Question 25 Write a query to fetch all employees who also hold the managerial position.

SELECT E.EmpFname, E.EmpLname, P.EmpPosition
FROM EmployeeInfo E INNER JOIN EmployeePosition P ON
E.EmpID = P.EmpID AND P.EmpPosition IN ('Manager');

In MongoDB, you can use the $lookup stage to perform a similar operation to an SQL join. Here's the equivalent MongoDB query for your given SQL query:

MongoDB Query: 

db.EmployeeInfo.aggregate([
  {
    $lookup: {
      from: "EmployeePosition",
      localField: "EmpID",
      foreignField: "EmpID",
      as: "positions"
    }
  },
  {
    $match: {
      "positions.EmpPosition": "Manager"
    }
  },
  {
    $project: {
      _id: 0,
      EmpFname: 1,
      EmpLname: 1,
      EmpPosition: "$positions.EmpPosition"
    }
  }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $lookup: Performs a left outer join to the EmployeePosition collection based on the EmpID field.
  • $match: Filters the documents to include only those where the EmpPosition in the joined array is "Manager".
  • $project: Shapes the output document, including only the desired fields (EmpFname, EmpLname, and EmpPosition).

Adjust the collection and field names according to your actual MongoDB setup.

Question 26 Write a query to fetch the department-wise count of employees sorted by department’s count in ascending order.

SELECT Department, count(EmpID) AS EmpDeptCount 
FROM EmployeeInfo GROUP BY Department 
ORDER BY EmpDeptCount ASC;
Enter fullscreen mode Exit fullscreen mode

Certainly! Below is the equivalent MongoDB query for your given SQL query, which fetches the department-wise count of employees sorted by the department's count in ascending order:

MongoDB Query: 

db.EmployeeInfo.aggregate([
  {
    $group: {
      _id: "$Department",
      EmpDeptCount: { $sum: 1 }
    }
  },
  {
    $sort: {
      EmpDeptCount: 1
    }
  }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $group: Groups the documents by the Department field and calculates the count of employees in each department using $sum: 1.
  • $sort: Sorts the result set based on the EmpDeptCount field in ascending order (1).

Adjust the collection and field names according to your actual MongoDB setup.

Question 27 Write a query to retrieve two minimum and maximum salaries from the EmployeePosition table.
To retrieve two minimum salaries, you can write a query as below:

SELECT DISTINCT Salary FROM EmployeePosition E1 
 WHERE 2 >= (SELECTCOUNT(DISTINCT Salary)FROM EmployeePosition E2 
  WHERE E1.Salary >= E2.Salary) ORDER BY E1.Salary DESC;

--To retrieve two maximum salaries, you can write a query as below: 

SELECT DISTINCT Salary FROM EmployeePosition E1 
 WHERE 2 >= (SELECTCOUNT(DISTINCT Salary) FROM EmployeePosition E2 
  WHERE E1.Salary <= E2.Salary) ORDER BY E1.Salary DESC;
Enter fullscreen mode Exit fullscreen mode

For MongoDB, you can use the aggregation framework to achieve a similar result. Here's the equivalent MongoDB query for retrieving two minimum and maximum salaries from the EmployeePosition collection:

MongoDB Query for Two Minimum Salaries: 

db.EmployeePosition.aggregate([
  {
    $group: {
      _id: null,
      distinctSalaries: { $addToSet: "$Salary" }
    }
  },
  {
    $unwind: "$distinctSalaries"
  },
  {
    $sort: {
      distinctSalaries: 1
    }
  },
  {
    $limit: 2
  }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $group: Groups all documents into one group, creating an array distinctSalaries containing all unique salary values.
  • $unwind: Deconstructs the array distinctSalaries to transform each element of the array into a separate document.
  • $sort: Sorts the documents based on the distinctSalaries field in ascending order.
  • $limit: Limits the result set to the first 2 documents.

MongoDB Query for Two Maximum Salaries: 

db.EmployeePosition.aggregate([
  {
    $group: {
      _id: null,
      distinctSalaries: { $addToSet: "$Salary" }
    }
  },
  {
    $unwind: "$distinctSalaries"
  },
  {
    $sort: {
      distinctSalaries: -1
    }
  },
  {
    $limit: 2
  }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • Similar to the previous query, but sorts in descending order ($sort: { distinctSalaries: -1 }) to get the two maximum salaries.

Adjust the collection and field names according to your actual MongoDB setup.

Question 28 Write a query to retrieve duplicate records from a table.

SELECT EmpID, EmpFname, Department COUNT(*) 
FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department 
HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode

To retrieve duplicate records from a MongoDB collection, you can use the aggregation framework to group by the specified fields and filter based on the count. Here's the equivalent MongoDB query for your given SQL query:

MongoDB Query: 

db.EmployeeInfo.aggregate([
  {
    $group: {
      _id: { EmpID: "$EmpID", EmpFname: "$EmpFname", Department: "$Department" },
      count: { $sum: 1 }
    }
  },
  {
    $match: {
      count: { $gt: 1 }
    }
  }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • $group: Groups the documents by EmpID, EmpFname, and Department and calculates the count of each group using $sum: 1.
  • $match: Filters the grouped documents to include only those with a count greater than 1.

Adjust the collection and field names according to your actual MongoDB setup.

Question 29 Write a query to find the third-highest salary from the EmpPosition table.

SELECT TOP 1 salary
FROM(
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
Enter fullscreen mode Exit fullscreen mode

In MongoDB, finding the third-highest salary can be done using the aggregate framework. Here's the equivalent MongoDB query for your given SQL query:

db.EmpPosition.aggregate([
  {
    $group: {
      _id: null,
      salaries: { $addToSet: "$Salary" }
    }
  },
  {
    $project: {
      thirdHighestSalary: {
        $cond: {
          if: { $gt: [{ $size: "$salaries" }, 2] },
          then: { $arrayElemAt: ["$salaries", -3] },
          else: null
        }
      }
    }
  }
])
Enter fullscreen mode Exit fullscreen mode

Explanation:

  1. $group: Groups all documents into one group, creating an array salaries containing all unique salary values.
  2. $project: Projects a new field thirdHighestSalary that uses the $arrayElemAt operator to get the third-highest salary from the array.

Adjust the collection and field names according to your actual MongoDB setup.

Question 30 Write a query to fetch 50% records from the EmployeeInfo table.

SELECT * 
FROM EmployeeInfo WHERE
EmpID <= (SELECT COUNT(EmpID)/2 from EmployeeInfo);
Enter fullscreen mode Exit fullscreen mode

In MongoDB, you can achieve fetching 50% of records using the aggregate framework. Here's the equivalent MongoDB query for your given SQL query:

var totalRecords = db.EmployeeInfo.count();
var halfRecords = Math.ceil(totalRecords / 2);

db.EmployeeInfo.find().limit(halfRecords);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  1. var totalRecords = db.EmployeeInfo.count();: Calculates the total number of records in the EmployeeInfo collection.
  2. var halfRecords = Math.ceil(totalRecords / 2);: Calculates half of the total records, rounding up to ensure you get at least 50%.
  3. db.EmployeeInfo.find().limit(halfRecords);: Fetches records from the collection, limiting the result set to 50%.

This MongoDB query uses the find() method with the limit() method to achieve the same result.

Adjust the collection and field names according to your actual MongoDB setup.

💖 💪 🙅 🚩
akmaurya31
Avinash Maurya

Posted on January 30, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Top Asking SQL vs Mongo Queries
javascript Top Asking SQL vs Mongo Queries

January 30, 2024