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
How many Annual Meeting events happened in the United Kingdom region?
assistant
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'
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
What is the name of party with most number of members?
assistant
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
human
How many party events do we have?
assistant
SELECT count(*) FROM party_events
human
What are the names of parties with at least 2 events?
assistant
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
human
What is the name of member in charge of greatest number of events?
assistant
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 ;