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
Show the leader names and locations of colleges.
assistant
SELECT Leader_Name , College_Location FROM college
human
List the names of members who did not participate in any round.
assistant
SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM round)
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
List the names of members in ascending alphabetical order.
assistant
SELECT Name FROM member ORDER BY Name ASC
human
Show the names of members that have a rank in round higher than 3.
assistant
SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID = T2.Member_ID WHERE T2.Rank_in_Round > 3
human
Show the names of members in ascending order of their rank in rounds.
assistant
SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID = T2.Member_ID ORDER BY Rank_in_Round ASC ;