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
List the names of members in ascending alphabetical order.
assistant
SELECT Name FROM member ORDER BY Name ASC
human
How many members are there?
assistant
SELECT count(*) FROM member
human
Show the leader names and locations of colleges.
assistant
SELECT Leader_Name , College_Location FROM college
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 names of members and the decoration themes they have.
assistant
SELECT T1.Name , T2.Decoration_Theme FROM member AS T1 JOIN round AS T2 ON T1.Member_ID = T2.Member_ID ;