Exercices pour s'entraîner

Les exercices ci-dessous ont pour but de vous familiariser avec la création et la modification de bases de données en utilisant le langage sql et le logiciel DB Browser.

Rappels

• Chaque base de données doit être sauvegardée sous forme de fichier avec l'extension .db.
• Chaque fichier de création/modification de base doit être sauvegardé sous forme de fichier avec l'extension .sql.
• Enregistrez ces fichiers dans le dossier [B02-BDD_Relationnelle] avec le nom donné à l'exercice : ProgB02.03.db, ProgB02.03.sql, ProgB02.05.sql, etc...

Prog B02.03

On considère la relation Annuaire schématisée ci-contre.

1. Générez une nouvelle base de donnnées avec le logiciel DB Browser puis nommez cette base ProgB02.03.db

2. Dans l'onglet enregistrez un fichier au format SQL sous le nom ProgB02.03.sql.
   Complétez ensuite ce fichier en répondant aux questions qui suivent.

3. Quelle commande SQL permet de créer cette relation ?

   Une solution

   CREATE TABLE IF NOT EXISTS Annuaire (
       nom TEXT,
       prenom TEXT,
       tel TEXT PRIMARY KEY,
       CHECK (length(tel) >= 10)
       ) ;
   

4. Quelle commande SQL permet d’insérer l’enregistrement (Lhermite, Bernard, 0102030405) ?

   Une solution

   INSERT INTO Annuaire (nom, prenom, tel)
   VALUES ('Lhermite', 'Bernard', '0102030405') ;
   

5. Bernard Lhermite change de numéro de téléphone : 0504030201.
   Quelle commande SQL permet de mettre à jour la base de données ?

   Une solution

   UPDATE Annuaire
   SET tel = "0504030201"
   WHERE tel = '0102030405' ;
   

Prog B02.05

On considère les relations schématisées ci-dessous.

1. Générez une nouvelle base de donnnées avec le logiciel DB Browser puis nommez cette base ProgB02.05.db

2. Dans l'onglet enregistrez un fichier au format SQL sous le nom ProgB02.05.sql.
   Complétez ensuite ce fichier en répondant aux questions qui suivent.

3. Quelles commandes SQL permettent de créer les relations Client et Ville ?

   Une solution

   CREATE TABLE IF NOT EXISTS Ville (
       id_ville INT PRIMARY KEY,
       nom TEXT
   ) ;
   
   CREATE TABLE IF NOT EXISTS Client (
       id_client INT PRIMARY KEY,
       nom TEXT,
       prenom TEXT,
       id_ville INT,
       FOREIGN KEY (id_ville)
           REFERENCES Ville(id_ville)
   ) ;
   

4. Écrire les requêtes permettant d’insérer les données suivantes dans la base.

   Une solution

   INSERT INTO Ville
   VALUES (1, 'LYON'),
   (2, 'PARIS'),
   (3, 'MARSEILLE'),
   (4, 'GRENOBLE') ;
   
   
   INSERT INTO Client
   VALUES (4, 'Térieur', 'Alain', 3),
   (3, 'Parker', 'Peter', 2),
   (2, 'Zeblues', 'Agathe', 1),
   (1, 'Possible', 'Alain', 1) ;
   

5. Peter Parker est en fait originaire de GRENOBLE.
   Écrire la requête permettant de modifier la valeur correspondante.

   Une solution

   UPDATE Client
   SET id_ville=4
   WHERE id_client = 3;
   

6. Insérer la colonne genre dans la table Client.

   Une solution

   ALTER TABLE Client
   ADD COLUMN genre TEXT
   CHECK (genre == 'F' OR genre == 'M');
   

7. Insérer les valeurs de la colonne genre dans la table Client.
   Est-ce possible en seulement deux requêtes ?

   Une solution

   UPDATE Client
   SET genre = 'M';
   
   UPDATE Client
   SET genre = 'F'
   WHERE id_client = 2;
   

   ```

Prog B02.06

On considère les relations schématisées ci-dessous.

1. Quelles commandes SQL permettent de créer les relations Eleve, Coordonnee et Classe ?

   Une solution

   CREATE TABLE IF NOT EXISTS Eleve (
       id_eleve INT PRIMARY KEY,
       nom TEXT,
       prenom TEXT,
       date_naissance DATE
   ) ;
   
   CREATE TABLE IF NOT EXISTS Coordonnee (
       id_eleve INT PRIMARY KEY,
       adresse TEXT,
       cp TEXT,
       ville TEXT,
       email TEXT UNIQUE,
       FOREIGN KEY (id_eleve)
           REFERENCES Eleve(id_eleve)
   ) ;
   
   CREATE TABLE IF NOT EXISTS Classe (
       niveau TEXT,
       numero TEXT,
       id_eleve INT,
       PRIMARY KEY (niveau, numero),
       FOREIGN KEY (id_eleve)
           REFERENCES Eleve(id_eleve)
   ) ;
   

2. Enregistrer l’élève suivant dans la base de données :
   Ken OBI, né le 01 novembre 2005 et habitant 7, rue Montrouge – 69003 LYON. Élève en classe de 2nde 4, son numéro est 705412 et son email ken.obi@ben.star.

   Une solution

   INSERT INTO Eleve 
   VALUES (705412, 'OBI', 'Ken', '01/11/2005') ;
   
   INSERT INTO Coordonnee 
   VALUES (705412, '7, rue Montrouge', '69003', 'LYON', 'ken.obi@ben.star') ;
   
   INSERT INTO Classe 
   VALUES ('seconde', '4', 705412) ;