A games tábla a következő mezőket tartalmazza:

A users tábla a következő mezőket tartalmazza:

A recommendations tábla a következő mezőket tartalmazza:

Adatbázis létrehozása és importálás

Hozzátok létre egy adatbázist steam_recommendations néven, majd a forrásfájlok elemzését követően az adatok tárolására megfelelő adattáblákat games, users és recommendations néven. Az elsődleges kulcsokat is állítsátok be. Az idegen kulcsokat most még ne állítsátok be.

A logikai értékek tárolására ENUM(’false’,’true’) adattípust használjatok.

Órai parancsok

CREATE DATABASE steam_recommendations
CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE games(
    app_id int NOT NULL,
    title varchar(255),
    date_release date,
    win ENUM('false','true'),
    mac ENUM('false','true'),
    linux ENUM('false','true'),
    rating varchar(255),
    positive_ratio int,
    user_reviews int,
    price_final double,
    price_original double,
    discount double,
    steam_deck ENUM('false','true'),
    PRIMARY KEY (app_id)
)
CHARACTER SET utf8
COLLATE utf8_general_ci;

LOAD DATA LOCAL INFILE 'D:/games.csv' INTO TABLE steam_recommendations.games FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\\n' IGNORE 1 LINES;

CREATE TABLE users(
    user_id int NOT NULL,
    products int,
    reviews int,
    PRIMARY KEY (user_id)
)
CHARACTER SET utf8
COLLATE utf8_general_ci;

LOAD DATA LOCAL INFILE 'D:/users.csv' INTO TABLE steam_recommendations.users FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\\n' IGNORE 1 LINES;

CREATE TABLE recommendations(
    app_id int,
    helpful int,
    funny int,
    date_submitted date,
    is_recommended ENUM('false','true'),
    hours double,
    user_id int,
    review_id int NOT NULL,
    PRIMARY KEY (review_id)
)
CHARACTER SET utf8
COLLATE utf8_general_ci;

LOAD DATA LOCAL INFILE 'D:/recommendations.csv' INTO TABLE steam_recommendations.recommendations FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\\n' IGNORE 1 LINES;

ALTER TABLE recommendations
ADD FOREIGN KEY (app_id) REFERENCES games(app_id),
ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

ALTER TABLE recommendations ADD FOREIGN KEY (app_id) REFERENCES games(app_id);

ALTER TABLE recommendations ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

Adatok exportálása SQL formátumban

mysqldump -u root -p adatbázisneve táblaneve > sqlfájlelérésiútja

mysqldump -u root -p steam_recommendations users > C:/Users/csapo.gabor/Downloads/users.sql

mysqldump -u root -p steam_recommendations recommendations > C:/Users/csapo.gabor/Downloads/recommendations.sql

Feladatok

Oldjátok meg a következő feladatokat. Futtassátok le a lekérdezéseket PHPMyAdmin-ban és parancssorban is. Mit tapasztaltok a futási időket tekintve?

1. Írassuk ki az adatbázisban tárolt összes játék nevét ABC sorrendbe rendezve.

   SELECT title FROM games
   ORDER BY title;
   

2. Átlagosan mennyi játékkal rendelkeznek a felhasználók? Jelenítsük meg az értéket average_products mezőnévvel.

   SELECT AVG(products) AS average_products FROM users;
   

3. Melyik értékelést nyilvánították a leginkább hasznosnak (helpful) a felhasználók? Kérdezzük le az értékelés id-ját, annak a felhasználónak az azonosítóját aki írta azt, illetve a hasznosnak ítélt szavazatok számát.

   SELECT user_id, review_id, helpful FROM recommendations
   WHERE helpful = (SELECT MAX(helpful) FROM recommendations);
   

4. Az egyes játékokra összesen mennyi értékelés érkezett? Jelenítsük meg a játékok nevét és a kapott értékelések számát review_count aliassal. A megoldáshoz a games tábla user_reviews mezője nem használható.

   SELECT games.title, COUNT(recommendations.review_id) AS review_count FROM games
   INNER JOIN recommendations ON games.app_id = recommendations.app_id
   GROUP BY games.title;
   

Adatok módosítása