Initiation aux bases de données et à la programmation événementielle

Initiation aux bases de données et à la programmation événementielle Cours N°2 : langage d’interrogation de données. (Les requêtes statistiques et autres). Support de cours de Souheib BAARIR. Page web : pagesperso-systeme.lip6.fr/Souheib.Baarir/bdvba/support.htm E-mail : souheib.baarir@u-paris10.fr Université Paris Ouest Nanterre la Défense. 2009-2010.

SQL : requêtes statistiques • Plusieurs types de requêtes : • d’interrogation. • de manipulation : insert, update, delete… • de définition : create, alter, drop… Les requêtes statistiques SELECT [DISTINCT] liste de champs FROM liste de tables [WHERE prédicats] , fonctions d’agrégats Liste des champs servant à l’agrégation de plusieurs enregistrements [GROUP BY définition des groupes] [HAVING condition] Autre clause de restriction qui portent sur les fonctions et champs d’agrégat.

Requêtes statistiques • Le calcul se fait sur les données d’un groupe de plusieurs enregistrements d’une table R0. • Ces enregistrements doivent avoir un point commun : des champs qui ont la même valeur. • Ils sont agrégés pour n’en faire plus qu’unqui comprend : • Une partie des champs communs (éventuellement tous). • Les résultats des calculs. • Les enregistrements ainsi décrits forment une nouvelle table R1(le résultat de la requête).

Les fonctions d’agrégats • Syntaxe : nom_de_la_fonction (expression). • Sémantique : calcule une valeur à partir des valeurs de tous les enregistrements agrégés. • Les fonctions disponibles sont : • Sum : somme • Count : compte • Avg : moyenne • Min : le plus petit • Max : le plus grand • StDev : écart type • Var : variance • First : premier • Last : dernier

Fonctions d’agrégats : Sum Sum: fournit la somme d’un champ Stock La quantité de tout le matériel en stock ? SELECT Sum(quantité) FROM Stock SELECT Sum(quantité) as QG FROM Stock

Fonctions d’agrégats : Count (1/2) Count: permet de compter le nombre d’enregistrements étudiants Quel est le nombre d’étudiants par formation ? SELECT formation, count ([N° étudiant]) as Nombre FROM étudiant Group by formation

Fonctions d’agrégats : Count (2/2) étudiants Quelles sont les formations de Plus de 2 étudiants? SELECT formation, count([N° étudiant]) as Nombre FROM étudiant Group by formation Having count([N° étudiant]) > 2

Important ! • Tous les champs sur lesquels on fait la projection (clause Select) doivent servir dans laclause d’agrégat (clause Group By). • Mais,un champ peut figurer dans la clause d’agrégat sans servir à la projection. • De même, les champs utilisés dans la restriction Where peuvent ne pas servir à la projection.

Exemples SELECT formation, count([N° étudiant]) FROM étudiant Group by formation Having count([N° étudiant]) >2 formation  formation SELECT formation, prénom, count([N° étudiant]) FROM étudiant Group by formation Having count([N° étudiant]) >2 formation prénom  formation SELECT formation, prénom, count([N° étudiant]) FROM étudiant Group by formation, prénom Having count([N° étudiant]) >2 formation, prénom  formation, prénom formation SELECT formation, count([N° étudiant]) FROM étudiant Group by formation, prénom Having count([N° étudiant]) >2 prénom formation 

SQL : les tris • Plusieurs types de requêtes : • d’interrogation. • de manipulation : insert, update, delete… • de définition : create, alter, drop… SELECT [DISTINCT] liste de champs FROM liste de tables [WHERE prédicats] , fonctions d’agrégats [GROUP BY définition des groupes] [HAVING condition] [ORDER BY liste de champs]

Les tris : exemple 1 étudiants Quelles sont les formations triées par ordre alphabétique? SELECTformation FROM étudiant Order By formation SELECT Distinct formation FROM étudiant Order By formation

Les tris : exemple 2 étudiants Quelles sont les formations triées par ordre alphabétique inversé? SELECTformation FROM étudiant Order By formation DESC

