lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Roles` (
`role_code` VARCHAR(15) PRIMARY KEY,
`role_description` VARCHAR(80)
)

CREATE TABLE `Users` (
`user_id` INTEGER PRIMARY KEY,
`role_code` VARCHAR(15) NOT NULL,
`user_name` VARCHAR(40),
`user_login` VARCHAR(40),
`password` VARCHAR(40),
FOREIGN KEY (`role_code` ) REFERENCES `Roles`(`role_code` )
)

CREATE TABLE `Document_Structures` (
`document_structure_code` VARCHAR(15) PRIMARY KEY,
`parent_document_structure_code` VARCHAR(15),
`document_structure_description` VARCHAR(80)
)

CREATE TABLE `Functional_Areas` (
`functional_area_code` VARCHAR(15) PRIMARY KEY,
`parent_functional_area_code` VARCHAR(15),
`functional_area_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Images` (
`image_id` INTEGER PRIMARY KEY,
`image_alt_text` VARCHAR(80),
`image_name` VARCHAR(40),
`image_url` VARCHAR(255)
)

CREATE TABLE `Documents` (
`document_code` VARCHAR(15) PRIMARY KEY,
`document_structure_code` VARCHAR(15) NOT NULL,
`document_type_code` VARCHAR(15) NOT NULL,
`access_count` INTEGER,
`document_name` VARCHAR(80),
FOREIGN KEY (`document_structure_code` ) REFERENCES `Document_Structures`(`document_structure_code` )
)

CREATE TABLE `Document_Functional_Areas` (
`document_code` VARCHAR(15) NOT NULL,
`functional_area_code` VARCHAR(15) NOT NULL,
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` ),
FOREIGN KEY (`functional_area_code` ) REFERENCES `Functional_Areas`(`functional_area_code` )
)

CREATE TABLE `Document_Sections` (
`section_id` INTEGER PRIMARY KEY,
`document_code` VARCHAR(15) NOT NULL,
`section_sequence` INTEGER,
`section_code` VARCHAR(20),
`section_title` VARCHAR(80),
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` )
)

CREATE TABLE `Document_Sections_Images` (
`section_id` INTEGER NOT NULL,
`image_id` INTEGER NOT NULL,
PRIMARY KEY (`section_id`,`image_id`),
FOREIGN KEY (`section_id` ) REFERENCES `Document_Sections`(`section_id` ),
FOREIGN KEY (`image_id` ) REFERENCES `Images`(`image_id` )
)

### Question
List all the image name and URLs in the order of their names.

### SQL
SELECT image_name ,  image_url FROM images ORDER BY image_name
### Database Schema
CREATE TABLE `Roles` (
`role_code` VARCHAR(15) PRIMARY KEY,
`role_description` VARCHAR(80)
)

CREATE TABLE `Users` (
`user_id` INTEGER PRIMARY KEY,
`role_code` VARCHAR(15) NOT NULL,
`user_name` VARCHAR(40),
`user_login` VARCHAR(40),
`password` VARCHAR(40),
FOREIGN KEY (`role_code` ) REFERENCES `Roles`(`role_code` )
)

CREATE TABLE `Document_Structures` (
`document_structure_code` VARCHAR(15) PRIMARY KEY,
`parent_document_structure_code` VARCHAR(15),
`document_structure_description` VARCHAR(80)
)

CREATE TABLE `Functional_Areas` (
`functional_area_code` VARCHAR(15) PRIMARY KEY,
`parent_functional_area_code` VARCHAR(15),
`functional_area_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Images` (
`image_id` INTEGER PRIMARY KEY,
`image_alt_text` VARCHAR(80),
`image_name` VARCHAR(40),
`image_url` VARCHAR(255)
)

CREATE TABLE `Documents` (
`document_code` VARCHAR(15) PRIMARY KEY,
`document_structure_code` VARCHAR(15) NOT NULL,
`document_type_code` VARCHAR(15) NOT NULL,
`access_count` INTEGER,
`document_name` VARCHAR(80),
FOREIGN KEY (`document_structure_code` ) REFERENCES `Document_Structures`(`document_structure_code` )
)

CREATE TABLE `Document_Functional_Areas` (
`document_code` VARCHAR(15) NOT NULL,
`functional_area_code` VARCHAR(15) NOT NULL,
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` ),
FOREIGN KEY (`functional_area_code` ) REFERENCES `Functional_Areas`(`functional_area_code` )
)

