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: what are all the addresses including line 1 and line 2?
SQL: SELECT line_1 ,  line_2 FROM addresses

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

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

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

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

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

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

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

Question: 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: 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: How many departments offer any degree?
SQL: SELECT count(DISTINCT department_id) FROM Degree_Programs

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

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

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

Question: 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: 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: What are the names and descriptions of all the sections?
SQL: SELECT section_name ,  section_description FROM Sections

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

Question: 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: 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: List the section_name in reversed lexicographical order.
SQL: SELECT section_name FROM Sections ORDER BY section_name DESC

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

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