20 Déc2015

Les requêtes SELECT- Base de données RH

CHAOULID |  SQL |  Chapitre: Langage de manipulation de données |  76204 visites |  Moyen |  Corrigé

Objectif: Écrire des requêtes SELECT comportant la projection, le regroupement, le tri et les jointures.

Soit le schéma relationnel suivant :

rh2
Modèle Relationnel : RH

Travail à faire:

Écrire les requêtes SELECT répondant aux questions suivantes:

  1. Donner la liste des numéros et noms des employés du département 20
  2. Donner la liste des numéros et noms des ouvriers et leur numéro de département
  3. Donner les noms des vendeurs du département 30 dont le salaire est supérieur à 1500
  4. Donner la liste des noms, fonctions et salaires des directeurs et des présidents
  5. Donner la liste des noms, fonctions et salaires des directeurs et des employés qui ont un salaire > 5000
  6. Donner la liste des noms et fonctions des directeurs du département 10 et des ouvriers du département 20.
  7. Donner la liste des noms, des fonctions et des numéros du département des employés qui ne sont pas ni ouvrier ni directeur
  8. Donner la liste des noms, fonctions et salaires des employés qui gagnent entre 2200 et 2800
  9. Donner la liste des noms, des fonctions et des numéros du département des employés ouvrier, secrétaire ou vendeur
  10. Donner la liste des employés dont le responsable est connu
  11. Donner la liste des employés dont le responsable n’est pas connu
  12. Donner la liste des salaires, fonctions et noms des employés du département 20, selon l’ordre croissant des salaires
  13. Donner la liste des salaires, fonctions et noms des employés du département 20, selon l’ordre décroissant des salaires
  14. Donner la liste des employés triée selon l’ordre croissant des fonctions et l’ordre décroissant des salaires
  15. Donner la moyenne des salaires des tous les employés
  16. Donner la moyenne des salaires des ouvriers.
  17. Donner le plus haut salaire et le plus bas salaire de tous les employés
  18. Donner le nombre d’employés du départment 10
  19. Donner le nombre de différentes fonctions occupées par les employés du département 10
  20. Donner la moyenne des salaires pour chaque département (afficher numéro de département et moyenne),
  21. Donner pour chaque département, le salaire annuel moyen des employés qui ne sont pas ni directeurs ni président
  22. Donner pour chaque fonction de chaque département le nombre d’employés et le salaire moyen.
  23. Donner la liste des salaires moyens pour les fonctions comportant plus de deux employés.
  24. Donner la liste des numéros de départements avec au moins deux secrétaires.
  25. Donner le nom de chaque employé et la ville dans laquelle il/elle travaille.
  26. Donner la ville dans laquelle travaille l'employé 1035.
  27. Donner les noms, fonctions et noms des départements des employés des départements 20 et 30
  28. Donner les noms des tous employés et les noms de leur responsable (renommer l’attribut responsable en ‘CHEF’)
  29. Donner la liste des noms et salaires des employés qui gagnent au moins que leur responsable (afficher aussi les noms des responsables)
  30. Donner la liste des noms, salaires, fonctions des employés qui gagnent plus que l'employé 1035.
  31. Donner les noms des tous employés et, s’il est connu, les noms de leur responsable (renommer l’attribut responsable en ‘CHEF’)
  32. Donner les noms des employés et les noms de projets aux quels ils participent.
  33. Donner les projets aux quels l'employé 1035 participe.
  34. Donner les noms des ingénieurs qui participent au projet ‘EAST_MARKETS’
  35. Donner les noms des tous les employés et les noms de projets aux quels ils participent (même s’ils ne participent à aucun projet)
  36. Donner les noms des tous les directeurs et les noms de projets aux quels ils participent (même s’ils ne participent à aucun projet)
  37. Donner les noms et fonctions des employés qui gagnent plus que 'Mahmoudi'.
  38. Donner les noms département où il y a des employés qui gagnent plus de 3000.
  39. Donner les fonctions dont la moyenne des salaires est inférieure à la moyenne de celle des ingénieurs
  40. Donner les employés qui ont occupé les fonctions de vendeurs et d’ouvriers
  41. Donner les employés qui n’ont jamais été vendeurs
  42. Donner les projets où ne travaillent que des ingénieurs
