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
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 names of the heads who are born outside the California state?
assistant
SELECT name FROM head WHERE born_state != 'California'
human
Show the name and number of employees for the departments managed by heads whose temporary acting value is 'Yes'?
assistant
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'
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
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 head's name has the substring 'Ha'? List the id and name.
assistant
SELECT head_id , name FROM head WHERE name LIKE "%Ha%" ;