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 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 different countries and the number of members from each.
assistant
SELECT Country , COUNT(*) FROM member GROUP BY Country
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
Which countries have more than two members?
assistant
SELECT Country FROM member GROUP BY Country HAVING COUNT(*) > 2
human
What are the names and countries of members?
assistant
SELECT Name , Country FROM member
human
How many members are there?
assistant
SELECT COUNT( * ) FROM member ;