SHARE
TWEET

Untitled

a guest Apr 20th, 2019 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. mysql> SELECT count(wizard.id), team.name FROM wizard
  2.     -> INNER JOIN player ON wizard.id=player.wizard_id
  3.     -> INNER JOIN team ON player.team_id=team.id
  4.     -> GROUP BY team.name
  5.     -> GROUP BY count(wizard.id) DESC;
  6. +------------------+------------+
  7. | count(wizard.id) | name       |
  8. +------------------+------------+
  9. |               36 | Gryffindor |
  10. |               21 | Slytherin  |
  11. |               15 | Ravenclaw  |
  12. |               12 | Hufflepuff |
  13. +------------------+------------+
  14. 4 rows in set (0.00 sec)
  15.  
  16. mysql> SELECT COUNT(wizard.id), team.name FROM wizard
  17.     -> INNER JOIN player ON wizard.id=player.wizard_id
  18.     -> INNER JOIN team ON player.team_id=team.id
  19.     -> GROUP BY team.name
  20.     -> HAVING COUNT(wizard.id) >13
  21.     -> ORDER BY name ASC;
  22. +------------------+------------+
  23. | count(wizard.id) | name       |
  24. +------------------+------------+
  25. |               36 | Gryffindor |
  26. |               15 | Ravenclaw  |
  27. |               21 | Slytherin  |
  28. +------------------+------------+
  29. 3 rows in set (0.00 sec)
  30.  
  31. mysql> SELECT DAYOFWEEK(enrollment_date), CONCAT(firstname, ' ', lastname) AS fullname from wizard
  32.     -> JOIN player ON wizard.id=player.wizard_id
  33.     -> JOIN team ON player.team_id=team.id
  34.     -> WHERE DAYOFWEEK(enrollment_date)=2 AND name="Gryffindor"
  35.     -> ORDER BY enrollment_date;
  36. +----------------------------+----------------------+
  37. | DAYOFWEEK(enrollment_date) | fullname             |
  38. +----------------------------+----------------------+
  39. |                          2 | George Weasley       |
  40. |                          2 | Alice Longbottom     |
  41. |                          2 | Cadogan              |
  42. |                          2 | Godric Gryffindor    |
  43. |                          2 | Sirius Black         |
  44. |                          2 | Aberforth Dumbledore |
  45. |                          2 | Augusta Longbottom   |
  46. +----------------------------+----------------------+
  47. 7 rows in set (0.00 sec)
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