lianghsun/spider-text2sql-bench huggingface dataset:
### 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
What are the names and type codes of products?

### SQL
SELECT Product_Name ,  Product_Type_Code 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
Show the prices of the products named "Dining" or "Trading Policy".

### SQL
SELECT Product_Price FROM Products WHERE Product_Name  =  "Dining" OR Product_Name  =  "Trading Policy"
### 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
What is the average price for products?

### SQL
SELECT avg(Product_Price) 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
What is the name of the product with the highest price?

### SQL
SELECT Product_Name FROM Products ORDER BY Product_Price DESC LIMIT 1
### 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
Show different type codes of products and the number of products with each type code.

### SQL
SELECT Product_Type_Code ,  COUNT(*) FROM Products GROUP BY Product_Type_Code
### 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
Show the most common type code across products.

### SQL
SELECT Product_Type_Code FROM Products GROUP BY Product_Type_Code ORDER BY COUNT(*) DESC LIMIT 1
### 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
Show the product type codes that have at least two products.

### SQL
SELECT Product_Type_Code FROM Products GROUP BY Product_Type_Code 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
Show the product type codes that have both products with price higher than 4500 and products with price lower than 3000.

### SQL
SELECT Product_Type_Code FROM Products WHERE Product_Price  >  4500 INTERSECT SELECT Product_Type_Code FROM Products WHERE Product_Price  <  3000
### 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
Show the names of products and the number of events they are in.

### SQL
SELECT T1.Product_Name ,  COUNT(*) FROM Products AS T1 JOIN Products_in_Events AS T2 ON T1.Product_ID  =  T2.Product_ID GROUP BY T1.Product_Name
### 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
Show the names of products and the number of events they are in, sorted by the number of events in descending order.

### SQL
SELECT T1.Product_Name ,  COUNT(*) FROM Products AS T1 JOIN Products_in_Events AS T2 ON T1.Product_ID  =  T2.Product_ID GROUP BY T1.Product_Name ORDER BY COUNT(*) DESC
### 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
Show the names of products that are in at least two events.

### SQL
SELECT T1.Product_Name FROM Products AS T1 JOIN Products_in_Events AS T2 ON T1.Product_ID  =  T2.Product_ID GROUP BY T1.Product_Name 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
Show the names of products that are in at least two events in ascending alphabetical order of product name.

### SQL
SELECT T1.Product_Name FROM Products AS T1 JOIN Products_in_Events AS T2 ON T1.Product_ID  =  T2.Product_ID GROUP BY T1.Product_Name HAVING COUNT(*)  >=  2 ORDER BY T1.Product_Name
### 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 names of products that are not in any event.

### SQL
SELECT Product_Name FROM Products WHERE Product_ID NOT IN (SELECT Product_ID FROM Products_in_Events)
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
How many artworks are there?

### SQL
SELECT count(*) FROM artwork
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
List the name of artworks in ascending alphabetical order.

### SQL
SELECT Name FROM artwork ORDER BY Name ASC
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
List the name of artworks whose type is not "Program Talent Show".

### SQL
SELECT Name FROM artwork WHERE TYPE != "Program Talent Show"
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
What are the names and locations of festivals?

### SQL
SELECT Festival_Name ,  LOCATION FROM festival_detail
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
What are the names of the chairs of festivals, sorted in ascending order of the year held?

### SQL
SELECT Chair_Name FROM festival_detail ORDER BY YEAR ASC
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
What is the location of the festival with the largest number of audience?

### SQL
SELECT LOCATION FROM festival_detail ORDER BY Num_of_Audience DESC LIMIT 1
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
What are the names of festivals held in year 2007?

### SQL
SELECT Festival_Name FROM festival_detail WHERE YEAR  =  2007
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
What is the average number of audience for festivals?

### SQL
SELECT avg(Num_of_Audience) FROM festival_detail
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
Show the names of the three most recent festivals.

### SQL
SELECT Festival_Name FROM festival_detail ORDER BY YEAR DESC LIMIT 3
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
For each nomination, show the name of the artwork and name of the festival where it is nominated.

### SQL
SELECT T2.Name ,  T3.Festival_Name FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
Show distinct types of artworks that are nominated in festivals in 2007.

### SQL
SELECT DISTINCT T2.Type FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID WHERE T3.Year  =  2007
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
Show the names of artworks in ascending order of the year they are nominated in.

### SQL
SELECT T2.Name FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID ORDER BY T3.Year
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
Show the names of festivals that have nominated artworks of type "Program Talent Show".

### SQL
SELECT T3.Festival_Name FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID WHERE T2.Type  =  "Program Talent Show"
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
Show the ids and names of festivals that have at least two nominations for artworks.

### SQL
SELECT T1.Festival_ID ,  T3.Festival_Name FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID GROUP BY T1.Festival_ID HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
Show the id, name of each festival and the number of artworks it has nominated.

### SQL
SELECT T1.Festival_ID ,  T3.Festival_Name ,  COUNT(*) FROM nomination AS T1 JOIN artwork AS T2 ON T1.Artwork_ID  =  T2.Artwork_ID JOIN festival_detail AS T3 ON T1.Festival_ID  =  T3.Festival_ID GROUP BY T1.Festival_ID
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
Please show different types of artworks with the corresponding number of artworks of each type.

### SQL
SELECT TYPE ,  COUNT(*) FROM artwork GROUP BY TYPE
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
List the most common type of artworks.

### SQL
SELECT TYPE FROM artwork GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
List the year in which there are more than one festivals.

### SQL
SELECT YEAR FROM festival_detail GROUP BY YEAR HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
List the name of artworks that are not nominated.

### SQL
SELECT Name FROM Artwork WHERE Artwork_ID NOT IN (SELECT Artwork_ID FROM nomination)
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
Show the number of audience in year 2008 or 2010.

### SQL
SELECT Num_of_Audience FROM festival_detail WHERE YEAR  =  2008 OR YEAR  =  2010
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
What are the total number of the audiences who visited any of the festivals?

### SQL
SELECT sum(Num_of_Audience) FROM festival_detail
### Database Schema
CREATE TABLE "festival_detail" (
"Festival_ID" int,
"Festival_Name" text,
"Chair_Name" text,
"Location" text,
"Year" int,
"Num_of_Audience" int,
PRIMARY KEY ("Festival_ID")
)

CREATE TABLE artwork (
"Artwork_ID" int,
"Type" text,
"Name" text,
PRIMARY KEY ("Artwork_ID")
)

CREATE TABLE nomination (
"Artwork_ID" int,
"Festival_ID" int,
"Result" text,
PRIMARY KEY ("Artwork_ID","Festival_ID"),
FOREIGN KEY ("Artwork_ID") REFERENCES `artwork`("Artwork_ID"),
FOREIGN KEY ("Festival_ID") REFERENCES `festival_detail`("Festival_ID")
)

### Question
In which year are there festivals both inside the 'United States' and outside the 'United States'?

### SQL
SELECT YEAR FROM festival_detail WHERE LOCATION  =  'United States' INTERSECT SELECT YEAR FROM festival_detail WHERE LOCATION != 'United States'