lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many different products correspond to each order id?

### SQL
SELECT order_id ,  count(DISTINCT product_id) FROM Order_items GROUP BY order_id
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Show order ids and the total quantity in each order.

### SQL
SELECT order_id ,  sum(product_quantity) FROM Order_items GROUP BY order_id
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Give the order ids for all orders, as well as the total product quantity in each.

### SQL
SELECT order_id ,  sum(product_quantity) FROM Order_items GROUP BY order_id
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many products were not included in any order?

### SQL
SELECT count(*) FROM products WHERE product_id NOT IN ( SELECT product_id FROM Order_items )
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Count the number of products that were never ordered.

### SQL
SELECT count(*) FROM products WHERE product_id NOT IN ( SELECT product_id FROM Order_items )
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
How many churches opened before 1850 are there?

### SQL
SELECT count(*) FROM Church WHERE Open_Date  <  1850
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the name, open date, and organizer for all churches.

### SQL
SELECT name ,  open_date ,  organized_by FROM Church
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
List all church names in descending order of opening date.

### SQL
SELECT name FROM church ORDER BY open_date DESC
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the opening year in whcih at least two churches opened.

### SQL
SELECT open_date FROM church GROUP BY open_date HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the organizer and name for churches that opened between 1830 and 1840.

### SQL
SELECT organized_by ,  name FROM church WHERE open_date BETWEEN 1830 AND 1840
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show all opening years and the number of churches that opened in that year.

### SQL
SELECT open_date ,  count(*) FROM church GROUP BY open_date
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the name and opening year for three churches that opened most recently.

### SQL
SELECT name ,  open_date FROM church ORDER BY open_date DESC LIMIT 3
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
How many female people are older than 30 in our record?

### SQL
SELECT count(*) FROM people WHERE is_male  =  'F' AND age  >  30
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the country where people older than 30 and younger than 25 are from.

### SQL
SELECT country FROM people WHERE age  <  25 INTERSECT SELECT country FROM people WHERE age  >  30
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the minimum, maximum, and average age for all people.

### SQL
SELECT min(age) ,  max(age) ,  avg(age) FROM people
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the name and country for all people whose age is smaller than the average.

### SQL
SELECT name ,  country FROM people WHERE age  <  (SELECT avg(age) FROM people)
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the pair of male and female names in all weddings after year 2014

### SQL
SELECT T2.name ,  T3.name FROM wedding AS T1 JOIN people AS T2 ON T1.male_id  =  T2.people_id JOIN people AS T3 ON T1.female_id  =  T3.people_id WHERE T1.year  >  2014
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the name and age for all male people who don't have a wedding.

### SQL
SELECT name ,  age FROM people WHERE is_male  =  'T' AND people_id NOT IN (SELECT male_id FROM wedding)
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show all church names except for those that had a wedding in year 2015.

### SQL
SELECT name FROM church EXCEPT SELECT T1.name FROM church AS T1 JOIN wedding AS T2 ON T1.church_id  =  T2.church_id WHERE T2.year  =  2015
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show all church names that have hosted least two weddings.

### SQL
SELECT T1.name FROM church AS T1 JOIN wedding AS T2 ON T1.church_id  =  T2.church_id GROUP BY T1.church_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the names for all females from Canada having a wedding in year 2016.

### SQL
SELECT T2.name FROM wedding AS T1 JOIN people AS T2 ON T1.female_id  =  T2.people_id WHERE T1.year  =  2016 AND T2.is_male  =  'F' AND T2.country  =  'Canada'
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
How many weddings are there in year 2016?

### SQL
SELECT count(*) FROM wedding WHERE YEAR  =  2016
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show the church names for the weddings of all people older than 30.

### SQL
SELECT T4.name FROM wedding AS T1 JOIN people AS T2 ON T1.male_id  =  T2.people_id JOIN people AS T3 ON T1.female_id  =  T3.people_id JOIN church AS T4 ON T4.church_id  =  T1.church_id WHERE T2.age  >  30 OR T3.age  >  30
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
Show all countries and the number of people from each country.

