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.

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) est :

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

Accès à la base en ligne

Tables et relations entre tables :

Graphe complet des clés étrangères :

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

Formule de sélection

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)...

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>`	r₁ ⋈_formule r₂ located ⋈_{located.country = country.code} country
Agrégation	`SELECT <fonction d'agrégation> FROM <table>` Fonction d'agrégation parmi `COUNT SUM MAX MIN AVG` appliquée à des attributs. Souvent utilisé avec les clauses `GROUP BY` (regroupement de n-uplets) et `HAVING` (sélection à l’intérieur de `GROUP BY`).	\( \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
Regroupement	`SELECT <liste d'attributs 1> FROM <liste de tables> WHERE <formule de sélection> GROUP BY <liste d'attributs 2>`
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>`

Fonction d'agrégation :

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

***Agrégation***
Derrière...	Langage SQL
SELECT	`SELECT <fonction d'agrégation> FROM <liste de tables> WHERE <formule de sélection>`
HAVING	`SELECT <liste d'attributs> FROM <liste de tables> WHERE <formule de sélection> GROUP BY <liste d'attributs> HAVING <fonction d'agrégation>`

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 - Quelles sont les villes situées à la fois au bord d’un lac et le long d’une rivière ?

R2 - Quels sont les pays n’appartenant à aucune organisation ?

R3 - Quelles sont les capitales n’abritant le siège d’aucune organisation ?

R4 - Quels sont les pays membres de l’OTAN (NATO en anglais) ? (Plusieurs possibilités)

R5 - Combien de pays sont-ils membres de l’OTAN ?

R6 - Quels sont les pays membres de l’OTAN dont le nom commence par la lettre L ?

R7 - Quels sont les pays NON membres de l’OTAN ?
Attention un pays peut être membre de plusieurs organisations internationales.

R8 - Quels sont les pays membres d’une seule organisation ?

R9 - Quelles organisations ont-elles été créées avant 1964 (classées par année) ?
L’instruction EXTRACT(YEAR FROM datetime) permet de récupérer l’année d’une donnée datetime de la forme 2019-11-08 00:00:00.0 .
Par exemple, EXTRACT(YEAR FROM 2019-11-08 00:00:00.0) renvoie 2019.
Remarque : la fonction YEAR() existe mais semble ne pas fonctionner sur le site en ligne.

R10 - Lister les noms et les dates de création des organisations créées entre 2005 et 2010 et les classer par ordre décroissant de date.

R11 - Quels sont les pays dont le territoire fait partie de plusieurs continents ?

R12 - Quels sont les pays d’Asie (Asia) dont la densité est supérieure à 1000 habitants par kilomètre carré ?

R13 - Afficher la table language, observer. Afficher la liste des langues les plus répandues dans le monde classée par ordre décroissant.

R14 - Dans combien de pays la langue française est-elle parlée ?

R15 - Dans quels pays parle-t-on français ?

R16 - Quelles sont les langues parlées par moins de 30 000 personnes dans le monde ?

R17 - Classer les pays par ordre de densité de population décroissante en arrondissant à 3 chiffres après la virgule.

R18 - Quels sont les pays dont la densité de population est supérieure à la moyenne mondiale ?

R19 - Quels sont les continents dont la population est supérieure à un milliard ?

R20 - Quel est le pays possédant la plus grande densité de population au monde ?