human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Video_Games (GameID, GName, GType)
Plays_Games (StuID, GameID, Hours_Played)
SportsInfo (StuID, SportName, HoursPerWeek, GamesPlayed, OnScholarship)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the first name and age of the students who are playing both Football and Lacrosse.
assistant
SELECT fname , age FROM Student WHERE StuID IN (SELECT StuID FROM Sportsinfo WHERE SportName = 'Football' INTERSECT SELECT StuID FROM Sportsinfo WHERE SportName = 'Lacrosse')
human
Show all sport name and the number of students.
assistant
SELECT sportname , count(*) FROM Sportsinfo GROUP BY sportname
human
Find the last and first name of students who are playing Football or Lacrosse.
assistant
SELECT T2.lname , T2.fname FROM SportsInfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID WHERE T1.SportName = 'Football' OR T1.SportName = 'Lacrosse'
human
Which sport has most number of students on scholarship?
assistant
SELECT sportname FROM Sportsinfo WHERE onscholarship = 'Y' GROUP BY sportname ORDER BY count(*) DESC LIMIT 1
human
How many students play sports?
assistant
SELECT count(DISTINCT StuID) FROM Sportsinfo
human
What is the first and last name of the student who played the most sports?
assistant
SELECT T2.Fname , T2.Lname FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID GROUP BY T1.StuID ORDER BY COUNT( * ) DESC LIMIT 1 ;