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
What are the names and countries of members?
assistant
SELECT Name , Country FROM member
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
human
How many members are there?
assistant
SELECT count(*) FROM member
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
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 ) ;