human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Club (ClubID, ClubName, ClubDesc, ClubLocation)
Member_of_club (StuID, ClubID, Position)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name of all the clubs at 'AKW'.
assistant
SELECT clubname FROM club WHERE clublocation = 'AKW'
human
List the clubs that have at least a member with advisor '1121'.
assistant
SELECT DISTINCT t1.clubname FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid = t2.clubid JOIN student AS t3 ON t2.stuid = t3.stuid WHERE t3.advisor = 1121
human
How many clubs are there?
assistant
SELECT count(*) FROM club
human
What are the names of all clubs?
assistant
SELECT clubname FROM club
human
Find the names of the clubs that have at least a member from the city with city code 'HOU'.
assistant
SELECT DISTINCT t1.clubname FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid = t2.clubid JOIN student AS t3 ON t2.stuid = t3.stuid WHERE t3.city_code = 'HOU'
human
Which clubs have one or more members from the city with code 'BAL'? Give me the names of the clubs.
assistant
SELECT DISTINCT t1.clubname FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid = t2.clubid JOIN student AS t3 ON t2.stuid = t3.stuid WHERE t3.city_code = "BAL" ;