lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What is the id of the candidate whose email is stanley.monahan@example.org?

### SQL
SELECT T2.candidate_id FROM people AS T1 JOIN candidates AS T2 ON T1.person_id = T2.candidate_id WHERE T1.email_address = "stanley.monahan@example.org"
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
Find id of the candidate who most recently accessed the course?

### SQL
SELECT candidate_id FROM candidate_assessments ORDER BY assessment_date DESC LIMIT 1
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What is the id of the candidate who most recently accessed the course?

### SQL
SELECT candidate_id FROM candidate_assessments ORDER BY assessment_date DESC LIMIT 1
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What is detail of the student who registered the most number of courses?

### SQL
SELECT T1.student_details FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What are the details of the student who registered for the most number of courses?

### SQL
SELECT T1.student_details FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
List the id of students who registered some courses and the number of their registered courses?

### SQL
SELECT T1.student_id ,  count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
For every student who is registered for some course, how many courses are they registered for?

### SQL
SELECT T1.student_id ,  count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
How many registed students do each course have? List course name and the number of their registered students?

### SQL
SELECT T3.course_name ,  count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id JOIN courses AS T3 ON T2.course_id = T3.course_id GROUP BY T2.course_id
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
For each course id, how many students are registered and what are the course names?

### SQL
SELECT T3.course_name ,  count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id JOIN courses AS T3 ON T2.course_id = T3.course_id GROUP BY T2.course_id
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
Find id of candidates whose assessment code is "Pass"?

### SQL
SELECT candidate_id FROM candidate_assessments WHERE asessment_outcome_code = "Pass"
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What are the ids of the candidates that have an outcome code of Pass?

### SQL
SELECT candidate_id FROM candidate_assessments WHERE asessment_outcome_code = "Pass"
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
Find the cell mobile number of the candidates whose assessment code is "Fail"?

### SQL
SELECT T3.cell_mobile_number FROM candidates AS T1 JOIN candidate_assessments AS T2 ON T1.candidate_id = T2.candidate_id JOIN people AS T3 ON T1.candidate_id = T3.person_id WHERE T2.asessment_outcome_code = "Fail"
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What are the cell phone numbers of the candidates that received an assessment code of "Fail"?

### SQL
SELECT T3.cell_mobile_number FROM candidates AS T1 JOIN candidate_assessments AS T2 ON T1.candidate_id = T2.candidate_id JOIN people AS T3 ON T1.candidate_id = T3.person_id WHERE T2.asessment_outcome_code = "Fail"
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What are the id of students who registered course 301?

### SQL
SELECT student_id FROM student_course_attendance WHERE course_id  =  301
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What are the ids of the students who registered for course 301?

### SQL
SELECT student_id FROM student_course_attendance WHERE course_id  =  301
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What is the id of the student who most recently registered course 301?

### SQL
SELECT student_id FROM student_course_attendance WHERE course_id = 301 ORDER BY date_of_attendance DESC LIMIT 1
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What are the ids of the students who registered for course 301 most recently?

### SQL
SELECT student_id FROM student_course_attendance WHERE course_id = 301 ORDER BY date_of_attendance DESC LIMIT 1
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
Find distinct cities of addresses of people?

### SQL
SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
What are the different cities where people live?

### SQL
SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id
### Database Schema
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
line_1 VARCHAR(80),
line_2 VARCHAR(80),
city VARCHAR(50),
zip_postcode CHAR(20),
state_province_county VARCHAR(50),
country VARCHAR(50),
PRIMARY KEY (address_id)
)

CREATE TABLE People (
person_id INTEGER NOT NULL,
first_name VARCHAR(255),
middle_name VARCHAR(255),
last_name VARCHAR(255),
cell_mobile_number VARCHAR(40),
email_address VARCHAR(40),
login_name VARCHAR(40),
password VARCHAR(40),
PRIMARY KEY (person_id)
)

CREATE TABLE Students (
student_id INTEGER NOT NULL,
student_details VARCHAR(255),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES People (person_id)
)

CREATE TABLE Courses (
course_id VARCHAR(100) NOT NULL,
course_name VARCHAR(120),
course_description VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (course_id)
)

CREATE TABLE People_Addresses (
person_address_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
date_from DATETIME,
date_to DATETIME,
PRIMARY KEY (person_address_id),
FOREIGN KEY (person_id) REFERENCES People (person_id),
FOREIGN KEY (address_id) REFERENCES Addresses (address_id)
)

CREATE TABLE Student_Course_Registrations (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students (student_id),
FOREIGN KEY (course_id) REFERENCES Courses (course_id)
)

CREATE TABLE Student_Course_Attendance (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
date_of_attendance DATETIME NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id, course_id) REFERENCES Student_Course_Registrations (student_id,course_id)
)

CREATE TABLE Candidates (
candidate_id INTEGER NOT NULL ,
candidate_details VARCHAR(255),
PRIMARY KEY (candidate_id),
FOREIGN KEY (candidate_id) REFERENCES People (person_id)
)

CREATE TABLE Candidate_Assessments (
candidate_id INTEGER NOT NULL,
qualification CHAR(15) NOT NULL,
assessment_date DATETIME NOT NULL,
asessment_outcome_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, qualification),
FOREIGN KEY (candidate_id) REFERENCES Candidates (candidate_id)
)

### Question
Find distinct cities of address of students?

### SQL
SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id JOIN students AS T3 ON T2.person_id = T3.student_id