Programme

1. Programme • Introduction aux BD et aux SGBD • Le modèle relationnel • Le langage de requête SQL • La conception d’une BD relationnelle • Protection des informations • Perspectives des BD

2. SQL Structured Query Language

3. Schéma de la base de référence • Employé(Nom_emp, Prénom_emp, No_ss, Date_naiss, Adresse_emp, Sexe, Salaire, Comm, No_chef, Nod) • Département(Nom_d, No_dept, No_dir, Date_affect) • Dept_local(Num_dept, Id_loc) • Projet(Nom, No_proj, Local_proj, Nod) • Travaille_sur(No_ss, No_projet, Nb_heures)

4. Instance

5. Instance (suite)

6. Interrogation de la base de données • Présentation des résultats sous forme tabulaire où: • en-tête de colonne: attribut • largeur de colonne cf. définition du domaine de l'attribut • alias possible pour entête de colonne

7. Clauses minimales SELECT attr1, attr2, .... attrn FROM relation; • Exemples: SELECT Nom_d FROM Département; SELECT Nom, Nod "numéro" FROM Projet;

8. Joker SELECT * FROM Projet;

9. Elimination des doublons SELECT DISTINCT Local_proj FROM Projet;

10. Synonymes SELECT P.Nom, P.Nod FROM Projet P;

11. Clause WHERE SELECT attr1, attr2, .... attrn FROM relation WHERE condition; Exemple: « Liste des employés travaillant plus de 20 heures sur un projet. » SELECT No_ss, No_projet FROM Travaille_sur WHERE Nb_heures > 20;

12. Liste des employés dont la commission est supérieure à leur salaire SELECT No_ss FROM Employé WHERE Comm > Salaire; Liste des employés du département 5 dont le salaire est supérieur à 300 kF SELECT No_ss FROM Employé WHERE Salaire > 300 AND Nod = 5; Liste des employés qui ne sont pas du département 5 SELECT No_ss FROM Employé WHERE Nod <> 5; Liste des départements localisés à Paris ou à Fontainebleau SELECT Num_dept FROM Dept_local WHERE Id_loc IN ('Paris', 'Fontainebleau'); Exemples

13. Clause ORDER BY • ORDER BY {attribut [DESC | ASC]} • Liste des employés ordonnée par ordre croissant de salaire SELECT No_ss FROM Employé ORDER BY Salaire;

14. Liste des employés et leurs départements ordonnée par ordre décroissant de salaire à partir de 300 kF SELECT No_ss FROM Employé WHERE Salaire > 300 ORDER BY Salaire DESC; Liste des employés et leurs départements ordonnée par ordre croissant de salaire et par ordre décroissant de département SELECT No_ss FROM Employé ORDER BY Salaire, 1, Nod DESC; Exemples

15. Requête multi-relations • Critère de jointure: clause WHERE • Notation préfixée (relation.attribut) • Exemple: Dans quelles villes sont les départements R&D? (Equi-jointure) SELECT Id_loc FROM Département, Dept_local WHERE Nom_d = 'R&D' AND No_dept = Num_dept;

16. Sur quels projets travaillent des femmes ? SELECT No_projet FROM Travaille_sur, Employé WHERE Sexe = 'F' AND Travaille_sur.No_ss = Employé.No_ss; ou SELECT No_projet FROM Travaille_sur T, Employé E WHERE Sexe = 'F' AND T.No_ss = E.No_ss; Employés qui gagnent plus que leur responsable (auto-jointure) SELECT Nom_emp FROM Employé E, Employé CHEF WHERE E.No_chef = CHEF.No_ss AND E.Salaire > CHEF.Salaire Exemples

17. Sous-requêtes SELECT attr1, attr2, .... attrn FROM relation WHERE attribut opérateur (SELECT ...); • Quels sont les employés des départements R&D ? SELECT Nom_emp FROM Employé WHERE Nod IN ( SELECT No_dept FROM Département WHERE Nom_d = 'R&D');

18. Expressions et fonctions numériques dans la clause SELECT • Liste des employés et de leur rémunération totale (salaire et commission) SELECT E.No_ss, E.Salaire + E.Comm FROM Employé E;

19. Expressions numériques dans la clause WHERE • Liste des employés dont la commission dépasse la moitié de leur salaire. SELECT E.No_ss FROM Employé E WHERE E.Comm > 0.5 * E.Salaire

20. Expressions numériques dans la clause ORDER BY • Liste des employés triée par ordre croissant de rémunération totale SELECT E.No_ss FROM Employé E ORDER BY E.Comm+ E.Salaire;

21. Fonctions agrégats • 5 fonctions agrégatives: • AVG ( ): calcul de la moyenne • SUM ( ): calcul de la somme • MIN ( ): calcul de la valeur minimale • MAX ( ): calcul de la valeur maximale • COUNT ( ): calcul du nombre de tuples

22. AVG ( ) • Donner la moyenne des salaires. SELECT AVG(Salaire) FROM Employé; • Donner la moyenne des revenus d'un salarié SELECT AVG(Salaire + Comm) FROM Employé;

23. SUM( ) • Donner la somme des salaires, et la somme des commissions perçues par les employés. SELECT SUM(Salaire), SUM(Comm) FROM Employé; • Donner la somme des salaires des employés ne percevant pas de commission. SELECT SUM(Salaire) FROM Employé WHERE Comm = 0;

24. MIN( ) • Quel est le revenu minimum (commission comprise) ? SELECT MIN(Salaire + Comm) FROM Employé;

25. MAX( ) • Quel est la commission maximale perçue ? SELECT MAX(Comm) FROM Employé;

26. COUNT() • Quel est le nombre d'employés du département 5 ? SELECT COUNT(No_ss) FROM Employé WHERE Nod = 5; ou SELECT COUNT(*) FROM Employé WHERE Nod = 5;

