CREATE TABLE students (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(50),
    lname VARCHAR(50)
);
CREATE TABLE classes (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50),
    description VARCHAR(200),
    class_num INT
);
-- need to be created at last
CREATE TABLE enrollments (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    enrollment_date DATE,
    student_id INT,
    class_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

-- INSERT DATA
INSERT INTO students(fname, lname)
    VALUES ('Henry', 'Lee'),
    ('Ann', 'Ortiz'),
    ('Andre', 'Gross'),
    ('Tracy','Ortiz'),
    ('Victor', 'Blair'),
    ('Martha', 'Stevens'),
    ('Pat', 'Bush');

INSERT INTO classes(title, description, class_num)
    VALUES ('Botany', "Learn about plants", 101),
    ("Intro to Computer Science", "Find out how to code", 302),
    ("Biology", "We teach you the study of life", 105),
    ("Accounting", 'How to document financial transactions', 307),
    ("Intro to Music", 'Learn about variety types of music',113),
    ('Pharmacology', 'Study of drug', 553),
    ('Nursing', 'This course provides provision of professional nursing care', 321),
    ('European Literature', 'Read modern wirtten works from Europe',223),
    ('Drawing', "Course in basic drawing skills", 206),
    ('Photography', 'Fundamentals in technique with emphasis on individual projects', 601),
    ('Languages and Cultures of Asia', 'Comparative perspective on Asian languages', 333),
    ('Critical Reading and Writing', 'Introduction to literary analysis', 401),
    ('Intro to Buddhism', 'General survey of Buddihist worldview and lifestyle', 109),
    ('Western Civilization', 'Foundation of Western civilization', 003);

-- enroll students in class
INSERT INTO
enrollments(enrollment_date, student_id, class_id)
VALUES  ('2017-09-26', 3, 14), 
('2016-09-02', 6, 4), ('2015-02-07', 3, 7), ('2016-05-08', 7, 3), 
('2013-12-28', 6, 11), ('2018-10-16', 1, 2), ('2015-02-21', 6, 2), 
('2014-07-19', 4, 3), ('2016-11-27', 7, 1), ('2017-09-09', 6, 6), 
('2016-04-27', 3, 6), ('2015-10-05', 7, 12), ('2017-02-27', 6, 8), 
('2012-06-19', 4, 7), ('2014-09-05', 4, 4), ('2017-12-18', 2, 13), 
('2013-08-13', 2, 3), ('2019-01-28', 4, 14), ('2015-08-08', 5, 5), 
('2012-01-14', 6, 6), ('2016-05-12', 2, 9), ('2017-03-27', 1, 6), 
('2013-12-17', 4, 13), ('2016-10-06', 1, 2), ('2016-12-05', 3, 14), 
('2014-09-17', 2, 2), ('2013-10-16', 5, 12), ('2018-12-23', 5, 8), 
('2015-02-14', 1, 6), ('2012-06-14', 1, 13), ('2016-11-22', 4, 2), 
('2013-02-14', 5, 11), ('2012-06-22', 2, 4), ('2019-01-20', 6, 12), 
('2019-07-09', 4, 6), ('2016-04-25', 1, 6), ('2018-02-10', 4, 6), 
('2018-09-13', 5, 4), ('2012-08-26', 3, 11), ('2015-10-26', 6, 5), 
('2012-04-16', 4, 12), ('2018-01-12', 5, 11), ('2012-05-05', 5, 1);