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
What are the names and countries of members?
assistant
SELECT Name , Country FROM member
human
List the names of members in ascending alphabetical order.
assistant
SELECT Name FROM member ORDER BY Name ASC
human
Show the distinct leader names of colleges associated with members from country 'Canada'.
assistant
SELECT DISTINCT T1.Leader_Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID = T2.College_ID WHERE T2.Country = 'Canada'
human
Show the names of members and the locations of colleges they go to in ascending alphabetical order of member names.
assistant
SELECT T2.Name , T1.College_Location FROM college AS T1 JOIN member AS T2 ON T1.College_ID = T2.College_ID ORDER BY T2.Name ASC
human
Show the names of members and names of colleges they go to.
assistant
SELECT T2.Name , T1.Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID = T2.College_ID
human
Show the leader names and locations of colleges.
assistant
SELECT Leader_Name , College_Location FROM college ;