G2A Many GEOs
SHARE
TWEET

Untitled

a guest Mar 29th, 2020 116 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS team_players;
  2. DROP TABLE IF EXISTS player;
  3. DROP TABLE IF EXISTS club;
  4. DROP TABLE IF EXISTS league;
  5. DROP TABLE IF EXISTS user_team;
  6. DROP TABLE IF EXISTS user;
  7.  
  8. CREATE TABLE `league`
  9. (
  10.   `league_id` int
  11. (11) NOT NULL,
  12.   `league_name` varchar
  13. (45) DEFAULT NULL,
  14.   `country_name` varchar
  15. (45) DEFAULT NULL,
  16.   `no_of_clubs` int
  17. (11) DEFAULT NULL,
  18.   PRIMARY KEY
  19. (`league_id`)
  20. ) ENGINE=InnoDB;
  21.  
  22.  
  23. CREATE TABLE `user`
  24. (
  25.   `id` int
  26. (11) NOT NULL,
  27.   `full_name` varchar
  28. (45) NOT NULL,
  29.   `email` varchar
  30. (45) NOT NULL,
  31.   `password` varchar
  32. (45) NOT NULL,
  33.   PRIMARY KEY
  34. (`id`)
  35. ) ENGINE=InnoDB;
  36.  
  37.  
  38.  
  39. CREATE TABLE `club`
  40. (
  41.   `club_id` int
  42. (11) NOT NULL,
  43.   `club_name` varchar
  44. (45) DEFAULT NULL,
  45.   `club_league_id` int
  46. (11) NOT NULL,
  47.   PRIMARY KEY
  48. (`club_id`),
  49.   KEY `club_league_id_idx`
  50. (`club_league_id`),
  51.   CONSTRAINT `club_league_id` FOREIGN KEY
  52. (`club_league_id`) REFERENCES `league`
  53. (`league_id`)
  54. ) ENGINE=InnoDB ;
  55.  
  56.  
  57. CREATE TABLE `player`
  58. (
  59.   `player_id` int
  60. (11) NOT NULL,
  61.   `player_club_id` int
  62. (11) DEFAULT NULL,
  63.   `player_name` varchar
  64. (45) DEFAULT NULL,
  65.   `player_rating` int
  66. (11) DEFAULT NULL,
  67.   `player_position` varchar
  68. (45) DEFAULT NULL,
  69.   PRIMARY KEY
  70. (`player_id`),
  71.   KEY `player_club_id_idx`
  72. (`player_club_id`),
  73.   CONSTRAINT `player_club_id` FOREIGN KEY
  74. (`player_club_id`) REFERENCES `club`
  75. (`club_id`)
  76. ) ENGINE=InnoDB ;
  77.  
  78.  
  79. CREATE TABLE `user_team`
  80. (
  81.   `user_team_id` int
  82. (11) NOT NULL,
  83.   `user_id` int
  84. (11) DEFAULT NULL,
  85.   PRIMARY KEY
  86. (`user_team_id`),
  87.   KEY `user_id_idx`
  88. (`user_id`),
  89.   CONSTRAINT `user_id` FOREIGN KEY
  90. (`user_id`) REFERENCES `user`
  91. (`id`)
  92. ) ENGINE=InnoDB;
  93.  
  94.  
  95. CREATE TABLE `team_players`
  96. (
  97.   `team_player_id` int
  98. (11) NOT NULL,
  99.   `user_team_id` int
  100. (11) DEFAULT NULL,
  101.   `player_id` int
  102. (11) DEFAULT NULL,
  103.   PRIMARY KEY
  104. (`team_player_id`),
  105.   KEY `team_player_id_idx`
  106. (`user_team_id`),
  107.   KEY `player_id_idx`
  108. (`player_id`),
  109.   CONSTRAINT `player_id` FOREIGN KEY
  110. (`player_id`) REFERENCES `player`
  111. (`player_id`),
  112.   CONSTRAINT `team_player_id` FOREIGN KEY
  113. (`user_team_id`) REFERENCES `user_team`
  114. (`user_team_id`)
  115. ) ENGINE=InnoDB;
  116.  
  117. INSERT INTO `user`
  118.     (`id`, `full_name`, `email`, `password`) VALUES
  119. (1,'Sushant Baskota','sbaskota@go.olemiss.edu','thikxahai'),
  120. (2,'Ayush','aupadhyay@go.olemiss.edu','thikxaina'),
  121. (3, 'Thomas Shelby', 'tommy@shelbyltd.com', 'peakyblinders');
  122.  
  123. INSERT INTO `league` (`league_id`,`league_name`, `country_name`, `no_of_clubs`) VALUES
  124. (1,'Barclays','England',20),
  125. (2,'Laliga','Spain',20),
  126. (3,'Serie A','Italy',20),
  127. (4,'Bundesliga','Germany',18),
  128. (5,'Ligue 1','France',18);
  129.  
  130. INSERT INTO `club` (`club_id`,`club_name`, `club_league_id`) VALUES
  131. (1,'Manchester City',1),
  132. (2,'Liverpool',1),
  133. (3,'Arsenal',1),
  134. (4,'Barcelona',2),
  135. (5,'Real Madrid',2),
  136. (6,'PSG',5),
  137. (7,'Dortmund',4),
  138. (8,'Monaco',5),
  139. (9,'Juventus',3),
  140. (10,'Atletico Madrid',2),
  141. (11,'Bayern Munich',4);
  142.  
  143. INSERT INTO `player` (`player_id`,`player_club_id`, `player_name`, `player_rating`, `player_position`) VALUES
  144. (1,4,'Lionel Messi',5,'RW'),
  145. (2,4,'Suarez',4,'ST'),
  146. (3,4,'Frenkie Dejong',5,'CM'),
  147. (4,1,'Sergio Aguero',4,'ST'),
  148. (5,6,'Neymar Jr.',5,'LW'),
  149. (6,9,'Cristiano Ronaldo',5,'CF'),
  150. (7,6,'Angel Di Maria',4,'RW'),
  151. (8,1,'Mahrez',4,'RW'),
  152. (9,9,'Paulo Dybala', 5, 'RM');
  153.  
  154. INSERT INTO `user_team` (`user_team_id`,`user_id`) VALUES
  155. (1,1),
  156. (2,2),
  157. (3,3);
  158.  
  159. INSERT INTO `team_players` (`team_player_id`,`user_team_id`, `player_id`) VALUES
  160. (1,1,1),
  161. (2,1,2),
  162. (3,1,3),
  163. (4,1,4),
  164. (5,2,4),
  165. (6,2,2),
  166. (7,2,1),
  167. (8,2,4),
  168. (9,3,9),
  169. (10,3,8),
  170. (11,3,7),
  171. (12,3,6),
  172. (13,3,5);
  173.  
  174. SELECT full_name , group_concat(player_name) AS NumberOfPlayers
  175. FROM user natural join user_team natural join team_players natural join player  
  176. where   user.id = user_team.user_id and user_team.user_team_id= team_players.user_team_id
  177. Group by user.id;
  178.  
  179. SELECT league_name, COUNT(distinct player_id)
  180. from player natural join club natural join league  
  181. where player_club_id = club_id and club_league_id = league_id
  182. group by league_name;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top