Gabri_RDiaz

ScriptDida

Dec 12th, 2020
2,866
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP DATABASE IF EXISTS games;
  2. CREATE DATABASE IF NOT EXISTS games;
  3. USE games;
  4. CREATE 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.  
  13. CREATE TABLE platforms (
  14.     id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  15.     platform VARCHAR(50) NOT NULL
  16. );
  17.  
  18. CREATE TABLE genres (
  19.     id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  20.     genre VARCHAR(50) NOT NULL
  21. );
  22.  
  23. CREATE 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.  
  29. ALTER TABLE gameplatform
  30.     ADD FOREIGN KEY(idPlatform) REFERENCES platforms(id);
  31. ALTER TABLE gameplatform
  32.     ADD FOREIGN KEY(idGame) REFERENCES game(id);
  33.  
  34. CREATE 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.  
  40. ALTER TABLE gamegenre
  41.     ADD FOREIGN KEY(idGenre) REFERENCES genres(id);
  42. ALTER TABLE gamegenre
  43.     ADD FOREIGN KEY(idGame) REFERENCES game(id);
  44.  
  45.  
  46. INSERT 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.  
  50. INSERT 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*/
  56. SELECT
  57. g.title,gr.genre FROM game g
  58. JOIN gamegenre gg
  59. ON gg.idGame = g.id
  60. JOIN genres gr
  61. ON gr.id = gg.idGenre
  62. WHERE g.title = 'Octopath Traveler'
  63. ;
  64.  
  65. SELECT * FROM game g
  66. JOIN gamegenre gg
  67. ON gg.idGame = g.id
  68. JOIN genres gr
  69. ON gr.id = gg.idGenre
  70. WHERE gr.genre = "RPG"
  71. ;
  72.  
  73. SELECT p.platform, g.title, g.studio,g.release_date, g.multiplayer FROM game g
  74. JOIN gameplatform gp
  75. ON gp.idGame = g.id
  76. JOIN platforms p
  77. ON p.id = gp.idPlatform
  78. WHERE p.platform NOT IN ('Pc','Ps4','Xbox One','Nintendo Switch');
  79.  
  80. /*Filter by platform*/
  81. SELECT g.title, p.platform FROM game g
  82. JOIN gameplatform gp
  83. ON gp.idGame = g.id
  84. JOIN platforms p
  85. ON p.id = gp.idPlatform
  86. WHERE p.platform = "Nintendo Switch"
  87.  
  88. SELECT g.title, p.platform FROM game g
  89. JOIN gameplatform gp
  90. ON gp.idGame = g.id
  91. JOIN platforms p
  92. ON p.id = gp.idPlatform
  93. WHERE p.platform NOT LIKE "PC" AND "Ps4" AND "Xbox One" AND "Nintendo Switch";
  94.  
  95. SELECT g.title, p.platform FROM game g
  96. JOIN gameplatform gp
  97. ON gp.idGame = g.id
  98. JOIN platforms p
  99. ON p.id = gp.idPlatform
  100. WHERE g.title = "Octopath Traveler"
  101.  
  102. INSERT 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.  
  110. 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);
  111. INSERT 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.  
RAW Paste Data