Solutions

Voici les solutions pour les les exercices pour le cours 3. Utilisez la base de données cours3.sqlite.

Exercices (9)

Insertion (9.1)

Pour les clés étrangères, vous devez trouver celle correspondante dans la table. Dans le cas que la valeur n'existe pas dans la table parent, vous devez la créer en utilisant vos propres valeurs.

Table Cours

        

            Copié
            
        
        INSERT INTO Cours(Sigle, Nom, Description, HeureTheorie, HeurePratique, HeureMaison, EnseignantId)
VALUES
    ('420-3R1-DM', 'Réseau informatique 1', 'Ce cours montre les bases de la réseautique.', 2, 2, 2, 5),
    ('420-5R1-DM', 'Réseau informatique 2', 'Ce cours montre comment construire un réseau complexe.', 2, 4, 4, 5),
    ('420-3W1-DM', 'Développement d''applications Web 3', null, 2, 3, 2, 4);
    

Table Programmes

        

            Copié
            
        
        --Il faut ajouter les enseignants manquants
INSERT INTO Enseignants(EnseignantId, Prenom, Nom, Courriel, Anciennete)
VALUES
    (6, 'Sylvain', 'Poulin', 'sylvain.poulin@cegepdrummond.ca', 22),
    (7, 'Cindy', 'Asselin', 'cindy.asselin@cegepdrummond@cegepdrummond.ca', 33);

INSERT INTO Programmes(ProgrammeId, Code, Nom, EstTechnique, CoordonnateurEnseignantId)
VALUES
    (1, '420-B0', 'Techniques de l''informatique', 1, 5),
    (2, '200-B0', 'Sciences de la nature', 0, 6),
    (3, '241-A0', 'Techniques de génie mécanique', 1, 7),
    (4, '243-BB', 'Technologie de l''électronique : Ordinateurs et réseaux', 1, 5);
    

Table Etudiants

        

            Copié
            
        
        --Il faut ajouter les pays
INSERT INTO Pays(PaysId, Nom)
VALUES
    (1, 'Canada'),
    (2, 'Algérie'),
    (3, 'France');

INSERT INTO Etudiants(DA, Prenom, Nom, Courriel, ProgrammeId, PaysId)
VALUES
('09452145', 'Sonia', 'Rodrigue', 'sonia.rodrigue@etu.cegepdrummond.ca', 1, 1),
('22026544', 'Jean-Marc', 'Leclerc-Morin', 'jean-marc.leclerc-morin@etu.cegepdrummond.ca', 1, 2),
('22985654', 'Soufiane', 'Djamel', 'soufiane.djamel@etu.cegepdrummond.ca', 1, 2),
('14485965', 'Lise', 'Zouri', 'lise.zouri@etu.cegepdrummond.ca', 2, 1),
('19874562', 'Étienne', 'Gosselin', 'etienne.gosselin@etu.cegepdrummond.ca', 2, 3),
('21212565', 'Cindy', 'Asselin', 'cindy.asselin@etu.cegepdrummond.ca', 4, 1),
('22545744', 'Joëlle', 'Ste-Marie', 'joelle.ste-marie@etu.cegepdrummond.ca', 3, 3),
('08065432', 'Damien', 'Turgeon', 'damien.turgeon@etu.cegepdrummond.ca', 2, 1);
    

Sélection

Pour chacune des sélections, utilisez des alias de table et de champ lorsque c'est nécessaire.

1. Affichez tous les cours (Signe et Nom) de la première session avec le prénom et le nom de l'enseignant.

           
   
               Copié
               
           
           SELECT C.Sigle, C.Nom AS NomCours, E.Nom AS NomEnseignant
   FROM Cours C
   INNER JOIN Enseignants E on E.EnseignantId = C.EnseignantId
   WHERE
       C.Sigle LIKE '420-1%';
       

2. Affichez tous les cours qui ont une description.

           
   
               Copié
               
           
           SELECT *
   FROM Cours
   WHERE
       Description IS NOT NULL;
       

3. Affichez tous les cours qui ont 2 heures et plus de théorie.

           
   
               Copié
               
           
           SELECT *
   FROM Cours
   WHERE
      HeureTheorie >= 2;
       

