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: What are the full names of the 3 instructors who teach the most 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

Question: Which building does the instructor who teaches the most number of courses live in?
SQL: SELECT T2.Building FROM COURSE AS T1 JOIN FACULTY AS T2 ON T1.Instructor  =  T2.FacID GROUP BY T1.Instructor ORDER BY count(*) DESC LIMIT 1

Question: Give the building that the instructor who teaches the greatest number of courses lives in.
SQL: SELECT T2.Building FROM COURSE AS T1 JOIN FACULTY AS T2 ON T1.Instructor  =  T2.FacID GROUP BY T1.Instructor ORDER BY count(*) DESC LIMIT 1

Question: What are the name of courses that have at least five enrollments?
SQL: SELECT T1.CName FROM COURSE AS T1 JOIN ENROLLED_IN AS T2 ON T1.CID  =  T2.CID GROUP BY T2.CID HAVING COUNT(*)  >=  5

Question: Give the names of the courses with at least five enrollments.
SQL: SELECT T1.CName FROM COURSE AS T1 JOIN ENROLLED_IN AS T2 ON T1.CID  =  T2.CID GROUP BY T2.CID HAVING COUNT(*)  >=  5

Question: Find the first name and last name of the instructor of course that has course name
SQL: SELECT T2.Fname ,  T2.Lname FROM COURSE AS T1 JOIN FACULTY AS T2 ON T1.Instructor  =  T2.FacID WHERE T1.CName  =  "COMPUTER LITERACY"

Question: What is the full name of the instructor who has a course named COMPUTER LITERACY?
SQL: SELECT T2.Fname ,  T2.Lname FROM COURSE AS T1 JOIN FACULTY AS T2 ON T1.Instructor  =  T2.FacID WHERE T1.CName  =  "COMPUTER LITERACY"

Question: Find the department name and room of the course INTRODUCTION TO COMPUTER SCIENCE.
SQL: SELECT T2.Dname ,  T2.Room FROM COURSE AS T1 JOIN DEPARTMENT AS T2 ON T1.DNO  =  T2.DNO WHERE T1.CName  =  "INTRODUCTION TO COMPUTER SCIENCE"

Question: What are the department name and room for the course INTRODUCTION TO COMPUTER SCIENCE?
SQL: SELECT T2.Dname ,  T2.Room FROM COURSE AS T1 JOIN DEPARTMENT AS T2 ON T1.DNO  =  T2.DNO WHERE T1.CName  =  "INTRODUCTION TO COMPUTER SCIENCE"

Question: Find the student first and last names and grade points of all enrollments.
SQL: SELECT T3.Fname ,  T3.LName ,  T2.gradepoint FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade  =  T2.lettergrade AND T1.StuID  =  T3.StuID

Question: What are the full names and gradepoints for all enrollments?
SQL: SELECT T3.Fname ,  T3.LName ,  T2.gradepoint FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade  =  T2.lettergrade AND T1.StuID  =  T3.StuID

Question: Find the distinct student first names of all students that have grade point at least 3.8 in one course.
SQL: SELECT DISTINCT T3.Fname FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade  =  T2.lettergrade AND T1.StuID  =  T3.StuID WHERE T2.gradepoint  >=  3.8

Question: What are the distinct first names for students with a grade point of 3.8 or above in at least one course?
SQL: SELECT DISTINCT T3.Fname FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade  =  T2.lettergrade AND T1.StuID  =  T3.StuID WHERE T2.gradepoint  >=  3.8

Question: Find the full names of faculties who are members of department with department number 520.
SQL: SELECT T1.Fname ,  T1.Lname FROM FACULTY AS T1 JOIN MEMBER_OF AS T2 ON T1.FacID  =  T2.FacID WHERE T2.DNO  =  520

Question: What are the full names of faculty members who are a part of department 520?
SQL: SELECT T1.Fname ,  T1.Lname FROM FACULTY AS T1 JOIN MEMBER_OF AS T2 ON T1.FacID  =  T2.FacID WHERE T2.DNO  =  520

Question: What are the first names and last names of the students that minor in the department with DNO 140.
SQL: SELECT T2.Fname ,  T2.Lname FROM MINOR_IN AS T1 JOIN STUDENT AS T2 ON T1.StuID  =  T2.StuID WHERE T1.DNO  =  140

Question: What are the full names of students minoring in department 140?
SQL: SELECT T2.Fname ,  T2.Lname FROM MINOR_IN AS T1 JOIN STUDENT AS T2 ON T1.StuID  =  T2.StuID WHERE T1.DNO  =  140

Question: Find the last names of faculties who are members of computer science department.
SQL: SELECT T2.Lname FROM DEPARTMENT AS T1 JOIN FACULTY AS T2 ON T1.DNO  =  T3.DNO JOIN MEMBER_OF AS T3 ON T2.FacID  =  T3.FacID WHERE T1.DName  =  "Computer Science"

Question: What are the last names of faculty who are part of the computer science department?
SQL: SELECT T2.Lname FROM DEPARTMENT AS T1 JOIN FACULTY AS T2 ON T1.DNO  =  T3.DNO JOIN MEMBER_OF AS T3 ON T2.FacID  =  T3.FacID WHERE T1.DName  =  "Computer Science"

Question: Find the average grade point of student whose last name is Smith.
SQL: SELECT avg(T2.gradepoint) FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade  =  T2.lettergrade AND T1.StuID  =  T3.StuID WHERE T3.LName  =  "Smith"

Question: What is the average gradepoint for students with the last name Smith?
SQL: SELECT avg(T2.gradepoint) FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade  =  T2.lettergrade AND T1.StuID  =  T3.StuID WHERE T3.LName  =  "Smith"

Question: What is the maximum and minimum grade point of students who live in NYC?
SQL: SELECT max(T2.gradepoint) ,  min(T2.gradepoint) FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade  =  T2.lettergrade AND T1.StuID  =  T3.StuID WHERE T3.city_code  =  "NYC"

Question: Give the maximum and minimum gradepoints for students living in NYC?
SQL: SELECT max(T2.gradepoint) ,  min(T2.gradepoint) FROM ENROLLED_IN AS T1 JOIN GRADECONVERSION AS T2 JOIN STUDENT AS T3 ON T1.Grade  =  T2.lettergrade AND T1.StuID  =  T3.StuID WHERE T3.city_code  =  "NYC"

Question: Find the names of courses that have either 3 credits or 1 credit but 4 hours.
SQL: SELECT CName FROM COURSE WHERE Credits  =  3 UNION SELECT CName FROM COURSE WHERE Credits  =  1 AND Hours  =  4

Question: What are the names of courses that give either 3 credits, or 1 credit and 4 hours?
SQL: SELECT CName FROM COURSE WHERE Credits  =  3 UNION SELECT CName FROM COURSE WHERE Credits  =  1 AND Hours  =  4

Question: Find the names of departments that are either in division AS or in division EN and in Building NEB.
SQL: SELECT DName FROM DEPARTMENT WHERE Division  =  "AS" UNION SELECT DName FROM DEPARTMENT WHERE Division  =  "EN" AND Building  =  "NEB"