lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
What are the completion dates of all the tests that have result "Fail"?

### SQL
SELECT T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Student_Tests_Taken AS T2 ON T1.registration_id  =  T2.registration_id WHERE T2.test_result  =  "Fail"
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Return the completion date for all the tests that have "Fail" result.

### SQL
SELECT T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Student_Tests_Taken AS T2 ON T1.registration_id  =  T2.registration_id WHERE T2.test_result  =  "Fail"
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
List the dates of enrollment and completion of the student with personal name "Karson".

### SQL
SELECT T1.date_of_enrolment ,  T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.personal_name  =  "Karson"
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
On what dates did the student whose personal name is "Karson" enroll in and complete the courses?

### SQL
SELECT T1.date_of_enrolment ,  T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.personal_name  =  "Karson"
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
List the dates of enrollment and completion of the student with family name "Zieme" and personal name "Bernie".

### SQL
SELECT T1.date_of_enrolment ,  T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.family_name  =  "Zieme" AND T2.personal_name  =  "Bernie"
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
On what dates did the student with family name "Zieme" and personal name "Bernie" enroll in and complete the courses?

### SQL
SELECT T1.date_of_enrolment ,  T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.family_name  =  "Zieme" AND T2.personal_name  =  "Bernie"
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Find the student ID and login name of the student with the most course enrollments

### SQL
SELECT T1.student_id ,  T2.login_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
What are the student ID and login name of the student who are enrolled in the most courses?

### SQL
SELECT T1.student_id ,  T2.login_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Find the student ID and personal name of the student with at least two enrollments.

### SQL
SELECT T1.student_id ,  T2.personal_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Which student are enrolled in at least two courses? Give me the student ID and personal name.

### SQL
SELECT T1.student_id ,  T2.personal_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Find the student ID and middle name for all the students with at most two enrollments.

### SQL
SELECT T1.student_id ,  T2.middle_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  <=  2
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
What are the student IDs and middle names of the students enrolled in at most two courses?

### SQL
SELECT T1.student_id ,  T2.middle_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  <=  2
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Find the personal names of students not enrolled in any course.

### SQL
SELECT personal_name FROM Students EXCEPT SELECT T1.personal_name FROM Students AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.student_id  =  T2.student_id
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Which students not enrolled in any course? Find their personal names.

### SQL
SELECT personal_name FROM Students EXCEPT SELECT T1.personal_name FROM Students AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.student_id  =  T2.student_id
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
How many students did not have any course enrollment?

### SQL
SELECT count(*) FROM Students WHERE student_id NOT IN (SELECT student_id FROM Student_Course_Enrolment)
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Count the number of students who did not enroll in any course.

### SQL
SELECT count(*) FROM Students WHERE student_id NOT IN (SELECT student_id FROM Student_Course_Enrolment)
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Find the common login name of course authors and students.

### SQL
SELECT login_name FROM Course_Authors_and_Tutors INTERSECT SELECT login_name FROM Students
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
What are the login names used both by some course authors and some students?

### SQL
SELECT login_name FROM Course_Authors_and_Tutors INTERSECT SELECT login_name FROM Students
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
Find the common personal name of course authors and students.

### SQL
SELECT personal_name FROM Course_Authors_and_Tutors INTERSECT SELECT personal_name FROM Students
### Database Schema
CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
)

CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
)

CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
)

CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
)

CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
)

CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
FOREIGN KEY (`registration_id` ) REFERENCES `Student_Course_Enrolment`(`registration_id` )
)

### Question
What are the personal names used both by some course authors and some students?

### SQL
SELECT personal_name FROM Course_Authors_and_Tutors INTERSECT SELECT personal_name FROM Students
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Which claims caused more than 2 settlements or have the maximum claim value? List the date the claim was made and the claim id.

### SQL
SELECT T1.Date_Claim_Made ,  T1.Claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id  =  T2.Claim_id GROUP BY T1.Claim_id HAVING count(*)  >  2 UNION SELECT T1.Date_Claim_Made ,  T1.Claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id  =  T2.Claim_id WHERE T1.Amount_Claimed  =  ( SELECT max(Amount_Claimed) FROM Claims )
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Find the claims that led to more than two settlements or have the maximum claim value. For each of them, return the date the claim was made and the id of the claim.

### SQL
SELECT T1.Date_Claim_Made ,  T1.Claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id  =  T2.Claim_id GROUP BY T1.Claim_id HAVING count(*)  >  2 UNION SELECT T1.Date_Claim_Made ,  T1.Claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id  =  T2.Claim_id WHERE T1.Amount_Claimed  =  ( SELECT max(Amount_Claimed) FROM Claims )
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Which customer had at least 2 policies but did not file any claims? List the customer details and id.

### SQL
SELECT T1.customer_details ,  T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  2 EXCEPT SELECT T1.customer_details ,  T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id  =  T2.customer_id JOIN Claims AS T3 ON T2.policy_id  =  T3.policy_id
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Give me the the customer details and id for the customers who had two or more policies but did not file any claims.

### SQL
SELECT T1.customer_details ,  T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  2 EXCEPT SELECT T1.customer_details ,  T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id  =  T2.customer_id JOIN Claims AS T3 ON T2.policy_id  =  T3.policy_id
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
List the method, date and amount of all the payments, in ascending order of date.

### SQL
SELECT Payment_Method_Code ,  Date_Payment_Made ,  Amount_Payment FROM Payments ORDER BY Date_Payment_Made ASC
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
What are the method, date and amount of each payment? Sort the list in ascending order of date.

### SQL
SELECT Payment_Method_Code ,  Date_Payment_Made ,  Amount_Payment FROM Payments ORDER BY Date_Payment_Made ASC
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Among all the claims, what is the settlement amount of the claim with the largest claim amount? List both the settlement amount and claim amount.

### SQL
SELECT Amount_Settled ,  Amount_Claimed FROM Claims ORDER BY Amount_Claimed DESC LIMIT 1