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 all video game types.
assistant
SELECT DISTINCT gtype FROM Video_games
human
Show all video games and their types in the order of their names.
assistant
SELECT gname , gtype FROM Video_games ORDER BY gname
human
How many games are played for all students?
assistant
SELECT sum(gamesplayed) FROM Sportsinfo
human
Which game type has least number of games?
assistant
SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) LIMIT 1
human
Which game type has most number of games?
assistant
SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) DESC LIMIT 1
human
What are the names of all games played by Linda Smith?
assistant
SELECT Gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid = T2.gameid JOIN Student AS T3 ON T3.Stuid = T1.Stuid WHERE T3.Lname = "Smith" AND T3.Fname = "Linda" ;