Le langage SQL
Nous avons défini les contours d'une base de données relationnelle, nous allons maintenant apprendre à interroger une base de données.
Nous utiliserons à titre d'exemple la base de données MONDIAL.
Il s'agit d'une BDR qui compile un certain nombre de données géographiques et qui est gérée par l'université de Göttingen.
Il est possible d'interagir avec elle en utilisant un formulaire que l'on trouve à l'adresse : http://www.semwebtech.org/sqlfrontend/
Nous sommes en présence d'une architecture trois tiers : la première couche (le client) est représentée en HTML pour être exploitée par un navigateur web et sert d'interface entre l'homme et la machine. La seconde couche (le serveur d'application) est un serveur web qui reçoit des données textuelles de la part du client, les transmet sous la forme de requêtes SQL au serveur de la base puis actualise la page web du client pour y intégrer la réponse du serveur. Enfin, la troisième couche est un SGBD, ici Oracle Database, qui gère la base de données et répond aux sollicitations du serveur d'application.
Méthode : Obtenir la liste des tables de la base de données "Mondial"
Nous allons écrire une requête qui affichera la liste des tables accessible par un utilisateur de cette BDR.
SELECT table_name FROM user_tables;
Les mots-clés SELECT ... FROM réalisent l'interrogation.
À faire : Testez cette requête ; vous devez obtenir une liste de 47 noms de tables.
Méthode : Obtenir la description d'une table
La requête suivante permet d'afficher les attributs et leurs domaines.
On considère la table COUNTRY
DESC COUNTRY;
L'attribut NULLABLE (Yes ou No) permet de définir si lors de l'enregistrement, l'information est obligatoire ou pas.
À faire : Tester cette requête et expliquer l'affichage obtenu.
Méthode : Explorer une table
On considère la table COUNTRY dont les attributs sont NAME, CODE, CAPITAL, PROVINCE, AREA et POPULATION.
Commençons par extraire de cette table le nom de tous les pays qu'elle contient :
SELECT name FROM country
Remarque : la requête suivante produit le même effet :
SELECT NAME FROM COUNTRY
Les mots-clés SELECT ... FROM réalisent l'interrogation de la table. Dans le cas de l'exemple ci-dessus on ne liste qu'un seul des attributs de la table, pour en avoir plusieurs on sépare les attributs par une virgule.
SELECT name , capital FROM country
Pour les avoir tous on les désigne par une étoile.
SELECT * FROM country
À faire : tester ces différentes requêtes.
Méthode : Filtrage des données
Si par exemple on souhaite afficher la capitale du Kiribati, on utilisera le mot-clé WHERE qui permet de filtrer les données qui répondent à un critère de sélection :
SELECT capital FROM country WHERE name = 'Kiribati'
À faire : Vérifier que la capitale du Kiribati est Tarawa.
Il se peut que certains attributs d'un enregistrement soient manquants ; dans ce cas la valeur de cet attribut est NULL.
Par exemple, dans la table country un territoire peut ne pas posséder de capitale, pour le connaître on produit la requête :
SELECT name FROM country WHERE capital IS NULL
À faire : Existe t-il un pays sans capitale ?
À faire : Que produit cette requête ?
SELECT name, population FROM country WHERE population < 1000000;
Les conditions utilisables :
= (égal)
<> (pas égal)
!= (pas égal)
> (supérieur)
< (inférieur)
>=
<=
IS NULL ( valeur nulle - sans valeur)
IS NOT NULL
On peut également trier les résultats obtenus, la requête suivante trie les résultat par ordre croissant des noms :
SELECT name FROM country ORDER BY name ASC;
Celle-ci trie la liste par ordre décroissant des noms :
SELECT name FROM country ORDER BY name DESC;
À faire : Que produit cette requête ?
SELECT name,population FROM country ORDER BY population DESC;
Simulation : Exercice 1
Rédiger une requête SQL pour obtenir :
(Noter sur un document numérique la requête et collez une copie d'écran des premiers résultats)
la liste des pays dont la population excède 60 000 000 d'habitants
la même liste triée par ordre alphabétique
la liste de tous les pays et de leurs populations respectives, triée par ordre décroissant de leur superficie
la liste des pays ayant moins de 10 habitants au km² (On peut utiliser comme critère le résultat d'un calcul :
SELECT.....FROM..WHEREpercentage * population >1000)
Jointures
L'intérêt d'une base de données réside en particulier dans la possibilité de croiser des informations présentes dans plusieurs tables par l'intermédiaire d'une jointure.
Dans la base de données qui nous utilisons, on trouve une table nommée encompasses qui possède trois attributs : Country Continent Percentage ,le premier attribut est le code du pays, le deuxième le nom du continent et le dernier la portion du pays présente sur ce continent. La clé primaire de cette table est le couple (Country;Continent), et la valeur du troisième argument ne peut pas être nulle.
La table encompasses possède un attribut en commun avec la table country : l'attribut country de la table encompasses est identique à l'attribut code de la table country et va nous permettre par son intermédiaire de croiser les informations de ces deux tables.
Le croisement de deux tables, revient à créer une table virtuelle contenant les informations des deux tables qui possède huit attributs :
Méthode : Réaliser une jointure entre deux tables
Le principe d'une requête de jointure est : SELECT * FROM table1 JOIN table2 ON expression_logique
Par exemple cette requête affiche tous les attributs de la jointure des tables country et encompasses dont une fraction au moins est en Europe.
SELECT * FROM country JOIN encompasses ON country.code = encompasses.country WHERE encompasses.continent = 'Europe';
On peut l'écrire sur plusieurs ligne pour en faciliter la lecture...
SELECT * FROM country
JOIN encompasses ON country.code = encompasses.country
WHERE encompasses.continent = 'Europe';
Et celle-ci n'affiche que la liste des pays avec les mêmes contraintes :
SELECT country.name FROM country
JOIN encompasses ON country.code = encompasses.country
WHERE encompasses.continent = 'Europe';
À faire : Que produit cette requête ?
SELECT * FROM country
JOIN encompasses ON country.code = encompasses.country
WHERE encompasses.continent = 'Europe' AND encompasses.percentage < 100;
Simulation : Exercice 2
Rédiger une requête SQL pour obtenir :
le nom des pays qui sont à cheval sur plusieurs continents
les pays du continent américain (Amérique du nord et Amérique du sud) qui comptent moins de 10 habitants par km²
Complément : Une jointure de 3 tables
Dans la base de données figure une table nommée city qui possède les attributs suivants : Name Country Province Population Longitude Latitude
Nous allons déterminer les capitales européennes situées à une latitude supérieure à 60, pour cela nous allons réaliser une jointure des trois tables country, city et encompasses
Voici la requête :
SELECT country.name,country.capital FROM country
JOIN city ON country.code = city.country
JOIN encompasses ON country.code = encompasses.country
where encompasses.continent='Europe' AND city.name = country.capital AND city.latitude > 60
Simulation : Exercice 3
La table SPOKEN possède les attributs suivants : Country Language Percentage
L'attribut Country est le code du pays, Language le nom d'une langue parlée dans celui-ci, et Percentage la proportion d'habitants dont c'est la langue maternelle.
Rédiger une requête SQL pour obtenir :
Le nombre d'habitants de la ville de Limoges
les pays du monde où l'on parle français (même partiellement)
les langues parlées par moins de 30 000 personnes dans le monde et leur pays respectif
la liste ordonnée pour le continent africain des langues utilisées et les pays concernés, on précisera aussi pour chacune d'entre elles le nombres de personnes qui la parlent dans chaque pays.
Complément : A faire
Dans la BDR mondial se trouve une table economy qui possède les attributs suivants : Country (le code du pays), GDP (le PIB, en millions de dollars), agriculture (la part de l'agriculture dans le PIB, en pourcentage), Service (la part des services dans le PIB), Industry (la part de l'industrie dans le PIB), Inflation (le taux d'inflation) et Unemployment (le taux de chômage).
Par exemple, la requête suivante affiche les pays majoritairement agricole.
SELECT country.name, economy.agriculture FROM country
JOIN economy ON country.code = economy.country
where economy.agriculture>economy.industry AND economy.agriculture > economy.service;
Imaginez une requête qui utilise cette nouvelle table, et réalisez-la.