Requêtes complexes

1. Requêtes complexes

2. Opérateurs d’ensembles • Ils associent les rangées de 2 requêtes UNION [ALL] INTERSECT MINUS

3. Opérateurs d’ensembles • les 2 ensembles doivent : • avoir le même nombre de colonnes • les colonnes doivent être de même type • la longueur des colonnes peut-être différente

4. UNION • Exemple : • Afficher la liste des numéros de départementsituésà Montréal, ou qui contiennent au moins un employés qui toucheune commission plus grandeque $1000. SELECT id_departement FROM departement WHERE ville = ‘Montréal’ UNION  SELECT id_departement FROM employe WHERE commission >1000;

5. UNION suite • L’union inclus donc dans un nouvel ensemble tous les éléments des deux SELECT. • ALL = toutes les valeurs, même les doublons. Sans le All = valeurs distinctes 10 20 30 10 20 30 UNION

6. INTERSECT • Exemple • Trouver la liste des numéro de départementsituésà Montréal et qui contiennent au moins un employé qui gagne un salaire de plus de $2,900. SELECT id_departement FROM employe WHERE salaire > 2900 INTERSECT  SELECT id_departement FROM departement WHERE Ville = ‘Montréal’;

7. INTERSECT suite • L’intersection met dans un nouvel ensemble tous les éléments communs aux deux SELECT. 10 20 10 10 INTERSECT

8. MINUS • Afficher le numéro des départements qui ont au moins un employé qui gagne plus de $2900 et qui nesont pas situésà Ottawa SELECT id_departement FROM employe WHERE salaire > 2900 MINUS  SELECT id_departement FROM departement WHERE ville = ‘Ottawa’;

9. MINUS suite • Le MINUS met dans un nouvel ensemble la soustraction des éléments du premier SELECT qui sont communs au deuxième SELECT. 10 20 20 10 MINUS Ensemble vide 10 10 20 MINUS

10. Sous-requêtes • Synonyme : requêteimbriquée • Il s’agird’unerequête comprise dans la clause WHERE d’uneautrerequête • Exemple : • Afficher la liste des employés qui sontdumêmedépartementqueRivest SELECT nom FROM employe WHERE id_departement = (SELECT id_departement FROM employe WHERE nom= ‘rivest’);

11. Notes sur les sous-requêtes • L’imbricationpeut continuer indéfiniment. • Unesous-requêtepeutaccéderà des tables différentes

12. Exemple • Produire la liste des employés qui occupent le même type de poste que « Cabana » ou qui ont un salaire plus élevé que le sien: SELECT nom, poste, salaire FROM employe WHERE poste = ( SELECT poste FROM employe WHERE nom = 'cabana' ) OR salaire > ( SELECT salaire FROM employe WHERE nom= 'cabana' );

13. Les sous-requêtesexemple #2 • Trouver la liste des employés de Calgary qui ont un salaire plus élevé que celui de Bergeron. • Solution #1: SELECT imbriqués avec jonction de table SELECT nom, poste, salaire FROM employe, departement WHERE employe.id_departement = departement.id AND ville = 'Calgary‘ AND salaire > ( SELECT salaire FROM employe WHERE nom = 'Bergeron' ); • Solution #2: triple SELECTimbriqués SELECT nom, poste, salaire FROM employe WHERE id_departement IN ( SELECT id FROM departement WHERE ville = 'Calgary' ) AND salaire > ( SELECT salaire FROM employe WHERE nom = 'Bergeron' );

14. Sous-requêtes et fonctions de groupe • Exemple : • Produire la liste des plus anciens employés. SELECT nom FROM employe WHERE date_embauche = ( SELECT MIN(date_embauche) FROM employe);

15. Types de requêtes • Les requêtes peuvent être classées selon le nombre de rangées qu'elles retournent. • 1 - les requêtes qui retournent 0 ou 1 rangée. • 2 - les requêtes qui retournent plus qu'une rangée. • Certains opérateurs ( =,>,<,... ) exigent qu'une sous-requête soit du premier type. • Exemple: • Trouver la liste des employés qui ont le même salaire que Cabana. SELECT nom FROM employe WHERE salaire = ( SELECT // Comparaison de 1ier type salaire FROM employe WHERE nom= 'Cabana' );

16. Liste d’expressions • On peut comparer des listes d’expressions. • Exemple : • Trouver la liste des employés qui ont le même salaire et qui sont du même département que Cabana: SELECT nom FROM employe WHERE ( salaire, id_departement) = ( SELECT salaire, id_departement FROM employe WHERE nom = 'Cabana' );

