SHARE
TWEET

Untitled

a guest Apr 23rd, 2019 72 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
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