lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Which minister left office the latest?

### SQL
SELECT minister FROM party ORDER BY left_office DESC LIMIT 1
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
List member names and their party names.

### SQL
SELECT T1.member_name ,  T2.party_name FROM Member AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
What are the names of members and their corresponding parties?

### SQL
SELECT T1.member_name ,  T2.party_name FROM Member AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show all party names and the number of members in each party.

### SQL
SELECT T2.party_name ,  count(*) FROM Member AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
How many members are in each party?

### SQL
SELECT T2.party_name ,  count(*) FROM Member AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
What is the name of party with most number of members?

### SQL
SELECT T2.party_name FROM Member AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Return the name of the party with the most members.

### SQL
SELECT T2.party_name FROM Member AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show all party names and their region names.

### SQL
SELECT T1.party_name ,  T2.region_name FROM party AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
What are the names of parties and their respective regions?

### SQL
SELECT T1.party_name ,  T2.region_name FROM party AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show names of parties that does not have any members.

### SQL
SELECT party_name FROM party WHERE party_id NOT IN (SELECT party_id FROM Member)
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
What are the names of parties that have no members?

### SQL
SELECT party_name FROM party WHERE party_id NOT IN (SELECT party_id FROM Member)
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show the member names which are in both the party with id 3 and the party with id 1.

### SQL
SELECT member_name FROM member WHERE party_id  =  3 INTERSECT SELECT member_name FROM member WHERE party_id  =  1
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Which member names are shared among members in the party with the id 3 and the party with the id 1?

### SQL
SELECT member_name FROM member WHERE party_id  =  3 INTERSECT SELECT member_name FROM member WHERE party_id  =  1
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show member names that are not in the Progress Party.

### SQL
SELECT T1.member_name FROM Member AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id WHERE T2.Party_name != "Progress Party"
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Which member names corresponding to members who are not in the Progress Party?

### SQL
SELECT T1.member_name FROM Member AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id WHERE T2.Party_name != "Progress Party"
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
How many party events do we have?

### SQL
SELECT count(*) FROM party_events
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Count the number of party events.

### SQL
SELECT count(*) FROM party_events
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show party names and the number of events for each party.

### SQL
SELECT T2.party_name ,  count(*) FROM party_events AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
How many events are there for each party?

### SQL
SELECT T2.party_name ,  count(*) FROM party_events AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show all member names who are not in charge of any event.

### SQL
SELECT member_name FROM member EXCEPT SELECT T1.member_name FROM member AS T1 JOIN party_events AS T2 ON T1.member_id  =  T2.member_in_charge_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
What are the names of members who are not in charge of any events?

### SQL
SELECT member_name FROM member EXCEPT SELECT T1.member_name FROM member AS T1 JOIN party_events AS T2 ON T1.member_id  =  T2.member_in_charge_id
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
What are the names of parties with at least 2 events?

### SQL
SELECT T2.party_name FROM party_events AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Return the names of parties that have two or more events.

### SQL
SELECT T2.party_name FROM party_events AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
What is the name of member in charge of greatest number of events?

### SQL
SELECT T1.member_name FROM member AS T1 JOIN party_events AS T2 ON T1.member_id  =  T2.member_in_charge_id GROUP BY T2.member_in_charge_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Return the name of the member who is in charge of the most events.

### SQL
SELECT T1.member_name FROM member AS T1 JOIN party_events AS T2 ON T1.member_id  =  T2.member_in_charge_id GROUP BY T2.member_in_charge_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
find the event names that have more than 2 records.

### SQL
SELECT event_name FROM party_events GROUP BY event_name HAVING count(*)  >  2
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Which event names were used more than twice for party events?

### SQL
SELECT event_name FROM party_events GROUP BY event_name HAVING count(*)  >  2
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
How many Annual Meeting events happened in the United Kingdom region?

### SQL
SELECT count(*) FROM region AS t1 JOIN party AS t2 ON t1.region_id  =  t2.region_id JOIN party_events AS t3 ON t2.party_id  =  t3.party_id WHERE t1.region_name  =  "United Kingdom" AND t3.Event_Name  =  "Annaual Meeting"
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Count the number of Annual Meeting events that took place in the region of the United Kingdom.

### SQL
SELECT count(*) FROM region AS t1 JOIN party AS t2 ON t1.region_id  =  t2.region_id JOIN party_events AS t3 ON t2.party_id  =  t3.party_id WHERE t1.region_name  =  "United Kingdom" AND t3.Event_Name  =  "Annaual Meeting"
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
How many pilots are there?

### SQL
SELECT count(*) FROM pilot
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
List the names of pilots in ascending order of rank.

### SQL
SELECT Pilot_name FROM pilot ORDER BY Rank ASC
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
What are the positions and teams of pilots?

