· 4 years ago · Dec 13, 2020, 01:58 PM
1DROP DATABASE IF EXISTS games;
2CREATE DATABASE IF NOT EXISTS games;
3USE games;
4CREATE TABLE game (
5 id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
6 title VARCHAR(50) NOT NULL,
7 studio VARCHAR(100) NOT NULL,
8 release_date DATE NOT NULL,
9 multiplayer boolean NOT NULL,
10 img VARCHAR(300)
11);
12
13CREATE TABLE platforms (
14 id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
15 platform VARCHAR(50) NOT NULL
16);
17
18CREATE TABLE genres (
19 id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
20 genre VARCHAR(50) NOT NULL
21);
22
23CREATE TABLE gameplatform(
24 id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
25 idPlatform INT(3) NOT NULL,
26 idGame INT(3) NOT NULL
27);
28
29ALTER TABLE gameplatform
30 ADD FOREIGN KEY(idPlatform) REFERENCES platforms(id);
31ALTER TABLE gameplatform
32 ADD FOREIGN KEY(idGame) REFERENCES game(id);
33
34CREATE TABLE gamegenre(
35 id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
36 idGenre INT(3) NOT NULL,
37 idGame INT(3) NOT NULL
38);
39
40ALTER TABLE gamegenre
41 ADD FOREIGN KEY(idGenre) REFERENCES genres(id);
42ALTER TABLE gamegenre
43 ADD FOREIGN KEY(idGame) REFERENCES game(id);
44
45
46INSERT INTO platforms(platform) VALUES("PC"),("PS5"),("PS4"),("PS3"),("PS2"),("PlayStation"),("PSP"),("Xbox Series X/S"),("Xbox One"),("Xbox 360"),("Xbox"),("Nintendo Switch"),
47("Nintendo Wii U"),("Nintendo Wii"),("Nintendo GameCube"),("Nintendo 64"),("SNES"),("NES"),("Nintendo 3DS"),("Nintendo DS"),
48("Game Boy Advance"), ("Game Boy Color"),("Sega Mega Drive"), ("Sega Master System"),("Stadia"),("Android"),("iOS"),("PSVita");
49
50INSERT INTO genres(genre) VALUES("RPG"),("Adventure"),("Action"),("Simulation"),("Shooter"),("Sandbox"),("Hack And Slash"),("Horror"),
51("Visual Novel"),("MOBA"),("Puzzle"),("Platform"),("Fight"),("Sports"),("Music"),("Strategy"),("AR"),("Party Game"),("Battle Royale");
52
53INSERT INTO game (title,studio,release_date,multiplayer)VALUES
54("Octopath Traveler","Square Enix","2018-07-13",0),
55("Rocket League","Psyonix","2015-07-07",1),
56("Kimi ga shine","Nankidai","2017-08-28",0),
57("Minecraft","Mojang","2011,12,20",1),
58("NieR: Automata","Square Enix","2017-02-23",0),
59("Pokémon Go","Niantic","2016-07-06",1);
60
61INSERT 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);
62INSERT INTO gameplatform(idGame,idPlatform)VALUES(1,12),(1,1),(1,25),(2,1),(2,3),(2,9),(2,12),(3,1),(4,1),
63(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);
64
65/*Filter by genre*/
66SELECT
67g.title,gr.genre FROM game g
68JOIN gamegenre gg
69ON gg.idGame = g.id
70JOIN genres gr
71ON gr.id = gg.idGenre
72WHERE g.title = 'Octopath Traveler'
73;
74
75SELECT * FROM game g
76JOIN gamegenre gg
77ON gg.idGame = g.id
78JOIN genres gr
79ON gr.id = gg.idGenre
80WHERE gr.genre = "RPG"
81;
82
83SELECT p.platform, g.title, g.studio,g.release_date, g.multiplayer FROM game g
84JOIN gameplatform gp
85ON gp.idGame = g.id
86JOIN platforms p
87ON p.id = gp.idPlatform
88WHERE p.platform NOT IN ('Pc','Ps4','Xbox One','Nintendo Switch');
89
90/*Filter by platform*/
91SELECT g.title, p.platform FROM game g
92JOIN gameplatform gp
93ON gp.idGame = g.id
94JOIN platforms p
95ON p.id = gp.idPlatform
96WHERE p.platform = "Nintendo Switch"
97
98SELECT g.title, p.platform FROM game g
99JOIN gameplatform gp
100ON gp.idGame = g.id
101JOIN platforms p
102ON p.id = gp.idPlatform
103WHERE p.platform NOT LIKE "PC" AND "Ps4" AND "Xbox One" AND "Nintendo Switch";
104
105SELECT g.title, p.platform FROM game g
106JOIN gameplatform gp
107ON gp.idGame = g.id
108JOIN platforms p
109ON p.id = gp.idPlatform
110WHERE g.title = "Octopath Traveler"