### SQL
SELECT country ,  count(*) FROM people GROUP BY country
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
)

CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
)

### Question
How many churches have a wedding in year 2016?

### SQL
SELECT COUNT (DISTINCT church_id) FROM wedding WHERE YEAR  =  2016
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
How many artists do we have?

### SQL
SELECT count(*) FROM artist
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Count the number of artists.

### SQL
SELECT count(*) FROM artist
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show all artist name, age, and country ordered by the yeared they joined.

### SQL
SELECT name ,  age ,  country FROM artist ORDER BY Year_Join
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are the names, ages, and countries of artists, sorted by the year they joined?

### SQL
SELECT name ,  age ,  country FROM artist ORDER BY Year_Join
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are all distinct country for artists?

### SQL
SELECT DISTINCT country FROM artist
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Return the different countries for artists.

### SQL
SELECT DISTINCT country FROM artist
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show all artist names and the year joined who are not from United States.

### SQL
SELECT name ,  year_join FROM artist WHERE country != 'United States'
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are the names and year of joining for artists that do not have the country "United States"?

### SQL
SELECT name ,  year_join FROM artist WHERE country != 'United States'
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
How many artists are above age 46 and joined after 1990?

### SQL
SELECT count(*) FROM artist WHERE age  >  46 AND year_join  >  1990
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Count the number of artists who are older than 46 and joined after 1990.

### SQL
SELECT count(*) FROM artist WHERE age  >  46 AND year_join  >  1990
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What is the average and minimum age of all artists from United States.

### SQL
SELECT avg(age) ,  min(age) FROM artist WHERE country  =  'United States'
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Return the average and minimum ages across artists from the United States.

### SQL
SELECT avg(age) ,  min(age) FROM artist WHERE country  =  'United States'
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What is the name of the artist who joined latest?

### SQL
SELECT name FROM artist ORDER BY year_join DESC LIMIT 1
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Return the name of the artist who has the latest join year.

### SQL
SELECT name FROM artist ORDER BY year_join DESC LIMIT 1
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
How many exhibition are there in year 2005 or after?

### SQL
SELECT count(*) FROM exhibition WHERE YEAR  >=  2005
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Count the number of exhibitions that happened in or after 2005.

### SQL
SELECT count(*) FROM exhibition WHERE YEAR  >=  2005
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show theme and year for all exhibitions with ticket prices lower than 15.

### SQL
SELECT theme ,  YEAR FROM exhibition WHERE ticket_price  <  15
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are the theme and year for all exhibitions that have a ticket price under 15?

### SQL
SELECT theme ,  YEAR FROM exhibition WHERE ticket_price  <  15
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show all artist names and the number of exhibitions for each artist.

### SQL
SELECT T2.name ,  count(*) FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id  =  T2.artist_id GROUP BY T1.artist_id
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
How many exhibitions has each artist had?

### SQL
SELECT T2.name ,  count(*) FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id  =  T2.artist_id GROUP BY T1.artist_id
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What is the name and country for the artist with most number of exhibitions?

### SQL
SELECT T2.name , T2.country FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id  =  T2.artist_id GROUP BY T1.artist_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Return the name and country corresponding to the artist who has had the most exhibitions.

### SQL
SELECT T2.name , T2.country FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id  =  T2.artist_id GROUP BY T1.artist_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show names for artists without any exhibition.

### SQL
SELECT name FROM artist WHERE artist_id NOT IN (SELECT artist_id FROM exhibition)
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are the names of artists that have not had any exhibitions?

### SQL
SELECT name FROM artist WHERE artist_id NOT IN (SELECT artist_id FROM exhibition)
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What is the theme and artist name for the exhibition with a ticket price higher than the average?

### SQL
SELECT T1.theme ,  T2.name FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id  =  T2.artist_id WHERE T1.ticket_price  >  (SELECT avg(ticket_price) FROM exhibition)