L'E.S.N.

D4 SQL                 BTS SIO 2ème année SLAM-CF

 SQL

1. Contexte

Une E.S.N met à disposition de ses clients, des  personnels informaticiens. 

Cette mise à disposition se fait par un contrat signé avec le client qui précise le nombre de journées*hommes par qualification demandée, la date de début et de fin du contrat et son objet. Par exemple un contrat peut nécessiter 10 jours/homme de développement Web et 3 jours/homme d'administration de bases de données.

Les clients sont caractérisés par un code, un nom, un prénom, une adresse (rue), un code postal et une ville.

Les personnels de l'E.S.N sont individuellement connus (N° matricule, nom, prénom, adresse) et affectés dans les contrats signés. On doit connaître le nombre de jours réels pour lequel un informaticien intervient sur un contrat.

Les personnels ont une qualification standard, c'est celle avec laquelle ils interviennent pour un contrat. Une qualification est caractérisée par un libellé et un tarif horaire.

Voici le schéma relationnel correspondant

Personnels(matricule, nom, prenom, adresse, #code_qualif)

Clients(code_client, nom, prenom,adresse)

Contrats(code_contrat,datedebut,datefin,objet,#code_client)

Qualifications(code_qualif,libelle,tarifhoraire)

Interventions(#matricule,#code_contrat,nbjours_reels)

Besoins(#code_contrat,#code_qualif, nbjours_h)

1. Écrire les ordres SQL demandés

1. Liste des informaticiens avec leur nom,leur prénom en majuscule, leur login (six premières lettres du nom concaténé à la première lettre du prénom )

☝UPPER

Transforme les caractères d'une expression en minuscules en caractères majuscules.

Syntaxe

UPPER ( nom colonne )

☝SUBSTRING

Renvoie une partie d'une expression de type caractère

Syntaxe

SUBSTRING ( expression , start , length )

☝Pour concaténer deux chaînes de caractères on utilise la fonction CONCAT

1. Nom et prénom des personnels qui interviennent sur le contrat 1342, le contrat 2005 et le contrat 1457

1. Liste des contrats qui durent moins de 7 jours : noContrat, nomdu client, dateDebut, dateFin on utilisera la fonction datediff

☝Utilisation des fonctions date. Datediff permet de calculer une différence entre 2 dates en nombre de jours.

-- Datediff( date1,date2)

1. Nom et prénom des personnels qui interviennent sur le contrat 1342 avec la qualification de libellé "développeur"

1. Liste des contrats avec pour chaque qualif nécessaire (besoins), le montant, une journée fait 7h : noContrat, codeQualif, montant

1. Liste des personnels qui n'interviennent sur aucun contrat

1. Liste des qualifications qui ne sont exigées par aucun contrat

1. Liste des clients qui n'ont aucun contrat

1. Qualification qui a le tarif horaire le plus élevé : Libellé qualif, tarif

1. Nombre de personnels intervenant pour chaque contrat : noContrat, objet, nombre de personnes        

1. Pour chaque contrat, on veut son montant prévisionnel

1. On veut connaître le montant réel d'un contrat : noContrat, montant réel

1. Contrat qui mobilise le plus d'intervenants : noContrat, nomCli, nombre d'intervenants

C.Fichet        /        

...