Database: student_transcripts_tracking
Schema: CREATE TABLE `Addresses` ( `address_id` INTEGER PRIMARY KEY, `line_1` VARCHAR(255), `line_2` VARCHAR(255), `line_3` VARCHAR(255), `city` VARCHAR(255), `zip_postcode` VARCHAR(20), `state_province_county` VARCHAR(255), `country` VARCHAR(255), `other_address_details` VARCHAR(255) ); CREATE TABLE `Courses` ( `course_id` INTEGER PRIMARY KEY, `course_name` VARCHAR(255), `course_description` VARCHAR(255), `other_details` VARCHAR(255) ); CREATE TABLE `Departments` ( `department_id` INTEGER PRIMARY KEY, `department_name` VARCHAR(255), `department_description` VARCHAR(255), `other_details` VARCHAR(255) ); CREATE TABLE `Degree_Programs` ( `degree_program_id` INTEGER PRIMARY KEY, `department_id` INTEGER NOT NULL, `degree_summary_name` VARCHAR(255), `degree_summary_description` VARCHAR(255), `other_details` VARCHAR(255), FOREIGN KEY (`department_id` ) REFERENCES `Departments`(`department_id` ) ); CREATE TABLE `Sections` ( `section_id` INTEGER PRIMARY KEY, `course_id` INTEGER NOT NULL, `section_name` VARCHAR(255), `section_description` VARCHAR(255), `other_details` VARCHAR(255), FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ) ); CREATE TABLE `Semesters` ( `semester_id` INTEGER PRIMARY KEY, `semester_name` VARCHAR(255), `semester_description` VARCHAR(255), `other_details` VARCHAR(255) ); CREATE TABLE `Students` ( `student_id` INTEGER PRIMARY KEY, `current_address_id` INTEGER NOT NULL, `permanent_address_id` INTEGER NOT NULL, `first_name` VARCHAR(80), `middle_name` VARCHAR(40), `last_name` VARCHAR(40), `cell_mobile_number` VARCHAR(40), `email_address` VARCHAR(40), `ssn` VARCHAR(40), `date_first_registered` DATETIME, `date_left` DATETIME, `other_student_details` VARCHAR(255), FOREIGN KEY (`current_address_id` ) REFERENCES `Addresses`(`address_id` ), FOREIGN KEY (`permanent_address_id` ) REFERENCES `Addresses`(`address_id` ) ); CREATE TABLE `Student_Enrolment` ( `student_enrolment_id` INTEGER PRIMARY KEY, `degree_program_id` INTEGER NOT NULL, `semester_id` INTEGER NOT NULL, `student_id` INTEGER NOT NULL, `other_details` VARCHAR(255), FOREIGN KEY (`degree_program_id` ) REFERENCES `Degree_Programs`(`degree_program_id` ), FOREIGN KEY (`semester_id` ) REFERENCES `Semesters`(`semester_id` ), FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` ) ); CREATE TABLE `Student_Enrolment_Courses` ( `student_course_id` INTEGER PRIMARY KEY, `course_id` INTEGER NOT NULL, `student_enrolment_id` INTEGER NOT NULL, FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ), FOREIGN KEY (`student_enrolment_id` ) REFERENCES `Student_Enrolment`(`student_enrolment_id` ) ); CREATE TABLE `Transcripts` ( `transcript_id` INTEGER PRIMARY KEY, `transcript_date` DATETIME, `other_details` VARCHAR(255) ); CREATE TABLE `Transcript_Contents` ( `student_course_id` INTEGER NOT NULL, `transcript_id` INTEGER NOT NULL, FOREIGN KEY (`student_course_id` ) REFERENCES `Student_Enrolment_Courses`(`student_course_id` ), FOREIGN KEY (`transcript_id` ) REFERENCES `Transcripts`(`transcript_id` ) );

Question (medium): what are all the addresses including line 1 and line 2?
SQL: SELECT line_1 ,  line_2 FROM addresses

Question (medium): What is the first and second line for all addresses?
SQL: SELECT line_1 ,  line_2 FROM addresses

Question (easy): How many courses in total are listed?
SQL: SELECT count(*) FROM Courses

Question (easy): How many courses are there?
SQL: SELECT count(*) FROM Courses

Question (easy): How is the math course described?
SQL: SELECT course_description FROM Courses WHERE course_name  =  'math'

Question (easy): What are the descriptions for all the math courses?
SQL: SELECT course_description FROM Courses WHERE course_name  =  'math'

Question (easy): What is the zip code of the address in the city Port Chelsea?
SQL: SELECT zip_postcode FROM Addresses WHERE city  =  'Port Chelsea'

Question (easy): What is the zip code for Port Chelsea?
SQL: SELECT zip_postcode FROM Addresses WHERE city  =  'Port Chelsea'

Question (extra): Which department offers the most number of degrees? List department name and id.
SQL: SELECT T2.department_name ,  T1.department_id FROM Degree_Programs AS T1 JOIN Departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY T1.department_id ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the name and id of the department with the most number of degrees ?
SQL: select t2.department_name ,  t1.department_id from degree_programs as t1 join departments as t2 on t1.department_id  =  t2.department_id group by t1.department_id order by count(*) desc limit 1

Question (easy): How many departments offer any degree?
SQL: SELECT count(DISTINCT department_id) FROM Degree_Programs

Question (easy): How many different departments offer degrees?
SQL: SELECT count(DISTINCT department_id) FROM Degree_Programs

Question (easy): How many different degree names are offered?
SQL: SELECT count(DISTINCT degree_summary_name) FROM Degree_Programs

Question (easy): How many different degrees are offered?
SQL: SELECT count(DISTINCT degree_summary_name) FROM Degree_Programs

Question (medium): How many degrees does the engineering department offer?
SQL: SELECT count(*) FROM Departments AS T1 JOIN Degree_Programs AS T2 ON T1.department_id  =  T2.department_id WHERE T1.department_name  =  'engineer'

Question (medium): How many degrees does the engineering department have?
SQL: SELECT count(*) FROM Departments AS T1 JOIN Degree_Programs AS T2 ON T1.department_id  =  T2.department_id WHERE T1.department_name  =  'engineer'

Question (medium): What are the names and descriptions of all the sections?
SQL: SELECT section_name ,  section_description FROM Sections

Question (medium): What are the names and descriptions for all the sections?
SQL: SELECT section_name ,  section_description FROM Sections

Question (medium): What are the names and id of courses having at most 2 sections?
SQL: SELECT T1.course_name ,  T1.course_id FROM Courses AS T1 JOIN Sections AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_id HAVING count(*)  <=  2

Question (medium): What are the names and ids of every course with less than 2 sections?
SQL: SELECT T1.course_name ,  T1.course_id FROM Courses AS T1 JOIN Sections AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_id HAVING count(*)  <=  2

Question (easy): List the section_name in reversed lexicographical order.
SQL: SELECT section_name FROM Sections ORDER BY section_name DESC

Question (easy): What are the names of the sections in reverse alphabetical order?
SQL: SELECT section_name FROM Sections ORDER BY section_name DESC

Question (extra): What is the semester which most student registered in? Show both the name and the id.
SQL: SELECT T1.semester_name ,  T1.semester_id FROM Semesters AS T1 JOIN Student_Enrolment AS T2 ON T1.semester_id  =  T2.semester_id GROUP BY T1.semester_id ORDER BY count(*) DESC LIMIT 1

Question (extra): For each semester, what is the name and id of the one with the most students registered?
SQL: SELECT T1.semester_name ,  T1.semester_id FROM Semesters AS T1 JOIN Student_Enrolment AS T2 ON T1.semester_id  =  T2.semester_id GROUP BY T1.semester_id ORDER BY count(*) DESC LIMIT 1

Question (medium): What is the description of the department whose name has the substring the computer?
SQL: SELECT department_description FROM Departments WHERE department_name LIKE '%computer%'

Question (medium): What is the department description for the one whose name has the word computer?
SQL: SELECT department_description FROM Departments WHERE department_name LIKE '%computer%'

Question (medium): Who are enrolled in 2 degree programs in one semester? List the first name, middle name and last name and the id.
SQL: SELECT T1.first_name ,  T1.middle_name ,  T1.last_name ,  T1.student_id FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING count(*)  =  2

Question (medium): What are the first, middle, and last names, along with the ids, of all students who enrolled in 2 degree programs in one semester?
SQL: SELECT T1.first_name ,  T1.middle_name ,  T1.last_name ,  T1.student_id FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING count(*)  =  2

Question (hard): Who is enrolled in a Bachelor degree program? List the first name, middle name, last name.
SQL: SELECT DISTINCT T1.first_name ,  T1.middle_name ,  T1.last_name FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id JOIN Degree_Programs AS T3 ON T2.degree_program_id  =  T3.degree_program_id WHERE T3.degree_summary_name  =  'Bachelor'

Question (hard): What are the first, middle, and last names for everybody enrolled in a Bachelors program?
SQL: SELECT DISTINCT T1.first_name ,  T1.middle_name ,  T1.last_name FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id JOIN Degree_Programs AS T3 ON T2.degree_program_id  =  T3.degree_program_id WHERE T3.degree_summary_name  =  'Bachelor'

Question (extra): Find the kind of program which most number of students are enrolled in?
SQL: SELECT T1.degree_summary_name FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id GROUP BY T1.degree_summary_name ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the degree summary name that has the most number of students enrolled?
SQL: SELECT T1.degree_summary_name FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id GROUP BY T1.degree_summary_name ORDER BY count(*) DESC LIMIT 1

Question (extra): Find the program which most number of students are enrolled in. List both the id and the summary.
SQL: SELECT T1.degree_program_id ,  T1.degree_summary_name FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id GROUP BY T1.degree_program_id ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the program id and the summary of the degree that has the most students enrolled?
SQL: SELECT T1.degree_program_id ,  T1.degree_summary_name FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id GROUP BY T1.degree_program_id ORDER BY count(*) DESC LIMIT 1

Question (extra): Which student has enrolled for the most times in any program? List the id, first name, middle name, last name, the number of enrollments and student id.
SQL: SELECT T1.student_id ,  T1.first_name ,  T1.middle_name ,  T1.last_name ,  count(*) ,  T1.student_id FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the first, middle, and last name, along with the id and number of enrollments, for the student who enrolled the most in any program?
SQL: SELECT T1.student_id ,  T1.first_name ,  T1.middle_name ,  T1.last_name ,  count(*) ,  T1.student_id FROM Students AS T1 JOIN Student_Enrolment AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question (hard): Which semesters do not have any student enrolled? List the semester name.
SQL: SELECT semester_name FROM Semesters WHERE semester_id NOT IN( SELECT semester_id FROM Student_Enrolment )

Question (hard): What is the name of the semester with no students enrolled?
SQL: SELECT semester_name FROM Semesters WHERE semester_id NOT IN( SELECT semester_id FROM Student_Enrolment )

Question (easy): What are all the course names of the courses which ever have students enrolled in?
SQL: SELECT DISTINCT T1.course_name FROM Courses AS T1 JOIN Student_Enrolment_Courses AS T2 ON T1.course_id  =  T2.course_id

Question (easy): What are the names of all courses that have some students enrolled?
SQL: SELECT DISTINCT T1.course_name FROM Courses AS T1 JOIN Student_Enrolment_Courses AS T2 ON T1.course_id  =  T2.course_id

Question (extra): What's the name of the course with most number of enrollments?
SQL: SELECT  T1.course_name FROM Courses AS T1 JOIN Student_Enrolment_Courses AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the name of the course with the most students enrolled?
SQL: SELECT  T1.course_name FROM Courses AS T1 JOIN Student_Enrolment_Courses AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name ORDER BY count(*) DESC LIMIT 1

Question (extra): Find the last name of the students who currently live in the state of North Carolina but have not registered in any degree program.
SQL: SELECT T1.last_name FROM Students AS T1 JOIN Addresses AS T2 ON T1.current_address_id  =  T2.address_id WHERE T2.state_province_county  =  'NorthCarolina' EXCEPT SELECT DISTINCT T3.last_name FROM Students AS T3 JOIN Student_Enrolment AS T4 ON T3.student_id  =  T4.student_id

Question (extra): What are the last name of the students who live in North Carolina but have not registered in any degree programs?
SQL: SELECT T1.last_name FROM Students AS T1 JOIN Addresses AS T2 ON T1.current_address_id  =  T2.address_id WHERE T2.state_province_county  =  'NorthCarolina' EXCEPT SELECT DISTINCT T3.last_name FROM Students AS T3 JOIN Student_Enrolment AS T4 ON T3.student_id  =  T4.student_id

Question (medium): Show the date and id of the transcript with at least 2 course results.
SQL: SELECT T2.transcript_date ,  T1.transcript_id FROM Transcript_Contents AS T1 JOIN Transcripts AS T2 ON T1.transcript_id  =  T2.transcript_id GROUP BY T1.transcript_id HAVING count(*)  >=  2

Question (medium): What is the date and id of the transcript with at least 2 courses listed?
SQL: SELECT T2.transcript_date ,  T1.transcript_id FROM Transcript_Contents AS T1 JOIN Transcripts AS T2 ON T1.transcript_id  =  T2.transcript_id GROUP BY T1.transcript_id HAVING count(*)  >=  2

Question (medium): What is the phone number of the man with the first name Timmothy and the last name Ward?
SQL: SELECT cell_mobile_number FROM Students WHERE first_name  =  'Timmothy' AND last_name  =  'Ward'

Question (medium): What is the mobile phone number of the student named Timmothy Ward ?
SQL: select cell_mobile_number from students where first_name  =  'timmothy' and last_name  =  'ward'

Question (medium): Who is the first student to register? List the first name, middle name and last name.
SQL: SELECT first_name ,  middle_name ,  last_name FROM Students ORDER BY date_first_registered ASC LIMIT 1

Question (medium): What is the first, middle, and last name of the first student to register?
SQL: SELECT first_name ,  middle_name ,  last_name FROM Students ORDER BY date_first_registered ASC LIMIT 1

Question (medium): Who is the earliest graduate of the school? List the first name, middle name and last name.
SQL: SELECT first_name ,  middle_name ,  last_name FROM Students ORDER BY date_left ASC LIMIT 1

Question (medium): What is the first, middle, and last name of the earliest school graduate?
SQL: SELECT first_name ,  middle_name ,  last_name FROM Students ORDER BY date_left ASC LIMIT 1

Question (easy): Whose permanent address is different from his or her current address? List his or her first name.
SQL: SELECT first_name FROM Students WHERE current_address_id != permanent_address_id

Question (easy): What is the first name of the student whose permanent address is different from his or her current one?
SQL: SELECT first_name FROM Students WHERE current_address_id != permanent_address_id

Question (extra): Which address holds the most number of students currently? List the address id and all lines.
SQL: SELECT T1.address_id ,  T1.line_1 ,  T1.line_2 FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.current_address_id GROUP BY T1.address_id ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the id, line 1, and line 2 of the address with the most students?
SQL: SELECT T1.address_id ,  T1.line_1 ,  T1.line_2 FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.current_address_id GROUP BY T1.address_id ORDER BY count(*) DESC LIMIT 1

Question (easy): On average, when were the transcripts printed?
SQL: SELECT avg(transcript_date) FROM Transcripts

Question (easy): What is the average transcript date?
SQL: SELECT avg(transcript_date) FROM Transcripts

Question (medium): When is the first transcript released? List the date and details.
SQL: SELECT transcript_date ,  other_details FROM Transcripts ORDER BY transcript_date ASC LIMIT 1

Question (medium): What is the earliest date of a transcript release, and what details can you tell me?
SQL: SELECT transcript_date ,  other_details FROM Transcripts ORDER BY transcript_date ASC LIMIT 1

Question (easy): How many transcripts are released?
SQL: SELECT count(*) FROM Transcripts

Question (easy): How many transcripts are listed?
SQL: SELECT count(*) FROM Transcripts

Question (medium): What is the last transcript release date?
SQL: SELECT transcript_date FROM Transcripts ORDER BY transcript_date DESC LIMIT 1

Question (medium): When was the last transcript released?
SQL: SELECT transcript_date FROM Transcripts ORDER BY transcript_date DESC LIMIT 1

Question (hard): How many times at most can a course enrollment result show in different transcripts? Also show the course enrollment id.
SQL: SELECT count(*) ,  student_course_id FROM Transcript_Contents GROUP BY student_course_id ORDER BY count(*) DESC LIMIT 1

Question (hard): What is the maximum number of times that a course shows up in different transcripts and what is that course's enrollment id?
SQL: SELECT count(*) ,  student_course_id FROM Transcript_Contents GROUP BY student_course_id ORDER BY count(*) DESC LIMIT 1

Question (extra): Show the date of the transcript which shows the least number of results, also list the id.
SQL: SELECT T2.transcript_date ,  T1.transcript_id FROM Transcript_Contents AS T1 JOIN Transcripts AS T2 ON T1.transcript_id  =  T2.transcript_id GROUP BY T1.transcript_id ORDER BY count(*) ASC LIMIT 1

Question (extra): What is the date and id of the transcript with the least number of results?
SQL: SELECT T2.transcript_date ,  T1.transcript_id FROM Transcript_Contents AS T1 JOIN Transcripts AS T2 ON T1.transcript_id  =  T2.transcript_id GROUP BY T1.transcript_id ORDER BY count(*) ASC LIMIT 1

Question (extra): Find the semester when both Master students and Bachelor students got enrolled in.
SQL: SELECT DISTINCT T2.semester_id FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id WHERE degree_summary_name  =  'Master' INTERSECT SELECT DISTINCT T2.semester_id FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id WHERE degree_summary_name  =  'Bachelor'

Question (extra): What is the id of the semester that had both Masters and Bachelors students enrolled?
SQL: SELECT DISTINCT T2.semester_id FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id WHERE degree_summary_name  =  'Master' INTERSECT SELECT DISTINCT T2.semester_id FROM Degree_Programs AS T1 JOIN Student_Enrolment AS T2 ON T1.degree_program_id  =  T2.degree_program_id WHERE degree_summary_name  =  'Bachelor'

Question (easy): How many different addresses do the students currently live?
SQL: SELECT count(DISTINCT current_address_id) FROM Students

Question (easy): What are the different addresses that have students living there?
SQL: SELECT count(DISTINCT current_address_id) FROM Students

Question (easy): List all the student details in reversed lexicographical order.
SQL: SELECT other_student_details FROM Students ORDER BY other_student_details DESC

Question (easy): What other details can you tell me about students in reverse alphabetical order?
SQL: SELECT other_student_details FROM Students ORDER BY other_student_details DESC

Question (easy): Describe the section h.
SQL: SELECT section_description FROM Sections WHERE section_name  =  'h'

Question (easy): What is the description for the section named h?
SQL: SELECT section_description FROM Sections WHERE section_name  =  'h'

Question (hard): Find the first name of the students who permanently live in the country Haiti or have the cell phone number 09700166582 .
SQL: select t1.first_name from students as t1 join addresses as t2 on t1.permanent_address_id  =  t2.address_id where t2.country  =  'haiti' or t1.cell_mobile_number  =  '09700166582'

Question (hard): What are the first names of the students who live in Haiti permanently or have the cell phone number 09700166582 ?
SQL: select t1.first_name from students as t1 join addresses as t2 on t1.permanent_address_id  =  t2.address_id where t2.country  =  'haiti' or t1.cell_mobile_number  =  '09700166582'