-- College table
CREATE TABLE College( 
    CollegeID INT NOT NULL, 
    Name VARCHAR(45),
    City VARCHAR(45),
    State CHAR(2),
    PRIMARY KEY(CollegeID)
);

-- Bowl Game table
CREATE TABLE BowlGame(
    BowlGameID INT NOT NULL, 
    Bowl VARCHAR(45), 
    Stadium VARCHAR(45), 
    City VARCHAR(45), 
    State CHAR(2),
    WinningCollegeID INT, 
    PRIMARY KEY (BowlGameID)
);

-- Insert rows into College table
INSERT INTO College VALUES 
    (100, 'Pennsylvania State University', 'University Park', 'PA'), 
    (200, 'Ohio State University', 'Columbus', 'OH'), 
    (300, 'University of Southern California', 'Los Angeles', 'CA'), 
    (400, 'University of Notre Dame', 'Notre Dame', 'IN'), 
    (500, 'University of California', 'Los Angeles', 'CA'), 
    (600, 'Arizona State University', 'Tempe', 'AZ'), 
    (700, 'Texas A&M University', 'College Station', 'TX');

-- Insert rows into BowlGame table
INSERT INTO BowlGame VALUES
    (10, 'Cotton', 'AT&T Stadium', 'Arlington', 'TX', 200), 
    (20, 'Orange', 'Hard Rock Stadium', 'Miami Gardens', 'FL', 400), 
    (30, 'Rose', 'Rose Bowl Stadium', 'Pasadena', 'CA', 200), 
    (40, 'Sugar', 'Caesars Superdome', 'New Orleans', 'LA', NULL), 
    (50, 'Fiesta', 'State Farm Stadium', 'Glendale', 'AZ', NULL), 
    (60, 'Peach', 'Mercedes-Benz Stadium', 'Atlanta', 'GA', NULL);

SELECT 
    bg.Bowl,
    bg.Stadium, 
    c.Name 
FROM BowlGame bg 
LEFT JOIN College c ON bg.WinningCollegeID = c.CollegeID 

UNION


SELECT 
    NULL AS Bowl, 
    NULL AS Stadium, 
    c.Name 
FROM College c 
LEFT JOIN BowlGame bg ON c.CollegeID = bg.WinningCollegeID 
WHERE bg.WinningCollegeID IS NULL;