CREATE TABLE region (
    Region_ID NUMBER PRIMARY KEY,
    Region_name TEXT,
    Date TEXT,
    Label TEXT,
    Format TEXT,
    Catalogue TEXT
);
CREATE TABLE party (
    Party_ID NUMBER PRIMARY KEY,
    Minister TEXT,
    Took_office TEXT,
    Left_office TEXT,
    Region_ID NUMBER,
    Party_name TEXT,
    FOREIGN KEY ("Region_ID") REFERENCES region("Region_ID")
);
CREATE TABLE member (
    Member_ID NUMBER PRIMARY KEY,
    Member_Name TEXT,
    Party_ID TEXT,
    In_office TEXT,
    FOREIGN KEY ("Party_ID") REFERENCES party("Party_ID")
);
CREATE TABLE party_events (
    Event_ID NUMBER PRIMARY KEY,
    Event_Name TEXT,
    Party_ID NUMBER,
    Member_in_charge_ID NUMBER,
    FOREIGN KEY ("Member_in_charge_ID") REFERENCES member("Member_ID"),
    FOREIGN KEY ("Party_ID") REFERENCES party("Party_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