mysql or oracle or DB2 A company stores employee and department information in t
ID: 3918453 • Letter: M
Question
mysql or oracle or DB2
A company stores employee and department information in two data tables Employee and Deportment. Write a query to print the respective clepartment name and number of employees in each department for all departments in the Deportment table (even ones with no current employees). Sort the results by descending order ot the number of employees: ittwo or more departments have the same number o? employees, tnen sort those cepartments alphabetically by department name Each row of the resulting output must contain the following respective attributes for a department: 1. The name of the department 2. Tha number of employees in the department DEPARTHENT NAHE OF_EMPLOYEES IN T ? Table Schema EMPLOYEE Type Integer Stririg Integer Name ID A number which uniquely identifies the employee. This is a primary key. The name of the employee. The salary of the employee The department identification of the respective employec. This is a foreign kay to NAME SALARY DEPT ID Integer DEPARTMENT.ID DEPARTMENT Type Integer String String Name ID Tne department's unique identirication number. This is a primary key The name of the department. The place where the department is located. NAME LOCAI 10NExplanation / Answer
The required SQL query :
select d.name, count(*) from Department d, Employee e where e.dept_id = d.id group by d.name order by count(*) desc, d.name asc;
The above query will first join the tables Department and Employee by matching column dept_id in Employee and id in Department. Then it will group the resultant table by department name and count the number of employees in each department. The department name and count of employees will be displayed in descending order by count of employees and in case 2 department has same number of employees then department name will be used for ordering in ascending order of name.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.