BD II: SQL I

---
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 (I)

### 2023-09-21

#### [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>

- [Introduction](#introduction)
 
- [Requêtes mono-relation](#requetesmonorelation)
 
- [Requêtes multi-relations](#requetesmultirelations)
  
---
template: inter-slide
name: introduction
## Introduction

---

###  SQL : Structured Query Language

SQL est langage permettant :

- _interrogation_ des données (requêtes)

- _définition_ des données et des contraintes structurelles sur celles-ci

- _manipulation_ des données (insertion, suppression, mise à
  jour)

- définition des _vues_ et des _index_

- _administration_ des bases de données

---

### Plusieurs fois normalisés par l'ISO ...

- SQL-86,
- SQL-89,
- SQL-92,
- SQL-99.
- ...
- SQL-2003
- SQL-2008
- SQL-2011
- SQL-2016
- ...

Nous utiliserons le dialecte [`PostgreSQL`](https://www.postgresql.org)

???

A standard is reviewed every 5 years (roughly)

[ISO/IEC 9075 from www.iso.org](https://www.iso.org/standard/63555.html)

---

### SQL

.fl.w-50.pa2[

Une instruction SQL permet de _combiner_ :

- restriction/filtrage (sélection) `$\sigma$`

- projection `$\pi$`

- renommage `$\rho$`

-  ...

]

.fl.w-50.pa2[

L'instruction

```psql
SELECT <liste attribut>

FROM <table>

WHERE <condition> ;
```

traduit l'expression

`$\pi_{\text{<liste attribut>}}(\sigma_{\text{<condition>}}(\text{<table>}))$`

]

???

On peut faire autrement : dans les extensions du langage `R`, notamment celles qui relèvent de `tidyverse`, on associe des fonctions à chaque opérateur de l'algèbre
relationnelle. Pour opérateurs qui portent sur une seule table, le premier argument
de chaque fonction est toujours la table sur laquelle on opère. Les autres arguments
sont soit les attributs de projection, soit la spécification d'une condition de sélection, soit ...

L'usage d'un opérateur `pipe` (comme dans Unix) permet de composer de facon lisible les opérations.

```
<table> %>%
  filter(<condition> ) %>%
  select(<liste attribut>) %>% 
  ...
```

---

### Syntaxe - requêtes simples

.fl.w-50.pa2[

```psql
SELECT [DISTINCT] * | <liste attribut>

FROM <liste de tables>

[WHERE <condition>] ;
```
]
]

.fl.w-50.pa2[

- `*` : sélection de tous les attributs (le schéma reste inchangé)

- `[...]` : clause, expression facultative.

- `DISTINCT` : supprime les doublons.

]

---

### Requêtes simples sur exemple jouet

.fl.w-50.pa2[

```psql
Employe(Nu, NomE, Annee, Tel, Nudept)

Fournisseur (NomF, Ville, Adresse)

Piece(NomP, Prix, Couleur)
```

]

.fl.w-50.pa2[

```psql
SELECT NomF, Ville

FROM Fournisseur ;
```

Equivalent à :

`$$\pi_{\text{NomF, Ville}}(\text{Fournisseur})$$`

]

---

```psql
SELECT *

FROM Fournisseur ;
```

Equivalent à : `Fournisseur`

```psql
SELECT *

FROM Fournisseur

WHERE Ville='Paris' ;
```

Equivalent à : `$\sigma_{\texttt{Ville}=\texttt{'Paris'}}(\texttt{Fournisseur})$`

---
template: inter-slide
name: requetesmonorelation
## Requêtes mono-relation

---

### Structure de base

La structure de base d'une requête s'appuie sur les trois mots
clés suivants :

- .red[`SELECT`] correspond à l'opérateur de projection sur la liste d'attributs demandée, il peut aussi être suivi de fonctions d'attributs

- .red[`FROM`] indique la ou les relations concernées

- .red[`WHERE`] précise une condition et correspond à l'opération de restriction/sélection en algèbre relationnelle.

---

### Syntaxe de la condition de filtrage (`WHERE ...`)

Une condition se construit à l'aide des opérations suivantes :

- *Comparaison* avec opérateurs : .red[`=, <>, >,<, >=, <=`]

```psql
SELECT *

FROM Employe

*WHERE NomE <>'Durand'  ;
```

---

### Syntaxe de la condition de filtrage (`WHERE ...`)

*Combinaison* de conditions à l'aide des opérateurs logiques:  .red[`AND`, `OR`, `NOT`]

```sql
SELECT DISTINCT NomF

FROM Fournisseur

*WHERE (Ville='Londres') OR (Ville='Paris');
```

### Syntaxe de la condition `WHERE ...` (suite)

Test .red[BETWEEN] permettant de vérifier si la valeur d'un attribut est comprise entre deux constantes

```psql
SELECT NomE

FROM Employe

*WHERE Annee BETWEEN '22/06/03' AND '25/09/03';
```

---

### Syntaxe de la condition `WHERE ...` (suite)

- Test à .red[`NULL`] signifiant que la valeur est égale à `NULL` (c-à-d est inconnue)

```psql
SELECT *

FROM Employe

*WHERE Tel IS NULL;
```

### Syntaxe de la condition `WHERE ...` (suite)

Test d'*appartenance* .red[`IN`] qui permet de vérifier si la valeur d'un attribut appartient à une liste de constantes,

```psql
SELECT *

FROM Fournisseur

*WHERE Ville IN ('Paris', 'Londres');
```

---

### Syntaxe de la condition `WHERE ...` (suite)

Test .red[`LIKE`] permettant de vérifier si un attribut de type chaîne de
caractères contient une ou plusieurs sous-chaînes

`$\_$` : remplace n'importe quel caractère

`$\%$` : remplace n'importe quelle chaîne de caractères.

```psql
SELECT Ville

FROM Fournisseur

WHERE Ville LIKE 'Saint%';
```

---
template: inter-slide
name: requetesmultirelations

## Requêtes multi-relations

---

### Requêtes multi-relations

Quand l'information est dispersée sur plusieurs tables, on utilise des .red[jointures]

---

### On peut :

- utiliser plusieurs tables dans la clause `FROM`

- faire intervenir des conditions complexes impliquant ces tables dans la clause `WHERE`

---

### Détails du schema `World`

Images et tables créées grâce à [DbSchema](https://dbschema.com)

---

### Diagramme en pattes de corbeau

![img](./img/~LayoutwithSampleTools.svg)

---

### Table world.city 
| | | |
|---|---|---|
| * &#128273;  &#11019; | id| integer  |
| * | name| text  |
| * | countrycode| char(3)  |
| * | district| text  |
| * | population| integer  |

---

### Table world.country 
| | | |
|---|---|---|
| * &#128273;  &#11019; | code| char(3)  |
| * | name| text  |
| * | continent| text  |
| * | region| text  |
| * | surfacearea| real  |
|  | indepyear| smallint  |
| * | population| integer  |
|  | lifeexpectancy| real  |
|  | gnp| numeric(10,2)  |
|  | gnpold| numeric(10,2)  |
| * | localname| text  |
| * | governmentform| text  |
|  | headofstate| text  |
| &#11016; | capital| integer  |
| * | code2| char(2)  |

##### Foreign Keys
| | | |
|---|---|---|
|  | country_capital_fkey | ( capital ) ref [world.city](#city) (id) |

##### Constraints
| | |
|---|---|
| country_continent_check | ((continent = 'Asia'::text) OR (continent = 'Europe'::text) OR (continent = 'North America'::text) OR (continent = 'Africa'::text) OR (continent = 'Oceania'::text) OR (continent = 'Antarctica'::text) OR (continent = 'South America'::text)) |

---

### Table world.countrylanguage 
| | | |
|---|---|---|
| * &#128273;  &#11016; | countrycode| char(3)  |
| * &#128273;  | language| text  |
| * | isofficial| boolean  |
| * | percentage| real  |

##### Foreign Keys
| | | |
|---|---|---|
|  | countrylanguage_countrycode_fkey | ( countrycode ) ref [world.country](#country) (code) |

---

###  Première jointure sur schéma `world`

Quels sont les pays qui portent le même nom que leur capitale ?

```psql
SELECT name_country

*FROM country, city

WHERE capital=id AND name_country=name_city;
```

qui correspond à

`$$\pi_{\text{name_country}}\Bigl(
\sigma_{\text{capital}=\text{id} \wedge \text{name_country}=\text{name_city}}(\text{country }\times \text{city}) \Bigr)$$`

autrement dit

`$$\pi_{\text{name_country}}\Bigl(
\text{country} \bowtie_{\text{capital}=\text{id} \wedge  \text{name_country}=\text{name_city}} \text{city})
 \Bigr)$$`

---

### Syntaxe normalisée pour les jointures~: .red[JOIN] et .red[ON]

Quels sont les pays qui portent le même nom que leur capitale~?

```psql
SELECT name_country

*FROM country JOIN city

*ON capital=id

WHERE name_country=name_city;
```

qui correspond à

`$$\pi_{\text{name_country}}\Bigl(
\sigma_{\text{capital}=id \wedge  \text{name_country}=\text{name_city}}(\text{country} \times \text{city}) \Bigr)$$`

---

### Renommage

Si on veut faire une jointure d'une table avec elle-même, on utilise .red[AS]
pour obtenir deux tables de noms différents

- Quels sont les noms de ville qui apparaissent dans deux pays différents~?

```psql
SELECT DISTINCT c1.name_city, c1.countrycode, c2.countrycode

*FROM city AS c1 JOIN city AS c2

* ON c1.countrycode!=c2.countrycode AND c1.name_city=c2.name_city

ORDER BY c1.name_city;
```

---

### Jointure naturelle

Quels sont les noms des pays où le français est parlé~?

```psql
SELECT name_country

FROM country, countrylanguage

WHERE country.countrycode=countrylanguage.countrycode
      AND language='French';
```

Syntaxe normalisée pour la jointure naturelle~: .red[NATURAL JOIN]

```psql
SELECT name_country

*FROM country NATURAL JOIN countrylanguage

WHERE language='French';
```

---

### Jointure naturelle (suite)

Quels sont les pays dont une ville a le même nom qu'une langue parlée dans ce pays~?

```psql
SELECT DISTINCT name_country, name_city, language

*FROM country NATURAL JOIN
*    city natural JOIN
*    countrylanguage

WHERE language=name_city;
```

.plot-callout.fr[
  
<img src="./img/~LayoutwithSampleTools.svg" width="40%" height="99%" style="display: block; margin: auto 0 auto auto;" />

]

---

### Jointure externe

- .red[LEFT OUTER JOIN] (resp. .red[RIGHT OUTER JOIN]) : on garde les tuples de la table de gauche (resp. droite) qui n'ont pas de complément dans l'autre table, la condition étant spécifiée dans la clause .red[ON]

- .red[FULL OUTER JOIN] : on garde les tuples de chacune des tables qui n'ont pas de complément.

- On complète les valeurs non renseignées par .red[NULL]

---

### Exemple de jointure externe

- Certains pays n'ont pas de capitale~: la requête

```psql
SELECT name_country

FROM country

WHERE capital IS NULL;
```

renvoie 8 lignes.

- Quels sont les pays qui n'ont pas de capitale ou portent le même nom que leur capitale~?

```psql
SELECT name_country, name_city

*FROM country LEFT OUTER JOIN city
*    ON capital=id

*WHERE name_city IS NULL OR name_country=name_city ;
```

---

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

# The End