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)