Database: department_management

CREATE TABLE IF NOT EXISTS "department" (
"Department_ID" int,
"Name" text,
"Creation" text,
"Ranking" int,
"Budget_in_Billions" real,
"Num_Employees" real,
PRIMARY KEY ("Department_ID")
);
CREATE TABLE IF NOT EXISTS "head" (
"head_ID" int,
"name" text,
"born_state" text,
"age" real,
PRIMARY KEY ("head_ID")
);
CREATE TABLE IF NOT EXISTS "management" (
"department_ID" int,
"head_ID" int,
"temporary_acting" text,
PRIMARY KEY ("Department_ID","head_ID"),
FOREIGN KEY ("Department_ID") REFERENCES `department`("Department_ID"),
FOREIGN KEY ("head_ID") REFERENCES `head`("head_ID")
);

Question: How many heads of the departments are older than 56 ?
SQL: SELECT count(*) FROM head WHERE age  >  56

Question: List the name, born state and age of the heads of departments ordered by age.
SQL: SELECT name ,  born_state ,  age FROM head ORDER BY age

Question: List the creation year, name and budget of each department.
SQL: SELECT creation ,  name ,  budget_in_billions FROM department

Question: What are the maximum and minimum budget of the departments?
SQL: SELECT max(budget_in_billions) ,  min(budget_in_billions) FROM department

Question: What is the average number of employees of the departments whose rank is between 10 and 15?
SQL: SELECT avg(num_employees) FROM department WHERE ranking BETWEEN 10 AND 15

Question: What are the names of the heads who are born outside the California state?
SQL: SELECT name FROM head WHERE born_state != 'California'

Question: What are the distinct creation years of the departments managed by a secretary born in state 'Alabama'?
SQL: SELECT DISTINCT T1.creation FROM department AS T1 JOIN management AS T2 ON T1.department_id  =  T2.department_id JOIN head AS T3 ON T2.head_id  =  T3.head_id WHERE T3.born_state  =  'Alabama'

Question: What are the names of the states where at least 3 heads were born?
SQL: SELECT born_state FROM head GROUP BY born_state HAVING count(*)  >=  3

Question: In which year were most departments established?
SQL: SELECT creation FROM department GROUP BY creation ORDER BY count(*) DESC LIMIT 1

Question: Show the name and number of employees for the departments managed by heads whose temporary acting value is 'Yes'?
SQL: SELECT T1.name ,  T1.num_employees FROM department AS T1 JOIN management AS T2 ON T1.department_id  =  T2.department_id WHERE T2.temporary_acting  =  'Yes'

Question: How many acting statuses are there?
SQL: SELECT count(DISTINCT temporary_acting) FROM management

Question: How many departments are led by heads who are not mentioned?
SQL: SELECT count(*) FROM department WHERE department_id NOT IN (SELECT department_id FROM management);

Question: What are the distinct ages of the heads who are acting?
SQL: SELECT DISTINCT T1.age FROM management AS T2 JOIN head AS T1 ON T1.head_id  =  T2.head_id WHERE T2.temporary_acting  =  'Yes'

Question: List the states where both the secretary of 'Treasury' department and the secretary of 'Homeland Security' were born.
SQL: SELECT T3.born_state FROM department AS T1 JOIN management AS T2 ON T1.department_id  =  T2.department_id JOIN head AS T3 ON T2.head_id  =  T3.head_id WHERE T1.name  =  'Treasury' INTERSECT SELECT T3.born_state FROM department AS T1 JOIN management AS T2 ON T1.department_id  =  T2.department_id JOIN head AS T3 ON T2.head_id  =  T3.head_id WHERE T1.name  =  'Homeland Security'

Question: Which department has more than 1 head at a time? List the id, name and the number of heads.
SQL: 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

Question: Which head's name has the substring 'Ha'? List the id and name.
SQL: SELECT head_id ,  name FROM head WHERE name LIKE '%Ha%'