Formulation de requêtes SQL

TCH054-01-02-03
Bases de données
Laboratoire #2
SEG – Cheminement universitaire
Révision : 2017-02-14 (Hiver 2017)
Auteurs : L. THERIAULT, M. NAYROLLES Page 1
Formulation de requêtes SQL
Objectifs généraux
L'objectif principal de ce deuxième laboratoire d'une durée de 2 semaines est la formulation de
requêtes SQL permettant de répondre à des questions d’affaires dans le domaine de la gestion
d’inventaire.
Ce laboratoire est à réaliser en équipe de deux étudiants (la même équipe que celle du premier
laboratoire).
Ce deuxième laboratoire se présente sous la forme de questions d’affaires. Pour répondre à
chacune des questions, vous devez formuler une requête de type SELECT permettant d’y
répondre.
Modèle relationnel de données
Le modèle relationnel de données est le résultat de l’adaptation du modèle conceptuel de données
en fonction du système de gestion de base de données relationnel Oracle.
Une courte description de chacune des tables vous est présentée ci-dessous :
La table CHEF contient l’information décrivant les chefs de projet, plus particulièrement leurs
noms, sous la forme d’un attribut composé Prénom Nom (ex. Richard Rail), et leur date de
naissance.
Chaque instance (ligne) de la table PROJET représente un projet avec ses dates de début et de fin.
Si sa date de fin est pour l’instant indéterminée, celle-ci est nulle. Mais elle peut être future et
connue, donc inscrite.
Chaque instance de la table PRODUIT représente un type de produit conservé en inventaire. Ces
produits ont une description et sont conservés dans une étagère selon une unité de mesure,
laquelle sert à exprimer le coût unitaire du produit ainsi que sa quantité en main. Tous les
produits sont associés à une classe pour des raisons de gestion.
La table SORTIE présente les transactions de sortie d’inventaire. On y consigne le code et la
quantité de produit, la date de la sortie et le projet qui sera imputé de la dépense.
La table CLASSE contient le nom et le caractère taxable (0 pour non, 1 pour oui) de la classe.
Le schéma relationnel de la base de données est illustré à la section suivante.

TCH054-01-02-03
Bases de données
Laboratoire #2
SEG – Cheminement universitaire
Révision : 2017-02-14 (Hiver 2017)
Auteurs : L. THERIAULT, M. NAYROLLES Page 2
Schéma relationnel
Vous devez utiliser le schéma relationnel suivant pour formuler chaque requête :
Informations supplémentaires
 Les clés primaires sont soulignées
 Les clés étrangères sont indiquées par (FK).
 Les symboles ø indiquent une colonne qui accepte les valeurs NULL.
 Les colonnes quantite et cout sont d'un type numérique.
 Les colonnes dont le nom comporte le mot "date" sont de type date.
 La colonne taxable est un nombre entier, n'ayant que les valeurs 0 pour non et 1 pour oui.
 Toutes les autres colonnes sont d'un type caractère.
Consigne
Vous devez utiliser la base de données Inventaire. Il suffit de vous connecter en utilisant vos
données d’authentification (numéro d’équipe et mot de passe) au serveur Oracle. Elle est
disponible publiquement à tous en mode lecture seul.

