Guest User

Untitled

a guest
Apr 23rd, 2019
85
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. mysql> USE wild;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  4.  
  5. Database changed
  6. mysql> SHOW TABLES;
  7. +----------------+
  8. | Tables_in_wild |
  9. +----------------+
  10. | player |
  11. | team |
  12. | wizard |
  13. +----------------+
  14. 3 rows in set (0.00 sec)
  15.  
  16. mysql> SELECT name, COUNT(player.id) AS nbrplayer
  17. -> FROM player JOIN team
  18. -> ON team.id=player.team_id
  19. -> GROUP BY team.name
  20. -> ORDER BY nbrplayer DESC;
  21. +------------+-----------+
  22. | name | nbrplayer |
  23. +------------+-----------+
  24. | Gryffindor | 36 |
  25. | Slytherin | 21 |
  26. | Ravenclaw | 15 |
  27. | Hufflepuff | 12 |
  28. +------------+-----------+
  29. 4 rows in set (0.00 sec)
  30.  
  31. mysql> SELECT name, COUNT(player.id) AS nbrPlayer
  32. -> FROM player
  33. -> JOIN team
  34. -> ON team.id=player.team_id
  35. -> GROUP BY team_id
  36. -> HAVING nbrPlayer >= 14
  37. -> ORDER BY name;
  38. +------------+-----------+
  39. | name | nbrPlayer |
  40. +------------+-----------+
  41. | Gryffindor | 36 |
  42. | Ravenclaw | 15 |
  43. | Slytherin | 21 |
  44. +------------+-----------+
  45. 3 rows in set (0.00 sec)
  46.  
  47. mysql> SELECT firstname, lastname
  48. -> FROM player
  49. -> JOIN team
  50. -> ON team.id=player.team_id
  51. -> JOIN wizard
  52. -> ON wizard.id=player.wizard_id
  53. -> WHERE name="Gryffindor"
  54. -> AND DAYOFWEEK(enrollment_date)=2;
  55. +-----------+------------+
  56. | firstname | lastname |
  57. +-----------+------------+
  58. | Sirius | Black |
  59. | Aberforth | Dumbledore |
  60. | Godric | Gryffindor |
  61. | Alice | Longbottom |
  62. | Augusta | Longbottom |
  63. | George | Weasley |
  64. | Cadogan | |
  65. +-----------+------------+
  66. 7 rows in set (0.00 sec)
  67.  
  68. mysql>
RAW Paste Data