· 4 years ago · Dec 12, 2020, 12:56 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
53
54
55/*Filter by genre*/
56SELECT
57g.title,gr.genre FROM game g
58JOIN gamegenre gg
59ON gg.idGame = g.id
60JOIN genres gr
61ON gr.id = gg.idGenre
62WHERE g.title = 'Octopath Traveler'
63;
64
65SELECT * FROM game g
66JOIN gamegenre gg
67ON gg.idGame = g.id
68JOIN genres gr
69ON gr.id = gg.idGenre
70WHERE gr.genre = "RPG"
71;
72
73SELECT p.platform, g.title, g.studio,g.release_date, g.multiplayer FROM game g
74JOIN gameplatform gp
75ON gp.idGame = g.id
76JOIN platforms p
77ON p.id = gp.idPlatform
78WHERE p.platform NOT IN ('Pc','Ps4','Xbox One','Nintendo Switch');
79
80/*Filter by platform*/
81SELECT g.title, p.platform FROM game g
82JOIN gameplatform gp
83ON gp.idGame = g.id
84JOIN platforms p
85ON p.id = gp.idPlatform
86WHERE p.platform = "Nintendo Switch"
87
88SELECT g.title, p.platform FROM game g
89JOIN gameplatform gp
90ON gp.idGame = g.id
91JOIN platforms p
92ON p.id = gp.idPlatform
93WHERE p.platform NOT LIKE "PC" AND "Ps4" AND "Xbox One" AND "Nintendo Switch";
94
95SELECT g.title, p.platform FROM game g
96JOIN gameplatform gp
97ON gp.idGame = g.id
98JOIN platforms p
99ON p.id = gp.idPlatform
100WHERE g.title = "Octopath Traveler"
101
102INSERT INTO game (title,studio,release_date,multiplayer)VALUES
103("Octopath Traveler","Square Enix","2018-07-13",0)
104("Rocket League","Psyonix","2015-07-07",1),
105("Kimi ga shine","Nankidai","2017-08-28",0),
106("Minecraft","Mojang","2011,12,20",1),
107("NieR: Automata","Square Enix","2017-02-23",0),
108("Pokémon Go","Niantic","2016-07-06",1);
109
110INSERT 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);
111INSERT INTO gameplatform(idGame,idPlatform)VALUES(1,12),(1,1),(1,25),(2,1),(2,3),(2,9),(2,12),(3,1),(4,1),
112(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);
113