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 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
What are the names of the heads who are born outside the California state?
assistant
SELECT name FROM head WHERE born_state != 'California'
human
List the name, born state and age of the heads of departments ordered by age.
assistant
SELECT name , born_state , age FROM head ORDER BY age
human
How many heads of the departments are older than 56 ?
assistant
SELECT count(*) FROM head WHERE age > 56
human
How many acting statuses are there?
assistant
SELECT count(DISTINCT temporary_acting) FROM management
human
What are the distinct ages of the heads who are acting?
assistant
SELECT DISTINCT T1.age FROM management AS T2 JOIN head AS T1 ON T1.head_id = T2.head_id WHERE T2.temporary_acting = "Yes" ;