Opération	Algèbre rel.	SQL
Projection	\(\Pi_{\text{liste d'attributs}}(R)\)	`SELECT attributs FROM R`
Sélection	\({\Large \sigma}_{\text{condition}}{R}\)	`SELECT * FROM R WHERE condition`
Renommage	\(\rho_{\text{nom1} \rightarrow \text{nom2}}(R)\)	`SELECT nom1 AS nom2 FROM table1 AS table2`
Union	\(R \cup S\)	`(SELECT * FROM R) UNION (SELECT * FROM S)`
Différence	\(R - S\)	`(SELECT * FROM R) EXCEPT (SELECT * FROM S)`
Produit	\(R \times S\)	`SELECT * FROM R,S`
Intersection	\(R \cap S\)	`(SELECT * FROM R) INTERSECT (SELECT * FROM S)`

Opérations avancées

Jointures

La jointure est une opération qui permet de recoller deux relations ensemble. On la note avec le signe \(\bowtie_C\) où \(C\) est une condition. Formellement, ce n’est qu’une sélection selon la condition \(C\) d’un produit cartésien, c’est-à-dire, par définition : \[R {\Large \bowtie}_C S = {\Large \sigma}_{C}{(R \times S)}.\]

Cette opération est très courante dans les requêtes. Cela vient du fait que les bases de données sont conçues pour ne pas avoir d’information redondante.

Prenons l’exemple du schéma world et des tables world.country et world.city. La table world.country possède une colonne capital qui contient le numéro d’identification d’une ville de la table world.city. Par exemple, pour la France, la colonne capital vaut 2974 et la ville numéro 2974 est Paris:

bd_2023-24> SELECT capital 
FROM world.country 
WHERE countrycode='FRA';
bd_2023-24> SELECT name 
FROM world.city 
WHERE id = 2974;

Pour lister les noms des capitales mondiales, on va avoir besoin de recoller les deux tables en prenant soin de respecter la condition d’égalité entre la capitale et le numéro d’identification de la ville. Cela peut s’écrire en SQL comme suit:

SELECT name
FROM world.city AS V JOIN 
     world.country AS P ON 
     V.id = P.capital;

Parfois, on veut recoller deux tables avec la condition que les colonnes qui ont le même nom doivent être égales. Cela arrive souvent si la base de données a bien été conçue. On appelle cette jointure une jointure naturelle, qu’on peut écrire avec NATURAL JOIN. La requête

SELECT name 
FROM world.city NATURAL JOIN world.country
WHERE continent = 'Oceania';

renvoie la liste des villes d’Océanie. La jointure est faite implicitement sur la condition

world.city.countrycode = world.country.countrycode

la table résultante n’aura qu’une seule colonne countrycode. Si l’on souhaite ne faire la jointure naturelle que sur un sous-ensemble a1,...,ak des attributs communs, on peut utiliser JOIN ... USING (a1,...,ak).

Requêtes nommées : utilisation de `WITH`

Lorsque les requêtes se compliquent, il est parfois commode de la découper en plusieurs requêtes qu’on manipulera par la suite. Par exemple, trouver le nom des capitales européennes ayant plus d’un million d’habitants peut se découper ainsi: on résout la requête CAP qui contient le nom, le continent et la population des capitales. Puis on résout la requête finale en filtrant CAP. Cela peut s’écrire avec l’opérateur WITH:

WITH CAP AS (
  SELECT name, continent, population
  FROM world.city AS V JOIN 
    world.country AS P ON  V.id=P.capital
)

SELECT name
FROM CAP
WHERE continent = 'Europe' AND population > 1000000;

Requêtes

Quels sont les noms des capitales Sud-Américaines ? (14 lignes)

Quels sont les noms des pays où le français est langue officielle ? (18 lignes)

Quels sont les pays dont le gouvernement est monarchique (constitutionnelle ou pas) ? (43 lignes)

Quelles sont les monarchies d’Europe dont la capitale compte moins d’un million d’habitants ? (8 lignes)

Quelles sont les régions où on ne trouve pas de monarchie ? (9 lignes)

Quelles sont les langues qui ne sont langues officielles dans aucun pays ? (355 lignes)

Quels sont les pays qui n’ont pas de langue officielle ? (49 lignes)

Quels sont les pays dont la seule langue officielle est le français ? (9 lignes)

Les noms des pays où le français n’est pas la seule langue officielle. (9 lignes)

Quelles sont les régions qui ne comportent qu’une seule forme de gouvernement ? (3 lignes)

Quelles sont les langues officielles des pays dont la capitale compte plus de 5 000 000 d’habitants ? (12 lignes)

Quels sont les pays où au moins trois langues sont parlées chacune par strictement plus de \(10\%\) de la population ? (35 lignes)

Quelles sont les régions où il existe deux pays dont les espérances de vie diffèrent par au moins \(10\) ans ? (16 lignes)

Quels sont les pays où l’anglais et le français sont des langues officielles ? (3 lignes)

Montrer comment calculer l’intersection de deux tables avec une jointure (sous certaines conditions).

Schéma world (rappel)

Écriture de requêtes

Opérations avancées

Jointures

Requêtes nommées : utilisation de WITH

Requêtes

Schéma `world` (rappel)

Requêtes nommées : utilisation de `WITH`