Database: party_people

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 regions do we have?
SQL: SELECT count(*) FROM region

Question: Count the number of regions.
SQL: SELECT count(*) FROM region

Question: Show all distinct region names ordered by their labels.
SQL: SELECT DISTINCT region_name FROM region ORDER BY Label

Question: What are the different region names, ordered by labels?
SQL: SELECT DISTINCT region_name FROM region ORDER BY Label

Question: How many parties do we have?
SQL: SELECT count(DISTINCT party_name) FROM party

Question: Count the number of different parties.
SQL: SELECT count(DISTINCT party_name) FROM party

Question: Show the ministers and the time they took and left office, listed by the time they left office.
SQL: SELECT minister ,  took_office ,  left_office FROM party ORDER BY left_office

Question: Who are the ministers, when did they take office, and when did they leave office, ordered by when they left office?
SQL: SELECT minister ,  took_office ,  left_office FROM party ORDER BY left_office

Question: Show the minister who took office after 1961 or before 1959.
SQL: SELECT minister FROM party WHERE took_office  >  1961 OR took_office  <  1959

Question: Who are the ministers who took office after 1961 or before 1959?
SQL: SELECT minister FROM party WHERE took_office  >  1961 OR took_office  <  1959

Question: Show all ministers who do not belong to Progress Party.
SQL: SELECT minister FROM party WHERE party_name != 'Progress Party'

Question: Which ministers are not a part of the Progress Party?
SQL: SELECT minister FROM party WHERE party_name != 'Progress Party'

Question: Show all ministers and parties they belong to in descending order of the time they took office.
SQL: SELECT minister ,  party_name FROM party ORDER BY took_office DESC

Question: Who are the ministers and what parties do they belong to, listed descending by the times they took office?
SQL: SELECT minister ,  party_name FROM party ORDER BY took_office DESC

Question: Return the minister who left office at the latest time.
SQL: SELECT minister FROM party ORDER BY left_office DESC LIMIT 1

Question: Which minister left office the latest?
SQL: SELECT minister FROM party ORDER BY left_office DESC LIMIT 1

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

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

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

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

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

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

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

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

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)

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)

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

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

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"

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"

Question: How many party events do we have?
SQL: SELECT count(*) FROM party_events

Question: Count the number of party events.
SQL: SELECT count(*) FROM party_events

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

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

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

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

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

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

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

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

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

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

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"

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"