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 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
Show all student ids and the number of hours played.
assistant
SELECT Stuid , sum(hours_played) FROM Plays_games GROUP BY Stuid
human
List ids for all student who are on scholarship.
assistant
SELECT StuID FROM Sportsinfo WHERE onscholarship = 'Y'
human
Show student ids who don't have any sports.
assistant
SELECT StuID FROM Student EXCEPT SELECT StuID FROM Sportsinfo
human
Show ids for all students who have advisor 1121.
assistant
SELECT StuID FROM Student WHERE Advisor = 1121
human
Show ids for all students who live in CHI.
assistant
SELECT StuID FROM Student WHERE city_code = "CHI" ;