27. Exemples • Attention SELECT Nom_emp, AVG(Salaire) FROM Employé; • Donner le plus haut salaire, le plus bas salaire et la différence entre les deux SELECT MAX(Salaire), MIN(Salaire), MAX(Salaire) - MIN(Salaire) FROM Employé;

28. Clause GROUP BY • Regroupement de tuples • Relation initiale partitionnée horizontalement selon les valeurs d'un attribut ou d'un groupe d'attributs. • Permet de répondre aux questions du type: • donner le nombre d'employés par département.  • constituer des groupes d'employés pour chaque département • évaluer au sein de chacun de ces groupes le nombre d'éléments

29. Exemple • Donner le nombre d'employés par département. SELECT Nod, COUNT(*) FROM Employé GROUP BY Nod;

30. Evaluation du résultat • Relation initiale

31. Evaluation du résultat • Tri et partitionnement

32. Evaluation du résultat • Résultat

33. Exemples • Attention: tous les attributs figurant dans la clause SELECT sans être soumis à un agrégat doivent apparaître dans la clause GROUP BY SELECT Nom_emp, AVG(Salaire) FROM Employé GROUP BY Nod; • Problème

34. Exemples • Donner le salaire moyen des femmes par département. SELECT Nod, AVG(Salaire + Comm) FROM Employé WHERE Sexe = 'F' GROUP BY Nod;

35. Clause HAVING • Exprimer des conditions sur le résultat après partitionnement • Exemple: Donner la liste des salaires moyens par département si celui-ci excède 400 kF. SELECT Nod, AVG(Salaire) FROM Employé GROUP BY Nod HAVING AVG(Salaire) > 400;

36. Exemple • Donner la liste des salaires moyens des femmes par département si celui-ci excède 400 kF. SELECT Nod, AVG(Salaire) FROM Employé WHERE Sexe = 'F' GROUP BY Nod HAVING AVG(Salaire) > 400;

37. Opérateurs ensemblistes • Opérateurs binaires • Deux relations de même schéma en entrée

38. Union • Donner la liste des dépenses composant la masse salariale (salaire et commission). SELECT Salaire FROM Employé UNION SELECT Comm FROM Employé • L'opérateur d'union élimine automatiquement les doublons

39. Exemple • Attention aux schémas • Donner la liste des départements avec leurs directeurs et des départements avec leurs localisations SELECT No_dept, No_dir FROM Département UNION SELECT Num_dept, Id_loc FROM Dept_local • Problème

40. Intersection • Quels sont les employés qui encadrent d'autres employés et qui travaillent sur un projet ? SELECT No_chef FROM Employé INTERSECT SELECT No_ss FROM Travaille_sur

41. Différence • Donner la liste des employés qui n'encadrent personne. SELECT No_ss FROM Employé EXCEPT SELECT No_chef FROM Employé

42. Questions quantifiées • Prédicats • ANY • ALL • EXISTS

43. ANY • Teste si la valeur d'un attribut satisfait un critère de comparaison avec au moins un résultat d'une sous-requête • Exemple: Quels sont les employés travaillant sur au moins un des projets de l'employé 13334. SELECT No_ss FROM Travaille_sur WHERE No_projet = ANY (SELECT No_projet FROM Travaille_sur WHERE No_ss =13334)

44. ALL • Teste si la valeur d'un attribut satisfait un critère de comparaison avec tous les résultats d'une sous-requête. • Exemple: Quels sont les employés les mieux payés. SELECT No_ss FROM Employé WHERE Salaire >= ALL (SELECT Salaire FROM employé)

45. EXISTS • Teste si la réponse à une sous-requête est vide. • Exemple: Donner le nom des employés travaillant au moins sur un projet. SELECT Nom_emp FROM Employé E WHERE EXISTS ( SELECT No_projet FROM Travaille_sur T WHERE T.No_ss = E.No_ss);

46. Exemple • Quels sont les noms d'employés ayant au moins un autre employé sous leurs ordres. SELECT Nom_emp FROM Employé E1 WHERE EXISTS ( SELECT No_ss FROM Employé E2 WHERE E2.No_chef = E1.No_ss) ORDER BY Nom_emp;

47. Prédicat EXISTS pour exprimer une division • Quels sont les départements répartis sur tous les sites? • Un département est conservé s'il n'existe aucun site sur lequel il ne soit pas situé. SELECT No_dept FROM département D WHERE NOT EXISTS (SELECT Id_loc FROM Dept_local L1 WHERE NOT EXIST (SELECT L2.* FROM Dept_local L2 WHERE L2.Id_loc = L1.Id_loc AND L2.Num_dept = D.No_dept))

48. Synthèse SQLEquivalent AR • SELECT liste d'attributs, agrégats Projection • FROM liste de relations Produit cartésien • WHERE conditions Restriction + jointure ou autre requête Division, jointure • GROUP BY liste d'attributs • HAVING condition, agrégats • ORDER BY liste d'attributs • UNION | INTERSECTION | EXCEPT Union, intersection, différence

49. Exemple complet • Liste des départements avec le nombre d'employés ne percevant pas de commission et travaillant sur un projet si celui-ci est supérieur à 1 triée par ordre croissant de département. SELECT D.Nom_d, D.No_dept, COUNT(*) FROM Département D, Employé E, Travaille_sur T WHERE E.Comm=0 AND D.No_dept =E.Nod AND E.No_ss = T.No_ss GROUP BY No_dept HAVING COUNT(*) > 1 ORDER BY No_dept;

50. Commandes de mise à jour • Insertion de nouveaux tuples: INSERT • Mise à jour de valeurs: UPDATE • Suppression de tuples: DELETE • Suppression de tous les tuples d'une relation: TRUNCATE