CREATE TABLE Activity (
    actid NUMBER PRIMARY KEY,
    activity_name TEXT
);
CREATE TABLE Participates_in (
    stuid NUMBER,
    actid NUMBER,
    FOREIGN KEY ("actid") REFERENCES Activity("actid"),
    FOREIGN KEY ("stuid") REFERENCES Student("StuID")
);
CREATE TABLE Faculty_Participates_in (
    FacID NUMBER,
    actid NUMBER,
    FOREIGN KEY ("actid") REFERENCES Activity("actid"),
    FOREIGN KEY ("FacID") REFERENCES Faculty("FacID")
);
CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);
CREATE TABLE Faculty (
    FacID NUMBER PRIMARY KEY,
    Lname TEXT,
    Fname TEXT,
    Rank TEXT,
    Sex TEXT,
    Phone NUMBER,
    Room TEXT,
    Building TEXT
);

Question: Give me the the first and last name of the faculty who advises the most students.
SQL: SELECT T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID ORDER BY count(*) DESC LIMIT 1

Question: Show the ids for all the faculty members who have at least 2 students.
SQL: SELECT T1.FacID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID HAVING count(*)  >=  2

Question: Which faculty members advise two ore more students? Give me their faculty ids.
SQL: SELECT T1.FacID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID HAVING count(*)  >=  2

Question: Show ids for the faculty members who don't advise any student.
SQL: SELECT FacID FROM Faculty EXCEPT SELECT advisor FROM Student

Question: What are the ids of the faculty members who do not advise any student.
SQL: SELECT FacID FROM Faculty EXCEPT SELECT advisor FROM Student

Question: What activities do we have?
SQL: SELECT activity_name FROM Activity

Question: List all the activities we have.
SQL: SELECT activity_name FROM Activity

Question: How many activities do we have?
SQL: SELECT count(*) FROM Activity

Question: Find the number of activities available.
SQL: SELECT count(*) FROM Activity

Question: How many faculty members participate in an activity?
SQL: SELECT count(DISTINCT FacID) FROM Faculty_participates_in

Question: Give me the number of faculty members who participate in an activity
SQL: SELECT count(DISTINCT FacID) FROM Faculty_participates_in

Question: Show the ids of the faculty who don't participate in any activity.
SQL: SELECT FacID FROM Faculty EXCEPT SELECT FacID FROM Faculty_participates_in

Question: Which faculty do not participate in any activity? Find their faculty ids.
SQL: SELECT FacID FROM Faculty EXCEPT SELECT FacID FROM Faculty_participates_in

Question: Show the ids of all the faculty members who participate in an activity and advise a student.
SQL: SELECT FacID FROM Faculty_participates_in INTERSECT SELECT advisor FROM Student

Question: What are ids of the faculty members who not only participate in an activity but also advise a student.
SQL: SELECT FacID FROM Faculty_participates_in INTERSECT SELECT advisor FROM Student

Question: How many activities does Mark Giuliano participate in?
SQL: SELECT count(*) FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID WHERE T1.fname  =  "Mark" AND T1.lname  =  "Giuliano"

Question: Find the number of activities Mark Giuliano is involved in.
SQL: SELECT count(*) FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID WHERE T1.fname  =  "Mark" AND T1.lname  =  "Giuliano"

Question: Show the names of all the activities Mark Giuliano participates in.
SQL: SELECT T3.activity_name FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN Activity AS T3 ON T3.actid  =  T2.actid WHERE T1.fname  =  "Mark" AND T1.lname  =  "Giuliano"

Question: What are the names of the activities Mark Giuliano is involved in
SQL: SELECT T3.activity_name FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN Activity AS T3 ON T3.actid  =  T2.actid WHERE T1.fname  =  "Mark" AND T1.lname  =  "Giuliano"

Question: Show the first and last name of all the faculty members who participated in some activity, together with the number of activities they participated in.
SQL: SELECT  T1.fname ,  T1.lname ,  count(*) ,  T1.FacID FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID GROUP BY T1.FacID

Question: What is the first and last name of the faculty members who participated in at least one activity? For each of them, also show the number of activities they participated in.
SQL: SELECT  T1.fname ,  T1.lname ,  count(*) ,  T1.FacID FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID GROUP BY T1.FacID

Question: Show all the activity names and the number of faculty involved in each activity.
SQL: SELECT T1.activity_name ,  count(*) FROM Activity AS T1 JOIN Faculty_participates_in AS T2 ON T1.actID  =  T2.actID GROUP BY T1.actID

Question: How many faculty members participate in each activity? Return the activity names and the number of faculty members.
SQL: SELECT T1.activity_name ,  count(*) FROM Activity AS T1 JOIN Faculty_participates_in AS T2 ON T1.actID  =  T2.actID GROUP BY T1.actID

Question: What is the first and last name of the faculty participating in the most activities?
SQL: SELECT  T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID GROUP BY T1.FacID ORDER BY count(*) DESC LIMIT 1

Question: Find the first and last name of the faculty who is involved in the largest number of activities.
SQL: SELECT  T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID GROUP BY T1.FacID ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the activity that has the most faculty members involved in?
SQL: SELECT T1.activity_name FROM Activity AS T1 JOIN Faculty_participates_in AS T2 ON T1.actID  =  T2.actID GROUP BY T1.actID ORDER BY count(*) DESC LIMIT 1

Question: Which activity has the most faculty members participating in? Find the activity name.
SQL: SELECT T1.activity_name FROM Activity AS T1 JOIN Faculty_participates_in AS T2 ON T1.actID  =  T2.actID GROUP BY T1.actID ORDER BY count(*) DESC LIMIT 1

Question: Show the ids of the students who don't participate in any activity.
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Participates_in

Question: What are the ids of the students who are not involved in any activity
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Participates_in

Question: Show the ids for all the students who participate in an activity and are under 20.
SQL: SELECT StuID FROM Participates_in INTERSECT SELECT StuID FROM Student WHERE age  <  20

Question: What are the ids of the students who are under 20 years old and are involved in at least one activity.
SQL: SELECT StuID FROM Participates_in INTERSECT SELECT StuID FROM Student WHERE age  <  20

Question: What is the first and last name of the student participating in the most activities?
SQL: SELECT T1.fname ,  T1.lname FROM Student AS T1 JOIN Participates_in AS T2 ON T1.StuID  =  T2.StuID GROUP BY T1.StuID ORDER BY count(*) DESC LIMIT 1

Question: Tell me the first and last name of the student who has the most activities.
SQL: SELECT T1.fname ,  T1.lname FROM Student AS T1 JOIN Participates_in AS T2 ON T1.StuID  =  T2.StuID GROUP BY T1.StuID ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the activity with the most students?
SQL: SELECT T1.activity_name FROM Activity AS T1 JOIN Participates_in AS T2 ON T1.actID  =  T2.actID GROUP BY T1.actID ORDER BY count(*) DESC LIMIT 1

Question: Find the name of the activity that has the largest number of student participants.
SQL: SELECT T1.activity_name FROM Activity AS T1 JOIN Participates_in AS T2 ON T1.actID  =  T2.actID GROUP BY T1.actID ORDER BY count(*) DESC LIMIT 1