Database: restaurant_1

create table Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 );
create table Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
);
create table Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
);
create table Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
);
create table Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
);

Question: Show me all the restaurants.
SQL: SELECT ResName FROM Restaurant;

Question: What is the address of the restaurant Subway?
SQL: SELECT Address FROM Restaurant WHERE ResName = "Subway";

Question: What is the rating of the restaurant Subway?
SQL: SELECT Rating FROM Restaurant WHERE ResName = "Subway";

Question: List all restaurant types.
SQL: SELECT ResTypeName FROM Restaurant_Type;

Question: What is the description of the restaurant type Sandwich?
SQL: SELECT ResTypeDescription FROM Restaurant_Type WHERE ResTypeName = "Sandwich";

Question: Which restaurants have highest rating? List the restaurant name and its rating.
SQL: SELECT ResName , Rating FROM Restaurant ORDER BY Rating DESC LIMIT 1;

Question: What is the age of student Linda Smith?
SQL: SELECT Age FROM Student WHERE Fname = "Linda" AND Lname = "Smith";

Question: What is the gender of the student Linda Smith?
SQL: SELECT Sex FROM Student WHERE Fname = "Linda" AND Lname = "Smith";

Question: List all students' first names and last names who majored in 600.
SQL: SELECT Fname , Lname FROM Student WHERE Major  =  600;

Question: Which city does student Linda Smith live in?
SQL: SELECT city_code FROM Student WHERE Fname = "Linda" AND Lname = "Smith";

Question: Advisor 1121 has how many students?
SQL: SELECT count(*) FROM Student WHERE Advisor =  1121;

Question: Which Advisor has most of students? List advisor and the number of students.
SQL: SELECT Advisor ,  count(*) FROM Student GROUP BY Advisor ORDER BY count(Advisor) DESC LIMIT 1;

Question: Which major has least number of students? List the major and the number of students.
SQL: SELECT Major ,  count(*) FROM Student GROUP BY Major ORDER BY count(Major) ASC LIMIT 1;

Question: Which major has between 2 and 30 number of students? List major and the number of students.
SQL: SELECT Major ,  count(*) FROM Student GROUP BY Major HAVING count(Major) BETWEEN 2 AND 30;

Question: Which student's age is older than 18 and is majoring in 600? List each student's first and last name.
SQL: SELECT Fname , Lname FROM Student WHERE Age  >  18 AND Major = 600;

Question: List all female students age is older than 18 who is not majoring in 600. List students' first name and last name.
SQL: SELECT Fname , Lname FROM Student WHERE Age  >  18 AND Major != 600 AND Sex = 'F';

Question: How many restaurant is the Sandwich type restaurant?
SQL: SELECT count(*) FROM Restaurant JOIN Type_Of_Restaurant ON Restaurant.ResID =  Type_Of_Restaurant.ResID JOIN Restaurant_Type ON Type_Of_Restaurant.ResTypeID = Restaurant_Type.ResTypeID GROUP BY Type_Of_Restaurant.ResTypeID HAVING Restaurant_Type.ResTypeName = 'Sandwich'

Question: How long does student Linda Smith spend on the restaurant in total?
SQL: SELECT sum(Spent) FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith";

Question: How many times has the student Linda Smith visited Subway?
SQL: SELECT count(*) FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith" AND Restaurant.ResName = "Subway";

Question: When did Linda Smith visit Subway?
SQL: SELECT TIME FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith" AND Restaurant.ResName = "Subway";

Question: At which restaurant did the students spend the least amount of time? List restaurant and the time students spent on in total.
SQL: 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;

Question: Which student visited restaurant most often? List student's first name and last name.
SQL: 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;