Skip to content

Instantly share code, notes, and snippets.

@Lio-code

Lio-code/gist:6b04f322bde2d993c2678b5056dd524e

Created June 8, 2020 10:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Lio-code/6b04f322bde2d993c2678b5056dd524e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Lio-code/6b04f322bde2d993c2678b5056dd524e to your computer and use it in GitHub Desktop.
Download ZIP
Lionel 06 - SQL avancé
Raw
gistfile1.txt
mysql> SELECT * FROM player p
-> JOIN team t
-> ON t.id=p.team_id;
+------+-----------+---------+--------+-----------------+----+------------+
| id | wizard_id | team_id | role | enrollment_date | id | name |
+------+-----------+---------+--------+-----------------+----+------------+
| 1 | 1 | 4 | beater | 1995-10-09 | 4 | Hufflepuff |
| 2 | 2 | 1 | chaser | 1995-08-17 | 1 | Gryffindor |
| 3 | 3 | 1 | seeker | 1994-12-03 | 1 | Gryffindor |
| 4 | 4 | 3 | chaser | 1995-03-24 | 3 | Slytherin |
| 5 | 5 | 3 | keeper | 1997-07-16 | 3 | Slytherin |
| 6 | 6 | 1 | beater | 1994-01-10 | 1 | Gryffindor |
| 7 | 7 | 4 | chaser | 1999-01-21 | 4 | Hufflepuff |
| 8 | 8 | 4 | keeper | 1991-10-20 | 4 | Hufflepuff |
| 10 | 10 | 1 | beater | 1991-08-03 | 1 | Gryffindor |
| 11 | 11 | 3 | beater | 1996-10-04 | 3 | Slytherin |
| 12 | 12 | 2 | chaser | 1992-01-27 | 2 | Ravenclaw |
| 13 | 13 | 2 | beater | 1991-01-11 | 2 | Ravenclaw |
| 14 | 14 | 2 | seeker | 1995-08-17 | 2 | Ravenclaw |
| 16 | 16 | 3 | beater | 1992-11-27 | 3 | Slytherin |
| 17 | 17 | 1 | seeker | 1993-07-07 | 1 | Gryffindor |
| 18 | 18 | 1 | keeper | 1991-05-01 | 1 | Gryffindor |
| 19 | 19 | 4 | keeper | 1997-11-02 | 4 | Hufflepuff |
| 20 | 20 | 1 | keeper | 1995-04-24 | 1 | Gryffindor |
| 21 | 21 | 1 | chaser | 1991-03-12 | 1 | Gryffindor |
| 22 | 22 | 2 | chaser | 1990-07-05 | 2 | Ravenclaw |
| 23 | 23 | 4 | beater | 1995-01-06 | 4 | Hufflepuff |
| 24 | 24 | 1 | beater | 1997-02-08 | 1 | Gryffindor |
| 25 | 25 | 3 | beater | 1996-12-16 | 3 | Slytherin |
| 26 | 26 | 2 | chaser | 1997-02-07 | 2 | Ravenclaw |
| 27 | 27 | 2 | chaser | 1999-07-31 | 2 | Ravenclaw |
| 28 | 28 | 3 | seeker | 1994-05-13 | 3 | Slytherin |
| 29 | 29 | 1 | chaser | 1997-08-14 | 1 | Gryffindor |
| 30 | 30 | 1 | seeker | 1993-08-30 | 1 | Gryffindor |
| 31 | 31 | 1 | beater | 1994-11-16 | 1 | Gryffindor |
| 32 | 32 | 4 | seeker | 1992-08-14 | 4 | Hufflepuff |
| 33 | 33 | 1 | keeper | 1995-12-02 | 1 | Gryffindor |
| 34 | 34 | 1 | chaser | 1996-01-31 | 1 | Gryffindor |
| 35 | 35 | 3 | chaser | 1992-03-21 | 3 | Slytherin |
| 36 | 36 | 3 | seeker | 1997-10-30 | 3 | Slytherin |
| 37 | 37 | 3 | chaser | 1991-04-27 | 3 | Slytherin |
| 38 | 38 | 2 | chaser | 1998-04-05 | 2 | Ravenclaw |
| 39 | 39 | 1 | beater | 1992-02-17 | 1 | Gryffindor |
| 40 | 40 | 1 | chaser | 1995-10-15 | 1 | Gryffindor |
| 41 | 41 | 1 | chaser | 1999-10-25 | 1 | Gryffindor |
| 42 | 42 | 1 | chaser | 1998-05-06 | 1 | Gryffindor |
| 43 | 43 | 2 | chaser | 1998-03-01 | 2 | Ravenclaw |
| 44 | 44 | 2 | chaser | 1991-03-11 | 2 | Ravenclaw |
| 46 | 46 | 3 | chaser | 1993-11-02 | 3 | Slytherin |
| 47 | 47 | 3 | chaser | 1992-03-12 | 3 | Slytherin |
| 48 | 48 | 3 | seeker | 1993-03-17 | 3 | Slytherin |
| 49 | 49 | 1 | beater | 1992-07-14 | 1 | Gryffindor |
| 50 | 50 | 3 | chaser | 1996-12-02 | 3 | Slytherin |
| 51 | 51 | 2 | chaser | 1995-06-25 | 2 | Ravenclaw |
| 52 | 52 | 3 | beater | 1991-12-14 | 3 | Slytherin |
| 55 | 55 | 1 | chaser | 1991-05-14 | 1 | Gryffindor |
| 56 | 56 | 1 | beater | 1997-03-05 | 1 | Gryffindor |
| 57 | 57 | 1 | beater | 1996-12-07 | 1 | Gryffindor |
| 58 | 58 | 1 | chaser | 1999-02-23 | 1 | Gryffindor |
| 59 | 59 | 2 | beater | 1995-09-23 | 2 | Ravenclaw |
| 60 | 60 | 2 | beater | 1992-04-12 | 2 | Ravenclaw |
| 61 | 61 | 2 | seeker | 1992-10-09 | 2 | Ravenclaw |
| 62 | 62 | 3 | chaser | 1990-02-27 | 3 | Slytherin |
| 64 | 64 | 4 | chaser | 1999-01-12 | 4 | Hufflepuff |
| 66 | 66 | 3 | seeker | 1991-02-23 | 3 | Slytherin |
| 67 | 67 | 4 | beater | 1996-07-18 | 4 | Hufflepuff |
| 68 | 68 | 4 | keeper | 1993-10-01 | 4 | Hufflepuff |
| 69 | 69 | 3 | beater | 1997-03-06 | 3 | Slytherin |
| 70 | 70 | 1 | chaser | 1995-11-08 | 1 | Gryffindor |
| 71 | 71 | 4 | beater | 1998-06-12 | 4 | Hufflepuff |
| 72 | 72 | 1 | beater | 1997-11-23 | 1 | Gryffindor |
| 73 | 73 | 3 | chaser | 1994-01-28 | 3 | Slytherin |
| 74 | 74 | 4 | beater | 1999-11-25 | 4 | Hufflepuff |
| 75 | 75 | 2 | seeker | 1991-12-28 | 2 | Ravenclaw |
| 76 | 76 | 3 | seeker | 1993-10-23 | 3 | Slytherin |
| 77 | 77 | 1 | seeker | 1990-07-31 | 1 | Gryffindor |
| 78 | 78 | 1 | beater | 1992-01-01 | 1 | Gryffindor |
| 79 | 79 | 1 | seeker | 1991-04-27 | 1 | Gryffindor |
| 81 | 81 | 1 | seeker | 1998-03-29 | 1 | Gryffindor |
| 82 | 82 | 1 | chaser | 1991-08-26 | 1 | Gryffindor |
| 83 | 83 | 1 | keeper | 1992-04-17 | 1 | Gryffindor |
| 85 | 85 | 1 | beater | 1990-09-05 | 1 | Gryffindor |
| 86 | 86 | 1 | seeker | 1997-06-22 | 1 | Gryffindor |
| 87 | 87 | 1 | chaser | 1999-04-08 | 1 | Gryffindor |
| 88 | 88 | 3 | beater | 1991-07-08 | 3 | Slytherin |
| 89 | 89 | 3 | chaser | 1996-09-25 | 3 | Slytherin |
| 90 | 90 | 1 | keeper | 1993-01-04 | 1 | Gryffindor |
| 91 | 91 | 4 | beater | 1993-11-04 | 4 | Hufflepuff |
| 92 | 92 | 2 | beater | 1997-12-14 | 2 | Ravenclaw |
| 93 | 93 | 1 | keeper | 1992-05-27 | 1 | Gryffindor |
+------+-----------+---------+--------+-----------------+----+------------+
84 rows in set (0.00 sec)
mysql> SELECT t.name, COUNT(*) AS nb_players
-> FROM player p
-> JOIN team t ON t.id=p.team_id
-> GROUP BY team_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wild_db_quest.t.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment