TP n 16 – Bases de données et langage SQL Objectifs du TP

Lycée Jean Bart – PCSI – Année 2013-2014 – 24 & 24 juin 2014
TP n0 16 – Bases de données et langage SQL
Objectifs du TP
•
•
•
•
•
de découvrir la notion de base de données relationnelles.
de découvrir la notion de gestionnaire de bases de données relationnelles.
de découvrir et se familiariser avec le langage SQL.
apprendre à accéder à une base de données, via une application graphique.
faire quelques requêtes élémentaires, créer et alimenter une base de données.
1. Principe de la méthode
En première approximation, on peut considérer qu’une base de données est un “grand tableau contenant beaucoup
d’informations” (des nombres entiers, des chaînes de caractères, des booléens. . . ). Un exemple classique de base de
données est celui donnant la liste des communes de France, avec leurs populations respectives et les départements
auxquelles elles appartiennent. Mais dans un “tableau” de cette dimension (il y a environ 35 000 communes dans
notre pays), difficile d’y voir clair et de repérer rapidement toutes celles dont la population est comprise entre 500
et 1000 habitants, ou toutes celles dont le nom comporte au moins 3 voyelles ∗.
Le rôle d’un gestionnaire de base de données est précisément de faire le “sale boulot” à notre place, et d’effectuer
les recherches qu’il serait très fastidieux de faire à la main. Celui que nous utiliserons s’appelle SQLite, et plus
précisément nous utiliserons son interface graphique plus conviviale nommée SQLiteman, téléchargeable ici :
http ://sqliteman.com/
Les instructions qui permettront de travailler sur les bases de données seront écrites dans un nouveau langage,
que vous connaissez sans doute de nom : le langage SQL. †
Pour faire le parallèle avec une situation plus familière, puisque vous avez déjà travaillé avec les tableurs au lycée :
le gestionnaire de base de données, c’est Excel ; la base de données, c’est le fichier .xls sur lequel vous travaillez ;
les requêtes SQL, ce sont les fonctions d’Excel que vous utilisez pour avoir des informations sur le contenu du
tableur. Ceci étant dit, comme un petit exemple vaut mieux qu’un long discours, passons à la pratique. Indiquons
simplement que les fichiers relatifs à ce TP peuvent comme toujours être téléchargés ici :
http ://www.pcsijbmath.sitew.fr
et qu’un excellent mode d’emploi (“pour les nuls”) des bases de données peut être consulté ici :
http ://www.lycee-pothier.com/LYCEE/pcsi1/file/ipt/TP/tp11/sqlman memento.pdf ‡
∗. L’intérêt de cette seconde question étant purement théorique, je vous l’accorde.
†. SQL comme Structured Query Language.
‡. Le lien est sur le site de la PCSI, inutile de retaper toute cette adresse.
2
PCSI — Année 2013-2014 — Informatique: TP — 24 & 24 juin 2014
2. Initiation au langage SQL
Exercice 1. — Requêtes élémentaires sur une base de données.
1) Sur le site (www.pcsijbmath.sitew.fr), télécharger et enregistrer dans votre dossier personnel le fichier appelé
“tpultime.db” (.db comme Data Base, base de données en Anglais).
2) Lancer SQLiteman et charger la base de données “tpultime.db”. Cette base de données est constituée de deux
tables (nommées joueurs et nations). Chaque ligne de la table “joueurs” correspond à un triplet :
(nom : chaîne de caractères ;
pays : chaîne de caractères ;
buts : nombre entier)
3) Lancer successivement les requêtes SQL suivantes, en réfléchissant à leur signification/objectif et avec un regard
critique sur le résultat produit. Remarque : une requête SQL doit toujours se terminer par un point virgule.
SELECT
SELECT
SELECT
SELECT
COUNT(*) FROM joueurs ;
* FROM joueurs WHERE buts=3 ;
nom, buts FROM joueurs WHERE pays=’Russie’ ;
nom, pays FROM joueurs WHERE buts>=2 ; §
Exercice 2. — Vous avez compris ? Alors en avant pour un test ! A l’aide d’une requête SQL, déterminer :
1) le nombre de joueurs ayant marqué exactement 3 buts ;
2) la liste des joueurs ayant marqué exactement 3 buts ;
3) le nombre de joueurs ayant marqué exactement au moins 3 buts ;
4) le nombre de joueurs Néerlandais ayant marqué au moins 2 buts ;
5) la liste des joueurs Néerlandais ayant marqué au moins 2 buts ;
6) le nombre de joueurs Italiens ayant marqué au moins 2 buts ; ¶
7) le nombre de buts inscrits par Odemwingie.
8) le nombre de buts inscrits par Ronaldo.
Exercice 3. — Vous comprenez toujours ? Nouvelle étape : dans la base de données “tpultime”, il y a une
autre table appelée “nations”. Chaque élément de cette nouvelle table est un couple :
(pays : chaîne de caractères ;
continent : chaîne de caractères)
Vous pouvez obtenir des informations sur cette table aussi aisément que précédemment. Juste pour ne pas perdre
la main, déterminer à l’aide de requêtes SQL :
1) le nombre d’éléments contenus dans la table “nations” ;
2) le nombre de pays Africains contenus dans cette table ;
3) la liste des pays asiatiques qu’elle contient.
§. Vous pouvez aussi tenter SELECT * FROM joueurs WHERE pays=’Coudekerque’ ;
¶. Loooooooooooool !
PCSI — Année 2013-2014 — Informatique: TP — 24 & 24 juin 2014
3
Exercice 4. — Maintenant que vous êtes parfaitement au point sur les requêtes SQL élémentaires, franchissons
un nouveau pas.
1) Exécutez la requête
SELECT joueurs.nom, nations.continent FROM joueurs JOIN nations ON joueurs.pays =
nations.pays ;
et observez avec des yeux émerveillés puis critiques le résultat obtenu.
2) Une fois remis de vos émotions, déterminez la liste des buteurs asiatiques.
3) Déterminez la liste des buteurs européens ayant inscrit au moins deux buts.
4) Déterminez la liste des buteurs européens ayant inscrit au moins deux buts, ainsi que leurs nombres de buts
respectifs.
5) Déterminez le nombre de buteurs africains.
6) Déterminez l’âge du capitaine.
Si vous avez réussi à faire tout cela (sauf éventuellement la dernière question), vous êtes arrivés à mettre en
relation deux tables de la base de données étudiée dans ce TP ; d’où la terminologie de base de données
relationnelles.
Exercice 5. — Sans transition :
1) Donner les noms et les symboles des éléments chimiques appartenant à la famille des métaux alcalins.
2) Donner le nom et les isotopes de l’élément dont le numéro atomique est 59.
3) Donner le numéro atomique, le symbole et les isotopes du zinc.
Remarque : vous pouvez faire cet exercice de mémoire, où vous aider de la base de données chimie.db, téléchargeable sur le site de la PCSI.