-- student table
-- primary key: id
-- first name, last name (50 characters), birthdate, age, 
-- completed_math_course (boolean)
CREATE TABLE student(
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birthdate DATE,
    age INT,
    completed_math_course BOOLEAN
);
INSERT INTO student (first_name, last_name, birthdate, age, completed_math_course) VALUES
    ("Mark", "Roa", "2020-7-11", 6, FALSE),
    ("Luke", "Roa", "2020-3-11", 6, FALSE),
    ("John", "Smith", "2011-2-11", 15, TRUE),
    ("Peter", "Parker", "2010-9-11", 16, TRUE),
    ("Bob", "Mason", "2014-10-11", 12, FALSE),
    ("Steve", "Rogers", "2019-8-11", 7, TRUE)
    ;
SELECT * FROM student;

CREATE TABLE result(
    result_id INT PRIMARY KEY,
    subject_name VARCHAR(50),
    score DOUBLE,
    student_id INT,
    FOREIGN KEY(student_id) REFERENCES student(student_id)
);
INSERT INTO result VALUES
    (4, "Math", 9.8, 1),
    (1, "English", 6.9, 3),
    (6, "History", 9.2, 6),
    (3, "Physics", 6.8, 4),
    (2, "Computer Science", 8.5, 2),
    (5, "Chemistry", 8.1, 5);
SELECT * FROM result;
-- SELECT * FROM student WHERE completed_math_course = TRUE;
-- SELECT * FROM student WHERE birthdate >  "2010-12-31";
-- UPDATE result SET score = 8.7 WHERE subject_name = "Computer Science";
-- SELECT * FROM result;
-- UPDATE result SET subject_name = "General History" WHERE result_id = 6;
-- SELECT * FROM result;
-- DELETE FROM result WHERE student_id = 1 OR student_id = 2 OR student_id = 5;
-- SELECT * FROM result;
-- DELETE FROM student WHERE completed_math_course = FALSE;
-- SELECT * FROM student;
DELETE FROM result WHERE score < 7;
SELECT * FROM result;