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. SQL. Structured Query Language. Schéma de la base de référence.
An Image/Link below is provided (as is) to download presentation
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.Content is provided to you AS IS for your information and personal use only.Download presentation by click this link.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.During download, if you can't get a presentation, the file might be deleted by the publisher.
E N D
More Related Content
Presentation Transcript
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
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
Clauses minimales SELECT attr1, attr2, .... attrn FROM relation; • Exemples: SELECT Nom_d FROM Département; SELECT Nom, Nod "numéro" FROM Projet;
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;
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
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;
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
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;
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
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');
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
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
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é;
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;
MIN( ) • Quel est le revenu minimum (commission comprise) ? SELECT MIN(Salaire + Comm) FROM Employé;
MAX( ) • Quel est la commission maximale perçue ? SELECT MAX(Comm) FROM Employé;
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;
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é;
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
Exemple • Donner le nombre d'employés par département. SELECT Nod, COUNT(*) FROM Employé GROUP BY Nod;
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
Exemples • Donner le salaire moyen des femmes par département. SELECT Nod, AVG(Salaire + Comm) FROM Employé WHERE Sexe = 'F' GROUP BY Nod;
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;
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;
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
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
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
Différence • Donner la liste des employés qui n'encadrent personne. SELECT No_ss FROM Employé EXCEPT SELECT No_chef FROM Employé
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)
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é)
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);
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;
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))
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
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;
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