Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

module D4-UV1 TD04 1 TD04 - LID - Requêtes SELECT La société CredAuto emploie d

module D4-UV1 TD04 1 TD04 - LID - Requêtes SELECT La société CredAuto emploie des intervenants qui gèrent des réaménagements de prêts et qui sont amenés à se déplacer. Ces intervenants se font rembourser leurs frais.  Extrait du schéma relationnel INTERVENANT(code, nom, prénom, codeMarque, codeRegion) code : clé primaire codeMarque : clé étrangère en référence à code de MARQUE (non présentée ici) codeRegion : clé étrangère en référence à code de REGION DOSSIERMENSUEL(codeIntervenant, annee, mois, dateReception, datePaiement, montantTotalDu) codeIntervenant, année, mois : clé primaire codeIntervenant : clé étrangère en référence à code de INTERVENANT NOTEDEFRAIS(codeIntervenant, date, numeroOrdre, montant, justificatif_O_N, codeType) codeIntervenant, date, numeroOrdre : clé primaire codeIntervenant: clé étrangère en référence à code de INTERVENANT codeType : clé étrangère en référence à code de TYPE TYPE(code, libellé, plafond ) code : clé primaire REGION(code, nom). code : clé primaire Remarques :  Pour certains intervenants, le codeRegion n’est pas renseigné.  Le champ justificatif_O_N de la table NOTEDEFRAIS prend les valeurs true ou false se- lon la présence ou l’absence de justificatif pour la note de frais.  Le champ MontantTotalDu prend pour valeur le cumul de tous les remboursements pla- fonnés correspondant aux notes de frais déposées par un intervenant pour le mois.  Le montant des frais par type de frais (nuité, repas, déplacement, …) est plafonné. Vous ne tiendrez pas compte des plafonds, dans un premier temps. 631146681.doc 1/3 module D4-UV1 TD04 1 Travail à faire : Créer les requêtes SQL suivantes : 1. Afficher le montant total des remboursements dus à l’intervenant de code "980045" au titre de l’année 2016. SELECT SUM(montant) FROM NOTEDEFRAIS n INNER JOIN DOSSIERMENSUEL d on n.date = d.annee WHEN codeIntervenant = ‘980045’ AND année = ‘2016’ 2. Afficher les dossiers mensuels des intervenants dont le nom commence par "DUP" au titre du mois de mars de l’année 2016. SELECT * FROM DOSSIERMENSUEL d INNER JOIN INTERVENANT i on d.codeIntervenant = i.code WHERE d.nom LIKE ‘DUP%’ AND d.annee = ‘2016’ AND d.mois = mars 3. Afficher le nombre de notes de frais sans justificatif par code intervenant et par mois pour l’année 2016, liste triée par ordre croissant des codes d’intervenant et par nombre décroissant de notes de frais. SELECT COUNT(*) as nbJustificatif, codeIntervenant, MONTH(date) as Mois FROM NOTEDEFRAIS WHERE justificatif_O_N = false AND année = 2016 GROUP BY codeIntervenant, Mois ORDER BY codeIntervenant ASC, nbJustificatif DESC ; 4. Afficher les codes des intervenants qui n'ont déposé aucune demande de rembourse- ment de frais en avril 2016. SELECT code FROM INTERVENANT i WHERE NOT IN (SELECT codeIntervenant FROM NOTEDEFRAIS 631146681.doc 2/3 module D4-UV1 TD04 1 WHERE MONTH(date) != 4 AND YEAR(date) !=2016’) 5. Afficher le nom et le prénom de (ou des) l’intervenant qui ont pour le mois de mai 2016, le montant de DOSSIERMENSUEL le plus élevé de ce mois. 6. Un intervenant intervient soit dans une région, soit pour une marque précise. Afficher les intervenants qui ne respectent pas cette règle de gestion. 7. Afficher les notes de frais qui dépassent le plafond. 8. Afficher le code des intervenants dont le montant du dossierMensuel pour le mois de juin 2016 dépasse la moyenne.des montants des dossierMensuel de ce mois. 9. Afficher les dossierMensuel qui ont été réglés en plus d’un mois à partir de la date de ré- ception. 10.Afficher les intervenants avec le nom de la région à laquelle ils appartiennent éventuelle- ment. 11. Afficher le code intervenant, l’année, le mois pour les intervenants dont le montantTotal- Du du dossier mensuel dépasse la moyenne des montantTotalDu du mois concerné. 12.Afficher le montant dû pour chaque note de frais, en tenant compte du plafond. On affi- chera le code intervenant, la date, le numéro d’ordre et le montant dû. 13.Afficher le total montant dû calculé, à partir des notes de frais, pour chaque intervenant, pour le mois de mai de l’année 2018. On affichera le code intervenant, le mois, l’année et le montant total dû. (SELECT dans le FROM). 631146681.doc 3/3 uploads/S4/ sio-d4-uv1-sql-td04.pdf