CM 6 - BDD

name: inverse
layout: true
class: center, middle, inverse

---
# Base de données - CM 6
## Inès de Courchelle
## 2024-2025

![image](img/CY-tech.png)

---
layout: false

# Rappel 1/4
- Les fonctions d'agregats
- Having
- Group By
- Les requetes imbriquées

---
# Rappel 2/4
## Les fonctions d'agrégats
SUM COUNT AVG MAX MIN
## Les HAVING + Fonction d'agrégats
Pour récupérer ou réaliser une fonction d'agrégat à la fin d'une requête
## GROUP BY
- Réaliser les fonctions d'agrégats en fonction d'un ou plusieurs attributs
- .u[Règle] le contenu du GROUP BY doit être le même

---
## Exemple
| id | quantite | prix | appelation |
|----|----------|------|------------|
| 1  | 10       | 499  | ps5        |
| 2  | 15       | 290  | switch     |
| 3  | 15       | 90   | wii        |
| 4  | 15       | 200  | xbox       |

**Afficher toutes les produits dont la Quantité * Prix dépasse 3000 €**

```sql
SELECT idProduit,appelation, sum(quantite*prix) as Total
FROM Produit
GROUP BY idProduit,nom
HAVING SUM(Total) > 3000;
```

---
# Rappel 3/4
## Requêtes imbriquées
- **Pourquoi ?** pour creuser les données
- **Comment ?** Deux types :
 - À valeur unique avec [= | > | < | >= | <= | < >]
 - À valeur multiple avec [ IN | NOT IN | ALL | ANY ]

## exemple

| idJoueur    | nom     | prenom    | poste     | nb buts |
| ----------- | -----   | -----     | ----      | ----    |
| 01          | Ronaldo | Cristiano | Attaquant | 42      |
| 02          | Mbappé  | Kylian    | Attaquant | 37      |
| 03          | Ozil    | Mesut     | Milieu    | 1       |
| 04          | Zidane  | Zinedine  | Milieu    | 12      |
| 05          | Giroud  | Olivier   | Attaquant | 42      |
| 06          | Varane  | Raphael   | Defenseur | 10      |

---
# Rappel 4/4
Quels sont les joueurs qui jouent au même poste que Kylian Mbappé ?
```sql
SELECT nom,prenom
FROM Joueur
Where poste = (SELECT poste
               FROM Joueur
               WHERE prenom="Kylian"
               );
```
Quels sont les joueurs qui n'ont jamais été remplacés ?
```sql
SELECT nom, prenom
FROM Joueur
WHERE idJoueur NOT IN (SELECT idJoueur1
                   FROM Remplacement
                   );
```
---
# Le retour du Select
## Plan
1. Apperçu de la différence entre les jointures
  - Jointure avec JOIN (rappel)
  - avec pivot (rappel)
  - naturelle
2. Les jointures Left | Right | outter
3. Les sous requêtes dépendantes
4. Les requêtes complexes

---
# Cas d'étude
Afin d'illustrer les définitions suivantes nous considérons le MLD suivant :

