DROP TABLE students CASCADE CONSTRAINTS;
DROP TABLE courses CASCADE CONSTRAINTS;
DROP TABLE attend CASCADE CONSTRAINTS;

Create table students (
    student_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    full_name VARCHAR2(100) NOT NULL,
    country VARCHAR2(50) NOT NULL,
    PRIMARY KEY(student_id)
);

CREATE TABLE courses (
    depart VARCHAR2(100) NOT NULL,
    courseNum NUMBER(3) GENERATED BY DEFAULT AS IDENTITY,
    title VARCHAR2(1000) NOT NULL,
    PRIMARY KEY(depart,courseNum)
);

CREATE TABLE attend (
    semester VARCHAR(1) NOT NULL,
    mark NUMBER(3),
    student_id NUMBER,
    depart VARCHAR2(100) NOT NULL,
    courseNum NUMBER(3),
    PRIMARY KEY(student_id,depart,courseNum),
    CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES students(student_id),
    CONSTRAINT fk_course FOREIGN KEY (depart,courseNum) REFERENCES courses(depart,courseNum)
);

INSERT INTO students (full_name,country) VALUES ('Hunter Klein','Seychelles');
INSERT INTO students (full_name,country) VALUES ('Drew Shannon','Marshall Islands');
INSERT INTO students (full_name,country) VALUES ('Gregory Horn','Turkmenistan');
INSERT INTO students (full_name,country) VALUES ('Stephanie Watson','Anguilla');
INSERT INTO students (full_name,country) VALUES ('Ira Dunlap','Bouvet Island');
INSERT INTO students (full_name,country) VALUES ('Vaughan Murray','Antigua and Barbuda');
INSERT INTO students (full_name,country) VALUES ('Erica Hansen','Tanzania');
INSERT INTO students (full_name,country) VALUES ('Sawyer Ellis','Aruba');
INSERT INTO students (full_name,country) VALUES ('Yoshio Nicholson','Romania');
INSERT INTO students (full_name,country) VALUES ('Jared Best','Zambia');
INSERT INTO students (full_name,country) VALUES ('Kirk Riley','Cameroon');
INSERT INTO students (full_name,country) VALUES ('Eugenia Santiago','Portugal');
INSERT INTO students (full_name,country) VALUES ('Aubrey Baird','Croatia');
INSERT INTO students (full_name,country) VALUES ('Evan Stewart','Japan');
INSERT INTO students (full_name,country) VALUES ('Alec Lindsay','Cocos (Keeling) Islands');
INSERT INTO students (full_name,country) VALUES ('George Leon','Cambodia');
INSERT INTO students (full_name,country) VALUES ('Jordan Mccullough','Serbia');
INSERT INTO students (full_name,country) VALUES ('Walter Frye','Curaçao');
INSERT INTO students (full_name,country) VALUES ('Tallulah Shepard','Japan');
INSERT INTO students (full_name,country) VALUES ('Rigel Saunders','Iraq');
INSERT INTO students (full_name,country) VALUES ('Ulric Valdez','Yemen');
INSERT INTO students (full_name,country) VALUES ('Stacy Armstrong','Iceland');
INSERT INTO students (full_name,country) VALUES ('Ocean Rivera','South Sudan');
INSERT INTO students (full_name,country) VALUES ('Barbara Gonzalez','Algeria');
INSERT INTO students (full_name,country) VALUES ('Fitzgerald Green','Mauritius');
INSERT INTO students (full_name,country) VALUES ('Kermit Vasquez','Botswana');
INSERT INTO students (full_name,country) VALUES ('Scarlett Everett','El Salvador');
INSERT INTO students (full_name,country) VALUES ('Stacy Moody','Cuba');
INSERT INTO students (full_name,country) VALUES ('Lars Nash','Sweden');
INSERT INTO students (full_name,country) VALUES ('Bertha Lawrence','United Arab Emirates');
INSERT INTO students (full_name,country) VALUES ('Bell Brooks','Chad');
INSERT INTO students (full_name,country) VALUES ('Kelsie Vance','Sudan');
INSERT INTO students (full_name,country) VALUES ('Cathleen Willis','French Guiana');
INSERT INTO students (full_name,country) VALUES ('Judah Mcknight','Wallis and Futuna');
INSERT INTO students (full_name,country) VALUES ('Teegan Gill','Mali');
INSERT INTO students (full_name,country) VALUES ('Kuame Deleon','Virgin Islands, British');
INSERT INTO students (full_name,country) VALUES ('Joan Page','Grenada');
INSERT INTO students (full_name,country) VALUES ('Ryan Richmond','Bahrain');
INSERT INTO students (full_name,country) VALUES ('Cynthia Landry','Liechtenstein');
INSERT INTO students (full_name,country) VALUES ('Lester Salinas','French Polynesia');
INSERT INTO students (full_name,country) VALUES ('Jermaine Frye','United Kingdom (Great Britain)');
INSERT INTO students (full_name,country) VALUES ('William Daniels','Monaco');
INSERT INTO students (full_name,country) VALUES ('Jesse Browning','Mayotte');
INSERT INTO students (full_name,country) VALUES ('Wylie Molina','Canada');
INSERT INTO students (full_name,country) VALUES ('Joy Lang','Burundi');
INSERT INTO students (full_name,country) VALUES ('Roary Preston','Equatorial Guinea');
INSERT INTO students (full_name,country) VALUES ('Kathleen Sargent','British Indian Ocean Territory');
INSERT INTO students (full_name,country) VALUES ('Kelly Frederick','San Marino');
INSERT INTO students (full_name,country) VALUES ('Cassandra Livingston','Poland');
INSERT INTO students (full_name,country) VALUES ('Anika Mayo','Cook Islands');
INSERT INTO students (full_name,country) VALUES ('Baxter Klein','Tunisia');
INSERT INTO students (full_name,country) VALUES ('Alvin Vang','Australia');
INSERT INTO students (full_name,country) VALUES ('Damon Bishop','Tanzania');
INSERT INTO students (full_name,country) VALUES ('Nell Conrad','Virgin Islands, United States');
INSERT INTO students (full_name,country) VALUES ('Jamal Copeland','Reunion');
INSERT INTO students (full_name,country) VALUES ('Remedios Pickett','Sao Tome and Principe');
INSERT INTO students (full_name,country) VALUES ('Ivan Wolf','Oman');
INSERT INTO students (full_name,country) VALUES ('Kevin Bryant','Italy');
INSERT INTO students (full_name,country) VALUES ('Leroy Webb','Jordan');
INSERT INTO students (full_name,country) VALUES ('Wang Kirkland','Uruguay');
INSERT INTO students (full_name,country) VALUES ('Amery Estes','Rwanda');
INSERT INTO students (full_name,country) VALUES ('Brenden Garrison','Slovakia');
INSERT INTO students (full_name,country) VALUES ('Erich Powell','Finland');
INSERT INTO students (full_name,country) VALUES ('Hilary Maldonado','Peru');
INSERT INTO students (full_name,country) VALUES ('Brooke Freeman','Rwanda');
INSERT INTO students (full_name,country) VALUES ('Sandra Kirkland','Vanuatu');
INSERT INTO students (full_name,country) VALUES ('Illana Downs','South Georgia and The South Sandwich Islands');
INSERT INTO students (full_name,country) VALUES ('Ainsley Ratliff','Kyrgyzstan');
INSERT INTO students (full_name,country) VALUES ('Ava Carson','Jersey');
INSERT INTO students (full_name,country) VALUES ('Octavius Cantu','Liechtenstein');
INSERT INTO students (full_name,country) VALUES ('Nigel Schwartz','Dominican Republic');
INSERT INTO students (full_name,country) VALUES ('Angelica Tyler','Comoros');
INSERT INTO students (full_name,country) VALUES ('Catherine Collier','Albania');
INSERT INTO students (full_name,country) VALUES ('Dara Randall','Liechtenstein');
INSERT INTO students (full_name,country) VALUES ('Molly Riggs','Saint Lucia');
INSERT INTO students (full_name,country) VALUES ('Alden Valencia','Niger');
INSERT INTO students (full_name,country) VALUES ('Odessa Young','Greece');
INSERT INTO students (full_name,country) VALUES ('Cooper Ray','Turkmenistan');
INSERT INTO students (full_name,country) VALUES ('Zelenia Livingston','Georgia');
INSERT INTO students (full_name,country) VALUES ('Eden Simon','Jersey');
INSERT INTO students (full_name,country) VALUES ('Bell Guthrie','Austria');
INSERT INTO students (full_name,country) VALUES ('Robin Carney','Malta');
INSERT INTO students (full_name,country) VALUES ('Uma Byers','Ecuador');
INSERT INTO students (full_name,country) VALUES ('Rylee Bartlett','Singapore');
INSERT INTO students (full_name,country) VALUES ('Philip Mcbride','Qatar');
INSERT INTO students (full_name,country) VALUES ('Jacqueline Dejesus','Singapore');
INSERT INTO students (full_name,country) VALUES ('Boris Patel','Turkmenistan');
INSERT INTO students (full_name,country) VALUES ('Patricia Copeland','El Salvador');
INSERT INTO students (full_name,country) VALUES ('MacKenzie Wilson','Sint Maarten');
INSERT INTO students (full_name,country) VALUES ('Brenna Atkins','Curaçao');
INSERT INTO students (full_name,country) VALUES ('Madeson Santos','Croatia');
INSERT INTO students (full_name,country) VALUES ('Venus Hensley','Serbia');
INSERT INTO students (full_name,country) VALUES ('Wade Pate','Macao');
INSERT INTO students (full_name,country) VALUES ('Eric Cortez','Mozambique');
INSERT INTO students (full_name,country) VALUES ('Amery Gay','Singapore');
INSERT INTO students (full_name,country) VALUES ('Linus Ramsey','Virgin Islands, United States');
INSERT INTO students (full_name,country) VALUES ('Jennifer Bauer','Tunisia');
INSERT INTO students (full_name,country) VALUES ('Breanna Bond','United Kingdom (Great Britain)');
INSERT INTO students (full_name,country) VALUES ('Len Benjamin','Chile');
INSERT INTO students (full_name,country) VALUES ('Marcia Harrington','Romania');

INSERT INTO courses (depart,title) VALUES ('Software Engineering','egestas. Duis ac');
INSERT INTO courses (depart,title) VALUES ('Computer Science','metus. Aliquam erat');
INSERT INTO courses (depart,title) VALUES ('Computer Science','nec mauris blandit');
INSERT INTO courses (depart,title) VALUES ('Computer Science','luctus, ipsum leo');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','sapien. Nunc pulvinar');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','scelerisque neque. Nullam');
INSERT INTO courses (depart,title) VALUES ('Mathematics','sem elit, pharetra');
INSERT INTO courses (depart,title) VALUES ('Computer Science','sit amet risus.');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','non, cursus non,');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','at lacus. Quisque');
INSERT INTO courses (depart,title) VALUES ('Mathematics','id risus quis');
INSERT INTO courses (depart,title) VALUES ('Computer Science','egestas. Aliquam fringilla');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','arcu. Vivamus sit');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','viverra. Maecenas iaculis');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','diam dictum sapien.');
INSERT INTO courses (depart,title) VALUES ('Mathematics','Nulla aliquet. Proin');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','eget varius ultrices,');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','pede. Cras vulputate');
INSERT INTO courses (depart,title) VALUES ('Mathematics','congue turpis. In');
INSERT INTO courses (depart,title) VALUES ('Mathematics','inceptos hymenaeos. Mauris');
INSERT INTO courses (depart,title) VALUES ('Mathematics','sem. Nulla interdum.');
INSERT INTO courses (depart,title) VALUES ('Computer Science','orci, adipiscing non,');
INSERT INTO courses (depart,title) VALUES ('Computer Science','risus. In mi');
INSERT INTO courses (depart,title) VALUES ('Mathematics','In nec orci.');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','Aenean sed pede');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','metus. Aenean sed');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','consectetuer ipsum nunc');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','eu, euismod ac,');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','non, egestas a,');
INSERT INTO courses (depart,title) VALUES ('Mathematics','adipiscing elit. Etiam');
INSERT INTO courses (depart,title) VALUES ('Mathematics','turpis nec mauris');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','rutrum urna, nec');
INSERT INTO courses (depart,title) VALUES ('Computer Science','sagittis felis. Donec');
INSERT INTO courses (depart,title) VALUES ('Mathematics','porttitor interdum. Sed');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','Aliquam rutrum lorem');
INSERT INTO courses (depart,title) VALUES ('Mathematics','a nunc. In');
INSERT INTO courses (depart,title) VALUES ('Computer Science','aliquet magna a');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','non arcu. Vivamus');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','consequat auctor, nunc');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','et, lacinia vitae,');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','lorem, luctus ut,');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','sed dictum eleifend,');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','Donec felis orci,');
INSERT INTO courses (depart,title) VALUES ('Mathematics','ultricies ornare, elit');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','Vestibulum ante ipsum');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','eget mollis lectus');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','nec, mollis vitae,');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','erat vel pede');
INSERT INTO courses (depart,title) VALUES ('Mathematics','Nulla facilisi. Sed');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','pellentesque, tellus sem');
INSERT INTO courses (depart,title) VALUES ('Computer Science','erat. Vivamus nisi.');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','cursus, diam at');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','arcu. Nunc mauris.');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','Donec vitae erat');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','nunc sit amet');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','vitae, erat. Vivamus');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','nec, leo. Morbi');
INSERT INTO courses (depart,title) VALUES ('Computer Science','Morbi non sapien');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','auctor velit. Aliquam');
INSERT INTO courses (depart,title) VALUES ('Mathematics','sit amet ornare');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','mus. Proin vel');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','sit amet lorem');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','Maecenas malesuada fringilla');
INSERT INTO courses (depart,title) VALUES ('Mathematics','Ut nec urna');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','turpis. In condimentum.');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','metus sit amet');
INSERT INTO courses (depart,title) VALUES ('Computer Science','est. Mauris eu');
INSERT INTO courses (depart,title) VALUES ('Mathematics','egestas. Aliquam fringilla');
INSERT INTO courses (depart,title) VALUES ('Mathematics','nulla at sem');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','purus sapien, gravida');
INSERT INTO courses (depart,title) VALUES ('Mathematics','Nullam nisl. Maecenas');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','aliquet nec, imperdiet');
INSERT INTO courses (depart,title) VALUES ('Computer Science','ac ipsum. Phasellus');
INSERT INTO courses (depart,title) VALUES ('Computer Science','In scelerisque scelerisque');
INSERT INTO courses (depart,title) VALUES ('Mathematics','sociis natoque penatibus');
INSERT INTO courses (depart,title) VALUES ('Computer Science','sit amet ante.');
INSERT INTO courses (depart,title) VALUES ('Mathematics','elit. Etiam laoreet,');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','sem eget massa.');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','purus mauris a');
INSERT INTO courses (depart,title) VALUES ('Mathematics','Donec non justo.');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','elit fermentum risus,');
INSERT INTO courses (depart,title) VALUES ('Mathematics','enim non nisi.');
INSERT INTO courses (depart,title) VALUES ('Mathematics','quis turpis vitae');
INSERT INTO courses (depart,title) VALUES ('Mathematics','Integer aliquam adipiscing');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','gravida sit amet,');
INSERT INTO courses (depart,title) VALUES ('Embedded Systems','consequat dolor vitae');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','eros. Proin ultrices.');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','egestas, urna justo');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','vel turpis. Aliquam');
INSERT INTO courses (depart,title) VALUES ('Data Analytics','nulla. Cras eu');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','pellentesque massa lobortis');
INSERT INTO courses (depart,title) VALUES ('Computer Science','est ac mattis');
INSERT INTO courses (depart,title) VALUES ('Mathematics','in, tempus eu,');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','nec quam. Curabitur');
INSERT INTO courses (depart,title) VALUES ('Computer Science','iaculis nec, eleifend');
INSERT INTO courses (depart,title) VALUES ('Computer Science','euismod mauris eu');
INSERT INTO courses (depart,title) VALUES ('Mathematics','dictum ultricies ligula.');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','amet, consectetuer adipiscing');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','elit. Curabitur sed');
INSERT INTO courses (depart,title) VALUES ('Software Engineering','auctor quis, tristique');

INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',88,17,93,'X');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',88,60,9,'X');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',26,29,61,'H');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',46,35,70,'A');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',90,91,6,'C');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Computer Science',22,18,23,'H');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',79,69,18,'B');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Embedded Systems',45,94,48,'C');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',53,71,62,'G');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Computer Science',67,16,29,'A');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Embedded Systems',59,55,63,'G');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',9,90,38,'X');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Embedded Systems',59,46,42,'H');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Computer Science',12,8,58,'G');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',47,54,98,'X');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',79,5,24,'H');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Embedded Systems',59,47,44,'G');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Computer Science',76,85,75,'G');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Embedded Systems',10,74,9,'A');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Data Analytics',42,82,62,'B');
INSERT INTO attend (depart,courseNum,student_id,mark,semester) VALUES ('Computer Science',96,91,20,'B');
