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
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
How many games are played for all students?
assistant
SELECT sum(gamesplayed) FROM Sportsinfo
human
What is the first name and last name of the student who have most number of 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
human
What is the type of video game Call of Destiny.
assistant
SELECT gtype FROM Video_games WHERE gname = 'Call of Destiny'
human
what is the last name and gender of all students who played both Call of Destiny and Works of Widenius?
assistant
SELECT lname , sex FROM Student WHERE StuID IN ( SELECT T1.StuID FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.GameID = T2.GameID WHERE T2.Gname = "Call of Destiny" INTERSECT SELECT T1.StuID FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.GameID = T2.GameID WHERE T2.Gname = "Works of Widenius" ) ;