DIDA2

DROP DATABASE IF EXISTS games;
CREATE DATABASE IF NOT EXISTS games;
USE games;
CREATE TABLE game (
    id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    studio VARCHAR(100) NOT NULL,
    release_date DATE NOT NULL,
    multiplayer boolean NOT NULL,
    img VARCHAR(300)
);

CREATE TABLE platforms (
    id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(50) NOT NULL
);

CREATE TABLE genres (
    id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    genre VARCHAR(50) NOT NULL
);

CREATE TABLE gameplatform(
    id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    idPlatform INT(3) NOT NULL,
    idGame INT(3) NOT NULL
);

ALTER TABLE gameplatform
    ADD FOREIGN KEY(idPlatform) REFERENCES platforms(id);
ALTER TABLE gameplatform
    ADD FOREIGN KEY(idGame) REFERENCES game(id);

CREATE TABLE gamegenre(
    id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    idGenre INT(3) NOT NULL,
    idGame INT(3) NOT NULL
);

ALTER TABLE gamegenre
    ADD FOREIGN KEY(idGenre) REFERENCES genres(id);
ALTER TABLE gamegenre
    ADD FOREIGN KEY(idGame) REFERENCES game(id);


INSERT INTO platforms(platform) VALUES("PC"),("PS5"),("PS4"),("PS3"),("PS2"),("PlayStation"),("PSP"),("Xbox Series X/S"),("Xbox One"),("Xbox 360"),("Xbox"),("Nintendo Switch"),
("Nintendo Wii U"),("Nintendo Wii"),("Nintendo GameCube"),("Nintendo 64"),("SNES"),("NES"),("Nintendo 3DS"),("Nintendo DS"),
("Game Boy Advance"), ("Game Boy Color"),("Sega Mega Drive"), ("Sega Master System"),("Stadia"),("Android"),("iOS"),("PSVita");

INSERT INTO genres(genre) VALUES("RPG"),("Adventure"),("Action"),("Simulation"),("Shooter"),("Sandbox"),("Hack And Slash"),("Horror"),
("Visual Novel"),("MOBA"),("Puzzle"),("Platform"),("Fight"),("Sports"),("Music"),("Strategy"),("AR"),("Party Game"),("Battle Royale");

INSERT INTO game (title,studio,release_date,multiplayer)VALUES
("Octopath Traveler","Square Enix","2018-07-13",0),
("Rocket League","Psyonix","2015-07-07",1),
("Kimi ga shine","Nankidai","2017-08-28",0),
("Minecraft","Mojang","2011,12,20",1),
("NieR: Automata","Square Enix","2017-02-23",0),
("Pokémon Go","Niantic","2016-07-06",1);

INSERT INTO gamegenre (idGame,idGenre) VALUES(1,1),(1,2),(2,14),(3,2),(3,9),(3,11),(4,6),(5,1),(5,2),(5,7),(6,17),(6,1);
INSERT INTO gameplatform(idGame,idPlatform)VALUES(1,12),(1,1),(1,25),(2,1),(2,3),(2,9),(2,12),(3,1),(4,1),
(4,3),(4,4),(4,9),(4,12),(4,13),(4,25),(4,26),(4,27),(4,28),(4,19),(5,1),(5,3),(5,9),(6,26);

/*Filter by genre*/
SELECT
g.title,gr.genre FROM game g
JOIN gamegenre gg
ON gg.idGame = g.id
JOIN genres gr
ON gr.id = gg.idGenre
WHERE g.title = 'Octopath Traveler'
;

SELECT * FROM game g
JOIN gamegenre gg
ON gg.idGame = g.id
JOIN genres gr
ON gr.id = gg.idGenre
WHERE gr.genre = "RPG"
;

SELECT p.platform, g.title, g.studio,g.release_date, g.multiplayer FROM game g
JOIN gameplatform gp
ON gp.idGame = g.id
JOIN platforms p
ON p.id = gp.idPlatform
WHERE p.platform NOT IN ('Pc','Ps4','Xbox One','Nintendo Switch');

/*Filter by platform*/
SELECT g.title, p.platform FROM game g
JOIN gameplatform gp
ON gp.idGame = g.id
JOIN platforms p
ON p.id = gp.idPlatform
WHERE p.platform = "Nintendo Switch"

SELECT g.title, p.platform FROM game g
JOIN gameplatform gp
ON gp.idGame = g.id
JOIN platforms p
ON p.id = gp.idPlatform
WHERE p.platform NOT LIKE "PC" AND "Ps4" AND "Xbox One" AND "Nintendo Switch";

SELECT g.title, p.platform FROM game g
JOIN gameplatform gp
ON gp.idGame = g.id
JOIN platforms p
ON p.id = gp.idPlatform
WHERE g.title = "Octopath Traveler"