CREATE TABLE `Document_Sections` (
`section_id` INTEGER PRIMARY KEY,
`document_code` VARCHAR(15) NOT NULL,
`section_sequence` INTEGER,
`section_code` VARCHAR(20),
`section_title` VARCHAR(80),
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` )
)

CREATE TABLE `Document_Sections_Images` (
`section_id` INTEGER NOT NULL,
`image_id` INTEGER NOT NULL,
PRIMARY KEY (`section_id`,`image_id`),
FOREIGN KEY (`section_id` ) REFERENCES `Document_Sections`(`section_id` ),
FOREIGN KEY (`image_id` ) REFERENCES `Images`(`image_id` )
)

### Question
What are the names and urls of images, sorted alphabetically?

### SQL
SELECT image_name ,  image_url FROM images ORDER BY image_name
### Database Schema
CREATE TABLE `Roles` (
`role_code` VARCHAR(15) PRIMARY KEY,
`role_description` VARCHAR(80)
)

CREATE TABLE `Users` (
`user_id` INTEGER PRIMARY KEY,
`role_code` VARCHAR(15) NOT NULL,
`user_name` VARCHAR(40),
`user_login` VARCHAR(40),
`password` VARCHAR(40),
FOREIGN KEY (`role_code` ) REFERENCES `Roles`(`role_code` )
)

CREATE TABLE `Document_Structures` (
`document_structure_code` VARCHAR(15) PRIMARY KEY,
`parent_document_structure_code` VARCHAR(15),
`document_structure_description` VARCHAR(80)
)

CREATE TABLE `Functional_Areas` (
`functional_area_code` VARCHAR(15) PRIMARY KEY,
`parent_functional_area_code` VARCHAR(15),
`functional_area_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Images` (
`image_id` INTEGER PRIMARY KEY,
`image_alt_text` VARCHAR(80),
`image_name` VARCHAR(40),
`image_url` VARCHAR(255)
)

CREATE TABLE `Documents` (
`document_code` VARCHAR(15) PRIMARY KEY,
`document_structure_code` VARCHAR(15) NOT NULL,
`document_type_code` VARCHAR(15) NOT NULL,
`access_count` INTEGER,
`document_name` VARCHAR(80),
FOREIGN KEY (`document_structure_code` ) REFERENCES `Document_Structures`(`document_structure_code` )
)

CREATE TABLE `Document_Functional_Areas` (
`document_code` VARCHAR(15) NOT NULL,
`functional_area_code` VARCHAR(15) NOT NULL,
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` ),
FOREIGN KEY (`functional_area_code` ) REFERENCES `Functional_Areas`(`functional_area_code` )
)

