Skip to content

Instantly share code, notes, and snippets.

@stephanBerger

stephanBerger/challenge.txt

Last active October 28, 2019 10:24
Show Gist options
  • Save stephanBerger/47299aa0fa5b9e66da5e03124e90512c to your computer and use it in GitHub Desktop.
Save stephanBerger/47299aa0fa5b9e66da5e03124e90512c to your computer and use it in GitHub Desktop.
Download ZIP
06 - SQL Avancé
Raw
challenge.txt
mysql> SELECT t.name,COUNT(*) as players FROM player p
JOIN team t
ON t.id=p.team_id
GROUP BY t.name;
+------------+---------+
| name | players |
+------------+---------+
| Gryffindor | 36 |
| Hufflepuff | 12 |
| Ravenclaw | 15 |
| Slytherin | 21 |
+------------+---------+
4 rows in set (0.00 sec)
mysql> SELECT t.name,COUNT(*) as players
FROM player p
JOIN team t ON t.id=p.team_id
GROUP BY t.name
HAVING players>=14;
+------------+---------+
| name | players |
+------------+---------+
| Gryffindor | 36 |
| Ravenclaw | 15 |
| Slytherin | 21 |
+------------+---------+
3 rows in set (0.00 sec)
mysql> SELECT firstname,lastname, name, enrollement_date FROM player p
JOIN wizard w ON w.id = wizard_id
JOIN team t ON t.id= team_id
WHERE t.name = "Gryffindor" AND DAYNAME(enrollement_date) = "monday";
+-----------+------------+------------+------------------+
| firstname | lastname | name | enrollement_date |
+-----------+------------+------------+------------------+
| Sirius | Black | Gryffindor | 1994-01-10 |
| Aberforth | Dumbledore | Gryffindor | 1995-04-24 |
| Godric | Gryffindor | Gryffindor | 1993-08-30 |
| Alice | Longbottom | Gryffindor | 1992-02-17 |
| Augusta | Longbottom | Gryffindor | 1999-10-25 |
| George | Weasley | Gryffindor | 1991-08-26 |
| Cadogan | | Gryffindor | 1993-01-04 |
+-----------+------------+------------+------------------+
7 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment