L'objectif de cette activité est de découvrir rapidement l'utilisation du logiciel UwAmp afin de créer ou d'accéder à une base de données et de la modifier en écrivant des requêtes SQL.

	Le logiciel UwAmp
	Création manuelle d'une base de données
	Modification des données
	Jointure entre deux tables
	Clé primaire et clé étrangère
	Importation des données dans UwAmp
	La base de données Gymnase2000
	Accès à une base de données en Python

UwAmp est un logiciel permettant la création rapide d'un serveur web ou d'un serveur de base de données sous Windows.

Télécharger UwAmp

Icône du logiciel UwAmp

Pour lancer le serveur de base de données il faut cliquer sur le bouton SQLite au lancement de UwAmp :

Le SGBD (Système de Gestion de Bases de Données) s'ouvre et permet de créer, modifier ou interroger une base de données :

Nous allons créer une nouvelle base de données contenant des informations sur des personnes.

La base de données s'appellera information.db et contient une seule table nommée personne.

La table personne contiendra 18 enregistrements sur les 18 personnes suivantes en précisant leur nom, leur prénom et leur age :

Pour créer une nouvelle base de données il faut cliquer sur New Database dans le menu File de UwAmp puis indiquer le nom d'un nouveau fichier. On peut ensuite créer les tables et ajouter des enregistrements en lançant des requêtes SQL dans l'onglet Execute SQL.

Créez la nouvelle base de données en cliquant sur New Database dans le menu File d'UwAmp, et enregistrez-la sous le nom information.db dans votre répertoire de travail.

Remarque : cliquez sur le bouton Cancel dans la boîte de dialogue Create Table afin de ne pas créer ici une nouvelle table (la table sera créée manuellement avec les commande SQL)

Nous allons créer une table personne contenant à 3 champs :

• nom est prenom sont des chaînes de caractères (de type char(20) en SQL)
• age est un nombre entier (de type int en SQL)

Pour cela il faut exécuter la ligne de commande SQL suivante dans l'onglet Execute SQL :

CREATE TABLE personne (nom char(20),prenom char(20),age int);

Création d'une table personne contenant 3 attributs

L'onglet Database Structure permet de voir la structure de la base de données (nom et type des attributs de chacune des tables s'il y a plusieurs tables). On y retrouve les 3 champs de la table personne ainsi que leur type :

L'onglet Browse Data permet de consulter les données entrées dans chacune des tables :

Pour l'instant la table personne est vide

Ajoutons un nouvel enregistrement dans la table personne.

Pour cela il faut exécuter la ligne de commande SQL suivante dans l'onglet Execute SQL :

INSERT INTO personne VALUES ('DUPONT','Virginie',18);

Ajout d'un nouvel enregistrement dans la table personne

La table personne contient maintenant 1 enregistrement. Pour voir le contenu de la table il faut retourner dans l'onglet Browse Data:

La table personne contient maintenant 1 enregistrement

Ajoutez les 3 enregistrements suivants en exécutant manuellement une par une les commandes INSERT INTO suivantes dans l'onglet Execute SQL :

INSERT INTO personne VALUES ('MARTIN','Pierre',17);
INSERT INTO personne VALUES ('BERNARD','Caroline',25);
INSERT INTO personne VALUES ('THOMAS','Roland',31);

La table personne contient alors 4 enregistrements :

La table personne contient maintenant 4 enregistrements

Remarque : l'onglet Execute SQL d'UwAmp permet d'exécuter une seule ligne SQL à la fois.

Mais alors comment ajouter plusieurs enregistrements à la fois dans la table ?

Voici les 14 derniers enregistrements à ajouter dans la table personne :

INSERT INTO personne VALUES ('PETIT','Oscar',16);
INSERT INTO personne VALUES ('ROBERT','Paul',11);
INSERT INTO personne VALUES ('RICHARD','Manon',24);
INSERT INTO personne VALUES ('DUBOIS','Maxence',22);
INSERT INTO personne VALUES ('LOPEZ','Jean',43);
INSERT INTO personne VALUES ('DURAND','Clément',36);
INSERT INTO personne VALUES ('HUBERT','Stella',29);
INSERT INTO personne VALUES ('DUPUIS','Simon',33);
INSERT INTO personne VALUES ('MOULIN','Fanny',19);
INSERT INTO personne VALUES ('BOUCHER','Antoine',28);
INSERT INTO personne VALUES ('POIRIER','Florent',26);
INSERT INTO personne VALUES ('PARIS','Josiane',15);
INSERT INTO personne VALUES ('FLEURY','Gaspard',41);
INSERT INTO personne VALUES ('FONTAINE','Xavier',21);

