Top Asking SQL vs Mongo Queries
Tags: javascript, mongodb, sql, mysql
Published: 2024-01-30 06:22:28

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:

```sql
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
```

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:

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

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:

```sql
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
```

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.

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

**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:

```sql
SELECT UPPER(EmpFname) AS EmpName
FROM EmployeeInfo;
```

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

### MongoDB Query:

```mongodb
db.EmployeeInfo.aggregate([
    {
        $project: {
            EmpName: { $toUpper: "$EmpFname" }
        }
    }
])
```

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:

```sql
SELECT COUNT(*) AS NumEmployeesInHR
FROM EmployeeInfo
WHERE Department = 'HR';
```

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:

```mongodb
db.EmployeeInfo.count({ Department: 'HR' })
```

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:

```sql
SELECT CURDATE() AS CurrentDate;
```

Explanation:
- `CURDATE()`: Returns the current date in MySQL.

### MongoDB Query:

```mongodb
db.runCommand({ date: 1 })
```

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:

```sql
SELECT SUBSTRING(EmpLname, 1, 4) AS FirstFourCharacters
FROM EmployeeInfo;
```

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:

```mongodb
db.EmployeeInfo.aggregate([
    {
        $project: {
            FirstFourCharacters: { $substr: ["$EmpLname", 0, 4] }
        }
    }
])
```

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:

```sql
SELECT *
FROM EmployeeInfo
WHERE Salary BETWEEN 50000 AND 100000;
```

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

### MongoDB Query:

```mongodb
db.EmployeeInfo.find({
    Salary: { $gte: 50000, $lte: 100000 }
})
```

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:

```sql
SELECT *
FROM EmployeeInfo
WHERE EmpName LIKE 'S%';
```

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

### MongoDB Query:

```mongodb
db.EmployeeInfo.find({
    EmpName: /^S/
})
```

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:

```sql
SELECT *
FROM YourTableName
LIMIT N;
```

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:

```mongodb
db.YourCollectionName.find().limit(N)
```

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:

```sql
SELECT CONCAT(EmpFname, ' ', EmpLname) AS FullName
FROM YourTableName;
```

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

Replace `YourTableName` with the actual name of your table.

### MongoDB Query:

```mongodb
db.YourCollectionName.aggregate([
    {
        $project: {
            FullName: { $concat: ["$EmpFname", " ", "$EmpLname"] }
        }
    }
])
```

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:

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

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:

```mongodb
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 }
        }
    }
])
```

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:

```sql
SELECT * FROM EmployeeInfo WHERE EmpLname LIKE '____a';
```

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:

```mongodb
db.EmployeeInfo.find({
  EmpLname: { $regex: /^.{4}a$/i }
})
```

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:

```sql
SELECT * FROM EmployeeInfo WHERE EmpLname LIKE '____a';
```

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:

```sql
SELECT *
FROM EmployeeInfo
WHERE EmpFname NOT IN ('Radha', 'Krishna');
```

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

### MongoDB Query:

```mongodb
db.EmployeeInfo.find({
    EmpFname: { $nin: ['Radha', 'Krishna'] }
})
```

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:

```mongodb
db.EmployeeInfo.find({
    Address: { $regex: /^DELHI\(DEL\)/ }
})
```

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:

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

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.

```sql 
SELECT Department, count(EmpID) AS EmpDeptCount 
FROM EmployeeInfo GROUP BY Department 
ORDER BY EmpDeptCount ASC;
```

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:

```mongodb
db.EmployeeInfo.aggregate([
  {
    $group: {
      _id: "$Department",
      EmpDeptCount: { $sum: 1 }
    }
  },
  {
    $sort: {
      EmpDeptCount: 1
    }
  }
])
```

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:
```sql 
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;
```

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:

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

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:

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

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.
 
```sql 
SELECT EmpID, EmpFname, Department COUNT(*) 
FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department 
HAVING COUNT(*) > 1;
```

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:

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

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.
```sql 
SELECT TOP 1 salary
FROM(
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
```

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:

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

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.
```sql
SELECT * 
FROM EmployeeInfo WHERE
EmpID <= (SELECT COUNT(EmpID)/2 from EmployeeInfo);
```
In MongoDB, you can achieve fetching 50% of records using the `aggregate` framework. Here's the equivalent MongoDB query for your given SQL query:

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

db.EmployeeInfo.find().limit(halfRecords);
```

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.