CREATE TABLE Projects( 
    ProjectID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(100) NOT NULL, 
    Type ENUM('Movie','TV Show', 'Podcast') NOT NULL, 
    ReleaseYear SMALLINT UNSIGNED, 
    Notes TEXT
    ); 

CREATE TABLE Roles ( 
    RoleID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
    ProjectID INT UNSIGNED, 
    CharacterName VARCHAR(50), 
    RoleType VARCHAR(50), 
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
    );

CREATE TABLE Episodes ( 
    EpisodeID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    ProjectID INT UNSIGNED, 
    Title VARCHAR(100), 
    SeasonNumber SMALLINT UNSIGNED, 
    EpisodeNumber SMALLINT UNSIGNED, 
    AirDate DATE, 
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
    );

INSERT INTO Projects (Title, Type, ReleaseYear, Notes) VALUES
    ('Cheaper By The Dozen', 'Movie', 2003, NULL), 
    ('The Fog', 'Movie', 2005, NULL), 
    ('Cheaper By The Dozen 2', 'Movie', 2005, NULL),
    ('Parkland', 'Movie', 2013, NULL),
    ('Draft Day', 'Movie', 2014, NULL), 
    ('The Choice', 'Movie', 2016, NULL), 
    ('Judging Amy', 'TV Show', 2001, NULL), 
    ('Smallville', 'TV Show', 2001, 'Also known as Smallville Beginnings'), 
    ('Lucifer', 'TV Show', 2017, NULL), 
    ('Professionals', 'TV Show', 2020, NULL);

INSERT INTO Roles(ProjectID, CharacterName, RoleType)VALUES 
    (1, 'Charlie Baker', 'Lead'), 
    (2, 'Nick Castle', 'Lead'), 
    (3, 'Charlie Baker', 'Lead'), 
    (4, 'Roy Kellerman', 'Supporting'), 
    (5, 'Brian Drew', 'Supporting'), 
    (6, 'Dr. Ryan McCarthy', 'Supporting'), 
    (7, 'Rob Meltzer', 'Recurring'), 
    (8, 'Clark Kent', 'Lead'), 
    (9, 'Marcus Pierce', 'Recurring'), 
    (10, 'Vincent Corbo', 'Lead'); 


SELECT Roles.CharacterName, Episodes.EpisodeNumber 
FROM Roles 
LEFT JOIN Episodes ON Roles.ProjectID = Episodes.ProjectID;