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
How many clubs does the student named 'Eric Tai' belong to?
assistant
SELECT count(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.fname = 'Eric' AND t3.lname = 'Tai'
human
How many members does the club 'Tennis Club' has?
assistant
SELECT count(*) 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 t1.clubname = 'Tennis Club'
human
How many clubs are there?
assistant
SELECT count(*) FROM club
human
Find the name of the club that has the most female students.
assistant
SELECT 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.sex = 'F' GROUP BY t1.clubname ORDER BY count(*) DESC LIMIT 1
human
What are the first names of all the students?
assistant
SELECT DISTINCT fname FROM student
human
How many students are there?
assistant
SELECT COUNT( * ) FROM student ;