[sql] Test

Viewer

  1. CREATE DATABASE `Test`;
  2. CREATE TABLE `Players` (
  3.   `ID_Player` INT(11) NOT NULL,
  4.   `name` INT(11) DEFAULT NULL,
  5.   `initials` VARCHAR(1) DEFAULT NULL,
  6.   `year_birth` INT(4) DEFAULT NULL,
  7.   `sex` VARCHAR(1) DEFAULT NULL,
  8.   `year_joined` INT(4) DEFAULT NULL,
  9.   `street` VARCHAR(30) DEFAULT NULL,
  10.   `house_num` VARCHAR(4) DEFAULT NULL,
  11.   `postcode` VARCHAR(6) DEFAULT NULL,
  12.   `town` VARCHAR(30) DEFAULT NULL,
  13.   `phone` VARCHAR(12) DEFAULT NULL,
  14.   `id_league` INT(4) DEFAULT NULL,
  15.   PRIMARY KEY (`ID_Player`)
  16. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
  17.  
  18. INSERT INTO `Players` (`ID_Player`, `name`, `initials`,`year_birth`,`sex`,`year_joined`,`street`,`house_num`,`postcode`,`town`,`phone`,`id_league`) VALUES 
  19. (2,"Sturaro","R",1978,"M",2005,"Corso Italia","43","3575NH","Milano","070-237893",2411),
  20. (6,"Pjanix","R",1978,"M",1994,"Via Torino","80","1234KK","Milano","070-237893",8467),
  21. (8,"Alex Sandro","B",1992,"F",2010,"Corso Libertà","4","6584WO","Roma","070-237893",2983),
  22. (27,"Cuadrado","DD",1994,"F",2013,"Via Del Corso","804","8457DK","Como","070-237893",2513),
  23. (44,"Buffon","E",1993,"M",2010,"Via Del Corso","804","8457DK","Roma","070-237893",1124),
  24. (18,"Bonucci","M",2001,"F",2015,"Via Del Corso","804","8457DK","Milano","070-237893",6409),
  25. (83,"Higuain","PK",1986,"M",2012,"Via Del Corso","804","8457DK","Milano","070-237893",1608),
  26. (100,"Pianic","P",1993,"M",2009,"Via Del Corso","804","8457DK","Milano","070-237893",6524),
  27. (104,"Matudi","D",2000,"F",2014,"Via Del Corso","804","8457DK","Como","070-237893",7060),
  28. (112,"Barzagli","IP",1993,"F",2014,"Via Del Corso","804","8457DK","Napoli","070-237893",1319);
  29.  
  30. INSERT INTO `Players` (`ID_Player`, `name`, `initials`,`year_birth`,`sex`,`year_joined`,`street`,`house_num`,`postcode`,`town`,`phone`) VALUES 
  31. (7,"Howedes","GWS",1993,"M",2011,"Via Tevere","39","9758VB","Milano","070-237893"),
  32. (28,"Cuadrado","C",1993,"F",2013,"Via Statuto","10","1294QK","Bergamo","070-237893"),
  33. (39,"Benatia","D",1986,"M",2010,"Piazza Indipendenza","78","9629CD","Milano","070-237893"),
  34. (95,"Marchisio","P",1964,"M",2002,"Corso Italia","33/a","5746OP","Torino","070-237893");
  35.  
  36.  
  37. -- --------------------------------------------------------
  38. --
  39. -- Table structure for table `Teams`
  40. --
  41.  
  42. CREATE TABLE `Teams` (
  43.   `ID_Team` INT(11) NOT NULL,
  44.   FOREIGN KEY (`ID_Player`) REFERENCES Players(`ID_Player`),
  45.   `division` VARCHAR(30) DEFAULT NULL,
  46.   PRIMARY KEY (`ID_Team`),
  47. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
  48.  
  49. INSERT INTO `Teams` (`ID_Team`, `ID_player`, `division`) VALUES 
  50. (1,6,"First"),
  51. (2,27,"Second");
  52.  
  53. -- --------------------------------------------------------
  54. --
  55. -- Table structure for table `Games`
  56. --
  57.  
  58. CREATE TABLE `Games` (
  59.   FOREIGN KEY (`ID_Team`) REFERENCES Teams(`ID_Team`) ,
  60.   FOREIGN KEY (`ID_Player`) REFERENCES Players(`ID_Player`),
  61.   `won` INT(11) DEFAULT NULL,
  62.   `lost` INT(11) DEFAULT NULL
  63. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
  64.  
  65.  
  66. INSERT INTO `Games` (`ID_Team`, `ID_player`, `won`, `lost`) VALUES
  67. (1, 2, 4, 8),
  68. (1, 6, 9, 1),
  69. (1, 8, 0, 1),
  70. (1, 44, 7, 5),
  71. (1, 18, 5, 0),
  72. (1, 83, 3, 3),
  73. (2, 8, 4, 4),
  74. (2, 27, 11, 2),
  75. (2, 104, 8, 4),
  76. (2, 112, 4, 8);
  77.  
  78. -- --------------------------------------------------------
  79.  
  80. --
  81. -- Table structure for table `Penalties`
  82. --
  83.  
  84. CREATE TABLE `Penalties` (
  85.   `ID_Payment` INT(11) NOT NULL AUTO_INCREMENT,
  86.   FOREIGN KEY (`ID_Player`) REFERENCES Players(`ID_Player`) ,
  87.   `pdate` VARCHAR(10) DEFAULT NULL,
  88.   `amount` INT(11) DEFAULT NULL,
  89.   PRIMARY KEY (`ID_Payment`)
  90. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
  91.  
  92. INSERT INTO `Penalties` (`ID_Payment`, `ID_player`, `pdate`, `amount`) VALUES 
  93. (1,6,"08/12/2010",1000),
  94. (2,44,"05/05/2011",750),
  95. (3,27,"10/09/2013",1000),
  96. (4,104,"08/12/2014",500),
  97. (5,44,"08/12/2010",250),
  98. (6,8,"08/12/2010",250),
  99. (7,44,"30/12/2012",300),
  100. (8,27,"12/11/2014",750);

Editor

You can edit this paste and save as new:


File Description
  • Test
  • Paste Code
  • 01 Mar-2021
  • 3.77 Kb
You can Share it: