Description de la base de données

La base de données « Mondial Database » a été créée en agrégeant un grand nombre de données issues de différentes sources.
Les tables ci-dessous sont extraites de cette base (cliquer sur les boutons pour afficher/cacher les tables utiles/inutiles).
(To encompass = englober, la table donne le continent de chaque pays)

Les clés primaires (pk = primary key) sont signalées par l'icône 🔑
La présence de plusieurs clés indique que la clé primaire est constituée de plusieurs attributs.

L'icône ❌ signifie que la valeur d'un attribut peut être NULL

Le graphe des clés étrangères (fk = foreign key) pour les tables ci-dessus est :

isMember(organization) → organization(abbreviation)
isMember(country) → country(code)
encompasses(country) → country(code)
located(country) → country(code)

Langage SQL - Algèbre relationnelle

Vocabulaire

Attributs = noms des colonnes d'une table.
n-uplets ou enregistrements = lignes d'une table.
Relation = ensemble des n-uplets d’une table.
On dit que la relation notée r d’une table suit le schéma de relation R(A1, A2, A3,…) (i.e. la table associée à la relation r met en relation les attributs A1, A2, A3… ou encore associe les attributs A1, A2, A3… les uns aux autres).
L’ensemble des schémas de relation s’appelle le schéma de la base de données.

Structure générale d'une requête

SELECT attribut(s)
FROM relation(s)
WHERE formule de sélection

Aide mémoire

Opérations élémentaires
Opération Langage SQL Algèbre relationnelle
Projection SELECT <liste d'attributs ou *>
FROM <table>		 \( \pi_{attributs}(r) \)
\( \pi_{name, capital}(country) \)
Sélection SELECT *
FROM <table>
WHERE <formule de sélection>		 \( \sigma_{formule}(r) \)
\( \sigma_{code=\textrm{'GR'}}(country) \)
Jointure SELECT *
FROM <table1> JOIN <table2>
ON <table1.attribut_x = table2.attribut_y>		 r1 ⋈formule r2
located ⋈located.country = country.code country
Agrégation SELECT <fonction d'agrégation>
FROM <table>
Cf. clauses GROUP BY (regroupement de n-uplets) et HAVING (sélection à l’intérieur de GROUP BY) ci-dessous. 		\( \gamma_{fonction(attribut)}(r) \)
\( \gamma_{COUNT(*)}(country) \)
Renommage SELECT <attribut> AS <nouveau_nom>
FROM <table>		 \( \rho_{attribut 1 \rightarrow attribut 2}(r) \)
\( \rho_{established \rightarrow creation}(organization) \)
Tri SELECT <liste d'attributs ou *>
FROM <table>
ORDER BY <liste d'attributs> [DESC]

Sous-requêtes
Requête derrière... Langage SQL Détails
FROM SELECT <liste d'attributs>
FROM (SELECT ...)
WHERE SELECT <liste d'attributs>
FROM <table>
WHERE <attribut> <opérateur> (SELECT ...)		 <opérateur> :
≤ ≥ < > = != [NOT] IN, [NOT] EXISTS
ANY, SOME, ALL

Regroupement
Langage SQL Exemple
Regroupement SELECT <liste d'attributs 1>
FROM <liste de tables>
WHERE  <formule de sélection>
GROUP BY <liste d'attributs 2>		 SELECT lake,count(*) AS nbre_villes
FROM located
WHERE lake IS NOT NULL
GROUP BY lake
Regroupement conditionnel SELECT <liste d'attributs 1>
FROM <liste de tables>
WHERE  <formule de sélection 1>
GROUP BY <liste d'attributs 2>
HAVING <formule de sélection 2>		 SELECT lake,count(*) AS nbre_villes
FROM located
WHERE lake IS NOT NULL
GROUP BY lake
HAVING COUNT(*)>2

Agrégation
Derrière... Langage SQL Exemple
SELECT SELECT <fonction d'agrégation>
FROM <liste de tables>
WHERE <formule de sélection>		 SELECT MAX(population)
FROM country
WHERE area<1000
HAVING SELECT <liste d'attributs>
FROM <liste de tables>
WHERE <formule de sélection>
GROUP BY <liste d'attributs>
HAVING <fonction d'agrégation>		 SELECT country,COUNT(*)
FROM located
WHERE river IS NOT NULL
GROUP BY country
HAVING COUNT(*)>10

Ressources en ligne :
- SQL.sh
- w3schools.com
- en cas de message d'erreur (incompris), copier le code de l'erreur (exemple : ORA-00934) et le coller dans un moteur de recherche.

Requêtes

R1 - Afficher la liste des pays dont le nom commence par "F".

R2 - Afficher la liste des pays dont le nom contient "an".

R3 - Afficher la liste des pays dont le nom commence par "F" et se termine par "e".

R4 - Quels sont les pays dont la population est inférieure à 1000 habitants ?

R5 - Afficher la liste des continents triée par ordre alphabétique (sans doublon).

R6 - Quels sont les noms des pays d'Europe (pas seulement leurs codes) ?

R7 - Combien y a-t-il de pays en Asie ?

R8 - Quelle est la moyenne de la population mondiale ?

R9 Exemple - Quel est le nom, la population et la surface du pays le plus petit ? (Solution fournie à titre d'exemple)

R10 - Quel est le nom et la densité de population du pays le plus grand ?

R11 Exemple - Quelle est la population du pays le plus peuplé d'Afrique ? (Solution fournie)

R12 - Quelle est la densité de population la plus élevée en Asie ?

R13 - Quel est le pays le plus peuplé d'Afrique ?

R14 - Quelle est la densité moyenne de population par continent ?

R15 - Combien y a-t-il de villes traversées par une rivière dans chaque pays ? (Classer sur le nombre)

R16 - Quels sont les pays possèdant plus de 20 villes traversées par une rivière ? (Classer sur le nombre)

R17 - Quels sont les continents dont la densité moyenne de population est supérieure à 500 hab/km² ?

×

Une formule de sélection est une formule construite à partir :

  • des attributs
  • des opérations usuelles (+ - * /)
  • des fonctions mathématiques (SIN, COS, EXP, LN, ROUND...)
  • de constantes
  • des opérateurs de comparaison ( ≤, ≥, <, > =, != )
  • des connecteurs logiques AND, OR, NOT
  • des opérateurs [NOT] IN, [NOT] EXISTS, [NOT] LIKE, BETWEEN, IS [NOT] NULL, DISTINCT, ANY, SOME, ALL

Utilisation de LIKE : LIKE 'F%' (1ère lettre = 'F'), LIKE 'Fr%' (2 premières lettres = 'Fr'), LIKE '%e' (dernière lettre = 'e'), LIKE '%_e_%' ('e' présent à l'intérieur du mot), LIKE '%e%' ('e' présent dans le mot)...

Utilisation de ROUND : ROUND(flottant, nombre de chiffres après la virgule).
Exemple : ROUND(4.5987, 2)

Exemple :

SELECT name
FROM country
WHERE code='SVAX'

×

Fonction d'agrégation :

  • COUNT(*) ou COUNT([DISTINCT] <attribut>)
  • MAX(<attribut>)
  • MIN(<attribut>)
  • SUM([DISTINCT] <attribut>)
  • AVG([DISTINCT] <attribut>)