Database: college_3

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)
);
create table Department (
       DNO   		INTEGER PRIMARY KEY,
       Division		VARCHAR(2),
       DName		VARCHAR(25),
       Room		VARCHAR(5),
       Building		VARCHAR(13),
       DPhone		INTEGER
);
create table Member_of (
       FacID 	       INTEGER,
       DNO	       INTEGER,
       Appt_Type       VARCHAR(15),
       FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
);
create table Course (
       CID   	    	VARCHAR(7) PRIMARY KEY,
       CName		VARCHAR(40),
       Credits		INTEGER,
       Instructor	INTEGER,
       Days		VARCHAR(5),
       Hours		VARCHAR(11),
       DNO		INTEGER,
       FOREIGN KEY(Instructor) REFERENCES Faculty(FacID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
);
create table Minor_in (
       StuID 	      INTEGER,
       DNO		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(DNO) REFERENCES Department(DNO)
);
create table Enrolled_in (
       StuID 		 INTEGER,
       CID		VARCHAR(7),
       Grade		VARCHAR(2),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(CID) REFERENCES Course(CID),
       FOREIGN KEY(Grade) REFERENCES Gradeconversion(lettergrade)
);
create table Gradeconversion (
       lettergrade	     VARCHAR(2) PRIMARY KEY,
       gradepoint	     FLOAT
);

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

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"

Question: What are the names of departments 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"

Question: Find the first name of students not enrolled in any course.
SQL: SELECT Fname FROM STUDENT WHERE StuID NOT IN (SELECT StuID FROM ENROLLED_IN)

Question: What are the first names of all students that are not enrolled in courses?
SQL: SELECT Fname FROM STUDENT WHERE StuID NOT IN (SELECT StuID FROM ENROLLED_IN)