420-3N3-DM

Solution des exercices du cours 2

Solutions

Voici les solutions pour les exercices pour le cours 2.

Table Appels

Contrainte

Identifiez toutes les contraintes de la table et les noms des champs.

Nom colonne	Type	Contrainte(s)
AppelId	INTEGER	Clé primaire NOT NULL
Telephone	TEXT	NOT NULL Valeur par défaut 'inconnu'
Date	TEXT	NOT NULL
Heure	TEXT	NOT NULL
Duree	INTEGER	NOT NULL
Interurbain	INTEGER	NOT NULL Valeur par défaut 0
Cout	NUMERIC
Commentaire	TEXT

Table Autos

Insertion

Créez les commandes d'insertion pour les données ci-dessous.

AutoId	Nom	Prix	Couleur
101	Liberty	53036	vert
102	Quest	15210	violet
103	Quest	134885	bleu
104	Monte Carlo	42047	orange
105	Ram 2500	89481	orange
106	Volvo	78966	rouge
107	Jetta	40515	rouge
108	Rio	22698	bleu
109	190E	113684	gris
110	Envoy	32626	gris
111	Volt	39856	blanc
112	Festiva	19272	vert
113	Jaguard	103170	noir
114	Riviera	106448	noir
115	Rav4	32293	vert

        
            Copié
            
        
        INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (101, 'Liberty', 53036, 'vert');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (102, 'Quest', 15210, 'violet');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (103, 'Quest', 134885, 'bleu');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (104, 'Monte Carlo', 42047, 'orange');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (105, 'Ram 2500', 89481, 'orange');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (106, 'Volvo', 78966, 'rouge');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (107, 'Jetta', 40515, 'rouge');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (108, 'Rio', 22698, 'bleu');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (109, '190E', 113684, 'gris');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (110, 'Envoy', 32626, 'gris');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (111, 'Volt', 39856, 'blanc');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (112, 'Festiva', 19272, 'vert');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (113, 'Jaguard', 103170, 'noir');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (114, 'Riviera', 106448, 'noir');
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (115, 'Rav4', 32293, 'vert');

Selection

Effectuez un SELECT pour obtenir toutes les voitures qui sont orange.

        
            Copié
            
        
        SELECT * 
FROM Autos
WHERE Couleur = 'orange';

Effectuez un SELECT pour obtenir toutes les voitures qui sont noir ou gris.

        
            Copié
            
        
        SELECT * 
FROM Autos
WHERE 
    Couleur = 'noir' OR
    Couleur = 'gris';

Effectuez un SELECT pour obtenir toutes les voitures que leur prix est plus petit ou égal à 40 000.

        
            Copié
            
        
        SELECT * 
FROM Autos
WHERE Prix <= 40000;

Effectuez un SELECT pour obtenir toutes les voitures que leur prix est plus petit ou égal à 40 000 et que la couleur est bleu ou vert.

        
            Copié
            
        
        SELECT * 
FROM Autos
WHERE 
    Prix <= 40000 AND
    (
        Couleur = 'bleu' OR
        Couleur = 'vert'
    );

Effectuez un SELECT pour obtenir toutes les voitures. Les colonnes affichées doivent être Couleur, Nom et Prix.

        
            Copié
            
        
        SELECT Couleur, Nom, Prix
FROM Autos;

Effectuez un SELECT pour obtenir uniquement le nom des voitures. Il ne doit pas avoir de doublon. Le nom doit être trié en ordre décroissant.

        
            Copié
            
        
        SELECT DISTINCT Nom
FROM Autos
ORDER BY Nom DESC;

Effectuez un SELECT pour obtenir le nom et la couleur. La liste doit être triée en ordre croissant en fonction du nom et ensuite du prix. La colonne prix ne doit pas être affichée.

        
            Copié
            
        
        SELECT Nom, Couleur
FROM Autos
ORDER BY Couleur, Prix ASC; --ASC n'est pas obligatoire

Table Employes

Insertion

Créez les commandes d'insertion pour les données ci-dessous.

Utilisez la notation simple ligne. Exécutez chacun de vos INSERT individuellement avant de passer au suivant.

Assurez-vous d'écrire correctement les nombres dans le bon format.

EmployeId	Prenom	Nom	Courriel	Poste	Salaire	NAS
2001	Martin	Couture	mcouture@abc.com	Commis	23,98	123 456 789
2002	Maxime	Couture	mcouture@abc.com	Commis	24,48	987 654 321
2003	Maxime	Dupuis	mdupuis@abc.com	Comptable	43,87	556 654 787
2004	Marc	Dion	mdion@abc.com	Programmeur	35,35	789 874 541
2005	Sandra	Tremblay	stremblay@abc.com	Directrice	55,63	787 874 694
2006	Sophie	Morin	smorin@abc.com	Présidente	null	null
2007	Julie	Turcotte	jturcotte@abc.com	null	19,87	556 654 787
2008	Martine	Marcotte	mcmarcotte@abc.com	null	53	458 781 210

        
            Copié
            
        
        INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2001, 'Martin', 'Couture', 'mcouture@abc.com', 'Commis', 23.98, '123 456 789');

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2002, 'Maxime', 'Couture', 'mcouture@abc.com', 'Commis', 24.48, '987 654 321'); -- Erreur courriel existant

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2003, 'Maxime', 'Dupuis', 'mdupuis@abc.com', 'Comptable', 43.87, '556 654 787');

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2004, 'Marc', 'Dion', 'mdion@abc.com', 'Programmeur', 35.35, '789 874 541');

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2005, 'Sandra', 'Tremblay', 'stremblay@abc.com', 'Directrice', 55.63, '787 874 694');

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2006, 'Sophie', 'Morin', 'smorin@abc.com', 'Présidente', null, null); -- Erreur NAS est null

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2007, 'Julie', 'Turcotte', 'jturcotte@abc.com', null, 19.87, '556 654 787'); -- Erreur Poste est null et NAS existant

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2008, 'Martine', 'Marcotte', 'marcotte@abc.com', null, 53.00, '458 781 210'); -- Erreur Poste est null

