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 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 first name for all students with major 600.
assistant
SELECT Fname FROM Student WHERE Major = 600
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
List ids for all student who are on scholarship.
assistant
SELECT StuID FROM Sportsinfo WHERE onscholarship = 'Y'
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" ;