Database: activity_1

create table Activity (
  actid INTEGER PRIMARY KEY,
  activity_name varchar(25)
);
create table Participates_in (
  stuid INTEGER,
  actid INTEGER,
  FOREIGN KEY(stuid) REFERENCES Student(StuID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
);
create table Faculty_Participates_in (
  FacID INTEGER,
  actid INTEGER,
  FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
);
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 Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
);

Question: How many faculty do we have?
SQL: SELECT count(*) FROM Faculty

Question: What is the total number of faculty members?
SQL: SELECT count(*) FROM Faculty

Question: What ranks do we have for faculty?
SQL: SELECT DISTINCT rank FROM Faculty

Question: Find the list of distinct ranks for faculty.
SQL: SELECT DISTINCT rank FROM Faculty

Question: Show all the distinct buildings that have faculty rooms.
SQL: SELECT DISTINCT building FROM Faculty

Question: What buildings have faculty offices?
SQL: SELECT DISTINCT building FROM Faculty

Question: Show the rank, first name, and last name for all the faculty.
SQL: SELECT rank ,  Fname ,  Lname FROM Faculty

Question: What are the rank, first name, and last name of the faculty members?
SQL: SELECT rank ,  Fname ,  Lname FROM Faculty

Question: Show the first name, last name, and phone number for all female faculty members.
SQL: SELECT Fname ,  Lname ,  phone FROM Faculty WHERE Sex  =  'F'

Question: What are the first name, last name, and phone number of all the female faculty members?
SQL: SELECT Fname ,  Lname ,  phone FROM Faculty WHERE Sex  =  'F'

Question: Show ids for all the male faculty.
SQL: SELECT FacID FROM Faculty WHERE Sex  =  'M'

Question: What are the faculty ids of all the male faculty members?
SQL: SELECT FacID FROM Faculty WHERE Sex  =  'M'

Question: How many female Professors do we have?
SQL: SELECT count(*) FROM Faculty WHERE Sex  =  'F' AND Rank  =  "Professor"

Question: Count the number of female Professors we have.
SQL: SELECT count(*) FROM Faculty WHERE Sex  =  'F' AND Rank  =  "Professor"

Question: Show the phone, room, and building for the faculty named Jerry Prince.
SQL: SELECT phone ,  room ,  building FROM Faculty WHERE Fname  =  "Jerry" AND Lname  =  "Prince"

Question: What are the phone, room, and building of the faculty member called Jerry Prince?
SQL: SELECT phone ,  room ,  building FROM Faculty WHERE Fname  =  "Jerry" AND Lname  =  "Prince"

Question: How many Professors are in building NEB?
SQL: SELECT count(*) FROM Faculty WHERE Rank  =  "Professor" AND building  =  "NEB"

Question: Count the number of Professors who have office in building NEB.
SQL: SELECT count(*) FROM Faculty WHERE Rank  =  "Professor" AND building  =  "NEB"

Question: Show the first name and last name for all the instructors.
SQL: SELECT fname ,  lname FROM Faculty WHERE Rank  =  "Instructor"

Question: What are the first name and last name of all the instructors?
SQL: SELECT fname ,  lname FROM Faculty WHERE Rank  =  "Instructor"

Question: Show all the buildings along with the number of faculty members the buildings have.
SQL: SELECT building ,  count(*) FROM Faculty GROUP BY building

Question: How many faculty members does each building have? List the result with the name of the building.
SQL: SELECT building ,  count(*) FROM Faculty GROUP BY building

Question: Which building has most faculty members?
SQL: SELECT building FROM Faculty GROUP BY building ORDER BY count(*) DESC LIMIT 1

Question: Find the building that has the largest number of faculty members.
SQL: SELECT building FROM Faculty GROUP BY building ORDER BY count(*) DESC LIMIT 1

Question: Show all the buildings that have at least 10 professors.
SQL: SELECT building FROM Faculty WHERE rank  =  "Professor" GROUP BY building HAVING count(*)  >=  10

Question: In which buildings are there at least ten professors?
SQL: SELECT building FROM Faculty WHERE rank  =  "Professor" GROUP BY building HAVING count(*)  >=  10

Question: For each faculty rank, show the number of faculty members who have it.
SQL: SELECT rank ,  count(*) FROM Faculty GROUP BY rank

Question: How many faculty members do we have for each faculty rank?
SQL: SELECT rank ,  count(*) FROM Faculty GROUP BY rank

Question: Show all the ranks and the number of male and female faculty for each rank.
SQL: SELECT rank ,  sex ,  count(*) FROM Faculty GROUP BY rank ,  sex

Question: How many faculty members do we have for each rank and gender?
SQL: SELECT rank ,  sex ,  count(*) FROM Faculty GROUP BY rank ,  sex

Question: Which rank has the smallest number of faculty members?
SQL: SELECT rank FROM Faculty GROUP BY rank ORDER BY count(*) ASC LIMIT 1

Question: Find the faculty rank that has the least members.
SQL: SELECT rank FROM Faculty GROUP BY rank ORDER BY count(*) ASC LIMIT 1

Question: Show the number of male and female assistant professors.
SQL: SELECT sex ,  count(*) FROM Faculty WHERE rank  =  "AsstProf" GROUP BY sex

Question: How many male and female assistant professors do we have?
SQL: SELECT sex ,  count(*) FROM Faculty WHERE rank  =  "AsstProf" GROUP BY sex

Question: What are the first name and last name of Linda Smith's advisor?
SQL: SELECT T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T2.fname  =  "Linda" AND T2.lname  =  "Smith"

Question: Who is the advisor of Linda Smith? Give me the first name and last name.
SQL: SELECT T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T2.fname  =  "Linda" AND T2.lname  =  "Smith"

Question: Show the ids of students whose advisors are professors.
SQL: SELECT T2.StuID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T1.rank  =  "Professor"

Question: Which students have professors as their advisors? Find their student ids.
SQL: SELECT T2.StuID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T1.rank  =  "Professor"

Question: Show first name and last name for all the students advised by Michael Goodrich.
SQL: SELECT T2.fname ,  T2.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T1.fname  =  "Michael" AND T1.lname  =  "Goodrich"

Question: Which students are advised by Michael Goodrich? Give me their first and last names.
SQL: SELECT T2.fname ,  T2.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T1.fname  =  "Michael" AND T1.lname  =  "Goodrich"

Question: Show the faculty id of each faculty member, along with the number of students he or she advises.
SQL: SELECT T1.FacID ,  count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID

Question: What are the faculty id and the number of students each faculty has?
SQL: SELECT T1.FacID ,  count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID

Question: Show all the faculty ranks and the number of students advised by each rank.
SQL: SELECT T1.rank ,  count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.rank

Question: How many students are advised by each rank of faculty? List the rank and the number of students.
SQL: SELECT T1.rank ,  count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.rank

Question: What are the first and last name of the faculty who has 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: 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

Question: Find the first names of the faculty members who are playing Canoeing or Kayaking.
SQL: SELECT DISTINCT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN activity AS T3 ON T2.actid  =  T2.actid WHERE T3.activity_name  =  'Canoeing' OR T3.activity_name  =  'Kayaking'

Question: Which faculty members are playing either Canoeing or Kayaking? Tell me their first names.
SQL: SELECT DISTINCT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN activity AS T3 ON T2.actid  =  T2.actid WHERE T3.activity_name  =  'Canoeing' OR T3.activity_name  =  'Kayaking'

Question: Find the first names of professors who are not playing Canoeing or Kayaking.
SQL: SELECT lname FROM faculty WHERE rank  =  'Professor' EXCEPT SELECT DISTINCT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN activity AS T3 ON T2.actid  =  T2.actid WHERE T3.activity_name  =  'Canoeing' OR T3.activity_name  =  'Kayaking'

Question: What are the first names of the professors who do not play Canoeing or Kayaking as activities?
SQL: SELECT lname FROM faculty WHERE rank  =  'Professor' EXCEPT SELECT DISTINCT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN activity AS T3 ON T2.actid  =  T2.actid WHERE T3.activity_name  =  'Canoeing' OR T3.activity_name  =  'Kayaking'

Question: Find the first names of the faculty members who participate in Canoeing and Kayaking.
SQL: SELECT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN activity AS T3 ON T2.actid  =  T2.actid WHERE T3.activity_name  =  'Canoeing' INTERSECT SELECT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN activity AS T3 ON T2.actid  =  T2.actid WHERE T3.activity_name  =  'Kayaking'

Question: What are the first names of the faculty members playing both Canoeing and Kayaking?
SQL: SELECT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN activity AS T3 ON T2.actid  =  T2.actid WHERE T3.activity_name  =  'Canoeing' INTERSECT SELECT T1.lname FROM Faculty AS T1 JOIN Faculty_participates_in AS T2 ON T1.facID  =  T2.facID JOIN activity AS T3 ON T2.actid  =  T2.actid WHERE T3.activity_name  =  'Kayaking'

Question: Find the ids of the students who participate in Canoeing and Kayaking.
SQL: SELECT T1.stuid FROM participates_in AS T1 JOIN activity AS T2 ON T2.actid  =  T2.actid WHERE T2.activity_name  =  'Canoeing' INTERSECT SELECT T1.stuid FROM participates_in AS T1 JOIN activity AS T2 ON T2.actid  =  T2.actid WHERE T2.activity_name  =  'Kayaking'

Question: Which students participate in both Canoeing and Kayaking as their activities? Tell me their student ids.
SQL: SELECT T1.stuid FROM participates_in AS T1 JOIN activity AS T2 ON T2.actid  =  T2.actid WHERE T2.activity_name  =  'Canoeing' INTERSECT SELECT T1.stuid FROM participates_in AS T1 JOIN activity AS T2 ON T2.actid  =  T2.actid WHERE T2.activity_name  =  'Kayaking'