Gabri_RDiaz

DIDA2

Dec 13th, 2020
1,010
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. INSERT 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.  
  61. 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);
  62. INSERT 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*/
  66. SELECT
  67. g.title,gr.genre FROM game g
  68. JOIN gamegenre gg
  69. ON gg.idGame = g.id
  70. JOIN genres gr
  71. ON gr.id = gg.idGenre
  72. WHERE g.title = 'Octopath Traveler'
  73. ;
  74.  
  75. SELECT * FROM game g
  76. JOIN gamegenre gg
  77. ON gg.idGame = g.id
  78. JOIN genres gr
  79. ON gr.id = gg.idGenre
  80. WHERE gr.genre = "RPG"
  81. ;
  82.  
  83. SELECT p.platform, g.title, g.studio,g.release_date, g.multiplayer FROM game g
  84. JOIN gameplatform gp
  85. ON gp.idGame = g.id
  86. JOIN platforms p
  87. ON p.id = gp.idPlatform
  88. WHERE p.platform NOT IN ('Pc','Ps4','Xbox One','Nintendo Switch');
  89.  
  90. /*Filter by platform*/
  91. SELECT g.title, p.platform FROM game g
  92. JOIN gameplatform gp
  93. ON gp.idGame = g.id
  94. JOIN platforms p
  95. ON p.id = gp.idPlatform
  96. WHERE p.platform = "Nintendo Switch"
  97.  
  98. SELECT g.title, p.platform FROM game g
  99. JOIN gameplatform gp
  100. ON gp.idGame = g.id
  101. JOIN platforms p
  102. ON p.id = gp.idPlatform
  103. WHERE p.platform NOT LIKE "PC" AND "Ps4" AND "Xbox One" AND "Nintendo Switch";
  104.  
  105. SELECT g.title, p.platform FROM game g
  106. JOIN gameplatform gp
  107. ON gp.idGame = g.id
  108. JOIN platforms p
  109. ON p.id = gp.idPlatform
  110. WHERE g.title = "Octopath Traveler"
RAW Paste Data