Exercice #1

Retrouvez le numéro, le nom et le lieu de chaque projet ainsi que le numéro du département qui les contrôle.

NUMÉRO, NOM, LIEU, DNUM( PROJET )

SELECT *FROM PROJET

Exercice #2

Retrouvez le nom des départements qui sont contrôlés par « Pierre St-Jean ».

DÉPARTEMENT.NOM, ( DEPARTEMENT |X|NAS_DIR = NAS EMPLOYE.NOM =

‘ST-JEAN’ EMPLOYE.PRÉNOM = ‘PIERRE’ EMPLOYE )

Exercice #2

Retrouvez le nom des départements qui sont contrôlés par « Pierre St-Jean ».

SELECT NOMFROM DEPARTEMENT WHERE NAS_DIR IS (SELECT NAS

FROM EMPLOYÉWHERE NOM LIKE ‘ST-JEAN’

AND PRÉNOM LIKE ‘PIERRE’ )

Exercice #3

Retrouvez les nom et prénom de tous les employés ayant un prénom composé. (Le trait d’union sépare les prénoms composés )

NOM, PRÉNOM ( PRÉNOM LIKE ‘%-%’ (EMPLOYE) )

SELECT NOM, PRÉNOMFROM EMPLOYÉWHERE PRÉNOM LIKE ‘%-%’

Exercice #4

Retrouvez les nom et prénom des employés ayant un deuxième prénom. (Ex. : John B Smith )

NOM, PRÉNOM ( NOM_MI ‘ ’ (EMPLOYE) )

SELECT NOM, PRÉNOMFROM EMPLOYÉWHERE NOM_MI IS NOT NULL

Exercice #5

Déterminez ce que serait le salaire de chaque employé du département « CONCEPTION » si celui-ci était doublé. Retrouvez les nom, prénom et le nouveau salaire.

EMPLOYE.NOM, PRÉNOM, SALAIRE*2 (EMPLOYE |X|DNO = NUMÉRO

DEPARTEMENT.NOM = ‘CONCEPTION’ DEPARTEMENT)

SELECT E.NOM, PRÉNOM, SALAIRE*2FROM EMPLOYÉ E, DEPARTEMENT DWHERE DNO = NUMÉRO AND

D.NOM = ‘CONCEPTION’

Exercice #6

Retrouvez le nom de tous les dépendants qui sont nés le même jour que l’employé de qui ils dépendent.

DÉPENDANT.NOM ( EMPLOYE |X|NAS = ENAS DÉPENDANT.DATE_N =

EMPLOYE.DATE_N DÉPENDANT )

SELECT D.NOMFROM EMPLOYÉ E, DÉPENDANT DWHERE NAS = ENAS ANDE.DATE_N = D.DATE_N

Exercice #7

Retrouvez les nom et prénom de toutes les femmes qui travaillent sur le projet numéro 7. ( ‘F’ = Féminin )

EMPLOYE.NOM, PRÉNOM( EMPLOYE |X|NAS = ENAS SEXE = ‘F’

PNUM = 7 TRAVAILLE_SUR )

SELECT NOM, PRÉNOMFROM EMPLOYÉ, TRAVAILLE_SURWHERE NAS = ENAS AND SEXE = ‘F’

AND PNUM = 7

Exercice #8

Pour tous les départements ayant plus de 5 femmes, retrouvez le nom de ces départements ainsi que le nombre de femmes qui y travaillent.

NB_F_DEPT DNAME COUNT NAS( EMPLOYE |X|DNO =

NUMÉRO SEXE = ‘F’ DEPARTEMENT )

COUNT_NAS > 5 (NB_F_DEPT)

SELECT D.NOM, COUNT(*)FROM EMPLOYÉ, DEPARTEMENT DWHERE DNO = NUMÉRO AND SEXE = ‘F’GROUP BY D.NOMHAVING COUNT(*) > 5

Exercice #9

Pour chaque employé travaillant sur plus de deux projets, retrouvez le NAS, le nom, le prénom et le nombre total d’heures travaillées de ceux qui ont travaillé moins de 50 heures.

EMP_PROJSENAS COUNT ENAS, SUM HEURES(TRAVAILLE_SUR)

NAS, NOM, PRÉNOM, SUM_HEURES( EMPLOYE |X|COUNT_ENAS > 2

SUM_HEURES < 50 ENAS = NAS EMP_PROJS )

Exercice #9

Pour chaque employé travaillant sur plus de deux projets, retrouvez le NAS, le nom, le prénom et le nombre total d’heures travaillées de ceux qui ont travaillé moins de 50 heures.

SELECT NAS, NOM, PRÉNOM, SUM( HEURES )FROM EMPLOYÉ , TRAVAILLE_SURWHERE NAS = ENAS GROUP BY NAS, NOM, PRÉNOMHAVING SUM( HEURES ) < 50 AND

