Objectifs: Réviser le langage de requête SQL, prise en main d'un SGBD.

L’objectif de ce TME est de savoir formuler des requêtes SQL avancées. Les notions principales à approfondir sont les jointures, les regroupements avec agrégation, les requêtes imbriquées, les négations. On utilise la base Mondial et il est important de connaitre le schéma de cette base avant la séance. Pour cela lire attentivement les 3 documents :

• la description détaillée du schéma,
• le diagramme Entité-Association
• et les références (clé étrangère vers clé primaire)

Vérifier que java est installé

   java –version 

sinon télécharger le dernier jdk java (ou la jre java 8 ) et l’installer.

Seulement si nécessaire, compléter le PATH Linux :

   export PATH=${PATH}:chemin vers le dossier bin contenant java

Windows dans une invite de commande :

   set PATH=%PATH%;chemin vers le dossier contenant java.exe

Il y a 2 outils distincts : un SGBD et une interface d’accès au SGBD :

• Le SGBD H2. Voir aussi le manuel H2 en anglais. Télécharger h2-1.4.200.jar dans votre dossier de travail. Optionnellement, vous pouvez utiliser wget pour le téléchargement :

wget https://h2database.com/h2-2019-10-14.zip
unzip h2-2019-10-14.zip h2/bin/h2*.jar
mv h2/bin/*.jar .
rm h2*.zip

• L’interface graphique SQLWorkbench. Télécharger sqlworkbench.jar dans votre dossier de travail. Optionnellement, vous pouvez utiliser wget pour le téléchargement :

wget https://nuage.lip6.fr/s/Pa2QTxx9My6E7AA/download -O sqlworkbench.jar

La base Mondial est déjà prête à l’emploi dans le fichier tpmondial.mv.db à télécharger dans votre dossier de travail. Noter l’emplacement absolu du dossier contenant le fichier tpmondial.mv.db dans votre machine car vous en aurez besoin pour vous connecter à la base.Exemple :

• Sous linux: ~/TP1
• Ou sous Windows C:\Users\PRENOM\Documents\TP1

Télécharger aussi l’exemple de requêtes tpmondial-exemple-requete.sql dans votre dossier de travail.

On démarre le SGBD H2 en mode “serveur”. Ouvrir un terminal et aller dans votre dossier de travail. On utilise le port 9093

cd votre_dossier_de_travail
java -cp h2-1.4.200.jar org.h2.tools.Server -tcp -tcpPort 9093 &

Vérifier que le message suivant s’affiche :

TCP server running at  tcp://127.0.1.1:9093 (only local connections)

Vérifier que le serveur H2 est démarré : afficher un processus nommé h2 :

ps -u | grep h2 | grep Server

cela affiche java -cp h2-1.4.200.jar org.h2.tools.Server -tcp -tcpPort 9093

On démarre SQLWorkbench pour accéder au SGBD H2. Pour cela, ouvrir un terminal et aller dans votre dossier de travail :

cd votre_dossier_de_travail
java -jar sqlworkbench.jar

Compléter les champs suivants dans le profil de connexion : Driver: sélectionner H2 Database Engine puis Cliquer sur Manage Drivers (ou Edit the driver definition) et sélectionner H2 database Engine dans le panneau de gauche et cliquer sur le bouton avec une icône de dossier pour indiquer l’emplacement du fichier h2-1.4.200.jar

Revenir dans la fenêtre « Select Connection profile »

Remplacer la valeur du champ URL par ceci pour charger la base existante tpmondial. Attention ne pas indiquer le suffixe .mv.db du fichier de la base. Exemples d’URL :

 jdbc:h2:tcp://localhost:9093/~/chemin_vers_le_dossier_contenant_tpmondial/tpmondial

Permet de charger la base se trouvant dans le fichier tpmondial.mv.db (emplacement absolu du dossier contenant le fichier tpmondial.mv.db dans votre machine).

Sous windows, cela charge la base tpmondial.mv.db se trouvant dans le dossier %USERPROFILE%\TP1 de l’utilisateur, avec USERPROFILE étant le dossier principal de l'utilisateur. Il est aussi possible de remplacer le tilde par un point pour indiquer un sous-dossier du dossier depuis lequel sqlworkbench a été lancé.

Puis compléter le champ Username pour indiquer que la base Mondial a été créée par l’utilisateur nommé etu :

• Username: etu
• Le champ Password reste vide.

Cocher la case Autocommit

Commencer par ouvrir l’onglet Database Explorer dans le menu Tools → Show Database Explorer. Database Explorer permet de consulter les tables existantes et leur schéma. Il permet aussi d’afficher le contenu des tables. Cliquer sur le nom d’une table et choisir dans le panneau de droite le sous-onglet Data.

Ouvrir un onglet avec les exemples de requêtes :

File → Open et sélectionner le fichier tpmondial-exemple-requete.sql dans votre dossier de travail. Cela permet de saisir des instructions SQL et de les envoyer au SGBD. La fenêtre est divisée en 2 parties : la partie supérieure sert à éditer les instructions SQL, la partie inférieure affiche le résultat des instructions. Pendant le TP, on interagit avec le SGBD uniquement au moyen d’instructions SQL, l’objectif est de progresser en SQL, pas de maîtriser toutes les fonctionnalités de l’outil SQLWorkbench.

Penser à bien séparer chaque instruction SQL par un point-virgule. Il y a deux possibilités pour exécuter une requête :

• Sélectionner les lignes de la requête puis Ctrl-E pour exécuter la partie de texte sélectionnée.
• Placer le curseur au début d’une ligne et faire Ctrl-Entrée. Cela envoie au SGBD toute la partie de texte depuis le précédent point-virgule jusqu’au point virgule suivant.

Possibilité d’ouvrir plusieurs onglets (View → Add Tab, Save Tab).

Continuez les requêtes du TD numérotées 10 à 20 sur la base MONDIAL, puis proposez vos propres requêtes.

• Vérifier vos réponses en consultant la table solution correspondant à chaque requête R1 à R20.

SELECT * FROM R1;
......
SELECT * FROM R20;

Rappel des requêtes n°7 à 20 du TD :

Aide pour requête n°14: les affluents des affluents des affluents du Nil n’ont aucun affluent.

 7. Pour chaque pays, le nombre de voisins
 8. Pour chaque pays, la population totale de ses voisins
 9. Pour chaque pays d’Europe, la population totale de ses voisins
 10. Les organisations, avec le nombre de membres et pop totale.
 11. Les organisations regroupant plus de 100 pays, avec le nombre de membre et pop totale
 12. Les pays d’Amérique avec leur plus haute montagne
 13. Les affluents directs du Nil : tous les fleuves qui se jettent dans le Nil.
 14. Tous les affluents du Nil : ceux qui s’écoulent directement ou indirectement dans le Nil. Remarque : les affluents des affluents des affluents du Nil n’ont aucun affluent.
 15. La longueur totale des cours d’eau alimentant le Nil, Nil inclus.
 16. a) La plus grande organisation en termes de nombre pays membre
 16. b) Les 3 plus grandes organisations en termes de nombre pays membre
 17. La densité de population (exprimée en nombre d’habitants par km2) de la zone formée de l’Algérie et la Lybie ainsi que de tous leurs voisins directs.
 18. Idem mais en enlevant tous les déserts de la zone en question.
 19. Le pourcentage de croyants de chaque religion dans la population mondiale
 20. Les couples de pays européens ayant exactement accès aux mêmes mers

Oui, avec les instructions SQL insert, update et delete. Faire un

 commit; 

à la fin de vos instructions pour que les données soient visibles des autres applis qui interrogent la base.

Ouvrir un terminal:

 cd votre_dossier_de_travail
 java -cp h2-1.4.200.jar org.h2.tools.Server -tcpShutdown tcp://localhost:9093

Dans un terminal, afficher un processus nommé h2 :

 ps -u | grep h2 | grep Server

Voir les indications complémentaires TP BD sous windows et les adapter à votre cas.

Les instructions SQL pour consulter le schéma d’une base sans utiliser Database Explorer :

 show tables;        - affiche le nom des tables 
 show columns from matable;         -affiche le schéma relationnel de matable.

Oui, pour cela il faut éteindre H2 puis le relancer avec l’option -ifNotExists

 java -cp h2-1.4.200.jar org.h2.tools.Server -ifNotExists -tcp -tcpPort 9093 &

La commande wget est bloquée car les connexions sortantes ne sont pas autorisée. Il faut préciser de passer par le proxy en ajoutant les options use_proxy et https_proxy :

wget use_proxy=on -e https_proxy=proxy:3128 url_du_fichier_à_télécharger