DROP DATABASE IF EXISTS simpsons;
CREATE DATABASE simpsons;
USE simpsons;

CREATE TABLE courses (
id INT UNSIGNED NOT NULL PRIMARY KEY, 
name VARCHAR(32) DEFAULT NULL, 
teacher_id INT UNSIGNED NOT NULL
);
INSERT INTO courses VALUES
(10001, 'Computer Science 142', 1234),
(10002, 'Computer Science 143', 5678),
(10003, 'Computer Science 190M', 9012),
(10004, 'Informatics 100', 1234);

CREATE TABLE grades (
student_id INT UNSIGNED NOT NULL, 
course_id INT UNSIGNED NOT NULL, 
grade varchar(2) DEFAULT NULL
);
INSERT INTO grades VALUES
(123, 10001, 'B-'),
(123, 10002, 'C'),
(456, 10001, 'B+'),
(888, 10002, 'A+'),
(888, 10003, 'A+'),
(404, 10004, 'D+');

CREATE TABLE teachers (
id INT UNSIGNED NOT NULL PRIMARY KEY, 
name VARCHAR(32) DEFAULT NULL
);

INSERT INTO teachers VALUES
(1234, 'Krabappel'),
(5678, 'Hoover'),
(9012, 'Stepp');

-- debug output to just show the contents of all tables
SHOW TABLES;
SELECT * FROM courses;
SELECT * FROM grades;
SELECT * FROM teachers;