CREATE TABLE `Document_Sections` (
`section_id` INTEGER PRIMARY KEY,
`document_code` VARCHAR(15) NOT NULL,
`section_sequence` INTEGER,
`section_code` VARCHAR(20),
`section_title` VARCHAR(80),
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` )
)

CREATE TABLE `Document_Sections_Images` (
`section_id` INTEGER NOT NULL,
`image_id` INTEGER NOT NULL,
PRIMARY KEY (`section_id`,`image_id`),
FOREIGN KEY (`section_id` ) REFERENCES `Document_Sections`(`section_id` ),
FOREIGN KEY (`image_id` ) REFERENCES `Images`(`image_id` )
)

### Question
Find the number of users in each role.

### SQL
SELECT count(*) ,  role_code FROM users GROUP BY role_code
### Database Schema
CREATE TABLE `Roles` (
`role_code` VARCHAR(15) PRIMARY KEY,
`role_description` VARCHAR(80)
)

CREATE TABLE `Users` (
`user_id` INTEGER PRIMARY KEY,
`role_code` VARCHAR(15) NOT NULL,
`user_name` VARCHAR(40),
`user_login` VARCHAR(40),
`password` VARCHAR(40),
FOREIGN KEY (`role_code` ) REFERENCES `Roles`(`role_code` )
)

CREATE TABLE `Document_Structures` (
`document_structure_code` VARCHAR(15) PRIMARY KEY,
`parent_document_structure_code` VARCHAR(15),
`document_structure_description` VARCHAR(80)
)

CREATE TABLE `Functional_Areas` (
`functional_area_code` VARCHAR(15) PRIMARY KEY,
`parent_functional_area_code` VARCHAR(15),
`functional_area_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Images` (
`image_id` INTEGER PRIMARY KEY,
`image_alt_text` VARCHAR(80),
`image_name` VARCHAR(40),
`image_url` VARCHAR(255)
)

CREATE TABLE `Documents` (
`document_code` VARCHAR(15) PRIMARY KEY,
`document_structure_code` VARCHAR(15) NOT NULL,
`document_type_code` VARCHAR(15) NOT NULL,
`access_count` INTEGER,
`document_name` VARCHAR(80),
FOREIGN KEY (`document_structure_code` ) REFERENCES `Document_Structures`(`document_structure_code` )
)

CREATE TABLE `Document_Functional_Areas` (
`document_code` VARCHAR(15) NOT NULL,
`functional_area_code` VARCHAR(15) NOT NULL,
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` ),
FOREIGN KEY (`functional_area_code` ) REFERENCES `Functional_Areas`(`functional_area_code` )
)

CREATE TABLE `Document_Sections` (
`section_id` INTEGER PRIMARY KEY,
`document_code` VARCHAR(15) NOT NULL,
`section_sequence` INTEGER,
`section_code` VARCHAR(20),
`section_title` VARCHAR(80),
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` )
)

CREATE TABLE `Document_Sections_Images` (
`section_id` INTEGER NOT NULL,
`image_id` INTEGER NOT NULL,
PRIMARY KEY (`section_id`,`image_id`),
FOREIGN KEY (`section_id` ) REFERENCES `Document_Sections`(`section_id` ),
FOREIGN KEY (`image_id` ) REFERENCES `Images`(`image_id` )
)

### Question
What are the different role codes for users, and how many users have each?

### SQL
SELECT count(*) ,  role_code FROM users GROUP BY role_code
### Database Schema
CREATE TABLE `Roles` (
`role_code` VARCHAR(15) PRIMARY KEY,
`role_description` VARCHAR(80)
)

CREATE TABLE `Users` (
`user_id` INTEGER PRIMARY KEY,
`role_code` VARCHAR(15) NOT NULL,
`user_name` VARCHAR(40),
`user_login` VARCHAR(40),
`password` VARCHAR(40),
FOREIGN KEY (`role_code` ) REFERENCES `Roles`(`role_code` )
)

CREATE TABLE `Document_Structures` (
`document_structure_code` VARCHAR(15) PRIMARY KEY,
`parent_document_structure_code` VARCHAR(15),
`document_structure_description` VARCHAR(80)
)

CREATE TABLE `Functional_Areas` (
`functional_area_code` VARCHAR(15) PRIMARY KEY,
`parent_functional_area_code` VARCHAR(15),
`functional_area_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Images` (
`image_id` INTEGER PRIMARY KEY,
`image_alt_text` VARCHAR(80),
`image_name` VARCHAR(40),
`image_url` VARCHAR(255)
)

CREATE TABLE `Documents` (
`document_code` VARCHAR(15) PRIMARY KEY,
`document_structure_code` VARCHAR(15) NOT NULL,
`document_type_code` VARCHAR(15) NOT NULL,
`access_count` INTEGER,
`document_name` VARCHAR(80),
FOREIGN KEY (`document_structure_code` ) REFERENCES `Document_Structures`(`document_structure_code` )
)

CREATE TABLE `Document_Functional_Areas` (
`document_code` VARCHAR(15) NOT NULL,
`functional_area_code` VARCHAR(15) NOT NULL,
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` ),
FOREIGN KEY (`functional_area_code` ) REFERENCES `Functional_Areas`(`functional_area_code` )
)