.pull-left[
Skater(idSkater,nom,prenom)
Competition(idCompetition,intitule) 
Vainqueur(#idSkater,#idCompetition,date)

.under[Skater]

| idSkater | nom       | prenom  |
| ---      | ---       | -----   |
| 01       | Rodriguez | Paul    |
| 02       | Cole      | Chris   |
| 03       | Koston    | Eric    |
| 04       | Margera   | Bam     |
| 05       | Steamer   | Elisa   |
| 06       | Torres    | Vanessa |
| 07       | Sheckler  | Ryan    |

]

.pull-right[

.under[Compétition]

| idCompetition | intitule       |
| ---           | ---            |
| 01            | Tampa AM       |
| 02            | Tampa Pro      |
| 03            | Street League  |
| 04            | X Games Street |

.under[Vainqueur]

| #idSkater | #idCompetition | dateContest |
| ----      | ----           | ----        |
| 01        | 03             | 2016        |
| 01        | 02             | 2010        |
| 03        | 04             | 2004        |
| 07        | 04             | 2003        |
| 07        | 04             | 2008        |

]

---
# Jointure avec JOIN (rappel)
## Squelette
```sql
SELECT <nom_col1>, <nom_col1>
FROM <nom_table1>
INNER JOIN <nom_table2>
ON <nom_table1.nom_col> = <nom_table1>.<nom_col>
```

## Qui a déjà gagné des contests ?

```sql
SELECT distinct(nom)
FROM Skater s
INNER JOIN Vainqueur v
ON s.idSkater=v.idSkater;
```

| nom       |
|-----------|
| Rodriguez |
| Torres    |
| Sheckler  |

.under[Si on veut aussi les intitulés des contests ?]

---
# Jointure avec JOIN (rappel)
## On peut faire une double Jointure !
.pull-left-small[
<center>
 <img src="https://media.giphy.com/media/m9jFnJOoQ3hWJ6Muaa/giphy.gif" width=200/>
 </center>
]
.pull-right-big[
```sql
SELECT distinct(nom), intitule, dateContest
FROM Skater s
INNER JOIN Vainqueur v
ON s.idSkater=v.idSkater
INNER JOIN Competition c
ON v.idCompetition=c.idCompetition ;
```
]

| nom       | intitule       | dateContest |
|-----------|----------------|-------------|
| Rodriguez | Tampa Pro      | 2010        |
| Rodriguez | Street League  | 2016        |
| Torres    | X Games Street | 2004        |
| Sheckler  | X Games Street | 2003        |
| Sheckler  | X Games Street | 2008        |

---
# Jointure avec Pivot (rappel)
## La même avec un Bernard !
```sql
SELECT nom,intitule,dateContest
FROM Skater s, Vainqueur v, Competition c
WHERE s.idSkater=v.idSkater
AND c.idCompetition=v.idCompetition
```
| nom       | intitule       | dateContest |
|-----------|----------------|-------------|
| Rodriguez | Tampa Pro      | 2010        |
| Rodriguez | Street League  | 2016        |
| Torres    | X Games Street | 2004        |
| Sheckler  | X Games Street | 2003        |
| Sheckler  | X Games Street | 2008        |

.under[ICI, avec Jointure ou avec Pivot, c'est la même !]

---
# Les jointures naturelles
## Objectifs
Faire le lien entre les clés primaires et étrangères qui ont le même nom
## Comment ?
Avec le mot clé .under[Natural JOIN]

```sql
SELECT distinct(nom), intitule,dateContest
FROM Skater s
Natural JOIN Vainqueur v
Natural JOIN Competition c
```
- On obtient encore le même résultat
- Sauf que l'on n'est pas obligé de préciser les ids
- Car ils ont les mêmes noms

---
# Les jointures [ LEFT | RIGHT ]

## Pourquoi ?
Pour manipuler des plusieurs tables dans différents sens
## Comment ?
Comme une jointure interne mais avec des mots clés en plus [ LEFT | RIGHT ]
## Quand ?
Bin ! quand tu veux quoi ?

---
# LEFT 1/4
## Définition
.pull-left[
```sql
 SELECT <listedeselection>
 FROM A LEFT JOIN B
 ON <condition>
```
]
.pull-right[

]

**On désire afficher tout les skateurs qui ont gagné au moins une compétition ?**

---
# LEFT 2/4
## Illustration
.pull-left[
```sql
SELECT DISTINCT(nom)
FROM Skater s
LEFT JOIN Vainqueur v
ON v.idSkater=s.idSkater;
```
]
.pull-right[
<center>
 <img src="img/left-wrong.png" width=200/>
</center>
]

Attention de ne pas se tromper dans l'ordre des colonnes !
Ici, tous les skaters sont selectionnés
.pull-left[

| nom       |
|-----------|
| Rodriguez |
| Cole      |
| Koston    |
| Margera   |
| Steamer   |
| Torres    |
| Sheckler  |
]

.pull-right[
<center>
 <img src="https://media.giphy.com/media/Wq9RLX06zRg4UM42Qf/giphy.gif" width=200/>
</center>

]

---
# LEFT 3/4
## Illustration
.pull-left[
```sql
SELECT DISTINCT(nom)
FROM Vainqueur v
LEFT JOIN Skater s
ON v.idSkater=s.idSkater;
```

]

.pull-right[

]

#### Ici, c'est bon !

.pull-left[

| nom       |
|-----------|
| Rodriguez |
| Torres    |
| Sheckler  |

]
.pull-column[
<center>
 <img src="https://media.giphy.com/media/xmxGoENM1V81q/giphy.gif" width=300/>
</center>
]
---
# LEFT 4/4
## Jo les faignants !
Si les clés ont les mêmes noms dans les tables
```sql
SELECT DISTINCT(nom)
FROM Vainqueur v
NATURAL LEFT JOIN Skater s;

```
<center>
 <img src="https://media.giphy.com/media/4KkSbPnZ5Skec/giphy.gif" width=300/>
</center>

---
# RIGHT 1/4
## Définition
.pull-left[
```sql
 SELECT <listedeselection>
 FROM A RIGHT JOIN B
 ON <condition>

```
]
.pull-right[
<center>
 <img src="img/right.png" width=200/>
</center>
]

**On désire afficher tout les skateurs qui ont gagné au moins une compétition ?**
<center>
 <img src="https://media.giphy.com/media/fivUEee3UHLneeEHIp/giphy.gif" width=300/>
</center>

---
# RIGHT 2/4
## Illustration
.pull-left[
```sql
SELECT DISTINCT(nom)
FROM Vainqueur v
RIGHT JOIN Skater s
ON v.idSkater=s.idSkater;
```
]
.pull-right[
<center>
 <img src="img/right-wrong.png" width=200/>
</center>
]

Attention de ne pas se tromper dans l'ordre des colonnes !
Ici, tous les skaters sont selectionnés
.pull-left[

| nom       |
|-----------|
| Rodriguez |
| Cole      |
| Koston    |
| Margera   |
| Steamer   |
| Torres    |
| Sheckler  |
]

.pull-right[
<center>
 <img src="https://media.giphy.com/media/Wq9RLX06zRg4UM42Qf/giphy.gif" width=200/>
</center>

]

---
# RIGHT 3/4
## Illustration
.pull-left[
```sql
SELECT DISTINCT(nom)
FROM Skater s
RIGHT JOIN Vainqueur v
ON v.idSkater=s.idSkater;
```

]

.pull-right[

]

#### Ici, c'est bon !

.pull-left[

| nom       |
|-----------|
| Rodriguez |
| Torres    |
| Sheckler  |

]
.pull-column[
<center>
 <img src="https://media.giphy.com/media/xmxGoENM1V81q/giphy.gif" width=200/>
</center>
]
---
# RIGHT 4/4
## Jo les faignants !
Si les clés ont les mêmes noms dans les tables
```sql
SELECT DISTINCT(nom)
FROM Skater s
NATURAL RIGHT JOIN Vainqueur v;

```
<center>
 <img src="https://media.giphy.com/media/4KkSbPnZ5Skec/giphy.gif" width=300/>
</center>

---
# LEFT sans l'intersection 1/3

## Objectif
Enlever l'intersection de gauche
## Comment ?
Avec une condition !

.pull-left[

```sql
SELECT <nom_col1>,<nom_col2>
FROM A
LEFT JOIN B
ON <condition>
WHERE B.key is NULL
```
]

.pull-right[
<center>
 <img src="img/left-inter.png" width=200/>
</center>
]

**Sélectionner tous les skaters qui n'ont jamais gagné de compétitions !**
<center>
 <img src="https://media.giphy.com/media/cr9vIO7NsP5cY/giphy.gif" width=100/>
</center>

---
# LEFT sans l'intersection 2/3
## Illustration
.pull-left[
```sql
SELECT DISTINCT(nom)
FROM  Skater s
LEFT JOIN Vainqueur v
ON v.idSkater=s.idSkater
WHERE v.idSkater is null;
```

]

.pull-right[
<center>
 <img src="img/left-alone.png" width=200/>
</center>

]

## Résultats

.left-column[

| nom     |
|---------|
| Cole    |
| Koston  |
| Margera |
| Steamer |

]

.right-column[
<center>
 <img src="https://media.giphy.com/media/JsyrX8siaom5cJpxwH/giphy.gif" width=300/>
</center>
]

---
# LEFT sans l'intersection 3/3
## L'astuce à Jo !
```sql
SELECT DISTINCT(nom)
FROM  Skater s
NATURAL LEFT JOIN Vainqueur v
WHERE v.idSkater is null;
```

---
# RIGHT sans l'intersection 1/3
## Objectifs
Enlever l'intersection de droite

## Comment ?
Avec une condition !

.pull-left[

```sql
SELECT <nom_col1>,<nom_col2>
FROM A
RIGHT JOIN B
ON <condition>
WHERE B.key is NULL
```
]

.pull-right[
<center>
 <img src="img/right-inter.png" width=200/>
</center>
]

**Sélectionner tous les skaters qui n'ont jamais gagné de compétitions !**
<center>
 <img src="https://media.giphy.com/media/3ov9jUWhoz5jrm7UcM/giphy.gif" width=200/>
</center>

---
# RIGHT sans l'intersection 2/3
## Illustration

.pull-left[
```sql
SELECT DISTINCT(nom)
FROM  Vainqueur v
RIGHT JOIN Skater s
ON v.idSkater=s.idSkater
WHERE v.idSkater is null;
```

]

.pull-right[
<center>
 <img src="img/right-alone.png" width=200/>
</center>

]

## Résultats

.left-column[

| nom     |
|---------|
| Cole    |
| Koston  |
| Margera |
| Steamer |

]

.right-column[
<center>
 <img src="https://media.giphy.com/media/x4qPgEQ3M6wNO/giphy.gif" width=300/>
</center>
]

---
# RIGHT sans l'intersection 3/3
## L'astuce à Jo !
```sql
SELECT DISTINCT(nom)
FROM Vainqueur v
NATURAL RIGHT JOIN Skater s
WHERE v.idSkater is null;
```

---
# Toutes les jointures !
## Stylé !
[Lien vers la doc](https://sql.sh/cours/jointures)

---
# Cas d'étude 1/2
## Illustration

* Concert(idConcert,titre)
* Musicien(idMusicien,nom)
* Jouer(#idConcert,#idMusicien,date)

---
# Cas d'étude 2/2
## La data
.pull-left[
| idConcert | titre                 |
| -----     | ----                  |
| 01        | Woodstock             |
| 02        | Monterey Pop Festival |
| 03        | Hellfest              |
| 04        | Garorock              |

]
.pull-right[
| idMusicien | nom           |
|------------|---------------|
| 01         | Lemmy         |
| 02         | Country Joe   |
| 03         | Steel Panther |
| 04         | Canned Heat   |

]

| #idConcert | #idMusicien | date |
| --- | ---- | --- |
| 01 | 02 | 1969 |
| 02 | 04 | 1967 |
| 03 | 03 | 2017 |
| 03 | 01 | 2015 |
| 03 | 01 | 2005 |
| 03 | 03 | 2019 |
| 03 | 01 | 2010 |

---
# Petit mot clé
## Illustration
#### WHERE EXISTS WHERE NOT EXISTS
```sql
SELECT c.idConcert
FROM Concert c
WHERE EXISTS
   (
    SELECT j.idConcert
    FROM Jouer j
    WHERE c.idConcert=j.idConcert
   );

```

.pull-left[
- Vérifier ou non si une sous requête retourne un ou plusieurs résultats
- Ne pas influencer le résultat de la requête principale (contrairement à une jointure)

]

.pull-right[

| idConcert |
|-----------|
| 1         |
| 2         |
| 3         |

]

---
# EXISTS OU  jointure ?

## Avec une Jointure

.pull-left[
```sql
SELECT c.idConcert
FROM Concert c
INNER JOIN Jouer j
ON c.idConcert=j.idConcert;

```
]
.pull-right[
Il y a les doublons

| idConcert    |
|-----------|---|
| 1         | 3 |
| 1         | 3 |
| 2         | 3 |
| 3         | 3 |

]

## Avec le WHERE EXISTS
.pull-left[
```sql
SELECT c.idConcert
FROM Concert c
WHERE EXISTS
(
SELECT j.idConcert
FROM Jouer j
WHERE c.idConcert=j.idConcert
);
```
]

.pull-right[
Il n'y a pas de doublons !

| idConcert |
|-----------|
| 1         |
| 2         |
| 3         |

]

---
# Les requêtes complexes

> .otro-blockquote[ Quel est le musicien qui a le plus joué le même concert ? Dans le résultat nous désirons afficher les différentes dates, le nom du concert et le nom du musicien.]

.under[Malheuresement, ici, nous ne pouvons réaliser cela en une seule et unique requête]

- etape 1 : Sélectionner toutes les informations que l'on désire !
- etape 2 : Trouver le nombre de concert Total
- etape 3 : Trouver le nombre de concert par musicien
- etape 4 : Trouver le nombre de concert par musicien et par concert
- etape 5 : Trouver le nombre max dans cette liste
- etape 6 : Assembler le tout, mais pas n'importe comment !

---
# Les requêtes complexes
## Étape 1 : Sélectionner toutes les informations  1/2
### Avec pivot
```sql
SELECT nom, dateConcert, titre
FROM Musicien m, Concert c, Jouer j
WHERE c.idConcert=j.idConcert
AND m.idMusicien = j.idMusicien
```
### Avec jointure
```sql
SELECT nom, dateConcert, titre
FROM Musicien m
INNER JOIN Jouer j
ON m.idMusicien = j.idMusicien
INNER JOIN Concert c
ON c.idConcert=j.idConcert
```

---
# Les requêtes complexes
## Étape 1 : Sélectionner toutes les informations 2/2
Dans tous les cas on obtient le résultat suivant :

---
# Les requêtes complexes
## Étape 2 : Trouver le nombre de concert total
Créer une requête (indépendante) qui compte le nombre de concert total joué
```sql
SELECT count(*) as nbConcerts
From Jouer;
```

---
# Les requêtes complexes
## Étape 3 : Trouver le nombre de concert par musicien 1/2
Créer une requête (indépendante) qui compte le nombre total mais par musicien !
```sql
SELECT idMusicien, count(*) as nbConcerts
From Jouer
```

Mais pourquoi cela ne marche pas ?
<center>
 <img src="https://media.giphy.com/media/SGCI8sWxJ7GwGMZrfM/giphy.gif" width=200 />
</center>

---
# Les requêtes complexes
## Étape 3 : Trouver le nombre de concert par musicien 2/2
Créer une requête qui compte le nombre total mais .underR[par musicien] !
```sql
SELECT idMusicien, count(*) as nbConcerts
From Jouer
GROUP BY idMusicien;
```
<center>
 <img src="https://media.giphy.com/media/goKkfkinQTdM4/giphy.gif" width=300 />
</center>

---
# Les requêtes complexes
## Étape 4 : Trouver le nombre de concert par musicien et par concert
Créer une requête qui compte le nombre total mais .underR[par musicien et par concert]  !

- Attention => .under[ Double GROUP BY ]
- .under[GROUP BY] doit avoir les mêmes attributs que dans le SELECT (sauf la fonction d'agrégat)
- Sinon, il y aura une erreur

```sql
SELECT idMusicien, idConcert, count(*) as nbConcerts
From Jouer
GROUP BY idMusicien,idConcert;
```

| idMusicien | idConcert | nbConcerts |
|------------|-----------|------------|
| 1          | 3         | 3          |
| 2          | 1         | 1          |
| 3          | 3         | 2          |
| 3          | 4         | 1          |
| 4          | 2         | 1          |

---
# Les requêtes complexes
## Étape 5 : Trouver le nombre max dans cette liste
- La réponse est dans le nom de l'étape en remplacant liste par table
- On va selectionner le resultat max dans une liste (dans une table quoi !?)

```sql
SELECT MAX(totalGigs) FROM (
 SELECT idMusicien,idConcert, count(*) as totalGigs
 FROM Jouer
 GROUP BY idMusicien,idConcert) as total;
```
Attention : on est obligé de donner un nom à la sous-table créée (sinon on aura une erreur) !
<center>
 <img src="img/etape5-sql.png" width=300/>
</center>

---
# Les requêtes complexes
## Étape 6 : Assembler le tout 1/6
Vous vous souvenez de l'étape 1 où on sélectionnait les informations qui nous interessaient !
```sql
SELECT nom, dateConcert, titre
FROM Musicien m, Concert c, Jouer j
WHERE c.idConcert=j.idConcert
AND m.idMusicien = j.idMusicien
```
Il faudrait donc que je trouve le musicien qui a le même nombre de concert que le nombre max
```sql
SELECT nom, dateConcert, titre
FROM Musicien m, Concert c, Jouer j
WHERE c.idConcert=j.idConcert
AND m.idMusicien = j.idMusicien
AND j.idMusicien = <???>
```
---
# Les requêtes complexes
## Étape 6 : Assembler le tout 2/6
```sql
SELECT nom, dateConcert, titre
FROM Musicien m, Concert c, Jouer j
WHERE c.idConcert=j.idConcert
AND m.idMusicien = j.idMusicien
AND j.idMusicien = <ETAPE 4>
```
On va isoler le nombre de concert pour chaque musicien (etape 4)
```sql
SELECT nom, dateConcert, titre
FROM Musicien m, Concert c, Jouer j
WHERE c.idConcert=j.idConcert
AND m.idMusicien = j.idMusicien
AND j.idMusicien =(
 SELECT idMusicien, idConcert, count(*) as nbConcerts
 From Jouer
 GROUP BY idMusicien,idConcert);

```
---
# Les requêtes complexes
## Étape 6 : Assembler le tout 3/6
<center>
 <img src="img/erroretape6.png" width=500/>
</center>
- **MAIS pourquoi ça marche pas ? **
Ici, on compare m.idMusicien dans la première requête à 3 attributs du SELECT !
- **MAIS Comment vais je faire ? **

---
# Les requêtes complexes
## Solution => je vais compter après !
```sql
SELECT nom, dateConcert, titre
FROM Musicien m, Concert c, Jouer j
WHERE c.idConcert=j.idConcert
AND m.idMusicien = j.idMusicien
AND j.idMusicien =(
 SELECT idMusicien
 From Jouer
 GROUP BY idMusicien,idConcert
 HAVING COUNT(*) = <???>);

```
---
# Les requêtes complexes
## Étape 6 : Assembler le tout 4/6
- La requête finale devra avoir la forme suivante :
- On va comparer le HAVING COUNT au max que l'on a trouver à l'étape 5
```sql
Requete 1 (etape1) =
  ( requete 2 (etape4)  =
      requete 3 (etape5)
  );
```
    - etape 1 : Sélectionner toutes les informations que l'on désire !
    - etape 4 : Trouver le nombre de concert par musicien et par concert
    - etape 5 : Trouver le nombre max dans cette liste

---
# Les requêtes complexes
## Étape 6 : Assembler le tout 5/6
#### Solution 1
```sql
SELECT nom, dateConcert, titre
FROM Musicien m, Concert c, Jouer j
WHERE c.idConcert=j.idConcert
AND m.idMusicien = j.idMusicien
AND  m.idMusicien =

(
     SELECT idMusicien
     FROM Jouer
     GROUP BY idMusicien,idConcert
     HAVING COUNT(*) = (SELECT MAX(totalGigs) FROM (
     SELECT idMusicien,idConcert, count(*) as totalGigs
     FROM Jouer
     GROUP BY idMusicien,idConcert) as total )
    );
```

---
# Les requêtes complexes
## Étape 6 : Assembler le tout 6/6
#### Solution 2
```sql
SELECT nom, dateConcert, titre
FROM Musicien m
INNER JOIN Jouer j
ON m.idMusicien = j.idMusicien
INNER JOIN Concert c
ON c.idConcert=j.idConcert
AND  m.idMusicien =