Database: company_office

CREATE TABLE "buildings" (
"id" int,
"name" text,
"City" text,
"Height" int,
"Stories" int,
"Status" text,
PRIMARY KEY("id")
);
CREATE TABLE "Companies" (
"id" int,
"name" text,
"Headquarters" text,
"Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value_billion" text,
PRIMARY KEY ("id")
);
CREATE TABLE "Office_locations" (
"building_id" int,
"company_id" int,
"move_in_year" int,
PRIMARY KEY ("building_id", "company_id"),
FOREIGN KEY ("building_id") REFERENCES "buildings"("id"),
FOREIGN KEY ("company_id") REFERENCES "Companies"("id")
);

Question: How many companies are there?
SQL: SELECT count(*) FROM Companies

Question: Count the number of companies.
SQL: SELECT count(*) FROM Companies

Question: List the names of companies in descending order of market value.
SQL: SELECT name FROM Companies ORDER BY Market_Value_billion DESC

Question: Sort the company names in descending order of the company's market value.
SQL: SELECT name FROM Companies ORDER BY Market_Value_billion DESC

Question: What are the names of companies whose headquarters are not "USA"?
SQL: SELECT name FROM Companies WHERE Headquarters != 'USA'

Question: Find the names of the companies whose headquarters are not located in "USA".
SQL: SELECT name FROM Companies WHERE Headquarters != 'USA'

Question: What are the name and assets of each company, sorted in ascending order of company name?
SQL: SELECT name ,  Assets_billion FROM Companies ORDER BY name ASC

Question: List the name and assets of each company in ascending order of company name.
SQL: SELECT name ,  Assets_billion FROM Companies ORDER BY name ASC

Question: What are the average profits of companies?
SQL: SELECT avg(Profits_billion) FROM Companies

Question: Compute the average profits companies make.
SQL: SELECT avg(Profits_billion) FROM Companies

Question: What are the maximum and minimum sales of the companies whose industries are not "Banking".
SQL: SELECT max(Sales_billion) ,  min(Sales_billion) FROM Companies WHERE Industry != "Banking"

Question: Find the maximum and minimum sales of the companies that are not in the "Banking" industry.
SQL: SELECT max(Sales_billion) ,  min(Sales_billion) FROM Companies WHERE Industry != "Banking"

Question: How many different industries are the companies in?
SQL: SELECT count(DISTINCT Industry) FROM Companies

Question: Count the number of distinct company industries.
SQL: SELECT count(DISTINCT Industry) FROM Companies

Question: List the names of buildings in descending order of building height.
SQL: SELECT name FROM buildings ORDER BY Height DESC

Question: What are the names of buildings sorted in descending order of building height?
SQL: SELECT name FROM buildings ORDER BY Height DESC

Question: Find the stories of the building with the largest height.
SQL: SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1

Question: What is the stories of highest building?
SQL: SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1

Question: List the name of a building along with the name of a company whose office is in the building.
SQL: SELECT T3.name ,  T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id

Question: For each company, return the company name and the name of the building its office is located in.
SQL: SELECT T3.name ,  T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id

Question: Show the names of the buildings that have more than one company offices.
SQL: SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id GROUP BY T1.building_id HAVING COUNT(*)  >  1

Question: Which buildings have more than one company offices? Give me the building names.
SQL: SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id GROUP BY T1.building_id HAVING COUNT(*)  >  1

Question: Show the name of the building that has the most company offices.
SQL: SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id GROUP BY T1.building_id ORDER BY COUNT(*) DESC LIMIT 1

Question: Which building has the largest number of company offices? Give me the building name.
SQL: SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id GROUP BY T1.building_id ORDER BY COUNT(*) DESC LIMIT 1

Question: Please show the names of the buildings whose status is "on-hold", in ascending order of stories.
SQL: SELECT name FROM buildings WHERE Status  =  "on-hold" ORDER BY Stories ASC

Question: Find the names of the buildings in "on-hold" status, and sort them in ascending order of building stories.
SQL: SELECT name FROM buildings WHERE Status  =  "on-hold" ORDER BY Stories ASC

Question: Please show each industry and the corresponding number of companies in that industry.
SQL: SELECT Industry ,  COUNT(*) FROM Companies GROUP BY Industry

Question: Whah are the name of each industry and the number of companies in that industry?
SQL: SELECT Industry ,  COUNT(*) FROM Companies GROUP BY Industry

Question: Please show the industries of companies in descending order of the number of companies.
SQL: SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC

Question: Sort all the industries in descending order of the count of companies in each industry
SQL: SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC

Question: List the industry shared by the most companies.
SQL: SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC LIMIT 1

Question: Which industry has the most companies?
SQL: SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC LIMIT 1

Question: List the names of buildings that have no company office.
SQL: SELECT name FROM buildings WHERE id NOT IN (SELECT building_id FROM Office_locations)

Question: Which buildings do not have any company office? Give me the building names.
SQL: SELECT name FROM buildings WHERE id NOT IN (SELECT building_id FROM Office_locations)

Question: Show the industries shared by companies whose headquarters are "USA" and companies whose headquarters are "China".
SQL: SELECT Industry FROM Companies WHERE Headquarters  =  "USA" INTERSECT SELECT Industry FROM Companies WHERE Headquarters  =  "China"

Question: Which industries have both companies with headquarter in "USA" and companies with headquarter in "China"?
SQL: SELECT Industry FROM Companies WHERE Headquarters  =  "USA" INTERSECT SELECT Industry FROM Companies WHERE Headquarters  =  "China"

Question: Find the number of companies whose industry is "Banking" or "Conglomerate",
SQL: SELECT count(*) FROM Companies WHERE Industry  =  "Banking" OR Industry  =  "Conglomerate"

Question: How many companies are in either "Banking" industry or "Conglomerate" industry?
SQL: SELECT count(*) FROM Companies WHERE Industry  =  "Banking" OR Industry  =  "Conglomerate"

Question: Show the headquarters shared by more than two companies.
SQL: SELECT Headquarters FROM Companies GROUP BY Headquarters HAVING COUNT(*)  >  2

Question: Which headquarter locations are used by more than 2 companies?
SQL: SELECT Headquarters FROM Companies GROUP BY Headquarters HAVING COUNT(*)  >  2