CREATE TABLE `Document_Sections` (
`section_id` INTEGER PRIMARY KEY,
`document_code` VARCHAR(15) NOT NULL,
`section_sequence` INTEGER,
`section_code` VARCHAR(20),
`section_title` VARCHAR(80),
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` )
)

CREATE TABLE `Document_Sections_Images` (
`section_id` INTEGER NOT NULL,
`image_id` INTEGER NOT NULL,
PRIMARY KEY (`section_id`,`image_id`),
FOREIGN KEY (`section_id` ) REFERENCES `Document_Sections`(`section_id` ),
FOREIGN KEY (`image_id` ) REFERENCES `Images`(`image_id` )
)

### Question
What document types have more than 2 corresponding documents?

### SQL
SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  2
### Database Schema
CREATE TABLE `Roles` (
`role_code` VARCHAR(15) PRIMARY KEY,
`role_description` VARCHAR(80)
)

CREATE TABLE `Users` (
`user_id` INTEGER PRIMARY KEY,
`role_code` VARCHAR(15) NOT NULL,
`user_name` VARCHAR(40),
`user_login` VARCHAR(40),
`password` VARCHAR(40),
FOREIGN KEY (`role_code` ) REFERENCES `Roles`(`role_code` )
)

CREATE TABLE `Document_Structures` (
`document_structure_code` VARCHAR(15) PRIMARY KEY,
`parent_document_structure_code` VARCHAR(15),
`document_structure_description` VARCHAR(80)
)

CREATE TABLE `Functional_Areas` (
`functional_area_code` VARCHAR(15) PRIMARY KEY,
`parent_functional_area_code` VARCHAR(15),
`functional_area_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Images` (
`image_id` INTEGER PRIMARY KEY,
`image_alt_text` VARCHAR(80),
`image_name` VARCHAR(40),
`image_url` VARCHAR(255)
)

CREATE TABLE `Documents` (
`document_code` VARCHAR(15) PRIMARY KEY,
`document_structure_code` VARCHAR(15) NOT NULL,
`document_type_code` VARCHAR(15) NOT NULL,
`access_count` INTEGER,
`document_name` VARCHAR(80),
FOREIGN KEY (`document_structure_code` ) REFERENCES `Document_Structures`(`document_structure_code` )
)

CREATE TABLE `Document_Functional_Areas` (
`document_code` VARCHAR(15) NOT NULL,
`functional_area_code` VARCHAR(15) NOT NULL,
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` ),
FOREIGN KEY (`functional_area_code` ) REFERENCES `Functional_Areas`(`functional_area_code` )
)

