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
List the names of companies by ascending number of sales.
assistant
SELECT Name FROM company ORDER BY Sales_in_Billion ASC
human
Show the different headquarters and number of companies at each headquarter.
assistant
SELECT Headquarters , COUNT(*) FROM company GROUP BY Headquarters
human
Show the names of employees that work for companies with sales bigger than 200.
assistant
SELECT 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 WHERE T3.Sales_in_Billion > 200
human
Show names of companies and that of employees in descending order of number of years working for that employee.
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 ORDER BY T1.Year_working
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 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 ;