DB_commands.sql

CREATE DATABASE IF NOT EXISTS myanimelist_db;

USE myanimelist_db;



CREATE EXTERNAL TABLE IF NOT EXISTS anime (
    anime_id INT,
    name STRING,
    english_name STRING,
    other_name STRING,
    score FLOAT,
    genres STRING,
    synopsis STRING,
    type STRING,
    episodes FLOAT,
    aired STRING,
    premiered STRING,
    status STRING,
    producers STRING,
    licensors STRING,
    studios STRING,
    source STRING,
    duration STRING,
    rating STRING,
    rank FLOAT,
    popularity INT,
    favorites INT,
    scored_by FLOAT,
    members INT,
    image_url STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

CREATE EXTERNAL TABLE IF NOT EXISTS users (
    mal_id INT,
    username STRING,
    gender STRING,
    birthday TIMESTAMP,
    location STRING,
    joined TIMESTAMP,
    days_watched FLOAT,
    mean_score FLOAT,
    watching FLOAT,
    completed FLOAT,
    on_hold FLOAT,
    dropped FLOAT,
    plan_to_watch FLOAT,
    total_entries FLOAT,
    rewatched FLOAT,
    episodes_watched FLOAT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;


CREATE EXTERNAL TABLE IF NOT EXISTS score (
    user_id INT,
    anime_id INT,
    rating INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
--------------------------------

CREATE TABLE IF NOT EXISTS users_score_2023 (
    user_id INT,
    username STRING,
    anime_id INT,
    anime_title STRING,
    rating INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

------------------------------

SELECT x.table
FROM users u
JOIN score s ON u.user_id = s.user_id
JOIN anime a ON u.anime_id = a.anime_id;