17. Liste d’expressions • Les 2 listes doivent avoir : • le même nombre d’expressions • les expressions doivent être de même type

18. Sous-requêtes de plusieurs rangées • Certains opérateurs acceptent des sous-requêtes qui retournent plusieurs rangées. • Ces opérateurs sont: • IN • ANY • ALL • EXISTS

19. L'opérateur IN • Compare une valeur à chaque valeur d'un ensemble. • Signifie "est égal à n'importe quel membre d'un ensemble  • Exemple: • Trouver la liste des employés qui travaillent dans un des départements situés à Québec. SELECT nom FROM employe WHERE id_departement IN (SELECT id FROM departement WHERE ville = 'Québec' );

20. L'opérateur ANY • Compare une valeur à chaque valeur d'un ensemble. • Doit être précédé de =,>,<,>= ou <=. • Exemple: • X > ANY (Ensemble) • retourne vrai si X est > que n'importe quellevaleur de l'ensemble.

21. Exemple • Trouvez le nom des gestionnaires qui gagnent un salaire plus petit que l’un des commis. SELECT nom FROM employe WHERE poste = ‘gestion’ AND salaire < ANY ( SELECT salaire FROM employe WHERE poste =‘commis’ );

22. ANY et IN • =ANY est équivalent à IN X =ANY ( A , B , C ) est la même chose que X IN ( A , B , C )

23. L’opérateur ALL • Compare une valeur à chaque valeur d'un ensemble • Doit être précédé de =,>,<,>=, ou <= • Exemple: • X > ALL ( ensemble) • retourne vrai si X est > que toutes les valeurs de l'ensemble.

24. Exemple • Trouvez la liste des employés qui ont été embauchés après les employés du département 20. SELECT nom FROM employe WHERE date_embuche > ALL( SELECT date_embauche FROM employe WHERE id_departement= 20 );

25. Opérateur EXISTS et NOT EXISTS • EXISTS ( Ensemble ) est vrai si l'ensemble contient au moins une valeur. • NOT EXISTS ( Ensemble ) est vrai si l'ensemble est vide.

26. Exemple • Trouver le nom des employés du département 30, si aucun employé du département 30 ne reçoit de commission. SELECT nom FROM employe WHERE id_departement= 30 AND NOT EXISTS ( SELECT * FROM employe WHERE id_departement= 30 AND commission IS NOT NULL );

27. Requête corrélative • Il s’agit d’une sous-requête qui fait référence à des données de la requête principale. • Exemple d’une sous-requête non corrélative: • Trouver les employés qui gagnent le salaire le plus petit du département 20. SELECT nom FROM employe WHERE salaire = (SELECT MIN(salaire) FROM employe WHERE id_departement = 20; La sous requête est exécutée une fois seulement.

28. Requête corrélative suite • Exemple d’une sous-requête corrélative: • Exemple: • Trouver les employés qui gagnent le plus petit salaire de leur département. SELECT nom FROM employee1 WHERE salaire = ( SELECT MIN(e2.salaire) FROM employe e2 WHERE e2.id_departement = e1.id_departement ); La sous requête est exécutée pour chaque rangée de la requête principale.

29. Corrélative et NOT EXISTS • Trouvez le nom des employés qui font partie d'un département dans lequel aucun employé ne reçoit de commission. SELECT e1.nom FROM employee1 WHERE NOT EXISTS ( SELECT * FROM employe e2 WHERE e2.id_departement = e1.id_departement AND e2.commission IS NOT NULL ); • NOT EXISTS doitrienretourné pour êtrevraie.

30. Corrélative et opérateur • Certain énoncés simples peuvent être complexes à résoudre. • Exemple : • quels sont les départements qui embauchent tous les employés de l’usine? • Étape 1: Tous les employés de l’usine SELECT Usine.id FROM employeUsine;

31. Corrélative et opérateur (suite) • Étape 2 : • Est-ce que le département X embauche tous les employés: SELECT ‘Oui’ FROM DUAL WHERE NOT EXISTS( (SELECT Usine.id FROM employe Usine) MINUS (SELECT EmpDept.id FROM employeEmpDept WHERE EmpDept.id = X) ); X n’est pas une valeur valide.

32. Corrélative et opérateur (suite) • Dernière étape : • Il faut généraliser pour tous les départements • (remplacer le X par un générateur) : SELECT e1.id_departement FROM employe e1 WHERE NOT EXISTS( (SELECT Usine.id FROM employe Usine) MINUS (SELECT EmpDept.id FROM employeEmpDept WHERE EmpDept.id = e1.id) );