Pour pouvoir tester vos compétences en sql , et plus précisément , en recherche des données via l’ordre SELECT; voilà un deuxième TP  ( TP SELECT ) qui présente un cas réel de gestion des données. Un TP avec 11 questions pour vous aidez à voir les différents scénarios possibles. Si vous n’avez pas vu le premier TP, le voilà : TP1 sur l’ordre select.

Vous devez suivez les questions et proposez votre solution dans la partie des commentaires. Ainsi, vous serez amené à comprendre vos erreurs et donc ne les refaire jamais par la suite.

TP SELECT : TP2

  Soit le shcéma relationnel suivant :

  • Emp(numE,nom,prenom,dateEmbauche,poste,#numSupérieur, #numdep,salaire, commission)
  • DEPT (numdep,nom,numregion)

Partie 1: 

1- Rechercher le nom et le poste des employés qui ont le même poste que ‘diouani’.

2- Dans ce qui précède, la sous-requête retourne une seule valeur, l’opérateur égalité peut être utilisé.
Si ce n’est pas le cas il faut utiliser les clauses
ANY ou ALL.

Question :   Rechercher le nom, le salaire et le numéro  de département des employés qui gagnent plus qu’un employé du département 31.Les données sont classés par numéro de département et salaire.

 3- Rechercher le nom, le salaire et le numéro de département des employés qui gagnent plus que tous les

employés du service 31, classés par numéro de département et salaire.

 

4- En fait «IN» est équivalent à «= ANY» et «NOT IN» est équivalent à «!= ALL».

Question :    Rechercher le nom et le poste des employés du service 31 qui ont un poste que l’on trouve dans le service 32.

 5- Rechercher le nom et le poste des employés du service 31 qui ont un poste que l’on ne trouve pas dans le service 32.

Partie 2 :

 6-  Rechercher le nom, le poste et le salaire des employés qui ont le même poste et le même salaire que ‘Alaoui’.

 7- Renommer une table et utiliser une sous-requête permet de synchroniser une sous-requête avec la requête

principale.

Question :  Rechercher  le numéro de département, le nom et le salaire des employés qui gagnent plus
que la moyenne de leur département, classés par département.

 

8- L’opérateur EXISTS permet de retourner des lignes si la sous-requête en retourne.

 Question :   Rechercher le numéro, le nom et le prénom des employés pour lesquels il existe au moins un Représentant dans leur département.

 9-   Rechercher le numéro et le nom du département dans lesquels il n’y a personne

10– donner la liste des employés qui ont le meme supérieur.

11– donner la liste des employés qui n’ont pas le meme supérieur.

TP SELECT : solution

1-  SELECT NOM, poste   FROM EMP

     WHERE poste = (SELECT poste  FROM EMP

                                  WHERE NOM = ‘Adili’)  ;

 2- SELECT NOM, SALAIRE, NODEPT    FROM EMP

    WHERE SALAIRE > ANY (SELECT SALAIRE    FROM EMP

                                                   WHERE NODEPT = 31)

     ORDER BY 3, 2 ;

 3- SELECT NOM, SALAIRE, NODEPT    FROM EMP

    WHERE SALAIRE > ALL (SELECT SALAIRE

                                                  FROM EMP

                                                  WHERE NODEPT = 31)

    ORDER BY 3, 2  ;

4- SELECT NOM, poste    FROM EMP

    WHERE NODEPT = 31

    AND poste IN (SELECT poste   FROM EMP

                            WHERE NODEPT = 32)

5- SELECT NOM, poste    FROM EMP

   WHERE NODEPT = 31

   AND poste NOT IN (SELECT poste          FROM EMP

                                         WHERE NODEPT = 32) ;

 6- SELECT NOM, TITRE, SALAIRE     FROM EMP

    WHERE (poste, SALAIRE) = (SELECT poste, SALAIRE             FROM EMP

                                                        WHERE NOM = ‘Alaoui’);

7- SELECT NODEPT, NOM, SALAIRE     FROM EMP E

    WHERE SALAIRE > (SELECT AVG(SALAIRE)            FROM EMP

                                        WHERE NODEPT = E.NODEPT)

    ORDER BY NODEPT ;

 8- SELECT NOEMP, NOM, PRENOM    FROM EMP E

    WHERE EXISTS (SELECT   *          FROM EMP

                                WHERE poste=’Representant’

                                 AND NODEPT = E.NODEPT) ;

solution 2 :

select *

from employe

where numdept in (select numdept

                                 from  employe

                                where  poste = ”representant”) ;

9-  SELECT NODEPT, NOM AS “Dept”      FROM DEPT

     WHERE NODEPT NOT IN (SELECT DEPT.NODEPT              

                                                        FROM EMP, DEPT              

                                                       WHERE EMP.NODEPT = DEPT.NODEPT) ;

10-select E2.*

from Emp  E1 , Emp E2

where E1.numsup=E2.numsup

and E1.numE1<>E2.num ;

11-  select  *

     from Emp

where  numE  not in ( select E2.numE

from Emp  E1 , Emp E2

where E1.numsup=E2.numsup

and E1.numE1<>E2.num) ;

Vous pouvez suivre notre cours complet d’SQL . Il vous aide à approfondir vos compétences sur la création d’une base de données relationnelle et la manipulation des données avec les ordres sql : select, update, delete, insert

Aussi, vous avez une playlist des vidéos (Explication en Darija arabe marocaine)  bien organisée qui traite : Les bases de données (partie SQL)

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *