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
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
Show total hours per week and number of games played for students under 20.
assistant
SELECT sum(hoursperweek) , sum(gamesplayed) FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID WHERE T2.age < 20
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
How many games are played for all students?
assistant
SELECT sum(gamesplayed) FROM Sportsinfo
human
What are the average, minimum, and max ages for each of the different majors?
assistant
SELECT major , avg ( age ) , MIN( age ) , MAX( age ) FROM Student GROUP BY major ;