Insertion

Corrigez les lignes problématiques pour être en mesure de les insérer.

        
            Copié
            
        
        INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2002, 'Maxime', 'Couture', 'mcouture2@abc.com', 'Commis', 24.48, '987 654 321'); -- courriel mcouture2@abc.com

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2006, 'Sophie', 'Morin', 'smorin@abc.com', 'Présidente', null, '123 456 777'); -- NAS 123 456 777

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2007, 'Julie', 'Turcotte', 'jturcotte@abc.com', 'Programmeur', 19.87, '556 654 111'); -- Poste Programmeur, NAS 556 654 111

INSERT INTO Employes(EmployeId, Prenom, Nom, Courriel, Poste, Salaire, NAS)
VALUES (2008, 'Martine', 'Marcotte', 'marcotte@abc.com', 'Commis', 53.00, '458 781 210'); -- Poste  Commis

Sélection

Effectuez un SELECT pour obtenir les employés que leur nom commence par C.

        
            Copié
            
        
        SELECT * 
FROM Employes
WHERE Nom like 'C%';

Effectuez un SELECT pour obtenir les employés que leur nom se termine par cotte.

        
            Copié
            
        
        SELECT * 
FROM Employes
WHERE Nom like '%cotte%';

Effectuez un SELECT pour obtenir les employés qui n'ont pas de salaire d'inscrit.

        
            Copié
            
        
        SELECT * 
FROM Employes
WHERE Salaire IS NULL;

Effectuez un SELECT pour obtenir les employés qui ont un salaire d'inscrit.

        
            Copié
            
        
        SELECT * 
FROM Employes
WHERE Salaire IS NOT NULL;

Effectuez un SELECT pour obtenir les employés. Le prénom doit être avant le nom. La liste doit être triée par prénom et ensuite par le nom en ordre alphabétique.

        
            Copié
            
        
        SELECT Prenom, Nom
FROM Employes
ORDER BY Prenom, Nom ASC; -- ASC n'est pas obligatoire

Table Appels

Insertion

Créez 10 enregistrements pour cette table.

Chaque enregistrement doit avoir une date différente entre le 2022-07-01 et 2022-08-12. Il doit avoir 5 enregistrements en juillet et 5 enregistrements en août.

Vous devez au moins 3 numéros de téléphone différents. Les numéros doivent être utilisés plusieurs fois.

Ne pas inclure la clé primaire dans l'insertion pour que la base de données s'en occupe.

Assurez-vous d'avoir des données variées. Si un champ peut être NULL, il faut au moins un enregistrement qui a la valeur NULL et au moins un avec une valeur.

La colonne téléphone doit avoir ce format xxx-xxx-xxxx.

La colonne Interurbain doit être considérée comme un booléen. Inscrire seulement 0 ou 1.

La colonne Duree est en minute.

La colonne Date doit avoir ce format yyyy-MM-jj.

La colonne Heure doit avoir ce format hh:mm; C'est le format 24h.

        
            Copié
            
        
        INSERT INTO Appels(Telephone, Date, Heure, Duree, Interurbain, Cout, Commentaire)
VALUES
    ('514-555-1234', '2022-07-09', '09:44', 5, 0, null, null),
    ('514-665-2222', '2022-07-16', '19:40', 14, 1, 0.34, null),
    ('514-665-2222', '2022-07-20', '19:55', 12, 1, 0.35, null),
    ('819-125-9998', '2022-07-22', '19:49', 14, 1, 0.39, null),
    ('514-665-2222', '2022-07-23', '22:44', 19, 1, 0.01, 'Rabais après 22h'),    
    ('819-125-9998', '2022-08-01', '23:01', 99, 1, 0.02, 'Rabais après 22h'),
    ('514-555-1234', '2022-08-02', '13:11', 13, 0, null, null),
    ('819-326-3345', '2022-08-05', '13:11', 10, 0, null, null),
    ('819-326-3345', '2022-08-06', '16:12', 12, 0, null, null),
    ('819-326-3345', '2022-08-07', '22:01', 0, 0, null, 'Occupé');

SELECT

Trouvez tous les appels du mois d'août.

        
            Copié
            
        
        SELECT * 
FROM Appels
WHERE 
    Date >= '2022-08-01' AND
    Date <= '2022-08-31';

Afficher uniquement la liste des numéros de téléphone. Il ne doit pas avoir de doublons. Les numéros doivent être triés en ordre croissant.

        
            Copié
            
        
        SELECT DISTINCT Telephone
FROM Appels
ORDER BY Telephone; --ASC peut être ajouté

Trouver tous les appels qui sont des interurbains.

        
            Copié
            
        
        SELECT *
FROM Appels
WHERE Interurbain = 1; -- 1 est vrai, 0 est faux

Trouvez tous les appels avec un commentaire.

        
            Copié
            
        
        SELECT *
FROM Appels
WHERE Commentaire IS NOT NULL;

Trouver tous les appels qui ont entre 10 minutes et 20 minutes et que l'appel n'est pas un interurbain.

        
            Copié
            
        
        SELECT *
FROM Appels
WHERE 
    Interurbain = 0 AND
    Duree >= 10 AND
    Duree <= 20;

Triez les appels en fonction du coût. Du plus cher au moins cher. Ne pas inclure les appels sans aucun coût.

        
            Copié
            
        
        SELECT *
FROM Appels
WHERE Cout IS NOT NULL    
ORDER BY Cout DESC;

Solutions

Table des matières