human
Schema:
region (Region_ID, Region_name, Date, Label, Format, Catalogue)
party (Party_ID, Minister, Took_office, Left_office, Region_ID, Party_name)
member (Member_ID, Member_Name, Party_ID, In_office)
party_events (Event_ID, Event_Name, Party_ID, Member_in_charge_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
Show all party names and their region names.
assistant
SELECT T1.party_name , T2.region_name FROM party AS T1 JOIN region AS T2 ON T1.region_id = T2.region_id
human
Show all party names and the number of members in each party.
assistant
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
human
Show member names that are not in the Progress Party.
assistant
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'
human
Show all member names who are not in charge of any event.
assistant
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
human
List member names and their party names.
assistant
SELECT T1.member_name , T2.party_name FROM Member AS T1 JOIN party AS T2 ON T1.party_id = T2.party_id
human
Show names of parties that does not have any members.
assistant
SELECT party_name FROM party WHERE party_id NOT IN ( SELECT party_id FROM Member ) ;