Project from Hyperskill: I will learn how to use basic SQL commands (DDL, DML, DQL) to build, sort, relate data, and manage and extract information from an SQL database.

VideoGameDatabase

CREATE TABLE video_games
(
    id             INTEGER      not null,
    name           VARCHAR(255) not null,
    game_genre     TEXT         not null,
    game_developer VARCHAR(255) not null,
    release_date   DATE         not null,
    PRIMARY KEY (id)
);

CREATE TABLE games_developers
(
    id      INTEGER      not null,
    name    VARCHAR(255) not null,
    address VARCHAR(255) not null,
    state   VARCHAR(255),
    city    VARCHAR(255) not null,
    country VARCHAR(255) not null,
    PRIMARY KEY (id)
);

CREATE TABLE platforms
(
    id             INTEGER      not null,
    name           VARCHAR(255) not null,
    company_id     INTEGER,
    company        VARCHAR(255) not null,
    release_date   DATE         not null,
    original_price FLOAT        not null,
    PRIMARY KEY (id)
);

CREATE TABLE platforms_games
(
    game_id       INTEGER      not null,
    platform_id   INTEGER      not null,
    platform_name VARCHAR(255) not null,
    PRIMARY KEY (game_id, platform_id),
    FOREIGN KEY (game_id) REFERENCES video_games (id),
    FOREIGN KEY (platform_id) REFERENCES platforms (id)
);

CREATE TABLE characters
(
    id       INTEGER not null,
    name     TEXT    not null,
    birthday DATE    not null,
    gender   VARCHAR(10),
    info     TEXT    not null,
    PRIMARY KEY (id)
);

CREATE TABLE games_characters
(
    character_id   INTEGER      not null,
    character_name INTEGER      not null,
    game_id        VARCHAR(255) not null,
    PRIMARY KEY (character_id, game_id),
    FOREIGN KEY (character_id) REFERENCES characters (id),
    FOREIGN KEY (game_id) REFERENCES video_games (id)
);

DELETE FROM games_characters WHERE character_id IS NULL;

-- It's required by task
UPDATE platforms set release_date = DATE();
UPDATE characters set birthday = DATE();