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
);
CREATE TABLE Department (
    DNO NUMBER PRIMARY KEY,
    Division TEXT,
    DName TEXT,
    Room TEXT,
    Building TEXT,
    DPhone NUMBER
);
CREATE TABLE Member_of (
    FacID NUMBER,
    DNO NUMBER,
    Appt_Type TEXT,
    FOREIGN KEY ("DNO") REFERENCES Department("DNO"),
    FOREIGN KEY ("FacID") REFERENCES Faculty("FacID")
);
CREATE TABLE Course (
    CID TEXT PRIMARY KEY,
    CName TEXT,
    Credits NUMBER,
    Instructor NUMBER,
    Days TEXT,
    Hours TEXT,
    DNO NUMBER,
    FOREIGN KEY ("DNO") REFERENCES Department("DNO"),
    FOREIGN KEY ("Instructor") REFERENCES Faculty("FacID")
);
CREATE TABLE Minor_in (
    StuID NUMBER,
    DNO NUMBER,
    FOREIGN KEY ("DNO") REFERENCES Department("DNO"),
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);
CREATE TABLE Enrolled_in (
    StuID NUMBER,
    CID TEXT,
    Grade TEXT,
    FOREIGN KEY ("Grade") REFERENCES Gradeconversion("lettergrade"),
    FOREIGN KEY ("CID") REFERENCES Course("CID"),
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);
CREATE TABLE Gradeconversion (
    lettergrade TEXT PRIMARY KEY,
    gradepoint NUMBER
);

Question: How many courses are there in total?
SQL: SELECT count(*) FROM COURSE

Question: Count the number of courses.
SQL: SELECT count(*) FROM COURSE

Question: How many courses have more than 2 credits?
SQL: SELECT count(*) FROM COURSE WHERE Credits  >  2

Question: Count the number of courses with more than 2 credits.
SQL: SELECT count(*) FROM COURSE WHERE Credits  >  2

Question: List all names of courses with 1 credit?
SQL: SELECT CName FROM COURSE WHERE Credits  =  1

Question: What are the names of courses with 1 credit?
SQL: SELECT CName FROM COURSE WHERE Credits  =  1

Question: Which courses are taught on days MTW?
SQL: SELECT CName FROM COURSE WHERE Days  =  "MTW"

Question: What are the course names for courses taught on MTW?
SQL: SELECT CName FROM COURSE WHERE Days  =  "MTW"

Question: What is the number of departments in Division "AS"?
SQL: SELECT count(*) FROM DEPARTMENT WHERE Division  =  "AS"

Question: How many departments are in the division AS?
SQL: SELECT count(*) FROM DEPARTMENT WHERE Division  =  "AS"

Question: What are the phones of departments in Room 268?
SQL: SELECT DPhone FROM DEPARTMENT WHERE Room  =  268

Question: Give the phones for departments in room 268.
SQL: SELECT DPhone FROM DEPARTMENT WHERE Room  =  268

Question: Find the number of students that have at least one grade "B".
SQL: SELECT COUNT(DISTINCT StuID) FROM ENROLLED_IN WHERE Grade  =  "B"

Question: How many students have had at least one "B" grade?
SQL: SELECT COUNT(DISTINCT StuID) FROM ENROLLED_IN WHERE Grade  =  "B"

Question: Find the max and min grade point for all letter grade.
SQL: SELECT max(gradepoint) ,  min(gradepoint) FROM GRADECONVERSION

Question: What are the maximum and minumum grade points?
SQL: SELECT max(gradepoint) ,  min(gradepoint) FROM GRADECONVERSION

Question: Find the first names of students whose first names contain letter "a".
SQL: SELECT DISTINCT Fname FROM STUDENT WHERE Fname LIKE '%a%'

Question: What are the first names for students who have an "a" in their first name?
SQL: SELECT DISTINCT Fname FROM STUDENT WHERE Fname LIKE '%a%'

Question: Find the first names and last names of male (sex is M) faculties who live in building NEB.
SQL: SELECT Fname ,  Lname FROM FACULTY WHERE sex  =  "M" AND Building  =  "NEB"

Question: What are the full names of faculties with sex M and who live in building NEB?
SQL: SELECT Fname ,  Lname FROM FACULTY WHERE sex  =  "M" AND Building  =  "NEB"

Question: Find the rooms of faculties with rank professor who live in building NEB.
SQL: SELECT Room FROM FACULTY WHERE Rank  =  "Professor" AND Building  =  "NEB"

Question: What are the rooms for members of the faculty who are professors and who live in building NEB?
SQL: SELECT Room FROM FACULTY WHERE Rank  =  "Professor" AND Building  =  "NEB"

Question: Find the department name that is in Building "Mergenthaler".
SQL: SELECT DName FROM DEPARTMENT WHERE Building  =  "Mergenthaler"

Question: What is the name of the department in the Building Mergenthaler?
SQL: SELECT DName FROM DEPARTMENT WHERE Building  =  "Mergenthaler"

Question: List all information about courses sorted by credits in the ascending order.
SQL: SELECT * FROM COURSE ORDER BY Credits

Question: What is all the information about courses, ordered by credits ascending?
SQL: SELECT * FROM COURSE ORDER BY Credits

Question: List the course name of courses sorted by credits.
SQL: SELECT CName FROM COURSE ORDER BY Credits

Question: What are the course names, ordered by credits?
SQL: SELECT CName FROM COURSE ORDER BY Credits

Question: Find the first name of students in the descending order of age.
SQL: SELECT Fname FROM STUDENT ORDER BY Age DESC

Question: What are the first names of students, ordered by age from greatest to least?
SQL: SELECT Fname FROM STUDENT ORDER BY Age DESC

Question: Find the last name of female (sex is F) students in the descending order of age.
SQL: SELECT LName FROM STUDENT WHERE Sex  =  "F" ORDER BY Age DESC

Question: What are the last names of female students, ordered by age descending?
SQL: SELECT LName FROM STUDENT WHERE Sex  =  "F" ORDER BY Age DESC

Question: Find the last names of faculties in building Barton in alphabetic order.
SQL: SELECT Lname FROM FACULTY WHERE Building  =  "Barton" ORDER BY Lname

Question: What are the last names of faculty in building Barton, sorted by last name?
SQL: SELECT Lname FROM FACULTY WHERE Building  =  "Barton" ORDER BY Lname

Question: Find the first names of faculties of rank Professor in alphabetic order.
SQL: SELECT Fname FROM FACULTY WHERE Rank  =  "Professor" ORDER BY Fname

Question: What are the first names for all faculty professors, ordered by first name?
SQL: SELECT Fname FROM FACULTY WHERE Rank  =  "Professor" ORDER BY Fname

Question: Find the name of the department that has the biggest number of students minored in?
SQL: SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MINOR_IN AS T2 ON T1.DNO  =  T2.DNO GROUP BY T2.DNO ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the department with the most students minoring in it?
SQL: SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MINOR_IN AS T2 ON T1.DNO  =  T2.DNO GROUP BY T2.DNO ORDER BY count(*) DESC LIMIT 1

Question: Find the name of the department that has no students minored in?
SQL: SELECT DName FROM DEPARTMENT EXCEPT SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MINOR_IN AS T2 ON T1.DNO  =  T2.DNO

Question: What is the name of the department htat has no students minoring in it?
SQL: SELECT DName FROM DEPARTMENT EXCEPT SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MINOR_IN AS T2 ON T1.DNO  =  T2.DNO

Question: Find the name of the department that has the fewest members.
SQL: SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MEMBER_OF AS T2 ON T1.DNO  =  T2.DNO GROUP BY T2.DNO ORDER BY count(*) ASC LIMIT 1

Question: What is the name of the department with the fewest members?
SQL: SELECT T1.DName FROM DEPARTMENT AS T1 JOIN MEMBER_OF AS T2 ON T1.DNO  =  T2.DNO GROUP BY T2.DNO ORDER BY count(*) ASC LIMIT 1

Question: Find the rank of the faculty that the fewest faculties belong to.
SQL: SELECT Rank FROM FACULTY GROUP BY Rank ORDER BY count(*) ASC LIMIT 1

Question: What is the least common faculty rank?
SQL: SELECT Rank FROM FACULTY GROUP BY Rank ORDER BY count(*) ASC LIMIT 1

Question: What are the first and last names of the instructors who teach the top 3 number of courses?
SQL: SELECT T2.Fname ,  T2.Lname FROM COURSE AS T1 JOIN FACULTY AS T2 ON T1.Instructor  =  T2.FacID GROUP BY T1.Instructor ORDER BY count(*) DESC LIMIT 3