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
Show last names for all student who are on scholarship.
assistant
SELECT T2.Lname FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID WHERE T1.onscholarship = 'Y'
human
Show ids of students who play video game and play sports.
assistant
SELECT StuID FROM Sportsinfo INTERSECT SELECT StuID FROM Plays_games
human
Show student ids who don't have any sports.
assistant
SELECT StuID FROM Student EXCEPT SELECT StuID FROM Sportsinfo
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
Show student ids who are on scholarship and have major 600.
assistant
SELECT StuID FROM Student WHERE major = 600 INTERSECT SELECT StuID FROM Sportsinfo WHERE onscholarship = 'Y'
human
What are the ids for all sporty students who are on scholarship?
assistant
SELECT StuID FROM Sportsinfo WHERE onscholarship = "Y" ;