skytrax-table-recreation.sql

--Tables recreation
DROP TABLE IF EXISTS skytrax.airline;
DROP TABLE IF EXISTS skytrax.airport;
DROP TABLE IF EXISTS skytrax.lounge;
DROP TABLE IF EXISTS skytrax.seat;

CREATE TABLE IF NOT EXISTS skytrax.airline(
    airline_name                    VARCHAR(100) DEFAULT 'Unknown',
    link                            VARCHAR(100) DEFAULT 'Unknown',
    title                           VARCHAR(150) DEFAULT 'Unknown',
    author                          VARCHAR(100) DEFAULT 'Unknown',
    author_country                  VARCHAR(100) DEFAULT 'Unknown',
    review_date                     DATE ,
    review_content                  VARCHAR(1000) DEFAULT 'Unknown',
    aircraft                        VARCHAR(100) DEFAULT 'Unknown',
    type_traveller                  VARCHAR(100) DEFAULT 'Unknown',
    cabin_flown                     VARCHAR(100) DEFAULT 'Unknown',
    route                           VARCHAR(100) DEFAULT 'Unknown',
    overall_rating                  FLOAT DEFAULT 0.0,
    seat_comfort_rating             FLOAT DEFAULT 0.0,
    cabin_staff_rating              FLOAT DEFAULT 0.0,
    food_beverages_rating           FLOAT DEFAULT 0.0,
    inflight_entertainment_rating   FLOAT DEFAULT 0.0,
    ground_service_rating           FLOAT DEFAULT 0.0,
    wifi_connectivity_rating        FLOAT DEFAULT 0.0,
    value_money_rating              FLOAT DEFAULT 0.0,
    recommended                     VARCHAR(100) DEFAULT 'Unknown'
);

CREATE TABLE IF NOT EXISTS skytrax.airport(
    airport_name                    VARCHAR(100) DEFAULT 'Unknown',
    link                            VARCHAR(100) DEFAULT 'Unknown',
    title                           VARCHAR(150) DEFAULT 'Unknown',
    author                          VARCHAR(100) DEFAULT 'Unknown',
    author_country                  VARCHAR(100) DEFAULT 'Unknown',
    review_date                     DATE ,
    review_content                  VARCHAR(1000) DEFAULT 'Unknown',
    experience_airport              VARCHAR(100) DEFAULT 'Unknown',
    date_visit                      DATE ,
    type_traveller                  VARCHAR(100) DEFAULT 'Unknown',
    overall_rating                  FLOAT DEFAULT 0.0,
    queuing_rating                  FLOAT DEFAULT 0.0,
    terminal_cleanness_rating       FLOAT DEFAULT 0.0,
    terminal_seating_rating         FLOAT DEFAULT 0.0,
    terminal_signs_rating           FLOAT DEFAULT 0.0,
    food_beverages_rating           FLOAT DEFAULT 0.0,
    airport_shopping_rating         FLOAT DEFAULT 0.0,
    wifi_connectivity_rating        FLOAT DEFAULT 0.0,
    airport_staff_rating            FLOAT DEFAULT 0.0,
    recommended                     VARCHAR(100) DEFAULT 'Unknown'
);

CREATE TABLE IF NOT EXISTS skytrax.lounge(
    airline_name                    VARCHAR(100) DEFAULT 'Unknown',
    link                            VARCHAR(100) DEFAULT 'Unknown',
    title                           VARCHAR(150) DEFAULT 'Unknown',
    author                          VARCHAR(100) DEFAULT 'Unknown',
    author_country                  VARCHAR(100) DEFAULT 'Unknown',
    review_date                     DATE ,
    review_content                  VARCHAR(1000) DEFAULT 'Unknown',
    lounge_name                     VARCHAR(100) DEFAULT 'Unknown',
    airport                         VARCHAR(100) DEFAULT 'Unknown',
    lounge_type                     VARCHAR(100) DEFAULT 'Unknown',
    date_visit                      DATE ,
    type_traveller                  VARCHAR(100) DEFAULT 'Unknown',
    overall_rating                  FLOAT DEFAULT 0.0,
    comfort_rating                  FLOAT DEFAULT 0.0,
    cleanness_rating                FLOAT DEFAULT 0.0,
    bar_beverages_rating            FLOAT DEFAULT 0.0,
    catering_rating                 FLOAT DEFAULT 0.0,
    washrooms_rating                FLOAT DEFAULT 0.0,
    wifi_connectivity_rating        FLOAT DEFAULT 0.0,
    staff_service_rating            FLOAT DEFAULT 0.0,
    recommended                     VARCHAR(100) DEFAULT 'Unknown'
);

CREATE TABLE IF NOT EXISTS skytrax.seat(
    airline_name                    VARCHAR(100) DEFAULT 'Unknown',
    link                            VARCHAR(100) DEFAULT 'Unknown',
    title                           VARCHAR(150) DEFAULT 'Unknown',
    author                          VARCHAR(100) DEFAULT 'Unknown',
    author_country                  VARCHAR(100) DEFAULT 'Unknown',
    review_date                     DATE ,
    review_content                  VARCHAR(1000) DEFAULT 'Unknown',
    aircraft                        VARCHAR(100) DEFAULT 'Unknown',
    seat_layout                     VARCHAR(50) DEFAULT 'Unknown',
    date_flown                      DATE ,
    cabin_flown                     VARCHAR(100) DEFAULT 'Unknown',
    type_traveller                  VARCHAR(100) DEFAULT 'Unknown',
    overall_rating                  FLOAT DEFAULT 0.0,
    seat_legroom_rating             FLOAT DEFAULT 0.0,
    seat_recline_rating             FLOAT DEFAULT 0.0,
    seat_width_rating               FLOAT DEFAULT 0.0,
    aisle_space_rating              FLOAT DEFAULT 0.0,
    viewing_tv_rating               FLOAT DEFAULT 0.0,
    power_supply_rating             FLOAT DEFAULT 0.0,
    seat_storage_rating             FLOAT DEFAULT 0.0,
    recommended                     VARCHAR(100) DEFAULT 'Unknown'
);