//Auteur: CHAOULID
//Copyright: Exelib.net
//**D'autres Écritures sont possibles**

//Q1
select Num_Emp,Nom_Emp
from EMPLOYE
where Num_Dep=20

//Q2
select Num_Emp,Nom_Emp,Num_Dep
from EMPLOYE
where Fonction_Emp='ouvrier'

//Q3
select Nom_Emp
from EMPLOYE
where Fonction_Emp='vendeur' and Num_Dep=30 and Salaire_Emp>1500

//Q4
select Nom_Emp,Fonction_Emp,Salaire_Emp
from EMPLOYE
where Fonction_Emp='President' or Fonction_Emp='Directeur'

//Q5
select Nom_Emp,Fonction_Emp,Salaire_Emp
from EMPLOYE
where (Fonction_Emp='President' or Fonction_Emp='Directeur') and Salaire_Emp>5000

//Q6
select Nom_Emp,Fonction_Emp
from EMPLOYE
where (Fonction_Emp='Directeur' and Num_Dep=10) or (Fonction_Emp='Ouvrier' and Num_Dep=20)

//Q7
select Nom_Emp,Fonction_Emp,Num_Dep
from EMPLOYE
where Fonction_Emp not in ('ouvrier','directeur')

//Q8
select Nom_Emp,Fonction_Emp,Salaire_Emp
from EMPLOYE
where Salaire_Emp between 2200 and 2800

//Q9
select Nom_Emp,Fonction_Emp,Num_Dep
from EMPLOYE
where Fonction_Emp in ('ouvrier','directeur','secrétaire')

//Q10
select *
from EMPLOYE
where Sup_Emp is not  null

//Q11
select *
from EMPLOYE
where Sup_Emp is null

//Q12
select Salaire_Emp,Fonction_Emp,Nom_Emp
from EMPLOYE
where Num_Dep=20
order by Salaire_Emp

//Q13
select Salaire_Emp,Fonction_Emp,Nom_Emp
from EMPLOYE
where Num_Dep=20
order by Salaire_Emp desc

//Q14
select *
from EMPLOYE
order by Fonction_Emp asc,Salaire_Emp desc

//Q15
select AVG(Salaire_Emp) as "Moyenne des salaires"
from EMPLOYE

//Q16
select AVG(Salaire_Emp) as "Moyenne des salaires des ouvriers"
from EMPLOYE
where Fonction_Emp='ouvrier'

//Q17
select MAX(Salaire_Emp) as "Salaire Max",MIN(Salaire_Emp) as "Salaire Min"
from EMPLOYE

//Q18
select COUNT(*) as "Nombre d'Employés du département 10"
from EMPLOYE
where Num_Dep=10

//Q19
select COUNT(distinct(Fonction_Emp)) as "Nombre de fonctions"
from EMPLOYE
where Num_Dep=10

//Q20
select Num_Dep,AVG(Salaire_Emp) as "Moyenne de salaires"
from EMPLOYE
group by Num_Dep

//Q21
select Num_Dep, AVG(Salaire_Emp*12) as "Salaire annuel moyen"
from EMPLOYE
where Fonction_Emp not in ('directeur','president')
group by Num_Dep

//Q22
select Num_Dep,Fonction_Emp,COUNT(Num_Emp) as "Nombre d'employés",AVG(Salaire_Emp) as "Salaire Moyen"
from EMPLOYE
group by Num_Dep,Fonction_Emp

//Q23
select Fonction_Emp,AVG(Salaire_Emp) as "Salaire Moyen"
from EMPLOYE
group by Fonction_Emp
having COUNT(Num_Emp)>2

//Q24
select Num_Dep
from EMPLOYE
where Fonction_Emp='secrétaire'
group by Num_Dep
having COUNT(Nom_Emp)>=2

//Q25
select e.Nom_Emp,d.site_Dep
from EMPLOYE e inner join DEPARTEMENT d on e.Num_Dep=d.Num_Dep

//Q26
select d.site_Dep as "Ville"
from EMPLOYE e inner join DEPARTEMENT d on e.Num_Dep=d.Num_Dep
where e.Num_Emp=1035

//Q27

