Olivier Catry - test 1 de BD 2008 1. Liste des noms des projets sur lesquels on travaille au moins 50 heures au total. Vous ferez figurer ce nombre d'heures dans la liste. *EXPLICATION DE LA REQUETE : Sélectionner les noms de projet et imbriquer : La sélection de la somme du champ duree de la table travail tel que le numero de projet de cette table travail est égal au numéro du projet de la table projet référencée en référence externe. (donner le nom somme_duree à cette requette imbriquée). (retour à notre requete initiale) noms de projet de la table projet tels que la sous requete suivante retourne un résultat (forcement numérique) supérieur à 50 : Sélection de la somme du champ duree de la table travail tel que le numero de projet de cette table travail est égal au numéro du projet de la table projet référencée en référence externe. *REQUETE : Select nomproj, (select sum(duree) from travail where travail.nuproj=projet.nuproj) somme_duree from projet where (select sum(duree) from travail where travail.nuproj=projet.nuproj)>=50 *RESULTAT : NOMPROJ SOMME_DUREE -------------------- ---------------------- erasmus 75 commet 140 eureka 90 esprit 115 *DEMARCHE : Tables à utiliser : 1- la table projet car c'est la seule à contenir l'attribut nomproj, qui retourne les noms de projet 2- la table travail car elle met en relation les numéros de projet que l'on retrouve dans projet et les durées qu'y consacrent les employés (verbe "travaille") Etapes : 3-Selection des noms de projet dans projet et des sommes de durées dans travail pour qu'ils soient affichés. On procede avec une imbrication de requetes. 4-pour faire cette somme on selectionne les durées dans la table travail tels que le numéro de projet est celui du projet selectionnée dans la table projet en reference externe. Et on en fait la somme. AINSI on retourne "les projets et le nombre d'heures durant lesquels on y travaille." 5-Il faut que ce nombre d'heure respecte la condition : supérieur ou égal à 50. En condition de la requete principale, la sous requete qui consiste à selectionne les heures durant lesquelles ont y travaille (comme en 4) doit retourner un resultat supérieur à 50. AINSI on retourne "les projets et le nombre d'heures durant lesquels on y travaille TELS QUE ce nombre d'heures soit supérieur à 50." AINSI on retourne : "La liste des noms des projets sur lesquels on travaille au moins 50 heures au total. On fait figurer ce nombre d'heures dans la liste." 2. Liste des noms d'employés et noms des projets sur lesquels ils travaillent et qui ne concernent pas leur service. *EXPLICATION DE LA REQUETE : Selection des noms d'employé de la table employé et des noms de projet de la table projet tels que : nuproj est égal au resultat retourné par la sous requete suivante : (selection des numeros de projet de la table travail tels que le numéro d'employé de la table travail soit égal au numéro d'employé de la table employe referencée en référence externe.) et tels que : le couple (affect,nuproj) ne soit pas retourné par la sous requete suivante : (selection des couples (nuserv,nuproj) de la table concerne) *REQUETE : Select nomempl, nomproj from employe, projet where nuproj IN (select nuproj from travail where travail.nuempl=employe.nuempl) and (affect,nuproj) not in (select nuserv,nuproj from concerne) *RESULTAT : NOMEMPL NOMPROJ -------------------- -------------------- leon zorro gedeon zorro edith zorro jules commet anne eureka pierre eureka bruno zorro marie esprit michele esprit germaine commet 10 rows selected *DEMARCHE : Tables à utiliser : -la table employé car elle contient l'attribut nomempl ("noms d'employé") -la table projet car elle contient l'attribut nomproj ("nom de projet") -la table travail car elle met en relation les employés et les projets sur lesquels ils travaillent.(verbe "travaillent") -la table concerne car elle met en relation les services et les projets qui les concernent. (verbe "concerne") Etapes : 1-Selectionner les noms d'employés et les noms de projets respectivement dans les tables employe et projet 2-Verifier que le numero de projet est retourné par la requete qui retourne les numéros de projets de la table travail tels que le numero d'employé est celui de l'employé de la table employe referencé en reference externe. AINSI on retourne "les noms d'employés et les noms de projets sur lesquels ils travaillent". 3-Verifier que les services auxquels les employés sont affectés (attribut affect) et "les projets sur lesquels ils travaillent" ne sont pas retournés par la requete qui retourne tous les services et projets qui les concernent, dans la table concerne donc. AINSI on retourne "les noms d'employés et les noms de projets sur lesquels ils travaillent tels qu'ils ne concernent pas le service auquel ils sont affectés." AINSI on retourne bien la "Liste des noms d'employés et noms des projets sur lesquels ils travaillent et qui ne concernent pas leur service." 3. Liste des noms de services, avec pour chaque service, le nombre de projets qui le concerne, et le nombre de projets où travaille au moins un employé du service *REQUETE : Select nomserv, (select count(*) from concerne where concerne.nuserv=service.nuserv)nombre_projets_qui_concernent , (select count(*) from projet where exists (select nuempl from travail where travail.nuproj=projet.nuproj and nuempl in (select nuempl from employe where employe.affect=service.nuserv)))nombre_projets_2 from service *RESULTAT : NOMSERV NOMBRE_PROJETS_QUI_CONCERNENT NOMBRE_PROJETS_2 -------------------- ----------------------------- ---------------------- achat 3 4 vente 5 5 informatique 4 4 comptabilite 5 6 direction 2 4 5 rows selected *DEMARCHE et EXPLICATIONS : I tables à utiliser : -Service car elle contient l'attribut nomserv (nom de service) Etapes : 1-Selection des noms de service dans la table sercice. AINSI on a "les services de la table service" II tables à utiliser : -Concerne car elle met en relation les services et les projets qui les concernent. (verbe "concerne") Etapes : 1-selection du compte des lignes de la table concerne telles que le numéro du service soit égale au numéro du service selectionné dans la table service en reference externe. AINSI on a "les services de la table service et le nombre de projets qui les concernent." III tables à utiliser : -Travail car elle met en relation les employés et les projets sur lesquels ils travaillent. (verbe "travaillent"). -Employe car elle met en relation les employés et les services auxquels ils sont affectés. Etapes : 1-Selection du compte des lignes de la table projet telles que pour chacune de ces lignes il existe au moins un resultat retourné par la sous requete suivante : 2-Selection des numéros d'employé de la table travail tels que le numero de projet de la table travail soit égale au numéro de projet selectionné dans la table projet par reference externe... 3-... et tels que le numero d'employé soit retourné par la sous requete suivante : 4-Selection des numeros d'employé de la table employe tels que le service auquel il est affecté soit égal au service selectionné par reference externe. AINSI on a "les services de la table service et le nombre de projets qui les concernent, ainsi que le nombre de projets qui où travaille au moins un employé affecté à ce service." AINSI on a donc la "Liste des noms de services, avec pour chaque service, le nombre de projets qui le concerne, et le nombre de projets où travaille au moins un employé du service". 4. Donnez le principe du « Exists », « not in », « group by » et du « having » dans un « Select » (Expliquez avec vos mots et pas ceux du cours). Principe du NOT IN : Si l'on veut verifier que la valeure d'un attribut donné ne soit pas retourné par une sous requete retournant des valeurs du meme type d'attribut, on utilise un NOT IN. Par exemple : "(nuproj,affect) not in (select nuproj,nuserv from basetd.concerne)" est un prédicat qui permet de verifier que le couple (numéro de projet, numéro d'affectation) n'est pas égal (NOT IN) à chacun des couples (numéro de projet, numéro de service) de la table concerne. (numéro de projet et numéro de projet étant du meme type, concerne et numéro de service aussi (car "concerne" est un numéro de service)) Principe du EXISTS : Si l'on veut verifier que ce qu'on retourne dans une requete admette un élément (pas obligatoirement selectionné par la requete) qui obéisse au moins une fois à une certaine condition. Par exemple : "select nomproj from projet where exists (select nuempl from travail where travail.nuproj=projet.nuproj)" Permet de selectionne les noms de projet de la table projet tels que : AU MOINS UNE FOIS un numéro d'employé se trouve dans la table travail alors que le numéro de projet correspondant est égal à celui selectionné dans la table projet en reference externe. Principe du GROUP BY : obligatoire à utiliser si un resultat va retourner plusieurs fois la meme ligne. On groupe selon un ou plusieurs atribut(s). par exemple, si l'on veut selectionner les numeros d'employé et le nombre de projets sur lesquels ils travaillent :(select nuempl, count(*) from travail) pour chaque ligne de la table travail ou l'employé apparait, on retourne le numero de cet employé et le nombre de lignes. S'il apparait deux fois, on retourne deux fois ce meme resultat. D'ou l'utilisation du group by, pour afficher une seule fois ce resultat en fonction du numéro d'employé : (select nuempl, count(*) from travail group by nuempl) principe du HAVING : Si l'on veut restreindre un ensemble de lignes retournées par une requete déjà groupée par un group by à un sous ensemble de ces lignes telles qu'il existe une condition particulière respectée par ce sous ensemble. Il vaut mieux que cette condition soit un critère d'évaluation du groupe. Par exemple : Si l'on veut, comme precedemment, selectionner les numéros d'employé et le nombre de projets sur lesquels ils travaillent, mais qu'en plus, cette somme soit égale à 1, on va verifier dans le group by que cette somme est égale à 1, par un "having" : select nuempl, count(*) from travail group by nuempl having count(*)=1