Pour insérer plusieurs enregistrements d'un coup il faut :

1 - enregistrer toutes les lignes SQL commençant par INSERT INTO dans un fichier texte portant l'extension .SQL (chaque ligne finissant par un point-virgule)
2 - cliquer dans le menu File + Import + DataBase from SQL file et choisir le fichier SQL contenant les lignes à exécuter
3 - répondre Non à la question "Voulez-vous créer une nouvelle base de données" afin d'insérer les nouveaux enregistrements dans la base de données courante

Importez les 14 enregistrements manquant afin que votre table personne soit complète et contienne les 18 enregistrements :

Voici ces 18 enregistrements au format CSV :

"nom","prenom","age"
"DUPONT","Virginie","18"
"MARTIN","Pierre","17"
"BERNARD","Caroline","25"
"THOMAS","Roland","31"
"PETIT","Oscar","16"
"ROBERT","Paul","11"
"RICHARD","Manon","24"
"DUBOIS","Maxence","22"
"LOPEZ","Jean","43"
"DURAND","Clément","36"
"HUBERT","Stella","29"
"DUPUIS","Simon","33"
"MOULIN","Fanny","19"
"BOUCHER","Antoine","28"
"POIRIER","Florent","26"
"PARIS","Josiane","15"
"FLEURY","Gaspard","41"
"FONTAINE","Xavier","21"

Pour insérer plusieurs enregistrements à partir d'un fichier CSV il faut :

1 - enregistrer tous les enregistrement dans un fichier texte portant l'extension .CSV (les champs étant séparés par une virgule)
2 - la première ligne du fichier CSV contient le nom des colonnes dans la table
3 - cliquer dans le menu File + Import + Table from CSV file et choisir le fichier CSV contenant les enregistrements
4 - préciser le nom de la table à créer (exemple : personne). ATTENTION : cette table ne doit pas encore exister
5 - cocher la case "Extract field names from first line" afin d'extraire les noms des colonnes à partir de la première ligne du fichier CSV

Remarque :

• si on crée une table en important les enregistrements à partir d'un fichier CSV, les colonnes ne sont pas typées (pas de distinction entre les chaînes de caractères et les entiers : elles sont consitérées comme du simple texte)
• le type des colonnes (char(20) ou int par exemple) se définissent à la création de la table avec l'instruction CREATE TABLE
• une fois la table créée, le type des colonnes est définitif et il est impossible de le modifier sous SQLite

Conclusion : il vaut mieux préférer le format SQL au format CSV pour l'importation des données dans une table

Et voici le résultat de l'exportation de la base de données complète au format SQL :

BEGIN TRANSACTION;
CREATE TABLE personne (nom char(20),prenom char(20),age int);
INSERT INTO personne VALUES('DUPONT','Virginie',18);
INSERT INTO personne VALUES('MARTIN','Pierre',17);
INSERT INTO personne VALUES('BERNARD','Caroline',25);
INSERT INTO personne VALUES('THOMAS','Roland',31);
INSERT INTO personne VALUES('PETIT','Oscar',16);
INSERT INTO personne VALUES('ROBERT','Paul',11);
INSERT INTO personne VALUES('RICHARD','Manon',24);
INSERT INTO personne VALUES('DUBOIS','Maxence',22);
INSERT INTO personne VALUES('LOPEZ','Jean',43);
INSERT INTO personne VALUES('DURAND','Clément',36);
INSERT INTO personne VALUES('HUBERT','Stella',29);
INSERT INTO personne VALUES('DUPUIS','Simon',33);
INSERT INTO personne VALUES('MOULIN','Fanny',19);
INSERT INTO personne VALUES('BOUCHER','Antoine',28);
INSERT INTO personne VALUES('POIRIER','Florent',26);
INSERT INTO personne VALUES('PARIS','Josiane',15);
INSERT INTO personne VALUES('FLEURY','Gaspard',41);
INSERT INTO personne VALUES('FONTAINE','Xavier',21);
COMMIT;

Voici le fichier SQL complet permettant de créer et de remplir la table personne en l'important dans UwAmp :

Fichier SQL complet de la table personne

Travail à faire :

1 - ajoutez les 6 personnes suivantes à la table personne qui doit alors contenir 24 enregistrements :

2 - affichez la totalité de la table personne

3 - affichez le nombre d'enregistrement présents dans la table personne