### SQL
SELECT POSITION ,  Team FROM pilot
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
List the distinct positions of pilots older than 30.

### SQL
SELECT DISTINCT POSITION FROM pilot WHERE Age  >  30
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show the names of pilots from team "Bradley" or "Fordham".

### SQL
SELECT Pilot_name FROM pilot WHERE Team  =  "Bradley" OR Team  =  "Fordham"
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
What is the joined year of the pilot of the highest rank?

### SQL
SELECT Join_Year FROM pilot ORDER BY Rank ASC LIMIT 1
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
What are the different nationalities of pilots? Show each nationality and the number of pilots of each nationality.

### SQL
SELECT Nationality ,  COUNT(*) FROM pilot GROUP BY Nationality
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show the most common nationality of pilots.

### SQL
SELECT Nationality FROM pilot GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show the pilot positions that have both pilots joining after year 2005 and pilots joining before 2000.

### SQL
SELECT POSITION FROM pilot WHERE Join_Year	 <  2000 INTERSECT SELECT POSITION FROM pilot WHERE Join_Year	 >  2005
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show the names of pilots and models of aircrafts they have flied with.

### SQL
SELECT T3.Pilot_name ,  T2.Model FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show the names of pilots and fleet series of the aircrafts they have flied with in ascending order of the rank of the pilot.

### SQL
SELECT T3.Pilot_name ,  T2.Fleet_Series FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID ORDER BY T3.Rank
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show the fleet series of the aircrafts flied by pilots younger than 34

### SQL
SELECT T2.Fleet_Series FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID WHERE T3.Age  <  34
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show the names of pilots and the number of records they have.

### SQL
SELECT T2.Pilot_name ,  COUNT(*) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID  =  T2.pilot_ID GROUP BY T2.Pilot_name
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show names of pilots that have more than one record.

### SQL
SELECT T2.Pilot_name ,  COUNT(*) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID  =  T2.pilot_ID GROUP BY T2.Pilot_name HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "aircraft" (
"Aircraft_ID" int,
"Order_Year" int,
"Manufacturer" text,
"Model" text,
"Fleet_Series" text,
"Powertrain" text,
"Fuel_Propulsion" text,
PRIMARY KEY ("Aircraft_ID")
)

CREATE TABLE "pilot" (
"Pilot_ID" int,
"Pilot_name" text,
"Rank" int,
"Age" int,
"Nationality" text,
"Position" text,
"Join_Year" int,
"Team" text,
PRIMARY KEY ("Pilot_ID")
)

CREATE TABLE "pilot_record" (
"Record_ID" int,
"Pilot_ID" int,
"Aircraft_ID" int,
"Date" text,
PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date"),
FOREIGN KEY (`Pilot_ID`) REFERENCES `pilot`(`Pilot_ID`),
FOREIGN KEY (`Aircraft_ID`) REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
List the names of pilots that do not have any record.

### SQL
SELECT Pilot_name FROM pilot WHERE Pilot_ID NOT IN (SELECT Pilot_ID FROM pilot_record)
### Database Schema
CREATE TABLE Ref_Document_Types (
document_type_code CHAR(15) NOT NULL,
document_type_description VARCHAR(255) NOT NULL,
PRIMARY KEY (document_type_code)
)

CREATE TABLE Roles (
role_code CHAR(15) NOT NULL,
role_description VARCHAR(255),
PRIMARY KEY (role_code)
)

CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
address_details VARCHAR(255),
PRIMARY KEY (address_id)
)

CREATE TABLE Ref_Document_Status (
document_status_code CHAR(15) NOT NULL,
document_status_description VARCHAR(255) NOT NULL,
PRIMARY KEY (document_status_code)
)

CREATE TABLE Ref_Shipping_Agents (
shipping_agent_code CHAR(15) NOT NULL,
shipping_agent_name VARCHAR(255) NOT NULL,
shipping_agent_description VARCHAR(255) NOT NULL,
PRIMARY KEY (shipping_agent_code)
)

CREATE TABLE Documents (
document_id INTEGER NOT NULL,
document_status_code CHAR(15) NOT NULL,
document_type_code CHAR(15) NOT NULL,
shipping_agent_code CHAR(15),
receipt_date DATETIME,
receipt_number VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (document_id),
FOREIGN KEY (document_type_code) REFERENCES Ref_Document_Types (document_type_code),
FOREIGN KEY (document_status_code) REFERENCES Ref_Document_Status (document_status_code),
FOREIGN KEY (shipping_agent_code) REFERENCES Ref_Shipping_Agents (shipping_agent_code)
)

CREATE TABLE Employees (
employee_id INTEGER NOT NULL,
role_code CHAR(15) NOT NULL,
employee_name VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (employee_id),
FOREIGN KEY (role_code) REFERENCES Roles (role_code)
)

CREATE TABLE Document_Drafts (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
draft_details VARCHAR(255),
PRIMARY KEY (document_id, draft_number),
FOREIGN KEY (document_id) REFERENCES Documents (document_id)
)

CREATE TABLE Draft_Copies (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
copy_number INTEGER NOT NULL,
PRIMARY KEY (document_id, draft_number, copy_number),
FOREIGN KEY (document_id, draft_number) REFERENCES Document_Drafts (document_id,draft_number)
)

CREATE TABLE Circulation_History (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
copy_number INTEGER NOT NULL,
employee_id INTEGER NOT NULL,
PRIMARY KEY (document_id, draft_number, copy_number, employee_id),
FOREIGN KEY (document_id, draft_number, copy_number) REFERENCES Draft_Copies (document_id,draft_number,copy_number),
FOREIGN KEY (employee_id) REFERENCES Employees (employee_id)
)

CREATE TABLE Documents_Mailed (
document_id INTEGER NOT NULL,
mailed_to_address_id INTEGER NOT NULL,
mailing_date DATETIME,
PRIMARY KEY (document_id, mailed_to_address_id),
FOREIGN KEY (document_id) REFERENCES Documents (document_id),
FOREIGN KEY (mailed_to_address_id) REFERENCES Addresses (address_id)
)

### Question
What document status codes do we have?

### SQL
SELECT document_status_code FROM Ref_Document_Status;
### Database Schema
CREATE TABLE Ref_Document_Types (
document_type_code CHAR(15) NOT NULL,
document_type_description VARCHAR(255) NOT NULL,
PRIMARY KEY (document_type_code)
)

CREATE TABLE Roles (
role_code CHAR(15) NOT NULL,
role_description VARCHAR(255),
PRIMARY KEY (role_code)
)

CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
address_details VARCHAR(255),
PRIMARY KEY (address_id)
)

CREATE TABLE Ref_Document_Status (
document_status_code CHAR(15) NOT NULL,
document_status_description VARCHAR(255) NOT NULL,
PRIMARY KEY (document_status_code)
)

CREATE TABLE Ref_Shipping_Agents (
shipping_agent_code CHAR(15) NOT NULL,
shipping_agent_name VARCHAR(255) NOT NULL,
shipping_agent_description VARCHAR(255) NOT NULL,
PRIMARY KEY (shipping_agent_code)
)

CREATE TABLE Documents (
document_id INTEGER NOT NULL,
document_status_code CHAR(15) NOT NULL,
document_type_code CHAR(15) NOT NULL,
shipping_agent_code CHAR(15),
receipt_date DATETIME,
receipt_number VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (document_id),
FOREIGN KEY (document_type_code) REFERENCES Ref_Document_Types (document_type_code),
FOREIGN KEY (document_status_code) REFERENCES Ref_Document_Status (document_status_code),
FOREIGN KEY (shipping_agent_code) REFERENCES Ref_Shipping_Agents (shipping_agent_code)
)

CREATE TABLE Employees (
employee_id INTEGER NOT NULL,
role_code CHAR(15) NOT NULL,
employee_name VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (employee_id),
FOREIGN KEY (role_code) REFERENCES Roles (role_code)
)

CREATE TABLE Document_Drafts (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
draft_details VARCHAR(255),
PRIMARY KEY (document_id, draft_number),
FOREIGN KEY (document_id) REFERENCES Documents (document_id)
)

CREATE TABLE Draft_Copies (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
copy_number INTEGER NOT NULL,
PRIMARY KEY (document_id, draft_number, copy_number),
FOREIGN KEY (document_id, draft_number) REFERENCES Document_Drafts (document_id,draft_number)
)

CREATE TABLE Circulation_History (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
copy_number INTEGER NOT NULL,
employee_id INTEGER NOT NULL,
PRIMARY KEY (document_id, draft_number, copy_number, employee_id),
FOREIGN KEY (document_id, draft_number, copy_number) REFERENCES Draft_Copies (document_id,draft_number,copy_number),
FOREIGN KEY (employee_id) REFERENCES Employees (employee_id)
)

CREATE TABLE Documents_Mailed (
document_id INTEGER NOT NULL,
mailed_to_address_id INTEGER NOT NULL,
mailing_date DATETIME,
PRIMARY KEY (document_id, mailed_to_address_id),
FOREIGN KEY (document_id) REFERENCES Documents (document_id),
FOREIGN KEY (mailed_to_address_id) REFERENCES Addresses (address_id)
)

### Question
What is the description of document status code 'working'?

### SQL
SELECT document_status_description FROM Ref_Document_Status WHERE document_status_code = "working";