CREATE TABLE `Document_Sections` (
`section_id` INTEGER PRIMARY KEY,
`document_code` VARCHAR(15) NOT NULL,
`section_sequence` INTEGER,
`section_code` VARCHAR(20),
`section_title` VARCHAR(80),
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` )
)

CREATE TABLE `Document_Sections_Images` (
`section_id` INTEGER NOT NULL,
`image_id` INTEGER NOT NULL,
PRIMARY KEY (`section_id`,`image_id`),
FOREIGN KEY (`section_id` ) REFERENCES `Document_Sections`(`section_id` ),
FOREIGN KEY (`image_id` ) REFERENCES `Images`(`image_id` )
)

### Question
Give the codes of document types that have more than 2 corresponding documents.

### SQL
SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  2
### Database Schema
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
### Database Schema
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
Count the number of companies.

### SQL
SELECT count(*) FROM Companies
### Database Schema
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
List the names of companies in descending order of market value.

### SQL
SELECT name FROM Companies ORDER BY Market_Value_billion DESC
### Database Schema
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
Sort the company names in descending order of the company's market value.

### SQL
SELECT name FROM Companies ORDER BY Market_Value_billion DESC
### Database Schema
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
What are the names of companies whose headquarters are not "USA"?

### SQL
SELECT name FROM Companies WHERE Headquarters != 'USA'
### Database Schema
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
Find the names of the companies whose headquarters are not located in "USA".

### SQL
SELECT name FROM Companies WHERE Headquarters != 'USA'
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
What are the average profits of companies?

### SQL
SELECT avg(Profits_billion) FROM Companies
### Database Schema
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
Compute the average profits companies make.

### SQL
SELECT avg(Profits_billion) FROM Companies
### Database Schema
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
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"
### Database Schema
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
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"
### Database Schema
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 different industries are the companies in?

### SQL
SELECT count(DISTINCT Industry) FROM Companies
### Database Schema
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
Count the number of distinct company industries.

### SQL
SELECT count(DISTINCT Industry) FROM Companies
### Database Schema
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
List the names of buildings in descending order of building height.

### SQL
SELECT name FROM buildings ORDER BY Height DESC
### Database Schema
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
What are the names of buildings sorted in descending order of building height?

### SQL
SELECT name FROM buildings ORDER BY Height DESC
### Database Schema
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
Find the stories of the building with the largest height.

### SQL
SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1
### Database Schema
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
What is the stories of highest building?

### SQL
SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
Please show each industry and the corresponding number of companies in that industry.

### SQL
SELECT Industry ,  COUNT(*) FROM Companies GROUP BY Industry
### Database Schema
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
Whah are the name of each industry and the number of companies in that industry?

### SQL
SELECT Industry ,  COUNT(*) FROM Companies GROUP BY Industry
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
List the industry shared by the most companies.

### SQL
SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
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
Which industry has the most companies?

### SQL
SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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"
### Database Schema
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
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"
### Database Schema
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
Find the number of companies whose industry is "Banking" or "Conglomerate",

### SQL
SELECT count(*) FROM Companies WHERE Industry  =  "Banking" OR Industry  =  "Conglomerate"
### Database Schema
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 in either "Banking" industry or "Conglomerate" industry?

### SQL
SELECT count(*) FROM Companies WHERE Industry  =  "Banking" OR Industry  =  "Conglomerate"
### Database Schema
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
Show the headquarters shared by more than two companies.

### SQL
SELECT Headquarters FROM Companies GROUP BY Headquarters HAVING COUNT(*)  >  2
### Database Schema
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
Which headquarter locations are used by more than 2 companies?

### SQL
SELECT Headquarters FROM Companies GROUP BY Headquarters HAVING COUNT(*)  >  2
### Database Schema
CREATE TABLE Addresses (
Address_ID INTEGER NOT NULL ,
address_details VARCHAR(255),
PRIMARY KEY (Address_ID),
UNIQUE (Address_ID)
)

CREATE TABLE Locations (
Location_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Location_ID)
)

CREATE TABLE Products (
Product_ID INTEGER NOT NULL,
Product_Type_Code CHAR(15),
Product_Name VARCHAR(255),
Product_Price DECIMAL(20,4),
PRIMARY KEY (Product_ID),
UNIQUE (Product_ID)
)

CREATE TABLE Parties (
Party_ID INTEGER NOT NULL,
Party_Details VARCHAR(255),
PRIMARY KEY (Party_ID)
)

CREATE TABLE Assets (
Asset_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Asset_ID)
)

CREATE TABLE Channels (
Channel_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Channel_ID)
)

CREATE TABLE Finances (
Finance_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Finance_ID)
)

CREATE TABLE Events (
Event_ID INTEGER NOT NULL ,
Address_ID INTEGER,
Channel_ID INTEGER NOT NULL,
Event_Type_Code CHAR(15),
Finance_ID INTEGER NOT NULL,
Location_ID INTEGER NOT NULL,
PRIMARY KEY (Event_ID),
UNIQUE (Event_ID),
FOREIGN KEY (Location_ID) REFERENCES Locations (Location_ID),
FOREIGN KEY (Address_ID) REFERENCES Addresses (Address_ID),
FOREIGN KEY (Finance_ID) REFERENCES Finances (Finance_ID)
)

CREATE TABLE Products_in_Events (
Product_in_Event_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
Product_ID INTEGER NOT NULL,
PRIMARY KEY (Product_in_Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID),
FOREIGN KEY (Product_ID) REFERENCES Products (Product_ID)
)

CREATE TABLE Parties_in_Events (
Party_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
Role_Code CHAR(15),
PRIMARY KEY (Party_ID, Event_ID),
FOREIGN KEY (Party_ID) REFERENCES Parties (Party_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
)

CREATE TABLE Agreements (
Document_ID INTEGER NOT NULL ,
Event_ID INTEGER NOT NULL,
PRIMARY KEY (Document_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
)

CREATE TABLE Assets_in_Events (
Asset_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
PRIMARY KEY (Asset_ID, Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
)

### Question
How many products are there?

### SQL
SELECT count(*) FROM Products
### Database Schema
CREATE TABLE Addresses (
Address_ID INTEGER NOT NULL ,
address_details VARCHAR(255),
PRIMARY KEY (Address_ID),
UNIQUE (Address_ID)
)

CREATE TABLE Locations (
Location_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Location_ID)
)

CREATE TABLE Products (
Product_ID INTEGER NOT NULL,
Product_Type_Code CHAR(15),
Product_Name VARCHAR(255),
Product_Price DECIMAL(20,4),
PRIMARY KEY (Product_ID),
UNIQUE (Product_ID)
)

CREATE TABLE Parties (
Party_ID INTEGER NOT NULL,
Party_Details VARCHAR(255),
PRIMARY KEY (Party_ID)
)

CREATE TABLE Assets (
Asset_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Asset_ID)
)

CREATE TABLE Channels (
Channel_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Channel_ID)
)

CREATE TABLE Finances (
Finance_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Finance_ID)
)

CREATE TABLE Events (
Event_ID INTEGER NOT NULL ,
Address_ID INTEGER,
Channel_ID INTEGER NOT NULL,
Event_Type_Code CHAR(15),
Finance_ID INTEGER NOT NULL,
Location_ID INTEGER NOT NULL,
PRIMARY KEY (Event_ID),
UNIQUE (Event_ID),
FOREIGN KEY (Location_ID) REFERENCES Locations (Location_ID),
FOREIGN KEY (Address_ID) REFERENCES Addresses (Address_ID),
FOREIGN KEY (Finance_ID) REFERENCES Finances (Finance_ID)
)

CREATE TABLE Products_in_Events (
Product_in_Event_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
Product_ID INTEGER NOT NULL,
PRIMARY KEY (Product_in_Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID),
FOREIGN KEY (Product_ID) REFERENCES Products (Product_ID)
)

CREATE TABLE Parties_in_Events (
Party_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
Role_Code CHAR(15),
PRIMARY KEY (Party_ID, Event_ID),
FOREIGN KEY (Party_ID) REFERENCES Parties (Party_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
)

CREATE TABLE Agreements (
Document_ID INTEGER NOT NULL ,
Event_ID INTEGER NOT NULL,
PRIMARY KEY (Document_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
)

CREATE TABLE Assets_in_Events (
Asset_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
PRIMARY KEY (Asset_ID, Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
)

### Question
List the name of products in ascending order of price.

### SQL
SELECT Product_Name FROM Products ORDER BY Product_Price ASC