human
Schema:
department (Department_ID, Name, Creation, Ranking, Budget_in_Billions, Num_Employees)
head (head_ID, name, born_state, age)
management (department_ID, head_ID, temporary_acting)
Please answer with SQL.
assistant
Sure, I understand.
human
Which department has more than 1 head at a time? List the ID, Name and the number of heads.
assistant
SELECT T1.department_id , T1.name , COUNT( * ) FROM management AS T2 JOIN department AS T1 ON T1.department_id = T2.department_id GROUP BY T1.department_id HAVING COUNT( * ) > 1;
human
Which Department has the most number of employee? tell me the rank and creation year.
assistant
SELECT ranking , creation FROM department WHERE num_employees = ( SELECT MAX( num_employees ) FROM department );
human
Which Department has the least number of employee? tell me the rank and creation year.
assistant
SELECT ranking , creation FROM department WHERE num_employees = ( SELECT MIN( num_employees ) FROM department );
human
Which Department has the highest Budget value? tell me the rank and creation year.
assistant
SELECT ranking , creation FROM department WHERE Budget_in_Billions = ( SELECT MAX( Budget_in_Billions ) FROM department ) ;