CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    line_1 TEXT,
    line_2 TEXT,
    line_3 TEXT,
    city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT,
    other_address_details TEXT
);
CREATE TABLE Courses (
    course_id NUMBER PRIMARY KEY,
    course_name TEXT,
    course_description TEXT,
    other_details TEXT
);
CREATE TABLE Departments (
    department_id NUMBER PRIMARY KEY,
    department_name TEXT,
    department_description TEXT,
    other_details TEXT
);
CREATE TABLE Degree_Programs (
    degree_program_id NUMBER PRIMARY KEY,
    department_id NUMBER,
    degree_summary_name TEXT,
    degree_summary_description TEXT,
    other_details TEXT,
    FOREIGN KEY ("department_id") REFERENCES Departments("department_id")
);
CREATE TABLE Sections (
    section_id NUMBER PRIMARY KEY,
    course_id NUMBER,
    section_name TEXT,
    section_description TEXT,
    other_details TEXT,
    FOREIGN KEY ("course_id") REFERENCES Courses("course_id")
);
CREATE TABLE Semesters (
    semester_id NUMBER PRIMARY KEY,
    semester_name TEXT,
    semester_description TEXT,
    other_details TEXT
);
CREATE TABLE Students (
    student_id NUMBER PRIMARY KEY,
    current_address_id NUMBER,
    permanent_address_id NUMBER,
    first_name TEXT,
    middle_name TEXT,
    last_name TEXT,
    cell_mobile_number TEXT,
    email_address TEXT,
    ssn TEXT,
    date_first_registered TIME,
    date_left TIME,
    other_student_details TEXT,
    FOREIGN KEY ("permanent_address_id") REFERENCES Addresses("address_id"),
    FOREIGN KEY ("current_address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Student_Enrolment (
    student_enrolment_id NUMBER PRIMARY KEY,
    degree_program_id NUMBER,
    semester_id NUMBER,
    student_id NUMBER,
    other_details TEXT,
    FOREIGN KEY ("student_id") REFERENCES Students("student_id"),
    FOREIGN KEY ("semester_id") REFERENCES Semesters("semester_id"),
    FOREIGN KEY ("degree_program_id") REFERENCES Degree_Programs("degree_program_id")
);
CREATE TABLE Student_Enrolment_Courses (
    student_course_id NUMBER PRIMARY KEY,
    course_id NUMBER,
    student_enrolment_id NUMBER,
    FOREIGN KEY ("student_enrolment_id") REFERENCES Student_Enrolment("student_enrolment_id"),
    FOREIGN KEY ("course_id") REFERENCES Courses("course_id")
);
CREATE TABLE Transcripts (
    transcript_id NUMBER PRIMARY KEY,
    transcript_date TIME,
    other_details TEXT
);
CREATE TABLE Transcript_Contents (
    student_course_id NUMBER,
    transcript_id NUMBER,
    FOREIGN KEY ("transcript_id") REFERENCES Transcripts("transcript_id"),
    FOREIGN KEY ("student_course_id") REFERENCES Student_Enrolment_Courses("student_course_id")
);

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