COUNT(*) > 2

Exercice #10

Retrouvez le numéro des départements qui ont plus de 5 employés.

NB_EMP_DEPT(DNO, NB_EMP) DNO COUNT NAS(

DEPARTEMENT |X|DNO = NUMÉRO EMPLOYE )

DNO ( NB_EMP > 5 NB_EMP_DEPT )

Exercice #10

Retrouvez le numéro des départements qui ont plus de 5 employés.

a) Sans utiliser l’instruction HAVINGSELECT NUMÉROFROM DEPARTEMENTWHERE ( SELECT COUNT(*)

FROM EMPLOYÉ WHERE DNO = NUMÉRO ) > 5

b) En utilisant l’instruction HAVING

SELECT NUMÉROFROM DEPARTEMENT, EMPLOYÉWHERE NUMÉRO = DNOGROUP BY NUMÉROHAVING COUNT(*) > 5

Exercice #11

Listez les noms des employés qui ont au moins un dépendant du même sexe qu'eux.

EMPLOYE.NOM ( EMPLOYE |X|NAS = ENAS DÉPENDANT.SEXE =

EMPLOYE.SEXEDÉPENDANT )

SELECT NOM, PRÉNOMFROM EMPLOYÉ EWHERE EXIST ( SELECT *

FROM DEPENDANTWHERE NAS = ENAS AND

SEXE = E.SEXE )

Exercice #12

Trouvez les noms des employés qui ne sont pas directement supervisés par "Albert Einstein".

NAS_AE NAS( NOM = ‘EINSTEIN’ PRÉNOM =‘ALBERT’EMPLOYE)

EMP_NAS_AE NOM, NAS_AE (NAS_AE X EMPLOYE )

EMP_NAS_SUP NOM, NAS_SUP ( EMPLOYE )

NOM ( EMP_NAS_SUP - EMP_NAS_AE )

Exercice #12

Trouvez les noms des employés qui ne sont pas directement supervisés par "Albert Einstein".

SELECT NOM,PRÉNOMFROM EMPLOYÉ WHERE NAS_SUP IS NOT

( SELECT NAS FROM EMPLOYE

WHERE NOM LIKE ‘EINSTEIN’ ANDPRÉNOM LIKE ‘ALBERT’ )

Exercice #13

Retrouvez les noms des employés qui travaillent sur aucun projet.

TRAV_ENAS ENAS, NOM ( TRAVAILLE_SUR |X|NAS=ENAS

EMPLOYE )

EMP_NAS_NOM NAS, NOM( EMPLOYE )

NOM ( EMP_NAS_NOM – TRAV_NAS )

Exercice #13

Retrouvez les noms des employés qui travaillent sur aucun projet.

SELECT NOM,PRÉNOMFROM EMPLOYÉWHERE NOT EXIST ( SELECT *

FROM TRAVAILLE_SURWHERE NAS = ENAS )

Exercice #14

Pour chaque employé, retrouvez les nom et prénom des employés ainsi que leur nombre de dépendants.

NB_F_DEPT NOM, PRÉNOM COUNT NAS( EMPLOYE |X|ENAS =

NAS DÉPENDANT )

SELECT E.NOM, PRÉNOM, COUNT(*)FROM EMPLOYÉ E, DÉPENDANTWHERE NAS = ENASGROUP BY E.NOM, PRÉNOM

Exercice #15

Recherchez les noms des employés du département “ Recherche ” qui ont travaillé moins de 10 heures sur le projet numéro 5.

EMP_D_RECH NAS, EMPLOYE.NOM (

EMPLOYE |X|NUMÉRO = NAS

DEPARTEMENT.NOM = ‘RECHERCHE’ DEPARTEMENT )

NOM( EMP_D_RECH |X|NAS = ENAS HEURES < 10 PNUM = 5

TRAVAILLE_SUR )

Exercice #15

Recherchez les noms des employés du département “ Recherche ” qui ont travaillé moins de 10 heures sur le projet numéro 5.

SELECT E.NOM, PRÉNOMFROM EMPLOYÉ E, DEPARTEMENT D,

TRAVAILLE_SURWHERE DNO= NUMÉRO AND

NAS = ENAS ANDD.NOM = ‘Recherche’ ANDPNUM = 5 ANDHEURES < 10

Exercice #16

Listez les noms des employés qui travaillent sur au moins un projet ayant pour lieu “ Houston ”.

P_HOUSTON ENAS, PNUM ( PROJET |X|NUMÉRO = PNUM

NOM = ‘Houston’ TRAVAILLE_SUR )

NOM, PRÉNOM ( P_HOUSTON |X|NAS = ENASEMPLOYE )

