MySQL Query Questions

QUESTION: 1 Write an SQL query to fetch the EmpId and FullName of all the employees working under Manager with id – ‘986’.

Ans. We can use the EmployeeDetails table to fetch the employee details with a where clause for the manager-

SELECT  EmpId, FullName 
FROM EmployeeDetails 
WHERE ManagerId = 986;

QUESTION: 2 Write an SQL query to fetch the different projects available from the EmployeeSalary table.

Ans. While referring to the EmployeeSalary table, we can see that this table contains project values corresponding to each employee, or we can say that we will have duplicate project values while selecting Project values from this table.

So, we will use the distinct clause to get the unique values of the Project.

SELECT DISTINCT(Project) 
FROM EmployeeSalary;

QUESTION: 3  Write an SQL query to fetch records that are present in one table but not in another table.

Ans. SQL Server – Using MINUS- operator-

SELECT * FROM EmployeeSalary 
MINUS 
SELECT * FROM ManagerSalary;

MySQL – Since MySQL doesn’t have MINUS operator so we can use LEFT join-

SELECT EmployeeSalary.
* FROM EmployeeSalary
LEFT JOIN 
ManagerSalary USING (EmpId) 
WHERE ManagerSalary.EmpId IS NULL;

QUESTION: 4 Write an SQL query to fetch the EmpIds that are present in both the tables –   ‘EmployeeDetails’ and ‘EmployeeSalary.

Ans. Using sub query-

SELECT EmpId FROM  
EmployeeDetails  
where EmpId IN  
(SELECT EmpId FROM EmployeeSalary);

QUESTION: 5 Fetch all the employees who are not working on any project.

Ans. This is one of the very basic interview questions in which the interviewer wants to see if the person knows about the commonly used – Is NULL operator.

SELECT EmpId  
FROM EmployeeSalary  
WHERE Project IS NULL;

Question: 6 Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000.

Ans. Here, we will use BETWEEN in the ‘where’ clause to return the EmpId of the employees with salary satisfying the required criteria and then use it as subquery to find the fullName of the employee from EmployeeDetails table.

SELECT FullName  
FROM EmployeeDetails  
WHERE EmpId IN  
(SELECT EmpId FROM EmployeeSalary  
WHERE Salary BETWEEN 5000 AND 10000);

QUESTION: 7 Write an SQL query to fetch all the Employees details from EmployeeDetails table who joined in the Year 2020.

Ans. Using BETWEEN for the date range ’01-01-2020′ AND ’31-12-2020′-

SELECT * FROM EmployeeDetails 
WHERE DateOfJoining BETWEEN '2020/01/01' 
AND '2020/12/31';

Also, we can extract year part from the joining date (using YEAR in mySQL)-

SELECT * FROM EmployeeDetails  
WHERE YEAR(DateOfJoining) = '2020';

QUESTION: 8 Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.

Ans. Using ‘Exists’-

SELECT * FROM EmployeeDetails E 
WHERE EXISTS 
(SELECT * FROM EmployeeSalary S  
WHERE  E.EmpId = S.EmpId);

QUESTION: 9 Write an SQL query to fetch project-wise count of employees sorted by project’s count in descending order.

Ans. The query has two requirements – first to fetch the project-wise count and then to sort the result by that count.
For project-wise count, we will be using the GROUP BY clause and for sorting, we will use the ORDER BY clause on the alias of the project-count.

SELECT Project, count
(EmpId) EmpProjectCount FROM EmployeeSalary 
GROUP BY Project 
ORDER BY EmpProjectCount DESC;

QUESTION: 10 Write a query to fetch employee names and salary records. Display the employee details even if the salary record is not present for the employee.

Ans. This is again one of the very common interview questions in which the interviewer just wants to check the basic knowledge of SQL JOINS.
Here, we can use left join with EmployeeDetail table on the left side of the EmployeeSalary table.

SELECT E.FullName, 
S.Salary  FROM EmployeeDetails E  
LEFT JOIN  
EmployeeSalary S 
ON E.EmpId = S.EmpId;

QUESTION: 11 How can you validate emails using a single query?

To validate emails you can use the regular expressions function (REGEXP_LIKE). Consider the below query.

SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

QUESTION: 12  If you are assigned a task, to find the information of PROCEDURES. What are the basic commands that you will use to do so?

To check the procedures, you can consider the following query.

SELECT * FROM SampleSource
WHERE Type=’PROCEDURE’
AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

To find the procedures columns information, you can consider the following query.

SELECT OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from ALL_ARGUMENTS

QUESTION: 13 Suppose you have to collect the first name, middle name and the last name of students from the below table. But, you observe that there few missing values either in the first name, middle name and the last name columns. How will you return the first non-null values?

StudentID	FirstName	MiddleName	LastName
1	Rohit	Kumar	NULL
2	Sakshi	Chowdhary	NULL
3	NULL	Yash	Singhania
4	Akash	NULL	Kumar
5	Avinash	NULL	Daksh

You can use the COALESCE function to return the first non-null value from the table. Consider the below query.

1	SELECT StudentID, COALESCE(FirstName, MiddleName, LastName) as Name FROM Student

QUESTION: 14 Identify what is wrong in the below query.
SELECT EmployeeID, AVG(Salary)
FROM EmployeeDetails
WHERE AVG(Salary) > 75
GROUP BY EmployeeID

The answer is quite simple. You cannot use the WHERE clause to restrict the groups. Instead of this, you have to use the HAVING clause.

Your query should be as follows:

1 2 3 4

SELECT EmployeeID, AVG(Salary) FROM EmployeeDetails HAVING AVG(Salary) > 75 GROUP BY EmployeeID

QUESTION: 15  How To Display Nth Highest Salary From A Table In A Mysql Query?

Consider the table named “Employee”.
Now, to find the Nth salary consider the below statement.

1	SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT n-1,1

So, if you want to find out the 7th largest salary, consider the below query.

1	SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 6,1