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
Which allergy type has most number of allergies?
assistant
SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) DESC LIMIT 1
human
Find the first name and major of the students who are not allegry to soy.
assistant
SELECT fname , major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy = 'Soy')
human
Which advisor has most number of students?
assistant
SELECT advisor FROM Student GROUP BY advisor ORDER BY count(*) DESC LIMIT 1
human
Show all majors and corresponding number of students.
assistant
SELECT major , count(*) FROM Student GROUP BY major
human
Show all majors.
assistant
SELECT DISTINCT Major FROM Student
human
What is the largest major?
assistant
SELECT major FROM Student GROUP BY major ORDER BY COUNT( * ) DESC LIMIT 1 ;