Démarrer SQLite Utilisation d`une base de données

MPSI 3 Lycée Carnot - Dijon
TP DÉCOUVERTE DE SQLITE
Démarrer SQLite
En salle de TP, nous allons utiliser SQLite (prononcer SQL-Lite). C’est une application qui
permet de gérer une base de donnée sans utiliser d’architecture client-serveur : tout se passe
sur place et les bases de données sont stockées dans des fichiers sur la machine.
Tout va se passer en ligne de commande. Pour lancer SQLite sur un fichier, il faut utiliser
la commande 1:
sqlite3 -column -header [chemin_eventuel\]nom_fichier.sqlite
L’option -column permet d’afficher les tables sous forme de tableaux, et l’option -header
permet d’afficher des en-têtes de colonnes2.
Si le fichier n’existe pas, il est créé directement.
Le shell de sqlite se présente alors à vous avec l’invite sqlite> . Les commande se
tapent directement : les commandes SQL doivent se terminer par ;et les commande sqlite
commencent par un point. .help permet de connaître les principales. Par exemple, .exit
permet de quitter sqlite, .tables permet de voir toutes les tables de la base, .databases
permet de voir toutes les bases de données connues.
Les commandes SQL peuvent se taper directement (bien finir la ligne par un ;) ou être
importées depuis un fichier grâce à la commande :
sqlite> .read [chemin_eventuel\]nom_fichier.sql
Utilisation d’une base de données
Nous allons créer la base de donnée lycee.sqlite sur le modèle étudier en cours :
taper sqlite3 -column -header lycee.sqlite dans un terminal.
Nous enregistrerons au fur et à mesure les commandes SQL dans un fichier lycee.sql.
On pourra choisir emacs comme éditeur de texte avec coloration syntaxique : taper sim-
plement emacs lycee.sql dans un autre terminal (ou mieux : nouvel onglet avec ctrl +
Maj +T).
Attention les raccourcis habituels ne fonctionnent pas sous emacs. Par exemple, pour
sauver : ctrl +Xpuis ctrl +S.
1. Se déplacer dans son dossier de travail pour éviter les chemins et faciliter les choses
2. Changeable dans sqlite directement avec .header on et .mode column.
TP découverte de SQLite - page 1
Dans emacs comme dans le terminal, les raccourcis habituels pour le copier-coller ne
fonctionnent pas non plus. Vous utiliserez les raccourcis standards unix :
•ctrl +insert : copier, •Maj +insert : coller, •Maj +suppr : couper.
Création des tables
La syntaxe SQL pour créer des tables est la suivante (attention : n’utilisez pas de tabula-
tions mais des espaces !) :
CREATE TABLE eleve (
num_p INTEGER PRIMARY KEY REFERENCES personne(num_p),
num_etudiant INTEGER,
lycee_origine TEXT,
num_classe INTEGER REFERENCES classe(no)
); -- Ne pas oublier le point-virgule !
BSi une clé primaire contient plusieurs attributs, il faut la déclarer à part avec
PRIMARY KEY (att_1, ..., att_n).
Créer, sur le même modèle, les tables personne,eleve,classe,prof et enseigne.
Vérifier avec .tables qu’elles sont bien créées.
Tester la commande .schema
On peut supprimer une table grâce à la commande
DROP TABLE nom_table; /*Bien réfléchir avant de l’utiliser !! */
Peuplement des tables
Pour peupler nos tables, on procède de la manière suivante :
INSERT INTO personne VALUES (6275,’Dupont’,’Pierre’,’0645456789’);
Peupler toutes les tables sur ce même principe.
Vérifier au fur et à mesure avec
SELECT *FROM nom_table;
On peut supprimer des tuples dans une table avec une commande de la forme :
DELETE FROM nom_table WHERE condition;
On peut modifier des tuples dans une table avec une commande de la forme :
UPDATE nom_table SET attr_1 = val_1, ..., attr_n = val_n
WHERE condition;
TP découverte de SQLite - page 2

Interrogation de la base de données
Vous pouvez maintenant utiliser des requêtes SQL pour interroger votre base de donnée.
Par exemple, pour chercher quels sont les noms et prénoms des élèves de MP* :
SELECT p.nom, p.prenom
FROM eleve e JOIN personne p ON e.num_p = p.num_p
JOIN classe c ON e.num_classe = c.no
WHERE c.nom = ’MP*’;
ou encore
SELECT p.nom, p.prenom
FROM eleve e, personne p, classe c
WHERE e.num_p = p.num_p
AND e.num_classe = c.no
AND c.nom = ’MP*’;
ce qui donne :
nom prenom
---------- ----------
Galois Evariste
On précédera toutes les requêtes par leur
intitulé en français placé en commentaire
dans le fichier lycee.sql.
Écrire en SQL les requêtes suivantes.
1. Donner le nom et le numéro de téléphone de toutes les personnes enregistrées et
qui ont bien un numéro de téléphone.
2. Donner le nom et le numéro de téléphone de tous les élèves.
Voici, par exemple, une autre possibilité :
SELECT nom, tel
FROM personne
WHERE num_p IN (SELECT num_p FROM eleve);
3. Donner le nom et le prénom de tous les élèves dont le nom de la classe contient ’SI’.
4. Donner d’au moins trois autres façons différentes le nom et le prénom de tous les
élèves de sup.
5. Y a-t-il une classe dont l’effectif est l’ancienneté d’un de ses professeurs ? Le double ?
Si oui, quel est le nom du professeur?
6. Déterminer les couples d’élèves venant d’un même lycée.
TP découverte de SQLite - page 3
7. Donner le nom et prénom des élèves et des professeurs de MPSI 3.
8. Donner le nom et le prénom des élèves ayant le même prénom qu’un de leurs pro-
fesseurs.
9. Donner le nom des élèves ayant un professeur avec moins de 15 ans d’ancienneté.
10. Donner le nom et la matière des professeurs enseignant dans une classe dont ils ne
sont pas professeur principal.
TP découverte de SQLite - page 4