The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.IdNameDepartmentManager ID101JohnAnull102DanA101103JamesA101104AmyA101105AnneA101106RonB101Given the Employee table, write a SQL query that finds out managers with at least 5 direct report. For the above table, your SQL query should return:NameJohnNote:No one would report to himself.Optionsselect Namefrom Employeewhere Id in ( select ManagerId from Employee group by ManagerId having count(*) >= 5);select Namefrom Employeewhere Id in ( select ManagerId from Employee having count(*) >= 5);select Name select ManagerId from Employee group by ManagerId having count(*) >= 5);select Namefrom Employeewhere Id in ( from Employee group by ManagerId having count(*) >= 5);
Question
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.IdNameDepartmentManager ID101JohnAnull102DanA101103JamesA101104AmyA101105AnneA101106RonB101Given the Employee table, write a SQL query that finds out managers with at least 5 direct report. For the above table, your SQL query should return:NameJohnNote:No one would report to himself.Optionsselect Namefrom Employeewhere Id in ( select ManagerId from Employee group by ManagerId having count() >= 5);select Namefrom Employeewhere Id in ( select ManagerId from Employee having count() >= 5);select Name select ManagerId from Employee group by ManagerId having count() >= 5);select Namefrom Employeewhere Id in ( from Employee group by ManagerId having count() >= 5);
Solution
The correct SQL query to find managers with at least 5 direct reports would be:
SELECT Name
FROM Employee
WHERE Id IN (
SELECT ManagerId
FROM Employee
GROUP BY ManagerId
HAVING COUNT(*) >= 5
);
Here's how this query works:
-
The subquery (the part within the parentheses) selects the
ManagerIdfrom theEmployeetable, groups them byManagerId, and then only keeps thoseManagerIds that have a count of 5 or more. This gives us the IDs of managers who have at least 5 direct reports. -
The main query then selects the
Namefrom theEmployeetable where theIdis in the list of IDs returned by the subquery. This gives us the names of the managers who have at least 5 direct reports.
Similar Questions
Select the correct answerThe Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.IdNameSalaryManagerId1Joe7000032Henry8000043Sam60000NULL4Max90000NULLGiven the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.EmployeeJoeOptionsselect e.Name as Employee from Employeeas e inner join Employeeselect e.Name as Employee to Employeefrom e inner join Employee has m on e.ManagerId = m.id where e.Salary > m.Salary;select e.Name as Employee from Employeeas e inner join Employee as m on e.ManagerId = m.idwhere e.Salary > m.Salary;select e.Name as Employeefrom Employee on e.ManagerId = m.id where e.Salary > m.Salary;
Retrieve the names of all employees who donot have supervisors.
For the following table:Employees (EmployeeId, EmployeeName, HireDate, Salary, ReportsTo)You want to identify the supervisor to which each employee reports. You write the following query.SELECT e.EmloyeeName AS [EmployeeName], s.EmployeeName AS [SuperVisorName] FROM Employees eYou need to ensure that the query returns a list of all employees and their respective supervisor. Which join clause should you use to complete the query?INNER JOIN Employees s ON e.ReportsTo = s.EmployeeId;INNER JOIN Employees s ON e.EmployeeId = s.EmployeeId;INNER JOIN Employees s ON e.EmployeeId = s. ReportsTo;RIGHT JOIN Employees s ON e. EmployeeId = s. ReportsTo;
Write a SQL query considering below schema. Create tables with Primary Key, foreign key constraints in given schemas.Manager (mid, eid, mname)Department (did, mid, dname, location)Employee (eid, ename, mobile, salary, joining_date, mid)i.Give the name and salary of employees whose salary are greater than each and every employees who are working under managerid 14.ii.Give the name of employees who have not assigned any department.iii.Give the employee names whose location is Kota.iv.Give the name of manager along with count of employees assigned to him/her in descending order
Find the following table EmployeeDetails, with the attributes EmployeeID, EmpName, Designation, Salary, DeptNo. You are assigned to a task of providing the solution to the operations department by writing the query which displays the employeeID and the Designation of all the employees whose Salaries are more than the Salary of employees designated as ManagerC
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.