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
List the creation year, name and budget of each department.
assistant
SELECT creation , name , budget_in_billions FROM department
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
What are the names of the states where at least 3 heads were born?
assistant
SELECT born_state FROM head GROUP BY born_state HAVING count(*) >= 3
human
What are the distinct creation years of the departments managed by a secretary born in state 'Alabama'?
assistant
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'
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
List the states where both the secretary of 'Treasury' department and the secretary of 'Homeland Security' were born.
assistant
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" ;