Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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");
- /*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"
- 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,15),(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);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement