initiation aux bases de données et à la …...•dans une bd, l’information statistique n’est...

25
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). (Les requêtes statistiques et autres). 1 Souheib BAARIR. Page web : http://pagesperso-systeme.lip6.fr/Souheib.Baarir/bdvba/support.htm E-mail : [email protected] Université Paris Ouest Nanterre la Défense. 2009-2010.

Upload: others

Post on 10-Jul-2020

1 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

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). (Les requêtes statistiques et autres).

1

Souheib BAARIR.

Page web :http://pagesperso-systeme.lip6.fr/Souheib.Baarir/bd vba/support.htm

E-mail : [email protected]

Université Paris Ouest Nanterre la Défense.2009-2010.

Page 2: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Données statistiques

Une définition minimale :Données dénombrées et dénombrables !

2

• Le nombre d’étudiants de chaque formation.• La moyenne des notes du module de BD.• Le produit le moins acheté dans un supermarché.• …

Page 3: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

•Dans une BD, l’information statistique n’est pas directement représentée …

•On aura, sinon, de la redondance !

• Sachant que les requêtes SQL de base (vus jusqu’à

Données statistiques et BD

3

• Sachant que les requêtes SQL de base (vus jusqu’àaujourd’hui) ne permettent qu’une extraction d’info rmation simple…,

• la question est, donc,

• comment extraire (visualiser) les données statistiq ues ?

Les requêtes statistiques Les requêtes statistiques

Page 4: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

� Le calcul se fait sur les données d’un groupe de plusieurs enregistrements d’une table T0.

� Ces enregistrements doivent avoir un point commun : des champs qui ont la même valeur .

Requêtes statistiques : principes

4

� Ils sont agrégés pour n’en faire plus qu’un qui comprend :

o Une partie des champs communs (éventuellement tous).

o Les résultats des calculs.

� Les enregistrements ainsi décrits forment une nouvelle table T 1(le résultat de la requête).

Page 5: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Requêtes statistiques : exempleN°auteur

Sexe Nom auteurPrénom auteur

A. naissance A. décès

1 M HUGO Victor 1802 1885

2 F SAND George 1804 1876

3 M DUMAS Alexandre 1802 1870

4 F YOURCENAR Marguerite 1903 1987

5 F De STAËL Germaine 1766 1817

6 M RACINE Jean 1639 1699

Sexe NB

M 4

F 3

T0

T1

5

7 M VERLAINE Paul 1844 1896

Combien y a-t-il d’auteurs par sexe dans la BD ?

� Deux groupes d’enregistrements : dans chaque groupe la valeur du champ sexe est identique.

� Agréger les enregistrements de chaque groupe (pour n’en faire qu’un), avecle résultat des calculs .

Page 6: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Requêtes statistiques : SQL

•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]

[GROUP BY définition des groupes]

[HAVING prédicats]

, fonctions d’agrégats

Liste des champs servant à l’agrégation

de plusieurs enregistrements

Autre clause de restriction qui portent sur les fonctions et

champs d’agrégat.6

Les requêtes statistiques

Page 7: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

• Syntaxe : nom_de_la_fonction (expression).

• Sémantique : calcule une valeur à partir des valeurs de tous lesenregistrements agrégés.

Les fonctions d’agrégats

• Les fonctions disponibles sont :� Sum : somme� Sum : somme�Count : compte� Avg : moyenne� Min : le plus petit� Max : le plus grand � StDev : écart type� Var : variance� First : premier� Last : dernier

7

Page 8: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Fonctions d’agrégats : Sum

Sum : fournit la somme d’un champ

N°Produit désignation quantité

1 Marteau 40

La quantité de tout le matériel en stock ?

Stock

2 Pelle 10

3 Truelle 5

4 Tournevis 100

SELECT Sum(quantité) FROM Stock

Exp1000

155

SELECT Sum(quantité) as QG FROM Stock

QG

155 8

Page 9: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Fonctions d’agrégats : Count (1/2)

Count : permet de compter le nombre d’enregistrements

Quel est le nombre d’étudiants par formation ?

étudiantsN°étudiant Nom Prénom formation

105230 MARTIN Marie MMIA

105234 BERNARD Louis Gestion

SELECT formation, count ([N°étudiant]) as NombreFROM étudiantGroup by formation formation Nombre

MMIA 1

Gestion 3

Anglais 2 9

105237 THOMAS Alice Anglais

105239 MARTIN Bernard Anglais

105250 MARIE Philippe Gestion

105211 Mounier Alice Gestion

Page 10: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Quelles sont les formations de plus de 2 étudiants?

N°étudiant Nom Prénom formation

105230 MARTIN Marie MMIA

105234 BERNARD Louis Gestion

105237 THOMAS Alice Anglais

105239 MARTIN Bernard Anglais

étudiants

Fonctions d’agrégats : Count (2/2)

SELECT formation, count([N°étudiant]) as NombreFROM étudiantGroup by formationHaving count([N°étudiant]) > 2

105250 MARIE Philippe Gestion

105211 Mounier Alice Gestion

formation Nombre

Gestion 3

10

Page 11: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

�Tous les champs sur lesquels on fait la projection (clause Select) doivent servir dans la clause d’agrégat (clause Group By).

Important !

11

�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 Wherepeuvent ne pas servir à la projection.

Page 12: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

ExemplesSELECT formation, count([N°étudiant])FROM étudiantGroup by formationHaving count([N°étudiant]) >2

