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: 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