human
Schema:
Allergy_Type (Allergy, AllergyType)
Has_Allergy (StuID, Allergy)
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the student id of the oldest student.
assistant
SELECT StuID FROM Student WHERE age = (SELECT max(age) FROM Student)
human
Show student ids for all male students.
assistant
SELECT StuID FROM Student WHERE Sex = 'M'
human
Show all advisors and corresponding number of students.
assistant
SELECT advisor , count(*) FROM Student GROUP BY advisor
human
How many different advisors are listed?
assistant
SELECT count(DISTINCT advisor) FROM Student
human
Which advisor has most number of students?
assistant
SELECT advisor FROM Student GROUP BY advisor ORDER BY count(*) DESC LIMIT 1
human
Who is the advisor of student with ID 1004?
assistant
SELECT Advisor FROM Student WHERE StuID = 1004 ;