select e.Nom_Emp,e.Fonction_Emp,d.Nom_Dep
from EMPLOYE e inner join DEPARTEMENT d on e.Num_Dep=d.Num_Dep
where d.Num_Dep=20 or d.Num_Dep=30

//Q28
select e1.Nom_Emp, e2.Nom_Emp as "CHEF"
from EMPLOYE e1 inner join EMPLOYE e2 on e1.Sup_Emp=e2.Num_Emp

//Q29
select e1.Nom_Emp,e1.Salaire_Emp,e2.Nom_Emp
from EMPLOYE e1 inner join EMPLOYE e2 on e1.Sup_Emp=e2.Num_Emp
where e1.Salaire_Emp>=e2.Salaire_Emp

//Q30
select Nom_Emp,Salaire_Emp,Fonction_Emp
from EMPLOYE
where Salaire_Emp> (select Salaire_Emp from EMPLOYE where Num_Emp=1)

//Q31
select e1.Nom_Emp, e2.Nom_Emp as "CHEF"
from EMPLOYE e1 left outer join EMPLOYE e2 on e1.Sup_Emp=e2.Num_Emp

//Q32
select e.Nom_Emp,p.Nom_Proj
from EMPLOYE e inner join TRAVAILLER t on e.Num_Emp=t.Num_Emp inner join PROJET p on t.Code_Proj=p.Code_Proj

//Q33
select p.*
from TRAVAILLER t inner join PROJET p on t.Code_Proj=p.Code_Proj
where t.Num_Emp=1035

//Q34
select e.Nom_Emp
from EMPLOYE e inner join TRAVAILLER t on e.Num_Emp=t.Num_Emp inner join PROJET p on t.Code_Proj=p.Code_Proj
where e.Fonction_Emp='Ingénieur' and p.Nom_Proj='EAST_MARKETS'

//Q35
select e.Nom_Emp
from EMPLOYE e left outer join TRAVAILLER t on e.Num_Emp=t.Num_Emp left outer join PROJET p on t.Code_Proj=p.Code_Proj

//Q36
select e.Nom_Emp
from EMPLOYE e left outer join TRAVAILLER t on e.Num_Emp=t.Num_Emp left outer join PROJET p on t.Code_Proj=p.Code_Proj
where e.Fonction_Emp='directeur'

//Q37
select Nom_Emp,Fonction_Emp
from EMPLOYE
where Salaire_Emp>ALL(select Salaire_Emp from EMPLOYE where Nom_Emp='Mahmoudi')

//Q38
select distinct(d.Nom_Dep)
from EMPLOYE e inner join DEPARTEMENT d on e.Num_Dep=d.Num_Dep
where e.Salaire_Emp>3000

//Q39
select Fonction_Emp
from EMPLOYE
group by Fonction_Emp
having AVG(Salaire_Emp)< (select AVG(Salaire_Emp) from EMPLOYE where Fonction_Emp='Ingénieur')

//Q40
select e.*
from EMPLOYE e inner join HISTORIQUE h on e.Num_Emp=h.Num_Emp
where h.Fonction_Emp='vendeur'
intersect
select e.*
from EMPLOYE e inner join HISTORIQUE h on e.Num_Emp=h.Num_Emp
where h.Fonction_Emp='ouvrier'

//Q41
select *
from EMPLOYE
where Num_Emp not in (select Num_Emp from HISTORIQUE where Fonction_Emp='vendeur')

//Q42
select distinct p.*
from PROJET p inner join TRAVAILLER t on p.Code_Proj=t.Code_Proj
where t.Code_Proj not in
(
select distinct p.Code_Proj
from PROJET p inner join TRAVAILLER t on p.Code_Proj=t.Code_Proj inner join EMPLOYE e on t.Num_Emp=e.Num_Emp
where e.Fonction_Emp !='Ingénieur'
)

 

  • Evaluez cet article :
    Note moyenne
    11 votes
  • Tags : select | SQL
  • Ajoutez aux favoris :
    Ajouter à vos favoris
    Cliquez pour ajouter cet article à vos favoris
  • Dernière modification :
    26-12-15 16:11

Nouveaux Exercices

Exercices populaires

Nouveaux Examens

Examens populaires