4. Affichez tous les cours (Sigle et nom) qui ont uniquement 1 heure à la maison avec le prénom et nom de l'enseignant.

           
   
               Copié
               
           
           SELECT C.Sigle, C.Nom AS NomCours, E.Prenom, E.Nom AS NomEnseignant
   FROM Cours C
   INNER JOIN Enseignants E on E.EnseignantId = C.EnseignantId
   WHERE
       C.HeureMaison = 1;
       

5. Affichez tous les cours (Signe et Nom) qui ont plus de 5 heures de cours de théorie et de pratique combiné avec le courriel de l'enseignant.

           
   
               Copié
               
           
           SELECT C.Sigle, C.Nom AS NomCours, E.Courriel
   FROM Cours C
   INNER JOIN Enseignants E on E.EnseignantId = C.EnseignantId
   WHERE
       C.HeureMaison + C.HeureTheorie > 5;
       

6. Affichez tous les enseignants que leur id est un nombre pair. Il faut utiliser un modulo %.

           
   
               Copié
               
           
           SELECT *
   FROM Enseignants
   WHERE
       EnseignantId % 2 = 0;
       

7. Affichez tous les programmes (Code, Nom) qui sont ne sont pas une technique.

           
   
               Copié
               
           
           SELECT Code, Nom
   FROM Programmes
   WHERE
       EstTechnique = 0;
       

8. Affichez tous les programmes (Code, Nom) qui sont une technique avec le prénom et le nom du coordonnateur.

           
   
               Copié
               
           
           SELECT P.Code, P.Nom AS NomProgramme, E.Prenom AS PrenomCoordonnateur, E.Nom AS NomCoordonnateur
   FROM Programmes P
   INNER JOIN Enseignants E ON E.EnseignantId = P.CoordonnateurEnseignantId
   WHERE
       EstTechnique = 1;
       

9. Affichez tous les enseignants (EnseignantId, Prenom, Nom) et leur programme de coordination (ProgrammeId, Nom)

           
   
               Copié
               
           
           SELECT E.EnseignantId, E.Prenom, E.Nom, P.ProgrammeId, P.Nom AS NomProgramme
   FROM Enseignants E
   INNER JOIN Programmes P ON E.EnseignantId = P.CoordonnateurEnseignantId;
       

10. Affichez tous les étudiants (Prenom, nom et courriel) avec le nom de leur pays. Triez par nom de pays, ensuite le prénom de l'étudiant et finalement par le nom de l'étudiant.

            
    
                Copié
                
            
            SELECT E.Prenom, E.Nom, E.Courriel, P.Nom AS NomPays
    FROM Etudiants E
    INNER JOIN Pays P ON P.PaysId = E.PaysId
    ORDER BY P.Nom, E.Prenom, E.Nom;
        

11. Affichez tous les étudiants (Prenom, nom et courriel) qui proviennent de la France.

            
    
                Copié
                
            
            SELECT E.Prenom, E.Nom, E.Courriel, P.Nom AS NomPays
    FROM Etudiants E
    INNER JOIN Pays P ON P.PaysId = E.PaysId
    WHERE
        P.Nom = 'France';
        

12. Affichez tous les étudiants avec les champs suivants : DA, nom complet (concaténation de prénom et nom) et le nom du programme.

            
    
                Copié
                
            
            SELECT E.DA, E.Prenom || ' ' || E.Nom AS NomCompletEtudiant, P.Nom AS NomProgramme
    FROM Etudiants E
    INNER JOIN Programmes P ON P.ProgrammeId = E.ProgrammeId
        

13. Affichez tous les étudiants avec les champs suivants : DA, nom complet (concaténation de nom et prénom, séparé par une virgule), le nom du programme et le nom du pays.

            
    
                Copié
                
            
            SELECT E.DA, E.Nom || ',' || E.Prenom AS NomCompletEtudiant, P.Nom AS NomProgramme, Pays.Nom AS NomPays
    FROM Etudiants E
    INNER JOIN Programmes P ON P.ProgrammeId = E.ProgrammeId
    INNER JOIN Pays ON Pays.PaysId = E.PaysId --Il n'est pas obligatoire de mettre un alias. Pays et plus explicite que P2.
        

