Réponses TP4 Intro BD

/* 1 Liste des noms de services avec le nom du chef de services et le nombre d’employés affectés
au service */

SELECT nomserv, e1.nuempl, count(*)
FROM BASETD.SERVICE, BASETD.EMPLOYE e1, BASETD.EMPLOYE e2
WHERE Service.chef = e1.nuempl AND BASETD.SERVICE.nuserv = e2.affect
GROUP BY nomserv, e1.nuempl;

/* 2 Liste des noms de projets avec le nom du responsable et le nombre d’employés qui y tra-
vaillent */

SELECT nomproj, e1.nomempl, count(*)
FROM BASETD.PROJET, BASETD.EMPLOYE e1, BASETD.TRAVAIL
WHERE PROJET.resp = e1.nuempl AND TRAVAIL.nuproj = BASETD.PROJET.NUPROJ
GROUP BY nomempl, nomproj;

/* 3 Liste des noms de projets avec la totalisation du nombre d’heures passées par les employés
qui y travaillent */

SELECT nomproj, SUM(duree)
FROM BASETD.PROJET, BASETD.TRAVAIL
WHERE PROJET.nuproj = TRAVAIL.nuproj
GROUP BY nomproj;

/* 4 Liste des noms de projets avec pour chaque service concerné, le nom du service et le nombre
d’employés de ce service qui travaillent sur ce projet */

SELECT nomproj, nomserv, count(*)
FROM BASETD.SERVICE, BASETD.PROJET, BASETD.CONCERNE, BASETD.EMPLOYE
WHERE PROJET.nuproj = CONCERNE.nuproj AND CONCERNE.nuserv = SERVICE.nuserv AND EMPLOYE.affect = SERVICE.nuserv
GROUP BY nomproj, nomserv
ORDER BY nomproj ASC;

/* 5 Liste des employés qui travaillent sur tous les projets */

SELECT nomempl
FROM BASETD.EMPLOYE
WHERE NOT EXISTS(SELECT * FROM BASETD.PROJET WHERE NOT EXISTS
(SELECT * FROM BASETD.TRAVAIL where TRAVAIL.nuempl = EMPLOYE.nuempl AND TRAVAIL.nuproj = PROJET.nuproj));

/* 6 Pour le service Achat, trouvez le nom du chef de service et le nombre d’employés qui y sont
affectés */

SELECT e1.nomempl, count(*)
FROM BASETD.EMPLOYE e1, BASETD.EMPLOYE e2, BASETD.SERVICE
WHERE SERVICE.nuserv = 1 AND SERVICE.chef = e1.nuempl AND e2.affect = SERVICE.nuserv
GROUP BY e1.nomempl;

/* 7 Liste des employés qui travaillent sur au moins un des projets sur lesquels ’sophie’ travaille. */

SELECT nomempl
FROM BASETD.EMPLOYE
WHERE EXISTS (
    SELECT *
    FROM BASETD.TRAVAIL
    WHERE EMPLOYE.nuempl = TRAVAIL.nuempl AND nuproj in(
        SELECT nuproj
        FROM BASETD.TRAVAIL
        WHERE nuempl = (
            SELECT nuempl
            FROM BASETD.EMPLOYE
            WHERE nomempl = 'sophie'))) AND nomempl != 'sophie';