4 - affichez seulement les personnes ayant plus de 20 ans

5 - afficher seulement les personnes dont le nom de famille commence par la lettre D

Voici les nouvelles commandes SQL à noter et à connaître désormais :

• création d'une table avec 3 champs : CREATE TABLE personne (nom char(20), prenom char(20), age int)
• ajout d'un enregistrement dans la table : INSERT INTO personne VALUES ('DUPONT','Alexandre',18)

Une fois que la base de données est créée et qu'elle contient des données il est possible de les modifier.

Travail à faire :

Testez les commandes SQL suivante sur votre table personne contenant les 24 enregistrements :

Modification d'un enregistrement :

UPDATE personne SET age=26 WHERE (nom='BERNARD')

Suppression d'un enregistrement précis :

DELETE FROM personne WHERE (nom='DUPUIS' AND prenom='Simon' AND age=33)

Suppression d'un ou plusieurs enregistrements :

DELETE FROM personne WHERE (age<18);

ATTENTION : si la condition logique passée en paramètre à WHERE concerne plusieurs lignes de la table, alors elles seront toutes détruites définitivement !

Problème des doublons : ajouter à la table personne un nouvel enregistrement identique à la dernière ligne. La table contient alors un doublon (2 lignes identiques mais correspondant à 2 personnes différentes qui ont le même nom, le même prénom et le même age). Peut-on supprimer une seule des 2 lignes identiques ? La notion de clé primaire permettra de solutionner ce problème.

Notre table personne contient pour l'instant seulement 3 champs : nom, prenom et age.

Nous voulons ajouter pour chaque nouvelle personne un numéro de téléphone. Pour cela il faut modifier la structure de la table en lui ajoutant un 4^ème champ qui sera nommé telephone.

Pour ajouter un nouveau champ à la table il faut utiliser la commande SQL ALTER TABLE :

ALTER TABLE personne ADD telephone char(10)

Ajout de nouveaux enregistrements avec le numéro de téléphone dans le 4ème champ :

INSERT INTO personne VALUES('ROUSSEAU','Valérie',35,'0687215465');
INSERT INTO personne VALUES('LUCIEN','Stéphane',44,'0619735582');

Problème : les anciens enregistrements ne possèdent pas de numéro de téléphone. Leur champ "telephone" est NULL (c'est à dire vide).

Affichage de tous les enregistrements n'ayant pas de numéro de téléphone (champ telephone vide, donc NULL) :

SELECT * FROM personne WHERE telephone IS NULL

Affichage de tous les enregistrements possédant un numéro de téléphone (champ telephone renseigné, donc NOT NULL) :

SELECT * FROM personne WHERE telephone IS NOT NULL

Remplissage de tous les champs telephone qui sont NULL :

UPDATE personne SET telephone='06xxxxxxxx' WHERE ( telephone IS NULL)

Modification de tous les champs telephone qui valent '06xxxxxxxx' :

UPDATE personne SET telephone='non renseigné' WHERE ( telephone='06xxxxxxxx')

Remarque : avec SQLite, la commande ALTER TABLE permet de rajouter une nouvelle colonne (un nouveau champ), mais ne permet :

• ni de supprimer une colonne
• ni de renommer une colonne
• ni de changer le type d'une colonne

A retenir : les noms et les types des colonne sont définitifs dès la création de la table avec CREATE TABLE.

Voici les nouvelles commandes SQL à noter et à connaître désormais :

• ajout d'un nouveau champ à une table : ALTER TABLE personne ADD telephone char(10)
• modification d'un enregistrement : UPDATE personne SET age=20 WHERE (nom='DUPONT')
• suppression d'un ou plusieurs enregistrements : DELETE FROM personne WHERE (nom='DUPONT')
• destruction de la table : DROP TABLE personne

Les jointures en SQL permettent d'associer plusieurs tables dans une même requête. Cela permet d'exploiter la puissance des bases de données relationnelles.

Il existe 3 types de jointures :

• la jointure naturelle (NATURAL JOIN) : elle se fait automatiquement selon un attribut de même nom
• la jointure interne (INNER JOIN) : on précise les attributs à joindre dans les 2 tables
• la jointure externe (OUTER JOIN) : elle conserve les lignes sans correspondance (il y a 3 jointures externes gauche, droite ou complète)

Créons une nouvelle base de données contenant 2 tables (vous l'enregistrerez sous le nom jointure.db) :

• une table personne avec 3 champs (nom, prenom, et code_ville donnant le numéro de la ville dans la table ville)
• une table ville avec 2 champs (le code de la ville dans un champ code_ville et le nom de la ville dans le champ ville)

Voici le contenu en clair de ces 2 tables :

Table ville :

Voici le code SQL complet permettant d'importer directement la totalité de cette base de données jointure.db dans UwAmp (création des 2 tables et insertions des enregistrements).

BEGIN TRANSACTION;
CREATE TABLE personne (nom char(20),prenom char(20),code_ville int);
INSERT INTO personne VALUES('DUPONT','Virginie',3);
INSERT INTO personne VALUES('MARTIN','Pierre',2);
INSERT INTO personne VALUES('BERNARD','Caroline',2);
INSERT INTO personne VALUES('THOMAS','Roland',1);
INSERT INTO personne VALUES('PETIT','Oscar',4);
INSERT INTO personne VALUES('ROBERT','Paul',3);
INSERT INTO personne VALUES('RICHARD','Manon',5);
INSERT INTO personne VALUES('DUBOIS','Maxence',4);
INSERT INTO personne VALUES('LOPEZ','Jean',5);
INSERT INTO personne VALUES('DURAND','Clément',1);
CREATE TABLE ville (code_ville int,ville char(20));
INSERT INTO ville VALUES(1,'Toulouse');
INSERT INTO ville VALUES(2,'Strasbourg');
INSERT INTO ville VALUES(3,'Lyon');
INSERT INTO ville VALUES(4,'Bordeaux');
INSERT INTO ville VALUES(5,'Marseille');
COMMIT;

Travail à faire :

Importez la totalité de cette nouvelle base de données dans UwAmp et enregistrez-la dans un fichier nommé jointure.db.

Testez ensuite dans UwAmp les différentes requêtes suivantes.

Affichage de toutes les lignes de la table personne :

SELECT * FROM personne

Affichage de toutes les lignes de la table ville :

SELECT * FROM ville

Jointure naturelle : pour chaque personne on obtient le nom de la ville en plus du code de la ville

SELECT * FROM personne NATURAL JOIN ville;

Jointure naturelle "inversée" (en inversant le nom des tables) :

SELECT * FROM ville NATURAL JOIN personne;

On obtient alors dans l'ordre :

• toutes les personnes habitant la ville 1
• puis toutes les personnes habitant la ville 2
• puis toutes les personnes habitant la ville 3
• puis toutes les personnes habitant la ville 4
• puis toutes les personnes habitant la ville 5

Jointure interne en utilisant le champ code_ville : on obtient le même résultat qu'avec la jointure naturelle

SELECT * FROM personne INNER JOIN ville USING (code_ville);

Jointure externe gauche en utilisant le champ code_ville : on obtient le même résultat qu'avec la jointure naturelle

SELECT * FROM personne LEFT OUTER JOIN ville USING (code_ville);

Jointure externe droite : on inverse le nom des tables dans la jointure externe gauche, et obtient le même résultat qu'avec la jointure naturelle "inversée"

SELECT * FROM ville LEFT OUTER JOIN personne USING (code_ville);

Pour l'instant, tous les codes de ville (entier de 1 à 5) présent dans la table personne existaient dans la table ville. Mais que se passe-t-il si un code de ville dans la table personne n'existe pas dans la table ville ?

Pour cela, commençons par ajouter 2 enregistrements à la table personne, utilisant des code de ville n'existant pas dans la table ville :

INSERT INTO personne VALUES('ROGER','Alexandre',6);
INSERT INTO personne VALUES('VIDAL','Sandrine',7);

Pour ces 2 nouveaux enregistrements il n'y a pas de correspondance avec la table ville (les codes de ville 6 et 7 n'existant pas dans la table ville).

QUESTION : Comment réagit alors la jointure naturelle ?

SELECT * FROM personne NATURAL JOIN ville;

RÉPONSE : On constate que la jointure naturelle n'affiche pas les lignes sans correspondance (ROGER Alexandre et VIDAL Sandrine sont absents du résultat de la jointure).

QUESTION : Comment réagit la jointure interne lorsque certaines ignes n'ont pas de correspondance ?

SELECT * FROM personne INNER JOIN ville USING (code_ville);

RÉPONSE : On obtient le même résultat qu'avec la jointure naturelle (seules les lignes avec correspondance sont affichées)

QUESTION : Comment réagit la jointure externe gauche lorsque certaines lignes n'ont pas de correspondance ?

SELECT * FROM personne LEFT OUTER JOIN ville USING (code_ville);

RÉPONSE : cette fois les lignes sans correspondance sont affichées (avec un champ vide dans la nouvelle colonne pour les lignes sans correspondance)

Remarque : la seule jointure externe implémentée dans UwAmp est la jointure externe GAUCHE :

SELECT * FROM table1 LEFT OUTER JOIN table2 USING (nom)

Les jointures externes droite et complète ne sont pas implémentées (indiqué par le message d'erreur "RIGHT and FULL OUTER JOINs are not currently supported") 

Pour réaliser une jointure externe droite il suffit d'inverser l'ordre des tables dans la jointure externe gauche :

SELECT * FROM table2 LEFT OUTER JOIN table1 USING (nom)

Si on utilise le mot clé JOIN dans une requête SQL sans préciser le type de jointure à faire (NATURAL, INNER ou OUTER) quel type de jointure est alors réalisée ? Testez ce cas et retenez la réponse :

SELECT * FROM personne JOIN ville

Que se passe-t-il si on réalise une jointure sur deux tables n'ayant aucun attribut commun ? Testez ce cas et retenez la réponse.

Que se passe-t-il si on réalise une jointure sur deux tables ayant plusieurs attributs communs ? Testez ce cas et retenez la réponse.

Comment réaliser une jointure externe complète en utilisant l'UNION et sachant que FULL OUTER JOIN ne fonctionne pas ?

Remarque complémentaire concernant la différence entre une jointure externe complète et une union :

Les jointures (JOIN) s'appuient sur des clés pour établir des relations, ce qui les rend essentielles pour les bases de données normalisées où les données sont réparties dans plusieurs tables. En revanche, l'opérateur UNION exige que les requêtes comportent le même nombre de colonnes, avec des types de données compatibles, et supprime généralement les lignes dupliquées, sauf si l'opérateur UNION ALL est utilisé.

Il existe deux types de clés :

• les clés primaires qui servent à identifier une ligne de manière unique
• les clés étrangères qui permettent de gérer des relations entre plusieurs tables, et garantissent la cohérence des données

Les différentes liaisons clé primaire/clé étrangère confère à la base de données une structure relationnelle.

Par définition une clé primaire est unique dans la table. La clé primaire garantit que chaque enregistrement d'une table est différent des autres : il est alors impossible qu'il y ait des doublons dans une table.

Dans la base de données information.db nous allons créer une nouvelle table nommée eleves et contenant le nom et le prénom des élèves inscrits dans un lycée. Cette nouvelle table contiendra 3 champs :

• un champs identifiant, unique pour chaque élève, et qui sera une clé primaire
• un champs nom (chaîne de caractères de type char(20))
• un champs prenom (chaîne de caractères de type char(20))

Le champs identifiant est obligatoire (il ne doit pas ête NULL) et il sera de type INT (nombres entiers commençant à 1).

Voici le contenu de cette table eleves :

Travail à faire :

Dans la base de données information.db créez cette nouvelle table eleves en précisant que le champs identifiant est une clé primaire :

CREATE TABLE eleves (identifiant INT NOT NULL PRIMARY KEY, nom char(20),prenom char(20));

Ajoutez chacun des 10 enregistrements ci-dessus dans la table eleves :

INSERT INTO eleves VALUES(1,'DUPONT','Virginie');
INSERT INTO eleves VALUES(2,'MARTIN','Pierre');
etc.

Affichez la table eleves complète et vérifiez qu'elle contient bien 10 enregistrements.

Que se passes-t-il si on ajoute un enregistrement contenant une clé primaire qui existe déjà dans la table ? Testez ce cas et retenez la réponse. Exemple :

INSERT INTO eleves VALUES(2,'CHEVALIER','Laurent');

Un nouvel élève de nom DURANT et de prénom Clément doit être inscrit au lycée. Or il y a déjà un élève DURANT Clément (d'identifiant 10) dans la table eleves.

Ajoutez le nouvel élèves DURANT Clément à la table eleve. Quel est alors l'identifiant de ce nouvel élève ?

La table eleve contient-elle des doublons, c'est à dire 2 lignes strictement identiques ?

Qu'est-ce qui distingue les deux élèves DURANT Clément dans la table eleves ?

L'ancien élève DURANT Clément (d'identifiant 10) a déménagé et doit être désinscrit du lycée : supprimez son enregistrement dans la table eleves sans suppimer l'enregistrement du nouvel élève DURANT Clément.

A retenir : une clé primaire interdit des doublons dans une table, et permet ici de distinguer 2 personnes ayant le même nom et le même prénom.

Une clé étrangère est un champs permettant de faire référence à une clé primaire dans une autre table.

Nous avons une table eleves contenant le nom des élèves du lycée. Nous allons mainenant créer une nouvelle table nommée specialite et contenant la composition des groupes de spécialité. Mais dans cette nouvelle table nous n'allons pas répéter le nom des élèves : on va simplement faire référence à leur identifiant dans la table eleves grâce à une clé étrangère.

Voici une exemple de contenu de la table specialite :

• elle contient 2 champs : identifiant et matiere
• le champs matiere de type chaîne de caractère contient le nom du groupe de la spécialité
• le champs identifiant est une clé étrangère pointant vers le champs identifiant de la table eleves

Créez cette nouvelle table specialite en précisant que le champs identifiant est une clé étrangère pointant vers le champs identifiant de la table eleves :

CREATE TABLE specialite (identifiant INT NOT NULL REFERENCES eleves, matiere char(20));

Insérez quelques lignes dans la table specialite pour créer les différents groupes de spécialité du lycée. Un élève donné pourra suivre jusqu'à 3 spécialités différentes. Exemple à compléter à loisir :

INSERT INTO specialite VALUES(2,'NSI');
INSERT INTO specialite VALUES(4,'NSI');
INSERT INTO specialite VALUES(2,'mathématiques');
INSERT INTO specialite VALUES(5,'mathématiques');
INSERT INTO specialite VALUES(3,'physique');
INSERT INTO specialite VALUES(2,'physique');

Pour afficher les spécialités de chacun des élèves on va faire une jointure naturelle : l'attribut commun entre les tables eleves et specialite étant identifiant il sera naturellement utilisé pour la jointure pour lier les deux tables. Testez, observez et retenez le résultat de la requête SQL suivante :

SELECT * FROM eleves NATURAL JOIN specialite;

On peut inverser l'ordre des tables dans la jointure en fonction des résultats recherchés. Testez, observez et retenez le résultat de la requête SQL suivante :

SELECT * FROM specialite NATURAL JOIN eleves;

Pour mettre un peu d'ordre on peut trier les lignes obtenues selon l'attribut identifiant. Testez, observez et retenez le résultat de la requête SQL suivante :

SELECT * FROM eleves NATURAL JOIN specialite ORDER BY identifiant;

Enfin on peut ordonner les lignes de la jointure inversée en fonction des élèves. Testez, observez et retenez le résultat de la requête SQL suivante :

SELECT * FROM specialite NATURAL JOIN eleves ORDER BY identifiant;

Remarque à retenir concernant les clés primaire et étrangère :

• dans la table eleves la clé primaire identifiant est unique (car chaque élève est unique) : ceci interdit les doublons dans la table
• dans la table specialite la clé étrangère identifiant peut avoir plusieurs fois la même valeur (car un élève peut suivre plusieurs spécialités)
• la clé primaire et la clé étrangère ont le même nom dans les deux tables (ici identifiant)

Remarque sur l'affichage des informations concernant la structure d'une table :

La commande SQL PRAGMA TABLE_INFO(ma_table) renvoie 5 colonnes :

• colonne 1 : index des attributs
• colonne 2 : nom des attributs
• colonne 3 : type des attributs
• colonne 4 : précise si un attributs est NOT NULL ou pas
• colonne 5 : précise si un attribut est une clé primaire ou pas

Testez la commande PRAGMA TABLE_INFO sur chacune des 3 tables de votre base de données information.db et observez les résultats obtenus :

• la table personne
• la table eleves
• la table specialite

Voici les nouvelles commandes SQL à noter et à connaître désormais :

• création d'une table avec une clé primaire : CREATE TABLE personne (id INT NOT NULL PRIMARY KEY,nom char(20), prenom char(20))
• ajout d'un enregistrement contenant la clé primaire : INSERT INTO personne VALUES (1,'JEAN','Pierre')
• création d'une table avec une clé étrangère : CREATE TABLE specialite (ideleves INT NOT NULL REFERENCES eleves, matiere char(20));
• affichage des informations sur une table : PRAGMA TABLE_INFO(ma_table)

Pour importer ou exporter les données d'une seule table on utilise le format de fichier CSV.

Pour importer ou exporter une bases de données complète on utilise le format SQL.

Importation des données d'une table au format CSV :

Exportation des données d'une table au format CSV :

Importation d'une base de données compète au format SQL :

Exportation d'une base de données compète au format SQL :

Dans cette activité, nous utilisons la base de données Gymnase2000, contenant les informations d'un petit club sportif réparties dans 7 tables différentes.

Schéma de la base de données Gymnase2000

Voici les fichiers de la base de données Gymnase2000 (contenant 7 tables) prêts à être importer dans UwAmp :

	Arbitrer.csv
	Entrainer.csv
	Gymnases.csv
	Jouer.csv
	Seances.csv
	Sportifs.csv
	Sports.csv
	Structure des 7 tables de la base Gymnase2000
	Fichier SQL complet de la base Gymnase2000

Voici les 7 tables de la base de données Gymnase2000 :

• Sports : sports pratiqués
• Sportifs : sportifs inscrits
• Gymnases : gymnases utilisés
• Seances : séances prévues (pour un sport, et entraînées chacune par un sportif)
• Jouer : qui joue à quoi ?
• Arbitrer : qui arbitre quoi ?
• Entrainer : qui entraîne quoi ?

Dans la table Seances et dans la table Entrainer, l'identifiant des sportifs entraîneurs est nommé IdSportifEntraineur. Et il faut faire attention aux minuscules/majuscules dans les différents champs de type texte. Si un sportif a un conseiller éventuellement, l'identifiant de celui-ci est indiqué dans IdSportifConseiller (NULL sinon).

En consultant la structure des 7 tables (cliquez ci-dessus sur le lien "Structure des 7 tables de la base Gymnase2000") repérer les clés primaires (PRIMARY KEY), les clés étrangères (REFERENCES), et représentez graphiquement sur le papier le schéma relationnel des 7 tables de la base de données Gymnase2000.

Travail à faire :

Téléchargez le fichier SQL correspondant à la base de données Gymnase2000 complète, importez-la dans UwAmp puis répondez aux questions suivantes en interrogeant la base de données dans UwAmp à l'aide de requêtes SQL.

En utilisant l'éditeur Notepad++ vous conserverez vos réponses et les requêtes SQL correspondantes dans un fichier texte nommé gymnase2000.txt sans oublier de préciser les questions initiales.

Voici la structure que doit suivre votre fichier texte gymnase2000.txt (chaque requête SQL est pécédées de la question à laquelle elle répond) :

=======================================================
Ficher texte gymnase2000.txt
Requêtes SQL sur la base de données Gymnase 2000
=======================================================

Requêtage simple

--------------------------------------------------------------------------------------

Simple

Lister le contenu de la table Seances :

SELECT * FROM Seances;

Lister les sportifs par ordre croissant d'âge :

SELECT * FROM Sportifs ORDER BY Age;

Lister les 5 gymnases les plus grands :

--------------------------------------------------------------------------------------

Restriction

Lister les sportifs (nom et prénom) agés strictement de plus de 30 ans :

etc.

Requêtage simple

Simple

1. Lister le contenu de la table Seances
2. Lister les sportifs par ordre croissant d'âge
3. Lister les 5 gymnases les plus grands

Restriction

1. Lister les sportifs (nom et prénom) agés strictement de plus de 30 ans
2. Lister les gymnases de la ville de "STAINS"
3. Lister les sportifs n'ayant pas de conseiller

Projection

1. Lister les sports pratiqués (uniquement le libellé de chaque sport)
2. Lister les différentes valeurs de Sexe possible

Calculs et fonctions

Calculs arithmétiques

1. Afficher en heure la durée de chaque séance (stockée en minute dans la table)
2. Convertir la surface (en m² dans la table) en pieds carrés des gymnases de "Pierrefitte"
   Remarque : le pied vaut exactement 0.3048 m, donc 1 pied carré vaut 0.3048² m² soit 0.09290304 m²)

Fonctions sur chaînes de caractères

1. Concaténer le nom des sportifs avec la première lettre du prénom suivie d'un point, le tout en minuscules (par exemple "jollois f.")
2. Afficher les gymnases situées sur une place (voir Adresse)

Fonctions sur les dates

1. Donner la date du jour
2. Donner le jour de la semaine du 1er janvier de l'année de naissance de chaque sportif

Traitement conditionnel

1. Afficher une nouvelle variable nommée Civilite, qui prendra "M." pour les hommes et "Mme" pour les femmes
2. Afficher une nouvelle variable TypeGymnase, qui prendra comme valeur "petit" si la surface est inférieure strictement à 400 m2, "moyen" si elle est entre 400 et 550 m2, et "grand" si elle est strictement supérieure à 550 m2

Agrégats

Dénombrements

1. Compter le nombre de sportifs
2. Compter le nombre de sportifs ayant un conseiller
3. Compter le nombre de villes distinctes

Calculs statistiques simples

1. Calculer la surface moyenne des gymnases
2. Calculer l'âge moyen, l'âge minimum et l'âge maximum des sportifs

Agrégats selon attribut(s)

1. Calculer le nombre de sportifs par sexe, ainsi que l'âge moyen
2. Calculer pour chaque ville la surface du plus petit et du plus grand gymnase

Restriction sur agrégats

1. Lister les villes ayant plus de 5 gymnases, dans l'ordre décroissant du nombre de gymnases

Exercices complémentaires

1. Lister les 5 sportifs masculins les plus âgés
2. Lister les villes dans lesquelles il y a des gymnases d'au moins 500 m2
3. Concaténer le nom des sportifs avec la première lettre du prénom suivie d'un point, en tenant compte des prénoms composés, suivi de l'année de naissance (par exemple "JOLLOIS F.-X. - 1977")
4. Lister les identifiants de sports ayant le plus de joueur
5. Donner le nombre de sportifs pour la répartition "junior" (de 20 à 24 ans), "senior 1" (de 25 à 30), "senior 2" (de 31 à 45)
6. Sachant des les heures de début de séances sont stockées en réel, avec 19.3 pour "19h30" par exemple, calculer l'heure de fin de chaque séance

Jointures

Nous allons maintenant utiliser toutes les notions vues pendant le cours :

• requêtage simple
• calculs et fonctions
• agrégat
• jointure
• sous-requête

Les demandes sont classées en trois catégories :

• simple
• moins simple
• complexe

Vous devez au minimum réussir à faire les premières (Niveau 1), et une partie des deuxièmes (Niveau 2). Dans l'absolu, les troisièmes (Niveau 3) correspondent à des demandes type rencontrables en entreprise.

Niveau 1

1. Quels sont les gymnases de "VILLETANEUSE" ou de "SARCELLES" qui ont une surface de plus de 400 m2 ?
2. Quels sont les sportifs (nom et prénom) qui pratiquent (c'est à dire qui jouent) du hand ball ?
3. Dans quels gymnases et quels jours y a-t'il des séances de hand ball ?
4. Dans quels gymnases peut-on jouer au hockey le mercredi après 15H ?
5. Quels gymnases proposent des séances de basket ball ou de volley ball ?
6. Quels sont les entraineurs qui sont aussi joueurs ?
7. Quels sont les sportifs qui sont des conseillers ?
8. Pour le sportif "Kervadec" quel est le nom de son conseiller ?
9. Quels entraineurs entrainent du hand ball et du basket ball ?
10. Quelle est la moyenne d’âge des sportives qui pratiquent du basket ball ?

Niveau 2

1. Quels sportifs (nom et prénom) ne pratiquent aucun sport ?
2. Pour chaque sportif donner le nombre de sports qu'il arbitre
3. Quels sont les gymnases ayant plus de 15 séances le mercredi ?
4. Dans quels gymnases et quels jours y a-t’il au moins 4 séances de volley ball dans la journée
5. Pour chaque entraîneur de hand ball quel est le nombre de séances journalières qu’il assure ?
6. Pour chaque gymnase de "MONTMORENCY" : quel est le nombre de séances journalières de chaque sport proposé ?
7. Quel est le sport pour lequel les joueurs sont les plus jeunes ?
8. Quels gymnases n'ont pas de séances le dimanche ?

Niveau 3

1. Pour chaque gymnase de "STAINS" donner par jour d’ouverture les horaires des premières et dernières séances
2. Quels entraîneurs n’entraînent que du hand ball et/ou du basket ball, mais aucun autre sport ?
3. Dans une même requête, donner pour chaque sportif (nom et prénom)
   • le nombre de sports qu'il joue
   • le nombre de sports qu'il arbitre
   • le nombre de sports qu'il entraîne
   • on doit avoir tous les sportifs, avec des 0 si besoin
4. Quels sont les couples de sportifs (Identifiant et nom et prénom de chaque sportif) ayant le même âge et le même conseiller ?
5. Quels sont les plus grands gymnases de chaque ville ?
6. Lister pour chaque gymnase et pour chaque jour, l'horaire de fin de la dernière séance

Pour accèder à une base de données en Python il faut utiliser le module SQLite3. Pour cela vous pouvez allez à l'activité suivante :

Utilisation du module sqlite3 de Python pour accéder à une base de données

Réalisé par Jean-Christophe MICHEL

© Janvier 2025