Exercice : base de données Cinéma • Catégories(codeC,intitulé) • Réalisateurs(codeR,nom,prénom,nationalité) • Films(codeF,titre,durée, refR,refC) • Acteurs(codeA,nom,prénom) • Joue(refF,refA) codeC codeR codeF refR refC codeA refF refA

BD Cinéma : Qui joue dans « mission impossible » ? • Catégories(codeC,intitulé) • Réalisateurs(codeR,nom,prénom,nationalité) • Films(codeF,titre,durée,refR,refC) • Acteurs(codeA,nom,prénom) • Joue(refF,refA) SELECT Acteurs.nom,Acteurs.prénom FROM Films INNER JOIN (Acteurs INNER JOIN Joue ON Acteur.codeA =Joue.refA) ON Films.codeF=Joue.refF WHERE films.titre="Mission impossible"

BD Cinéma : Combien de films a réalisé « Spielberg » ? • Catégories(codeC,intitulé) • Réalisateurs(codeR,nom,prénom,nationalité) • Films(codeF,titre,durée,refR,refC) • Acteurs(codeA,nom,prénom) • Joue(refF,refA) SELECT count(codeF) as Nombre FROM Réalisateurs INNER JOIN Films ON Films.refR=Réalisateurs.codeR WHERE Réalisateurs.nom="Spielberg"

BD Cinéma : Combien de films de chaque catégorie a réalisé « Spielberg » ? • Catégories(codeC,intitulé) • Réalisateurs(codeR,nom,prénom,nationalité) • Films(codeF,titre,durée,refR,refC) • Acteurs(codeA,nom,prénom) • Joue(refF,refA) SELECT Catégorie.intitulé,count(codeF) as Nombre FROM Réalisateurs INNER JOIN (Films INNER JOIN Catégories ON Films.refC= Catégories.codeC) ON Films.refR=Réalisateurs.codeR WHERE Réalisateurs.nom="Spielberg" Groupe By Catégorie.codeC, Catégorie.intitulé

BD Cinéma : Quels sont les réalisateurs dont le nom commence par « Spiel »? • Catégories(codeC,intitulé) • Réalisateurs(codeR,nom,prénom,nationalité) • Films(codeF,titre,durée,refR,refC) • Acteurs(codeA,nom,prénom) • Joue(refF,refA) SELECT Réalisateurs.nom FROM Réalisateurs WHERE Réalisateurs.nom LIKE"Spiel*" LIKE : compare deux expression avec des joker. * : Joker qui signifie une chaine de n’importe quelle taille. ? : Joker qui signifie un seul caractère.

BD Cinéma : Quels sont les films de durée entre 90 et 120 minutes? • Catégories(codeC,intitulé) • Réalisateurs(codeR,nom,prénom,nationalité) • Films(codeF,titre,durée,refR,refC) • Acteurs(codeA,nom,prénom) • Joue(refF,refA) SELECT Films.titre FROM Films WHERE Filmes.durée >=90 AND Filmes.durée <=120 SELECT Films.titre FROM Films WHERE Filmes.durée BETWEEN90 AND 120

BD Cinéma : Quels sont les films dont la catégorie n’est pas renseignée? • Catégories(codeC,intitulé) • Réalisateurs(codeR,nom,prénom,nationalité) • Films(codeF,titre,durée,refR,refC) • Acteurs(codeA,nom,prénom) • Joue(refF,refA) SELECT Films.titre FROM Films WHERE ISNULL(Filmes.refC) ISNULL : est une fonction qui teste le renseignement d’une valeur dans un champ.

SQL : sous-requêtes • Plusieurs types de requêtes : • d’interrogation. • de manipulation : insert, update, delete… • de définition : create, alter, drop… • exp op ANY (SELECT...) • exp op ALL (SELECT...) • exp IN (SELECT...) • exp NOT IN (SELECT...) • … Les sous-requêtes SELECT [DISTINCT] liste de champs FROM liste de tables [WHERE prédicats] , (SELECT…. )

Exemple étudiants Quelle est la formation ayant la le plus grand nombre d’étudiants ? SELECT formation FROM étudiant GROUP BY formation HAVING count(N° étudiant) = ( ) SELECT max(nb) as maximum FROM ( ) SELECT count(N° étudiant) as nb FROM étudiant GROUP BY formation

Initiation aux bases de données et à la programmation événementielle