Exercice #16

Listez les noms des employés qui travaillent sur au moins un projet ayant pour lieu “ Houston ”.

SELECT E.NOM, PRÉNOMFROM EMPLOYÉ E WHERE EXIST ( SELECT *

FROM PROJET P, TRAVAILLE_SUR

WHERE ENAS = NAS ANDPNUM = NUMÉRO ANDLIEU LIKE ‘Houston’

)

Exercice #17

Listez le nom de chaque projet avec le nom du département qui le contrôle ainsi que le nombre d’employés qui travaille dessus.

P_NB_EMPL( NUMÉRO, NOM, DNUM, NB_EMPL)

NUMÉRO, NOM, DNUM COUNT ENAS( PROJET |X|NUMÉRO = PNUM

TRAVAILLE_SUR )

P_NB_EMPL.NOM, DEPARTEMENT.NOM, NB_EMPL( P_NB_EMPL |X|

DNUM = DEPARTEMENT.NUMÉRO DEPARTEMENT )

Exercice #17

Listez le nom de chaque projet avec le nom du département qui le contrôle ainsi que le nombre d’employés qui travaille dessus.

SELECT P.NOM, D.NOM, COUNT(*)FROM PROJET P, DEPARTEMENT D,

TRAVAILLE_SURWHERE DNUM = D.NUMÉRO AND

PNUM = P.NUMÉROGROUP BY P.NOM, D.NOM

Exercice #18

Retrouvez le nom et le lieu de chaque département ainsi que le nombre de projet qu’il contrôle.

DEPT_NB_PROJ(DNUM, DNOM, NB_PROJ)

DEPARTEMENT.NUMÉRO, DEPARTEMENT. NOM COUNT PROJET.NUMÉRO

( DEPARTEMENT |X| DEPARTEMENT.NUMÉRO = DNUM PROJET)

DNOM, DLIEU, NB_PROJ( DEPT_NB_PROJ |X|DNUM=DNUMÉRO

LIEU_DEPT )

Exercice #18

Retrouvez le nom et le lieu de chaque département ainsi que le nombre de projet qu’il contrôle.

SELECT D.NOM, DLIEU, COUNT(*)FROM PROJET P, DEPARTEMENT D,

LIEU_DEPTWHERE DNUM = D.NUMÉRO AND

DNUMÉRO = D.NUMÉROGROUP BY D.NOM, DLIEU

Exercice #19

Retrouvez le nom et le lieu de chaque département ainsi que le nombre d’employés qu’il contienne.

DEPT_NB_EMPL(DNUM, DNOM, NB_EMPL) NUMÉRO,

DEPARTEMENT. NOM COUNT NAS ( DEPARTEMENT |X|

NUMÉRO = DNO EMPLOYE )

DNOM, DLIEU, NB_EMPL( DEPT_NB_EMPL |X|

DNUM=DNUMÉROLIEU_DEPT )

Exercice #19

Retrouvez le nom et le lieu de chaque département ainsi que le nombre d’employés qu’il contienne.

SELECT D.NOM, DLIEU, COUNT(*)FROM EMPLOYE, DEPARTEMENT D,

LIEU_DEPTWHERE DNO = NUMÉRO AND

DNUMÉRO = NUMÉROGROUP BY D.NOM, DLIEU

Exercice #20

Pour chaque département ayant plus de 10 employés, retrouvez leur nom et leur lieu ainsi que le nombre de projet qu’il contrôle.

DEPT_NB_EMPL(DNUMÉRO, DNOM, NB_EMPL) NUMÉRO,

DEPARTEMENT. NOM COUNT NAS ( DEPARTEMENT |X|

NUMÉRO = DNO EMPLOYE )

DEPT_NB_PROJ(DNUM, DNOM, NB_PROJ) DNUMÉRO,

DNOM COUNT PROJET.NUMÉRO ( DEPT_NB_EMPL |X|

DNUMÉRO = DNUM NB_EMPL > 10 PROJET)

(suite)

Exercice #20

DNOM, DLIEU, NB_PROJ( DEPT_NB_PROJ |X|DNUM=DNUMÉRO

LIEU_DEPT )

Exercice #20

Pour chaque département ayant plus de 10 employés, retrouvez leur nom et leur lieu ainsi que le nombre de projet qu’il contrôle.

SELECT D.NOM, DLIEU, COUNT(*)FROM PROJET P, DEPARTEMENT D,

LIEU_DEPTWHERE DNUM = D.NUMÉRO AND

DNUMÉRO = D.NUMÉRO AND( SELECT COUNT(*)FROM EMPLOYEWHERE DNO = D.NUMÉRO) > 10

GROUP BY D.NOM, DLIEU