API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 12th, 2015
287
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.49 KB | None | 0 0
raw download clone embed print report
  1. mysql> SELECT * FROM players;
  2. +-----------+---------+----------------------+----------------------+-----------------+------------------------------+-----------------------+---------------------+
  3. | player_id | team_id | player_name | player_jersey_number | player_position | player_email | player_contact_number | player_timestamp |
  4. +-----------+---------+----------------------+----------------------+-----------------+------------------------------+-----------------------+---------------------+
  5. | 1 | 4 | Popoy Alfonso | 2 | | popoyalfonso@gmail.com | 09263453234 | 2015-08-05 00:48:10 |
  6. | 2 | 4 | Karlo Ripas | 10 | | karloripas@yahoo.com | 09212354324 | 2015-08-05 00:50:03 |
  7. | 3 | 4 | VHaughn Von | 32 | | von@outlook.com | 09361234565 | 2015-08-05 00:51:00 |
  8. | 4 | 4 | Lordie Zalbahe | 23 | | lordiezalbahe@gmail.com | 09391222334 | 2015-08-05 00:52:42 |
  9. | 5 | 4 | Jigs Selda | 8 | | jigsselda@gmail.com | 09325566323 | 2015-08-05 00:53:36 |
  10. | 6 | 4 | Rhan Garniel | 3 | | rhangarniel@ymail.com | 09129503400 | 2015-08-05 00:54:20 |
  11. | 7 | 5 | Johnritz Rodriguez | 11 | | johnritz@gmail.com | 09231112346 | 2015-08-05 00:56:02 |
  12. | 8 | 5 | Garret Van Sarmiento | 7 | | garretvansarmiento@gmail.com | 09264565600 | 2015-08-05 00:56:53 |
  13. | 9 | 5 | Lester Selda Lineses | 12 | | lesterlineses@yahoo.com | 09068746354 | 2015-08-05 00:57:47 |
  14. | 10 | 5 | Laurence Lineses | 44 | | laurencelineses@yahoo.com | 09847354672 | 2015-08-05 00:59:33 |
  15. | 11 | 5 | Xandrix Buendia | 1 | | xandrixbuendia@yahoo.com | 09234665590 | 2015-08-05 01:00:12 |
  16. | 12 | 5 | Betoyskie Limpiada | 45 | | betoyskie@outlook.com | 09213456667 | 2015-08-05 01:01:15 |
  17. +-----------+---------+----------------------+----------------------+-----------------+------------------------------+-----------------------+---------------------+
  18.  
  19. mysql> SELECT * FROM teams;
  20. +---------+-----------+----------------------+---------------------+
  21. | team_id | season_id | team_name | team_timestamp |
  22. +---------+-----------+----------------------+---------------------+
  23. | 4 | 1 | Quiapo A | 2015-08-05 00:30:13 |
  24. | 5 | 1 | Quiapo B | 2015-08-05 00:30:25 |
  25. | 6 | 1 | Balik-Balik Warriors | 2015-08-05 00:31:13 |
  26. | 7 | 1 | Adamson Falcons | 2015-08-05 00:31:42 |
  27. | 8 | 1 | Pasay Flooders | 2015-08-05 00:32:04 |
  28. | 9 | 1 | Marina Dragons | 2015-08-05 00:32:22 |
  29. | 10 | 1 | MDC Archers | 2015-08-05 00:33:12 |
  30. | 11 | 2 | Quiapo A | 2015-08-05 00:34:25 |
  31. | 12 | 2 | Quiapo B | 2015-08-05 00:34:38 |
  32. | 13 | 2 | Marikina Eagels | 2015-08-05 00:35:11 |
  33. | 14 | 2 | TIP Steallers | 2015-08-05 00:35:32 |
  34. | 15 | 2 | Gasan Blue Eagles | 2015-08-05 00:36:12 |
  35. +---------+-----------+----------------------+---------------------+
  36.  
  37. mysql> SELECT * FROM seasons;
  38. +-----------+-------------+----------------------+---------------------+
  39. | season_id | season_name | season_event_name | season_timestamp |
  40. +-----------+-------------+----------------------+---------------------+
  41. | 1 | Season 1 | Summer Games | 2015-08-05 00:23:15 |
  42. | 2 | Season 2 | Aniversary Sportfest | 2015-08-05 00:25:10 |
  43. +-----------+-------------+----------------------+---------------------+
  44.  
  45. SELECT
  46. teams.team_name,
  47. (
  48. SELECT COUNT(*)
  49. FROM teams
  50. INNER JOIN players
  51. ON teams.team_id = players.team_id
  52. ) as num_of_players,
  53. teams.team_timestamp
  54. FROM teams
  55. INNER JOIN seasons
  56. ON seasons.season_id = teams.season_id
  57. GROUP BY teams.team_name;
  58.  
  59. +----------------------+----------------+---------------------+
  60. | team_name | num_of_players | team_timestamp |
  61. +----------------------+----------------+---------------------+
  62. | Adamson Falcons | 12 | 2015-08-05 00:31:42 |
  63. | Balik-Balik Warriors | 12 | 2015-08-05 00:31:13 |
  64. | Gasan Blue Eagles | 12 | 2015-08-05 00:36:12 |
  65. | Marikina Eagels | 12 | 2015-08-05 00:35:11 |
  66. | Marina Dragons | 12 | 2015-08-05 00:32:22 |
  67. | MDC Archers | 12 | 2015-08-05 00:33:12 |
  68. | Pasay Flooders | 12 | 2015-08-05 00:32:04 |
  69. | Quiapo A | 12 | 2015-08-05 00:30:13 |
  70. | Quiapo B | 12 | 2015-08-05 00:30:25 |
  71. | TIP Steallers | 12 | 2015-08-05 00:35:32 |
  72. +----------------------+----------------+---------------------+
  73.  
  74. +----------------------+----------------+---------------------+
  75. | team_name | num_of_players | team_timestamp |
  76. +----------------------+----------------+---------------------+
  77. | Adamson Falcons | 0 | 2015-08-05 00:31:42 |
  78. | Balik-Balik Warriors | 0 | 2015-08-05 00:31:13 |
  79. | Gasan Blue Eagles | 0 | 2015-08-05 00:36:12 |
  80. | Marikina Eagels | 0 | 2015-08-05 00:35:11 |
  81. | Marina Dragons | 0 | 2015-08-05 00:32:22 |
  82. | MDC Archers | 0 | 2015-08-05 00:33:12 |
  83. | Pasay Flooders | 0 | 2015-08-05 00:32:04 |
  84. | Quiapo A | 6 | 2015-08-05 00:30:13 |
  85. | Quiapo B | 6 | 2015-08-05 00:30:25 |
  86. | TIP Steallers | 0 | 2015-08-05 00:35:32 |
  87. +----------------------+----------------+---------------------+
  88.  
  89. SELECT
  90. teams.team_name,
  91. COUNT(players.player_id) as num_of_players,
  92. teams.team_timestamp
  93. FROM test.teams
  94. JOIN test.players ON (players.team_id=teams.team_id)
  95. JOIN seasons ON (seasons.season_id = teams.season_id)
  96. GROUP BY teams.team_name;
  97.  
  98. CREATE TABLE `player` (
  99. `player_id` int(11) DEFAULT NULL,
  100. `team_id` int(11) DEFAULT NULL,
  101. `player_name` varchar(25) DEFAULT NULL
  102. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  103. 1 row in set (0.00 sec)
  104.  
  105. CREATE TABLE `team` (
  106. `team_id` int(11) DEFAULT NULL,
  107. `season_id` int(11) DEFAULT NULL,
  108. `team_name` varchar(25) DEFAULT NULL
  109. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  110. 1 row in set (0.00 sec)
  111.  
  112. SELECT t1.team_id, t1.team_name,
  113. IFNULL(t2.num_players, 0) AS strength
  114. FROM team t1
  115. LEFT OUTER JOIN
  116. (SELECT team_id, COUNT(team_id) AS num_players
  117. FROM player
  118. GROUP BY team_id
  119. ) t2
  120. ON t1.team_id = t2.team_id
  121. ORDER BY strength DESC, team_name ASC;
  122.  
  123. +---------+----------------------+----------+
  124. | team_id | team_name | strength |
  125. +---------+----------------------+----------+
  126. | 4 | Quiapo A | 6 |
  127. | 5 | Quiapo B | 6 |
  128. | 7 | Adamson Falcons | 0 |
  129. | 6 | Balik-Balik Warriors | 0 |
  130. | 15 | Gasan Blue Eagles | 0 |
  131. | 13 | Marikina Eagels | 0 |
  132. | 9 | Marina Dragons | 0 |
  133. | 10 | MDC Archers | 0 |
  134. | 8 | Pasay Flooders | 0 |
  135. | 11 | Quiapo A | 0 |
  136. | 12 | Quiapo B | 0 |
  137. | 14 | TIP Steallers | 0 |
  138. +---------+----------------------+----------+
  139. 12 rows in set (0.00 sec)
  140.  
  141. mysql> SELECT * FROM player;
  142. +-----------+---------+----------------------+
  143. | player_id | team_id | player_name |
  144. +-----------+---------+----------------------+
  145. | 1 | 4 | Popoy Alfonso |
  146. | 2 | 4 | Karlo Ripas |
  147. | 3 | 4 | VHaughn Von |
  148. | 4 | 4 | Lordie Zalbahe |
  149. | 5 | 4 | Jigs Selda |
  150. | 6 | 4 | Rhan Garniel |
  151. | 7 | 5 | Johnritz Rodriguez |
  152. | 8 | 5 | Garret Van Sarmiento |
  153. | 9 | 5 | Lester Selda Lineses |
  154. | 10 | 5 | Laurence Lineses |
  155. | 11 | 5 | Xandrix Buendia |
  156. | 12 | 5 | Betoyskie Limpiada |
  157. +-----------+---------+----------------------+
  158. 12 rows in set (0.00 sec)
  159.  
  160. mysql> SELECT * FROM team;
  161. +---------+-----------+----------------------+
  162. | team_id | season_id | team_name |
  163. +---------+-----------+----------------------+
  164. | 4 | 1 | Quiapo A |
  165. | 5 | 1 | Quiapo B |
  166. | 6 | 1 | Balik-Balik Warriors |
  167. | 7 | 1 | Adamson Falcons |
  168. | 8 | 1 | Pasay Flooders |
  169. | 9 | 1 | Marina Dragons |
  170. | 10 | 1 | MDC Archers |
  171. | 11 | 2 | Quiapo A |
  172. | 12 | 2 | Quiapo B |
  173. | 13 | 2 | Marikina Eagels |
  174. | 14 | 2 | TIP Steallers |
  175. | 15 | 2 | Gasan Blue Eagles |
  176. +---------+-----------+----------------------+
  177. 12 rows in set (0.00 sec)
  178.  
  179. mysql>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!