14. Affichez tous les étudiants (Nom et Prenom uniquement) qui ont le code du programme 420-B0 et que leur pays est Canada.

            
    
                Copié
                
            
            SELECT E.Prenom, E.Nom
    FROM Etudiants E
    INNER JOIN Programmes P ON P.ProgrammeId = E.ProgrammeId
    INNER JOIN Pays ON E.PaysId = Pays.PaysId
    WHERE
        P.Code = '420-B0' AND
        Pays.Nom = 'Canada';
        

15. Affichez tous les étudiants (DA, nom et prénom), avec le nom du programme, le prénom du coordonnateur et le nom du coordonnateur.

            
    
                Copié
                
            
            SELECT Etu.DA, Etu.Prenom AS PrenomEtudiant, Etu.Nom AS NomEtudiant, P.Nom AS NomProgramme,
           Ens.Prenom AS PrenomCoordonnateur, Ens.Prenom AS PrenomCoordonnateur
    FROM Etudiants Etu
    INNER JOIN Programmes P ON P.ProgrammeId = Etu.ProgrammeId
    INNER JOIN Enseignants Ens ON P.CoordonnateurEnseignantId = Ens.EnseignantId;
        

16. Affichez tous les étudiants (Prenom, nom, courriel et nom pays) qui proviennent du Canada et qui ont l'enseignant #5 comme coordinateur de programme.

        

            Copié
            
        
        SELECT Etu.Prenom AS PrenomEtudiant, Etu.Nom AS NomEtudiant,Etu.Courriel, Pays.Nom AS NomPays
FROM Etudiants Etu
INNER JOIN Programmes P ON P.ProgrammeId = Etu.ProgrammeId
INNER JOIN Pays ON Etu.PaysId = Pays.PaysId
WHERE
    P.CoordonnateurEnseignantId = 5 AND
    Pays.Nom = 'Canada';
    

Exercices (10.5)

1. Ajouter 2 nouveaux pays.

           
   
               Copié
               
           
           INSERT INTO Pays(PaysId, Nom) 
   VALUES 
       (4, 'Italie'),
       (5, 'États-Unis');
       

2. Affichez la liste de tous les pays et des étudiants. Les pays sans étudiants doivent être dans la liste. Triez par nom de pays.

           
   
               Copié
               
           
           SELECT *
   FROM Pays
   LEFT OUTER JOIN  Etudiants E ON Pays.PaysId = E.PaysId
   ORDER BY Pays.Nom
       

3. Affichez le nom des pays qui n'ont aucun étudiant.

           
   
               Copié
               
           
           SELECT Pays.Nom
   FROM Pays
   LEFT OUTER JOIN  Etudiants E ON Pays.PaysId = E.PaysId
   WHERE
       E.DA IS NULL;
       

4. Ajoutez 2 nouveaux programmes.

           
   
               Copié
               
           
           INSERT INTO Programmes(ProgrammeId, Code, Nom, EstTechnique, CoordonnateurEnseignantId)
   VALUES
       (5, '300.A0', 'Sciences humaines', 0, 4),
       (6, '180.A0', 'Soins infirmiers', 1, 6);
       

5. Affichez la liste de tous les enseignants coordonnateurs (prénom et nom) avec le nom du programme et la liste des étudiants (prénom et nom). Les programmes sans étudiant doivent être dans la liste. Affichez les programmes sans étudiant en premier et ensuite triez par nom de programme.

        

            Copié
            
        
        SELECT Ens.Prenom AS PrenomCoordonnateur, Ens.Nom AS NomCoordonnateur, P.Nom AS NomProgramme,
       Etu.Prenom AS PrenomEtudiant, Etu.Nom AS NomEtudiant
FROM Enseignants Ens
INNER JOIN Programmes P on Ens.EnseignantId = P.CoordonnateurEnseignantId
LEFT OUTER JOIN Etudiants Etu on P.ProgrammeId = Etu.ProgrammeId
ORDER BY Etu.DA IS NULL DESC, P.Nom;
-- Etu.DA IS NOT NULL consiste à faire une vérification sur le champ DA si il est null, Donne 1 si vrai, 0 si faux.
-- Si le tri est asc, c'est trié par faux en premier et vrai par la suite. 
-- Si le tri est desc, c'est trié par vrai en premier et faux par la suite.