TCH054-01-02-03
Bases de données
Laboratoire #2
SEG – Cheminement universitaire
Révision : 2017-02-14 (Hiver 2017)
Auteurs : L. THERIAULT, M. NAYROLLES Page 3
Questions d’affaires
Voici la liste des questions d’affaires, pour y répondre vous devez formuler une requête
(SELECT) pour chaque question en utilisant le langage de manipulation de données (LMD SQL).
Regroupez les 34 requêtes dans un seul et même script SQL qui sera votre livrable (le code
source). Indiquez le numéro de la question avant chaque requête à l’aide d’un commentaire (voir
Labo_02_ExempleFichierRéponses.sql disponible sur le site Web du cours.
Question 1
Présentez la liste des sorties d'inventaire qui ont été effectuées entre le 10 mai 1989 et le 25
février 1990, exclusivement.
Question 2
Quel est le code des projets dont la date de fin est inconnue?
Question 3
Quelles sont les classes de produits étant associées à au moins un produit en inventaire, dont le
code se termine par 10?
Question 4
La liste de tous les projets qui furent terminés avant le 25 mars 1990, triés dans l'ordre
décroissant des codes de projet.
Question 5
Pour chacune des sorties d'inventaire impliquant une quantité égale ou supérieure à 10 unités,
fournissez le nom de la classe du produit et le nom du chef de projet.
Question 6
Fournissez la liste des produits qui ont été utilisés pour le compte d'un des projets suivants:
P1288 ou P1210. Si, et seulement si, la description du produit ne comprend pas le mot ACIER.
Question 7
Présentez une liste des noms de tous les chefs de projet avec la date de début de chacun de leurs
projets (lorsqu'ils en ont). Votre liste doit être en ordre alphabétique des noms, et ordre
chronologique descendant des dates de début.
Question 8
Présentez la liste des produits par ordre de classe et de code, et majorez leur coût de 20%.
Question 9
Quel est le nombre total de sorties d'inventaire enregistrées dans la table SORTIE?

TCH054-01-02-03
Bases de données
Laboratoire #2
SEG – Cheminement universitaire
Révision : 2017-02-14 (Hiver 2017)
Auteurs : L. THERIAULT, M. NAYROLLES Page 4
Question 10
Quel est le nombre total de sorties d'inventaire enregistrées pour chaque projet?
Question 11
Combien y a-t-il de sorties imputées aux projets P1206 et P1271? (la réponse est un nombre
unique)
Question 12
Présentez le total des quantités des produits en inventaire, pour chacune des classes de produit.
Question 13
Présentez le total des quantités des produits en inventaire, pour chacune des classes de produits,
mais seulement pour les classes qui regroupent cinq produits différents ou plus.
Question 14
Quel est le coût unitaire moyen des produits appartenant à une classe dont le code débute par la
lettre A? Présentez le nom de la classe et le coût associé. Arrondissez vos réponses à deux
décimales. (Assurez-vous que votre formatage est adéquat).
Question 15
Pour chaque produit, présentez la quantité totale d'unités sortie de l'inventaire, triée par numéro
de produit.
Question 16
Même question que précédemment, mais maintenant assurez-vous que tous les produits
apparaissent dans les résultats, y compris ceux qui ne sont pas mentionnés dans les sorties.
Question 17
Fournissez la liste des produits qui ont fait l'objet de deux sorties ou plus.
Question 18
Fournissez une liste des chefs de projet avec le nombre de projets auxquels chacun est associé.
Vous devez fournir le nom du chef, et n’oubliez pas que certains chefs ne dirigent aucun projet.
Question 19
Quelle est la valeur en dollars que représente le total des sorties imputées à chacun des projets,
en sachant qu’une majoration de 7% doit être appliquée pour les frais généraux? (Assumez que
tous les projets ont eu des sorties).
Question 20
Fournissez la liste des numéros de projets dont le montant total dépensé en produits est supérieur
à 1000$.

TCH054-01-02-03
Bases de données
Laboratoire #2
SEG – Cheminement universitaire
Révision : 2017-02-14 (Hiver 2017)
Auteurs : L. THERIAULT, M. NAYROLLES Page 5
Question 21
Combien de produits ont une description ayant plus de 15 caractères?
Question 22
Calculez l'âge, en années, de chacun des chefs au moment de l’exécution de la requête. Présentez
la liste des noms des chefs avec leurs âges (du plus jeune au plus vieux).
Question 23
Présentez la liste des codes de projets ainsi que leur durée respective, calculée en heures. Les
projets sans dates de fin doivent indiquer « -1».
Question 24
Présentez la liste des descriptions de produits « formatée » de sorte que la première lettre de la
description soit en majuscule et les autres en minuscules.
Question 25
Fournissez la liste des noms des chefs de projets avec, à côté de leur nom, la mention
expérimenté s'ils ont dirigés 2 projets ou plus, ou la mention débutant, le cas échéant. Votre liste
doit être en ordre alphabétique des noms.
Question 26
Présentez la liste des codes de projets ainsi que leur coût total en produits respectif. Votre liste
doit être présentée en ordre de code de projet. Le coût total s'obtient en calculant la valeur de
chacune des sorties (quantité de produits x coût du produit) et en faisant la somme
de toutes les sorties pour chacun des projets. Pour les produits taxables, ajoutez 5% pour la TPS et
9.975% pour la TVQ. Arrondissez vos réponses à deux décimales. (Assurez-vous que votre
formatage est adéquat).
Pour les requêtes suivantes, vous devez formuler toutes vos requêtes sous la forme de
requêtes imbriquées. Les produits cartésiens sont INTERDITS.
Question 27
Présentez la liste des codes de produits qui ont un coût unitaire plus élevé que la moyenne.
Question 28
Fournissez la liste des produits, incluant leur description, qui ont fait l'objet d'au moins une sortie
sur le compte du projet P1288.
Question 29
Quelles sont les produits qui n'ont jamais fait l'objet de sortie.
Question 30
Quel est le nom du chef de projet le/la plus jeune?