Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 1 Regroupement de données Les

Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 1 Regroupement de données Les requête de regroupement sont utilisées pour 1. totaliser des données, 2. Calculer des statistiques 3. Localiser des données erronées 4. Recherche de tendances Trois type de regroupement : Simple requête, sans la clause GROUP BY Requête utilisant la clause GROUP BY  Requête analyse croisée dynamique  Utilisant la clause TRANSFORM Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 2 Fonction prédéfinies Langage SQL!!! Type Description AVERAGE Moyenne d’un champs des tuples COUNT Le nombre des valeurs non vide de la colonne COUNT(*) Le nombres des tuples MINIMUM La valeur minimale MAXIMUM La valeur maximale SUM La somme des valeur d’un attribut Fiste([colonne]) Valeur de la première ligne non vide de la colonne Laste([Colonne]) Valeur de la dernière ligne non vide de la colonne StDev[(Colonne)] Calcule l’écart type pour les valeur non vide de la colonne StDeP[(Colonne)] Calcule l’écart type de population pour les valeur non vide Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 3 – COUNT : Compter le nombre des professeurs : SELECT Count(*) AS Nbr FROM Professeur; – SUM : Calculer la somme des coefficients de toutes les matières. SELECT SUM(Coef_Matiere) AS Somme FROM Matiere. – AVG : Affiche la note maximal de CC1 pour la matière de base de données : SELECT AVG(CC1) AS Moyenne FROM Notes WHERE Code_Matiere =‘M1’ S.Q.L Exemple d’utilisation des fonctions prédéfinies Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 4 S.Q.L E1 Alaoui Karim Tanger E4 Kalouch Ouard Tanger E2 Chakour Rachide Tetouan E3 Daoud Noufal Fes SELECT * FROM Etudiants GROUP BY Ville; Exemple 1 : Afficher tous les étudiants regroupés par ville Exemple 2 : Afficher la somme des notes pour chaque étudiant SELECT Code_Etudiant, SUM(CC1+CC2) Som FROM Notes GROUP BY Code_Etudiant; E1 12 E2 6 E3 14 E4 2 E5 14 Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 5 S.Q.L Exemple 1 : Afficher le nombre des étudiants qui ont passé l’examen pour chaque matière Exemple 2 : Afficher la somme des notes pour les étudiants qui ont passer trois examens. SELECT Code_Etudiant, Sum(CC1+CC2) Som FROM Notes GROUP BY Code_Etudiant HAVING count(*)=3; E1 12 E2 6 E3 14 SELECT Code_Matiere, COUNT(*) Nbr FROM Notes GROUP BY Code_Matiere M1 5 M2 4 M3 4 Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 6 S.Q.L Exemple 2 : Afficher la moyenne des notes pour chaque étudiants selon un ordre décroissante SELECT Code_Etudiant, AVG((CC1+CC2)*Coef) Moy FROM Notes N, Matiere M WHERE N.Code_Matiere = M.Code_Matiere GROUP BY Code_Etudiant ORDER BY Moy E1 12 E2 6 E3 14 Remarque : - Pour utiliser le champs ‘Coef’ il faut introduire la table ‘Matiere’ dans la clause FROM. Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 7 S.Q.L Les prédicats : 1. Prédicats de Simulation Opérateur Signification IN Il comporte une liste de valeurs et test si une valeur spécifiée apparaît dans une liste ou bien dans une sous- interrogation EXIST Il contient une sous-interrogation qui, associée à la condition de EXIST, peut être évaluée comme vrais ou fausse. NULL Spécifie un test pour détecter les valeurs NULL d’un champs. LIKE Spécifie une comparaison dans laquelle on va confronter un modèle générique avec : - ( _ ) : Pour représenter un blanc. - (?) : Pour représenter un caractère quel quant. - (%) : pour représenter une chaîne de caractères. Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 8 Exemple : IN Affiche tous les étudiants dont la note de CC1 de base de données soit 12,14 ou 16. SELECT Code_Etudiant, Nom_etudiant, CC1 FROM Notes N, Etudiants E WHERE N.Cod_etudiant = E.Cod_Etudiant AND Nom_Matiere = ‘BDD’ AND CC1 IN (12,14,16); Résultat E1 Alaoui 12 E2 Chakour 14 E4 Kalouch 16 Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 9 Exemple : NOT IN SELECT DISTINT Code_professeur, Nom_Professeur FROM Professeur WHERE Code_pro NOT IN (SELECT DISTINT Code_prof FROM Matiere); Résultat P5 Daouda Lister tous les professeurs qui n’enseigne aucune matière. Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 10 Exemple : EXISTS SELECT DISTINT Code_professeur, Nom_Professeur, Pre_Professeur FROM Professeur P WHERE EXISTS (SELECT * FROM Matiere M WHERE P.Code-Prof=M.Code_prof); Résultat Lister tous les professeurs qui enseignent au mois une matière. P1 Chaoui Adnan P2 Banani Adil P3 Samoud Koutar P4 Zine Monir Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 11 Exemple : IS NULL SELECT DISTINT Code_Etudiant, Nom_Etudiant FROM Etudiants E, Notes N WHERE E.Code_Etudiant = N.Code_Etudiant AND Cod_Matier = ‘M1’ AND Note_CC1 IS NUUL; Résultat Lister tous les étudiants qui n’ont pas passé Le CC2 de matière ‘BDD’ Code_Etudiant Nom_Etudiant E1 Alaoui E3 Daoud E5 Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 12 Exemple : IS NOT NULL Résultat Code_Etudiant Nom_Etudiant E1 Alaoui E3 Daoud E5 Lister tous les professeurs qui enseigne au mois une matière. SELECT DISTINT Code_professeur, Nom_Professeur, Pre_Professeur FROM Professeur P, Matières M WHERE P.code_Professeur = M.CodeProfesseur AND Note_CC1 IS NOT NUUL; Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 13 Remarques sur l’utilisation de NULL 1. Rien n’est égale à NULL, même la valeur NULL • WHERE Note_CC1 = NULL ne donne pas les matières qui n’ont pas la notes de CC1 2. La clause SELECT n’autorise pas l’utilisation de NULL • SELECT P.Code_Professeur, NULL FROM Professeur P, Matieres M Cette requête n’est pas valide. 3. On ne peut pas trouver la valeur NULL par exclusion Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 14 Prédicats de quantifiés 1. Nécessite ‘utilisation d’un prédicat de comparaison appliqué aux résultats d’un sous-interrogation, qui contient un opérateur de comparaison (= , >, < …) 2. Ils permettent de tester une valeur unique face à toutes celles qui figurent dans l’ensemble de valeurs de sous- interrogation. 3. Quand l’opérateur utilisé est égal ‘=‘ le terme ANY est interchangeable avec IN 4. Dans de nombreux cas, ANY a le même sens que celui de SOME. Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 15 S.Q.L Les prédicats : 1. Prédicats de quantifiés Opérateur Signification ALL Permet de comparer une valeur X avec un ensemble des valeurs de même type de celui de X d’une sous interrogation SELECT. Le test de comparaison ALL est valide si le test de comparaison entre la valeur X et chaque élément de l’ensemble est valide. ANY Permet de comparer une valeur X avec un ensemble des valeurs de même type de celui de la valeur X d’une sous interrogation SELECT. Le test de comparaison ANNY est valide si le test est valide, au mois entre la valeur X et un élément de l’ensemble. SOME Identique a celui de ANY Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 16 Exemple : ALL Afficher tous les étudiants qui ont la plus grande moyenne dans la matière de base de données. SELECT Code_Etudiant, Nom_etudiant, SUM(Coef*(CC1+CC2)) FROM Etudiants E, Notes N, Matiers M, WHERE N.Cod_etudiant = E.Cod_Etudiant AND N.Code_Matiere = M.Code_Matiere AND Code_Matiere = ‘M1’ GROUP BY 1,2 HAVING SUM(Coef*(CC1+CC2)) >= ALL ( SELECT SUM(Coef*(CC1+CC2)) FROM Notes N, Etudiants E, Matiers M, WHERE N.Cod_etudiant = E.Cod_Etudiant AND N.Code_Matiere = M.Code_Matiere AND Code_Matiere =‘M1’ GROUP BY 1,2); Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 17 Exemple : ANY/SOME Afficher les étudiants dont les moyennes de la matière de base de données supérieur à au mois de la moyenne d’un étudiant de la ville de ‘Tanger’ SELECT Code_Etudiant, Nom_etudiant, SUM(Coef*(CC1+CC2)) FROM Notes N, Etudiants E, Matiers M, WHERE N.Cod_etudiant = E.Cod_Etudiant AND N.Code_Matiere = M.Code_Matiere AND Code_Matiere =‘M1’ GROUP BY 1,2 HAVING SUM(Coef*(CC1+CC2)) >= ANY/SOME( SELECT SUM(Coef*(CC1+CC2)) FROM Notes N, Etudiants E, Matiers M, WHERE N.Cod_etudiant = E.Cod_Etudiant AND N.Code_Matiere = M.Code_Matiere AND ); Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 18 Exemple : MINUS Affiche tous les étudiants dont la note de CC1 de base de données soit 12,14 ou 16. SELECT Code_Etudiant, Nom_etudiant, CC1 FROM Notes N, Etudiants E WHERE N.Cod_etudiant = E.Cod_Etudiant AND Nom_Matiere = ‘BDD’ MINUS SELECT Code_Etudiant, Nom_etudiant, CC1 FROM Notes N, Etudiants E WHERE N.Cod_etudiant = E.Cod_Etudiant AND CC1 NOT IN (12,14,16); Résultat E1 Alaoui 12 E2 Chakour 14 E4 Kalouch 16 Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 19 Exemple : UNION Affiche tous les étudiants de la base de données et dont les notes de CC1 d’une matière de base de données différente de 12,14 et 16. SELECT Code_Etudiant, Nom_etudiant, CC1 FROM Notes N, Etudiants E WHERE N.Cod_etudiant = E.Cod_Etudiant AND Nom_Matiere = ‘BDD’ UNION SELECT Code_Etudiant, Nom_etudiant, CC1 FROM Notes N, Etudiants E WHERE N.Cod_etudiant = E.Cod_Etudiant AND CC1 NOT IN (12,14,16); Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 20 Code_Etud Nom_Etud Note_CC1 E1 Alaoui 12 E2 Chakour 14 E3 Daoud 2 E4 Kalouch 16 Résultat E1 Alaoui 6 E2 Chakour 2 E2 Chakour 3 E3 Daoud 2 E3 Daoud 1 E4 Kalouch 1 Pr. Ezziyyani Mostafa F.S.T de Tanger (2004/2005) 21 Langage de contrôle de donner • permet aux administrateurs et aux certains utilisateur autorisés, dans un environnement multi-utilisateur : – de contrôler les accès à la base de données – De supprimer les autorisations d’accès. – d’établir des procédures pour préserver l’intégrité de données. • Le contrôle des identifications des utilisateurs s’effectuées aux niveau uploads/S4/chapiter42-langage-sql.pdf