human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Restaurant (ResID, ResName, Address, Rating)
Type_Of_Restaurant (ResID, ResTypeID)
Restaurant_Type (ResTypeID, ResTypeName, ResTypeDescription)
Visits_Restaurant (StuID, ResID, Time, Spent)
Please answer with SQL.
assistant
Sure, I understand.
human
Which Advisor has most of students? List advisor and the number of students.
assistant
SELECT Advisor , count(*) FROM Student GROUP BY Advisor ORDER BY count(Advisor) DESC LIMIT 1;
human
Which student's age is older than 18 and is majoring in 600? List each student's first and last name.
assistant
SELECT Fname , Lname FROM Student WHERE Age > 18 AND Major = 600;
human
List all restaurant types.
assistant
SELECT ResTypeName FROM Restaurant_Type;
human
Which restaurants have highest rating? List the restaurant name and its rating.
assistant
SELECT ResName , Rating FROM Restaurant ORDER BY Rating DESC LIMIT 1;
human
At which restaurant did the students spend the least amount of time? List restaurant and the time students spent on in total.
assistant
SELECT Restaurant.ResName , sum(Visits_Restaurant.Spent) FROM Visits_Restaurant JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID GROUP BY Restaurant.ResID ORDER BY sum(Visits_Restaurant.Spent) ASC LIMIT 1;
human
Which student visited restaurant most often? List student's first name and last name.
assistant
SELECT Student.Fname , Student.Lname FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID GROUP BY Student.StuID ORDER BY COUNT( * ) DESC LIMIT 1 ;