human
Schema:
college (College_ID, Name, Leader_Name, College_Location)
member (Member_ID, Name, Country, College_ID)
round (Round_ID, Member_ID, Decoration_Theme, Rank_in_Round)
Please answer with SQL.
assistant
Sure, I understand.
human
Who are all the members from the country Canada?
assistant
SELECT Member_ID FROM member WHERE Country = 'Canada';
human
What are the names of the colleges they go to?
assistant
SELECT T2.Member_ID , T1.Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID = T2.College_ID WHERE T2.Country = 'Canada';
human
Who are the team leaders of these colleges?
assistant
SELECT T2.Member_ID , T1.Name , T1.leader_name FROM college AS T1 JOIN member AS T2 ON T1.College_ID = T2.College_ID WHERE T2.Country = "Canada" ;