SQL test 00 - test data

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109

-- 1. 對應 anime-dataset-2023.csv
CREATE TABLE ANIME_DATASET_2023 (
    ANIME_ID NUMBER PRIMARY KEY,
    NAME NVARCHAR2(500),
    ENGLISH_NAME NVARCHAR2(500),
    OTHER_NAME NVARCHAR2(500),
    SCORE NUMBER(5,2),
    GENRES NVARCHAR2(1000),
    SYNOPSIS CLOB,
    TYPE NVARCHAR2(50),
    EPISODES NUMBER,
    AIRED NVARCHAR2(100),
    PREMIERED NVARCHAR2(100),
    STATUS NVARCHAR2(100),
    PRODUCERS NVARCHAR2(1000),
    LICENSORS NVARCHAR2(1000),
    STUDIOS NVARCHAR2(1000),
    SOURCE NVARCHAR2(100),
    DURATION NVARCHAR2(100),
    RATING NVARCHAR2(200),
    RANK NUMBER,
    POPULARITY NUMBER,
    FAVORITES NUMBER,
    SCORED_BY NUMBER,
    MEMBERS NUMBER,
    IMAGE_URL NVARCHAR2(1000)
);

-- 2. 對應 anime-filtered.csv
CREATE TABLE ANIME_FILTERED (
    ANIME_ID NUMBER PRIMARY KEY,
    NAME NVARCHAR2(500),
    SCORE NUMBER(5,2),
    GENRES NVARCHAR2(1000),
    ENGLISH_NAME NVARCHAR2(500),
    JAPANESE_NAME NVARCHAR2(500),
    SYPNOPSIS CLOB,
    TYPE NVARCHAR2(50),
    EPISODES NUMBER,
    AIRED NVARCHAR2(100),
    PREMIERED NVARCHAR2(100),
    PRODUCERS NVARCHAR2(1000),
    LICENSORS NVARCHAR2(1000),
    STUDIOS NVARCHAR2(1000),
    SOURCE NVARCHAR2(100),
    DURATION NVARCHAR2(100),
    RATING NVARCHAR2(200),
    RANKED NUMBER,
    POPULARITY NUMBER,
    MEMBERS NUMBER,
    FAVORITES NUMBER,
    WATCHING NUMBER,
    COMPLETED NUMBER,
    ON_HOLD NUMBER,
    DROPPED NUMBER
);

-- 3. 對應 final_animedataset.csv
CREATE TABLE FINAL_ANIMEDATASET (
    USERNAME NVARCHAR2(255),
    ANIME_ID NUMBER,
    MY_SCORE NUMBER,
    USER_ID NUMBER,
    GENDER NVARCHAR2(50),
    TITLE NVARCHAR2(500),
    TYPE NVARCHAR2(50),
    SOURCE NVARCHAR2(100),
    SCORE NUMBER(5,2),
    SCORED_BY NUMBER,
    RANK NUMBER,
    POPULARITY NUMBER,
    GENRE NVARCHAR2(1000)
);

-- 4. 對應 user-filtered.csv
CREATE TABLE USER_FILTERED (
    USER_ID NUMBER,
    ANIME_ID NUMBER,
    RATING NUMBER
);

-- 5. 對應 users-details-2023.csv
CREATE TABLE USERS_DETAILS_2023 (
    MAL_ID NUMBER PRIMARY KEY,
    USERNAME NVARCHAR2(255),
    GENDER NVARCHAR2(50),
    BIRTHDAY NVARCHAR2(100),
    LOCATION NVARCHAR2(500),
    JOINED NVARCHAR2(100),
    DAYS_WATCHED NUMBER(10,2),
    MEAN_SCORE NUMBER(5,2),
    WATCHING NUMBER,
    COMPLETED NUMBER,
    ON_HOLD NUMBER,
    DROPPED NUMBER,
    PLAN_TO_WATCH NUMBER,
    TOTAL_ENTRIES NUMBER,
    REWATCHED NUMBER,
    EPISODES_WATCHED NUMBER
);

-- 6. 對應 users-score-2023.csv
CREATE TABLE USERS_SCORE_2023 (
    USER_ID NUMBER,
    USERNAME NVARCHAR2(255),
    ANIME_ID NUMBER,
    ANIME_TITLE NVARCHAR2(500),
    RATING NUMBER
);