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
How many heads of the departments are older than 56 ?
assistant
SELECT count(*) FROM head WHERE age > 56
human
What is the average number of employees of the departments whose rank is between 10 and 15?
assistant
SELECT avg(num_employees) FROM department WHERE ranking BETWEEN 10 AND 15
human
How many departments are led by heads who are not mentioned?
assistant
SELECT count(*) FROM department WHERE department_id NOT IN (SELECT department_id FROM management);
human
In which year were most departments established?
assistant
SELECT creation FROM department GROUP BY creation ORDER BY count(*) DESC LIMIT 1
human
List the creation year, name and budget of each department.
assistant
SELECT creation , name , budget_in_billions FROM department
human
What are the maximum and minimum budget of the departments?
assistant
SELECT MAX( budget_in_billions ) , MIN( budget_in_billions ) FROM department ;