human
Schema:
people (People_ID, Age, Name, Nationality, Graduation_College)
company (Company_ID, Name, Headquarters, Industry, Sales_in_Billion, Profits_in_Billion, Assets_in_Billion, Market_Value_in_Billion)
employment (Company_ID, People_ID, Year_working)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names of companies and the number of employees they have
assistant
SELECT T3.Name , COUNT(*) FROM employment AS T1 JOIN people AS T2 ON T1.People_ID = T2.People_ID JOIN company AS T3 ON T1.Company_ID = T3.Company_ID GROUP BY T3.Name
human
What are the headquarters and industries of all companies?
assistant
SELECT Headquarters , Industry FROM company
human
Show the names of companies and of employees.
assistant
SELECT T3.Name , T2.Name FROM employment AS T1 JOIN people AS T2 ON T1.People_ID = T2.People_ID JOIN company AS T3 ON T1.Company_ID = T3.Company_ID
human
Show the headquarters that have both companies in banking industry and companies in oil and gas industry.
assistant
SELECT Headquarters FROM company WHERE Industry = 'Banking' INTERSECT SELECT Headquarters FROM company WHERE Industry = 'Oil AND gas'
human
Show the different headquarters and number of companies at each headquarter.
assistant
SELECT Headquarters , COUNT(*) FROM company GROUP BY Headquarters
human
Show the headquarters that have at least two companies.
assistant
SELECT Headquarters FROM company GROUP BY Headquarters HAVING COUNT( * ) >= 2 ;