SELECT student_id FROM students WHERE student_id NOT IN (SELECT student_id FROM student_course_attendance)
List the id of students who never attends courses?
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)
);
INSERT INTO Addresses (`address_id`, `line_1`, `line_2`, `city`, `zip_postcode`, `state_province_county`, `country`) VALUES (5, '0900 Roderick Oval
INSERT INTO People (`person_id`, `first_name`, `middle_name`, `last_name`, `cell_mobile_number`, `email_address`, `login_name`, `password`) VALUES (111, 'Shannon', 'Elissa', 'Senger', '01955267735', 'javier.trantow@example.net', 'pgub', '5e4ff49a61b3544da3ad7dc7e2cf28847564c64c');
INSERT INTO Students (`student_id`,`student_details`) VALUES  (111,'Marry');
INSERT INTO Courses (`course_id`, `course_name`, `course_description`) VALUES ('301', 'statistics', 'statistics');
INSERT INTO People_Addresses (`person_address_id`, `person_id`, `address_id`, `date_from`, `date_to`) VALUES (122, 111, 9, '2012-09-26 13:21:00', '2018-03-21 09:46:30');
INSERT INTO Student_Course_Registrations (`student_id`,`course_id`,`registration_date`) VALUES (111,'301','2008-11-04 10:35:13');
INSERT INTO Student_Course_Attendance (`student_id`,`course_id`,`date_of_attendance`) VALUES (111,'301','2008-11-04 10:35:13');
INSERT INTO Candidates (`candidate_id`,`candidate_details`) VALUES  (111,'Jane');
INSERT INTO Candidate_Assessments (`candidate_id`,`qualification`,`assessment_date`,`asessment_outcome_code`) VALUES (111,'A','2010-04-07 11:44:34','Pass');