SELECT formation, prénom, count([N°étudiant])FROM étudiantGroup by formationHaving count([N ° étudiant]) >2

����

prénom

����

formation

formation

formation

formation

12

Having count([N ° étudiant]) >2

SELECT formation, prénom, count([N°étudiant])FROM étudiantGroup by formation, prénomHaving count([N°étudiant]) >2

����

SELECT formation, count([N°étudiant])FROM étudiantGroup by formation, prénomHaving count([N°étudiant]) >2

����

formation, prénom

formation, prénom

formation

formation prénom

Page 13: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

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]

[GROUP BY définition des groupes]

[HAVING condition]

, fonctions d’agrégats

13

[ORDER BY liste de champs]

Page 14: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Les tris : exemple 1

Quelles sont les formations triées par ordre alphabétique?

N°étudiant Nom Prénom formation

105230 MARTIN Marie MMIA

105234 BERNARD Louis Gestion

105237 THOMAS Alice Anglais

105239 MARTIN Bernard Anglais

105250 MARIE Philippe Gestion

étudiants

14

SELECT formation FROM étudiantOrder By formation

105250 MARIE Philippe Gestion

105211 MOUNIER Alice Gestion

Formation

Anglais

Anglais

Gestion

Gestion

Gestion

MMIA SELECT Distinct formation FROM étudiantOrder By formation

Formation

Anglais

Gestion

MMIA

Page 15: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Les tris : exemple 2

Quelles sont les formations triées par ordre alphabétique

inversé ?

N°étudiant Nom Prénom formation

105230 MARTIN Marie MMIA

105234 BERNARD Louis Gestion

105237 THOMAS Alice Anglais

105239 MARTIN Bernard Anglais

105250 MARIE Philippe Gestion

étudiants

15

SELECT formation FROM étudiantOrder By formation DESC

105250 MARIE Philippe Gestion

105211 MOUNIER Alice Gestion

Formation

MMIA

Gestion

Gestion

Gestion

Anglais

Anglais

Page 16: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Exercice : BD Cinéma

•Catégories (codeC,intitulé)

•Réalisateurs (codeR,nom,prénom,nationalité)codeR

codeC

16

•Films (codeF,titre,durée, refR,refC)

•Acteurs (codeA,nom,prénom)

•Joue (refF,refA)

refR refC

refF

codeF

refA

codeA

Page 17: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

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)

17

SELECT nom, prénom, nationalité

FROM Films INNER JOIN (Acteurs INNER JOIN Joue ONActeur.codeA =Joue.refA) ON Films.codeF=Joue.refF

WHERE titre= "Mission impossible"

•Joue (refF,refA)

Page 18: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

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)

18

SELECT count(codeF) as Nombre

FROM Réalisateurs INNER JOIN Films ON Films.refR=Réalisateurs.codeR

WHERE nom= "Spielberg"

•Joue (refF,refA)

Page 19: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

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)

19

•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é

Page 20: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

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)

20

SELECT Réalisateurs.nomFROM 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.

•Joue (refF,refA)

Page 21: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

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)

21

SELECT Films.titreFROM Films

WHERE Filmes.durée BETWEEN 90 AND 120

SELECT Films.titreFROM Films

WHERE Filmes.durée >= 90 AND Filmes.durée <=120

•Joue (refF,refA)

Page 22: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

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)

22

SELECT Films.titreFROM Films

WHERE ISNULL(Filmes.refC)

ISNULL : est une fonction qui teste le renseigneme nt d’une valeur dans un champ.

•Joue (refF,refA)

Page 23: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

SQL : sous -requêtes

•Plusieurs types de requêtes :� d’interrogation.� de manipulation : insert, update, delete… � de définition : create, alter, drop…

Les sous -requêtes

SELECT [DISTINCT] liste de champs

FROM liste de tables

[WHERE prédicats]

23

Les sous -requêtes

, (SELECT…. )• exp IN (SELECT...)• exp NOT IN (SELECT...)• exp op ANY (SELECT...)• exp op ALL (SELECT...)• EXISTS (SELECT...)• NOT EXISTS (SELECT...)

[GROUP BY groupes]

[HAVING prédicats]

Page 24: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

Sous -requêtes : exemple 1

•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)

24

SELECT nom, prénom

FROM Acteurs

WHERE CodeA NOT IN (SELECT refA FROM Joue)

Quels sont les acteurs qui n’ont joué dans aucun fi lm de notre BD ?

Page 25: Initiation aux bases de données et à la …...•Dans une BD, l’information statistique n’est pas directement représentée … •On aura, sinon, de la redondance ! • Sachant

N°étudiant Nom Prénom formation

105230 MARTIN Marie MMIA

105234 BERNARD Louis Gestion

105237 THOMAS Alice Anglais

105239 MARTIN Bernard Anglais

105250 MARIE Philippe Gestion

105211 Mounier Alice Gestion

Quelle est la formation ayant la le plusgrand nombre d’étudiants ?

étudiants

nb

1

Sous -requêtes : exemple 2

SELECT formationFROM étudiantGROUP BY formationHAVING count(N°étudiant) = (

)

105211 Mounier Alice Gestion

SELECT count(N°étudiant) as nb FROM étudiant GROUP BY formation

SELECT max(nb) as maximumFROM (

)

1

3

2

maximum

3

formation

Gestion