BD II: SQL II

---
name: layout-general
layout: true
class: left, middle

.remark-slide-number .progress-bar-container {
  position: absolute;
  bottom: 0;
  height: 4px;
  display: block;
  left: 0;
  right: 0;
}

.remark-slide-number .progress-bar {
  height: 100%;
  background-color: red;
}
</style>

<div>
<style type="text/css">.xaringan-extra-logo {
width: 110px;
height: 128px;
z-index: 0;
background-image: url(./img/UniversiteParisCite_logo_horizontal_couleur_RVB.jpeg);
background-size: contain;
background-repeat: no-repeat;
position: absolute;
top:1em;right:1em;
}
</style>
<script>(function () {
  let tries = 0
  function addLogo () {
    if (typeof slideshow === 'undefined') {
      tries += 1
      if (tries < 10) {
        setTimeout(addLogo, 100)
      }
    } else {
      document.querySelectorAll('.remark-slide-content:not(.hide_logo)')
        .forEach(function (slide) {
          const logo = document.createElement('a')
          logo.classList = 'xaringan-extra-logo'
          logo.href = 'http://master.math.univ-paris-diderot.fr/annee/m1-mi/'
          slide.appendChild(logo)
        })
    }
  }
  document.addEventListener('DOMContentLoaded', addLogo)
})()</script>
</div>

---

# Bases de Données : SQL (II), Requêtes avancées

### 2023-09-29

#### [Licence MIASHS et Mathématiques]()

#### [Bases de Données](http://stephane-v-boucheron.fr/courses/bdd/)

#### [Équipe BD](http://stephane-v-boucheron.fr)

---
template: inter-slide

### Plan  <svg aria-hidden="true" role="img" viewBox="0 0 576 512" style="height:1em;width:1.12em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M565.6 36.2C572.1 40.7 576 48.1 576 56V392c0 10-6.2 18.9-15.5 22.4l-168 64c-5.2 2-10.9 2.1-16.1 .3L192.5 417.5l-160 61c-7.4 2.8-15.7 1.8-22.2-2.7S0 463.9 0 456V120c0-10 6.1-18.9 15.5-22.4l168-64c5.2-2 10.9-2.1 16.1-.3L383.5 94.5l160-61c7.4-2.8 15.7-1.8 22.2 2.7zM48 136.5V421.2l120-45.7V90.8L48 136.5zM360 422.7V137.3l-144-48V374.7l144 48zm48-1.5l120-45.7V90.8L408 136.5V421.2z"/></svg>
  
- [Opérateurs ensemblistes:  `$\cap,\cup,\setminus$`](#operateursensemblistes)

- [Requêtes imbriquées](#requetesimbriquees)

---
name: operateursensemblistes
template: inter-slide

## Opérateurs ensemblistes

---

### Opérateurs ensemblistes:  `$\cap,\cup,\setminus$`

- Intersection ( `$\cap$` ) : les tuples qui sont à la fois dans la requête 1 
    et dans  la requête 2

```psql
  <requete1> INTERSECT <requete2>
```

- Union ( `$\cup$` )  :  les tuples de la requête 1 et ceux de la  requête 2

```psql
  <requete1> UNION <requete2>
```

- Différence ( `$\backslash$` ) :  les tuples de la requête 1 qui ne sont pas des tuples 
de la requête 2

```psql
  <requete1> EXCEPT <requete2>
```

---

### Exemple pour `EXCEPT`

```psql
Fournisseur (NomF, VilleF, AdresseF)

Piece(NomP, Prix, Couleur)

Livraison(NumLiv, NomP, NomF, DateLiv, Quantite)
```

Fournisseurs qui ont livré la pièce x22 mais pas la pièce x21

```psql
  SELECT NomF 
  FROM Livraison 
  WHERE NomP='x22'

* EXCEPT
  
  SELECT NomF
  FROM Livraison 
  WHERE NomP='x21';
```

---
name:requetesimbriquees
template: inter-slide

## Requêtes imbriquées

---

### Requêtes imbriquées `IN, EXISTS, ALL, ANY, LATERAL`

-  Le résultat d'une requête SQL est un ensemble de tuples... donc une relation.

- <svg aria-hidden="true" role="img" viewBox="0 0 384 512" style="height:1em;width:0.75em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M297.2 248.9C311.6 228.3 320 203.2 320 176c0-70.7-57.3-128-128-128S64 105.3 64 176c0 27.2 8.4 52.3 22.8 72.9c3.7 5.3 8.1 11.3 12.8 17.7l0 0c12.9 17.7 28.3 38.9 39.8 59.8c10.4 19 15.7 38.8 18.3 57.5H109c-2.2-12-5.9-23.7-11.8-34.5c-9.9-18-22.2-34.9-34.5-51.8l0 0 0 0c-5.2-7.1-10.4-14.2-15.4-21.4C27.6 247.9 16 213.3 16 176C16 78.8 94.8 0 192 0s176 78.8 176 176c0 37.3-11.6 71.9-31.4 100.3c-5 7.2-10.2 14.3-15.4 21.4l0 0 0 0c-12.3 16.8-24.6 33.7-34.5 51.8c-5.9 10.8-9.6 22.5-11.8 34.5H226.4c2.6-18.7 7.9-38.6 18.3-57.5c11.5-20.9 26.9-42.1 39.8-59.8l0 0 0 0 0 0c4.7-6.4 9-12.4 12.7-17.7zM192 128c-26.5 0-48 21.5-48 48c0 8.8-7.2 16-16 16s-16-7.2-16-16c0-44.2 35.8-80 80-80c8.8 0 16 7.2 16 16s-7.2 16-16 16zm0 384c-44.2 0-80-35.8-80-80V416H272v16c0 44.2-35.8 80-80 80z"/></svg> Dans la clause `WHERE` d'une requête, on peut utiliser une fonction d'une autre requête,  appelée **sous-requête**

- `IN, EXISTS, ALL, ANY` sont des fonctions qui prennent une sous-requête (une relation) en argument.

- Dans une sous-requête, on peut se référer à des attributs des tables de la clause `FROM`.

- Pour chaque ligne d'un item `FROM` qui fournit la colonne référencée, l'item `LATERAL` est évalué en utilisant cette ligne. Les lignes du résultat sont *jointes* avec la ligne qui a servi à les calculer.

- On peut utiliser ces résultats comme données d'une autre requête

- <svg aria-hidden="true" role="img" viewBox="0 0 512 512" style="height:1em;width:1em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M448 128l-177.6 0c1 5.2 1.6 10.5 1.6 16l0 16 32 0 144 0c8.8 0 16-7.2 16-16s-7.2-16-16-16zM224 144c0-17.7-14.3-32-32-32c0 0 0 0 0 0l-24 0c-66.3 0-120 53.7-120 120l0 48c0 52.5 33.7 97.1 80.7 113.4c-.5-3.1-.7-6.2-.7-9.4c0-20 9.2-37.9 23.6-49.7c-4.9-9-7.6-19.4-7.6-30.3c0-15.1 5.3-29 14-40c-8.8-11-14-24.9-14-40l0-40c0-13.3 10.7-24 24-24s24 10.7 24 24l0 40c0 8.8 7.2 16 16 16s16-7.2 16-16l0-40 0-40zM192 64s0 0 0 0c18 0 34.6 6 48 16l208 0c35.3 0 64 28.7 64 64s-28.7 64-64 64l-82 0c1.3 5.1 2 10.5 2 16c0 25.3-14.7 47.2-36 57.6c2.6 7 4 14.5 4 22.4c0 20-9.2 37.9-23.6 49.7c4.9 9 7.6 19.4 7.6 30.3c0 35.3-28.7 64-64 64l-64 0-24 0C75.2 448 0 372.8 0 280l0-48C0 139.2 75.2 64 168 64l24 0zm64 336c8.8 0 16-7.2 16-16s-7.2-16-16-16l-48 0-16 0c-8.8 0-16 7.2-16 16s7.2 16 16 16l64 0zm16-176c0 5.5-.7 10.9-2 16l2 0 32 0c8.8 0 16-7.2 16-16s-7.2-16-16-16l-32 0 0 16zm-24 64l-40 0c-8.8 0-16 7.2-16 16s7.2 16 16 16l48 0 16 0c8.8 0 16-7.2 16-16s-7.2-16-16-16l-24 0z"/></svg> Moyens : utiliser des fonctions de table dans la clause `WHERE`.

---
name: opérateurin
template: inter-slide

## Opérateur IN

---

### Utilisation de sous-requêtes : `IN`

- Opérateur `IN` déjà vu pour exprimer que l'attribut est à valeur dans une certaine liste

- Nouvelle utilisation de `IN` avec des sous-requêtes.

```psql
<attribut> [NOT] IN (<sous-requete>)
```

Evaluée à vraie si <attribut> appartient au résultat de la
sous-requête

---

### Utilisation de sous-requêtes : `IN`

Lister les villes de fournisseurs qui ont livré la pièce 'x21'.

.fl.w-50.pa2[

```psql
SELECT DISTINCT VilleF

FROM Fournisseur

*WHERE NomF IN ( SELECT NomF
*               FROM Livraison
*               WHERE NomP = 'x21') ;
```
]

.fl.w-50.pa2[
est équivalente à :

```psql
SELECT DISTINCT F.VilleF

FROM Fournisseur F, Livraison L

WHERE  (F. NomF = L. NomF) AND 
       (L. NomP = 'x21');
```
]

---

### Utilisation de sous-requêtes : `IN`

Lister les couleurs de pièces livrées par des fournisseurs de Paris

```psql
SELECT DISTINCT Couleur 
FROM Piece
WHERE NomP IN (   
*SELECT NomP
* FROM Livraison
*WHERE NomF IN (SELECT NomF
*                FROM Fournisseur
*            		 WHERE VilleF='Paris')
);  
```

est équivalente à

```psql
SELECT DISTINCT P.Couleur

FROM Piece P, Livraison L, Fournisseur F

WHERE P.NomP = L.NomP AND
      L.NomF = F.NomF 	
      VilleF = 'Paris'  ;
```

---

### Utilisation de sous-requêtes : `IN`

- Utilisation ci-dessus pas très utile mais...

- Pratique à utiliser sous la forme `NOT IN`

Lister les noms de pièces qui n'ont jamais été livrées.

```psql
SELECT NomP
FROM Piece P 
WHERE NomP NOT IN    (
*   SELECT NomP
*   FROM  Livraison
);
```

Equivalence en algèbre relationnelle :

`$$\pi_{\text{NomP}}(\text{Piece}) - \pi_{\text{NomP}}(\text{Livraison})$$`

.... Une des façons de coder la .blue[différence] en SQL

---

### Utilisation de sous-requêtes : `IN`

Les capitales dont la population est plus grande que la moitié de la population du pays

```psql
SELECT name_city FROM city
WHERE id IN (
  SELECT capital 
  FROM country
  WHERE population_city>=population_country*.5
);
```

est équivalente à :

```psql
SELECT name_city 
FROM country JOIN city 
     ON capital=id
WHERE population_city>=population_country*.5;
```

---
name: operateuralletany
template: inter-slide

## Opérateur ALL et ANY

---

### Utilisation de sous-requêtes : `ANY`, `ALL`

Deux nouveaux opérateurs manipulant des sous-requêtes : `ANY`, `ALL`

```psql
<attributs> =|<>|<=|<|>|=> ANY (<sous-requete>)
```

évaluée à vraie si au moins un des résultats de la sous requête vérifie la comparaison avec  `<attributs>`

```psql
<attributs> =|<>|<=|<|>|=> ALL (<sous-requete>)
```

évaluée à vraie si tous les  résultats de la sous-requête vérifient la comparaison avec  `<attributs>`

---

### Utilisation de sous-requêtes : `ANY`, `ALL`

Alternative au `IN`

lister les noms de fournisseurs qui ont livré la pièce 'x21'.

```psql
SELECT NomF
FROM Fournisseur
WHERE NomF = ANY (
	SELECT NomF
	FROM Livraison
	WHERE NomP = 'x21' );
```

---

### Utilisation de sous-requêtes : `ANY`, `ALL`

Information sur la commande dont la quantité de ièces livrées était la plus importante.

```psql
SELECT *
FROM Livraison
WHERE Quantite >= ALL (
	SELECT Quantite FROM Livraison);
```

---

### Utilisation de sous-requêtes : `ANY`, `ALL`

Noms et prix des pièces livrées les plus chères

```psql
SELECT P.NomP, P.Prix 
FROM Livraison L, Piece P
WHERE L.NomP = P.NomP AND 
      P.Prix>=ALL (
        SELECT Prix 
        FROM Piece
);
```

### Utilisation de sous-requêtes : `ANY`, `ALL`

La ville la plus peuplée de chaque pays

```psql
SELECT c1.name_city 
FROM city as c1
WHERE population_city >= ALL (
    SELECT c2.population_city 
    FROM city as c2
    WHERE c1.countrycode=c2.countrycode
);
```

---
name: operateurexists
template: inter-slide

## Opérateur EXISTS

---

### Utilisation de sous-requêtes : `EXISTS`

```psql
[NOT] EXISTS (<sous-requete>)
```

est évaluée à vraie si la sous-requête renvoie au moins un résultat.

Nom et prix des pièces qui ont été livrées

```psql
SELECT NomP, Prix 
FROM Piece P 
WHERE EXISTS (
  SELECT * 
  FROM Livraison 
  WHERE Livraison.NomP = P.NomP
);
```

L' utilisation de `EXISTS` s'apparente à une condition booléenne.

Pas de test sur la valeur d'un attribut particulier.

---

### Utilisation de sous-requêtes : `EXISTS`
  
Nom des Fournisseurs ayant livré la pièce x21 mais qui n'ont jamais livré la pièce a22.

```psql
SELECT DISTINCT NomF 
FROM Livraison L 
WHERE L.NomP='x21' AND 
  NOT EXISTS (
    SELECT * 
    FROM Livraison L1 
    WHERE L2.NomP='a22' AND L2.NomF=L.NomF
  );
```

---

### Utilisation de sous-requêtes : `EXISTS`

Les régions qui ont au moins une langue officielle :

```psql
SELECT DISTINCT region 
FROM world.country AS co
WHERE EXISTS (
  SELECT * 
  FROM world.countrylanguage AS cl
  WHERE co.countrycode = cl.countrycode  AND cl.isofficial
);
```

---
name: lateral
template: inter-slide

## Opérateur LATERAL

???

> PostgreSQL 9.3 has a new join type! Lateral joins arrived without a lot of fanfare, 
but they enable some powerful new queries that were previously only tractable with procedural code. 
In this post, I'll walk through a conversion funnel analysis that wouldn't be possible in PostgreSQL 9.2.

---

### Requêtes imbriquées : `LATERAL`

.bg-light-gray.b--light-gray.ba.bw2.br3.shadow-5.ph4.mt5[

Les sous-requêtes d'une clause `FROM` peuvent  être précédées du mot-clé  `LATERAL`
  
On peut alors faire référence à des colonnes des éléments précédents de la clause  `FROM`

]

???

> When a FROM item contains `LATERAL` cross-references, 
evaluation proceeds as follows: for each row of the `FROM` item providing the cross-referenced column(s), 
or set of rows of multiple `FROM` items providing the columns, 
the `LATERAL` item is evaluated using that row or row set’s values of the columns. 
The resulting row(s) are joined as usual with the rows they were computed from. 
This is repeated for each row or set of rows from the column source table(s).

---

### Pour les fonction-tables ...

Les arguments de la fonction peuvent contenir des références à des colonnes des items précédents de la clause `FROM`.

### Evaluation

Pour chaque ligne d'un item `FROM` qui fournit la colonne référencée, l'item `LATERAL` est évalué en utilisant cette ligne. Les lignes du résultat sont jointes avec la ligne qui a servi à les 
calculer

On peut parler de *sous-requête paramétrée*

---

### Un exemple de l'usage de `LATERAL`

```psql
SELECT name_country, name_city 
*FROM country, LATERAL (
* SELECT name_city
* FROM city
* WHERE city.countrycode=country.countrycode) ss
ORDER BY name_country;
```

équivalent à

```psql
select name_country, name_city 
from country natural join city
order by name_country;
```

---

### `LATERAL`  en action sur `world`

.fl.w-60.pa2.f6[

```psql
SELECT name_country, name_city

*FROM country, LATERAL (
* SELECT name_city
* FROM city
* WHERE city.countrycode=country.countrycode) ss

ORDER BY name_city

LIMIT 10 ;
```
]

.fl.w-40.pa2.f6[

|name_country|name_city|
|------------|---------|
|Spain|A Coruña (La Coruña)|
|Germany|Aachen|
|Denmark|Aalborg|
|Nigeria|Aba|
|Iran|Abadan|
|Brazil|Abaetetuba|
|Russian Federation|Abakan|
|Canada|Abbotsford|
|Nigeria|Abeokuta|
| ... | ... |

]

---

```psql
SELECT	name_country,	name_city, lifeexpectancy
FROM (SELECT countrycode,	name_country, lifeexpectancy
		  FROM country
		  WHERE gnpold >0 AND gnp / gnpold > 1.1
     ) AS better_off 
*JOIN LATERAL (SELECT c.countrycode, name_city
		          FROM country c
		          JOIN city ON (capital = id)
*	          WHERE better_off.countrycode=c.countrycode AND
			              population_city> 2000000
) AS big_capital 
*ON TRUE ;
```

.f6.fr[

| Country                             | City    |Life expectancy|
|:------------------------------------|---------|--------------|
|Iran                                 |Teheran  |          69.7|
|Congo, The Democratic Republic of the|Kinshasa |          48.8|
|Turkey                               |Ankara   |          71.0|

]

???

See [Trumpetting lateral join](https://heap.io/blog/postgresqls-powerful-new-join-type-lateral)

Could be useful for implementing exploratory pipelines into postgresql without to much tears and sweat.

---
name: lateral
template: inter-slide

## WITH

---

### Syntaxe des clauses `WITH`

```psql
WITH r AS (
  SELECT ...
  FROM ...
  WHERE ...
), s AS (
  SELECT ...
  FROM ...
  WHERE ...     -- possible reference to r 
)
SELECT ...
FROM   ... -- possible reference to r, s 
WHERE

```
---

Une clause `WITH` permet d'écrire des requêtes intermédiaires et de décomposer une requête complexe

Ces expressions/requêtes sont souvent désignées par le vocable 
`Common Table Expressions` ou CTEs

Les CTEs (éléments d'une clasuse  `WITH`) peuvent être des requêtes, mais aussi des expressions de 
manipulation (mise à jour, insertion, suppression) de données

???

Les CTEs permettent de coller plus fidèlement à l'algèbre relationnelle que les requêtes imbriquées

---

### ...

```psql
WITH better_off AS (
  SELECT countrycode, name_country, lifeexpectancy, capital
  FROM country
  WHERE gnpold >0 AND gnp / gnpold > 1.1
),
big_capital AS (
  SELECT id, countrycode
  FROM city 
  WHERE population > 2000000
)
SELECT bo.countrycode, name_country, lifeexpectancy
FROM better_off bo JOIN big_capital bc ON
  (bo.countrycode=bc.countrycode AND bc.id=bo.capital) ;
```

---

background-image: url('./img/pexels-cottonbro-3171837.jpg')
background-size: cover

# The End