Requtes avances en algbre relationnelle (1/3)
Algbre : suite et fin suivi du Chapitre 6
Q1 : Quelles sont les tailles de disques durs qui sont utilises par au moins 2 PCs ? Q2 : Quels sont les constructeurs vendant l'ordinateur le plus rapide ? Ces requtes sont difficiles exprimer en algbre car il faut compter (Q1) ou calculer un maximum (Q2) On a besoin de comparer des lignes on ne sait lignes, que comparer des colonnes !
1 2
Dpartement Informatique
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Requtes avances (2/3)
Comparer des colonnes Produit cartsien
Q1 : Quelles sont les tailles de disques durs qui sont utilises par au moins 2 PCs ? PC PC 1 x PC 2 Modle HD (renomage) 1001 1.6 1002 1003 1.6 2.5
[Link] 1001 1001 1001 1002 1002 1002 1003 1003 1003
Dpartement Informatique
Requtes avances (3/3)
Solution : Comparer des colonnes Produit cartsien
Q1 : Quelles sont les tailles de disques durs qui sont utilises par au moins 2 PCs ? PC Modle HD [Link] [Link] (PC 1 x PC 2) [Link]
[Link] 1.6 1.6 1.6 1.6 1.6 1.6 2.5 2.5 2.5
[Link] 1001 1002 1003 1001 1002 1003 1001 1002 1003
[Link] 1.6 1.6 2.5 1.6 1.6 2.5 1.6 1.6 2.5
1001 1002 1003
1.6 1.6 2.5
[Link] = [Link] [Link] 1001 1001 1001 1002 1002 1002 1003 1003 1003 [Link] 1.6 1.6 1.6 1.6 1.6 1.6 2.5 2.5 2.5 [Link] 1001 1002 1003 1001 1002 1003 1001 1002 1003 [Link] 1.6 1.6 2.5 1.6 1.6 2.5 1.6 1.6 2.5 4
On peut maintenant comparer les colonnes !
On peut maintenant comparer les colonnes !
3
Sarah Cohen-Boulakia, Bases de donnes
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Pouvoir expressif de lalgbre relationnelle (1/2)
Ce que lalgbre sait exprimer
Au moins 1, au moins 2, au moins 125 (classique, projection-selection-jointure) Tous, aucun (division, diffrence) Exactement 1, exactement 2, .., : difficile ! Mais possible le plus rapide : difficile (diffrence + produit cartsien) NB : Pensez exprimer la ngation de la requte et utilisez la diffrence Spectateurs naimant aucun des films quils ont vus Tous les spectateurs ceux qui ont aim au moins un film
5
Dpartement Informatique
Pouvoir expressif de lalgbre relationnelle (2/2)
Ce que lalgbre ne sait pas exprimer (SQL !) SQL
Combien ? au moins n (avec n dpendant dun calcul) Groupes de valeurs
NB : Certaines requtes ne peuvent pas tre exprimes en SQL non plus La BD est alors interface avec un autre langage (C, JAVA) ou bien on fait du PL/SQL
(cf. 2me anne pour les informaticiens !)
6
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Requtes simples
SELECT * * : liste de tous les attributs FROM FILM Acteur=Adjani FILM WHERE Acteur=Adjani
Chapitre 6 SQL avanc
SELECT Titre Titre[ Acteur=Adjani FILM] FROM FILM WHERE Acteur=Adjani Smantique formelle cas mono-relation :
SELECT B1 ... Bk FROM R WHERE C
7
B1Bk[C R]
8
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Requtes classiques
Renomage des attributs du schma cible
SELECT Titre AS Adjanis movies FROM FILM WHERE Acteur=Adjani
Conditions de la clause SELECT
Comparateurs habituels arithmtique, habituels, concatnation (||), ... Connecteurs : OR, AND et NOT
SELECT Titre FROM FILM WHERE Acteur=Adjani OR ralisateur=Poirier
Valeur des attributs = expression arithmtique
SELECT Titre, Dure*60 AS dure-en-minutes FROM FILM-dure
9
Dpartement Informatique
Motifs pour la recherche de chanes de caractres % : une chane quelconque
SELECT Titre FROM FILM WHERE Titre LIKE %retour%
Le retour du roi, Aliens le retour,
10
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Chanes de caractres (suite)
Majuscules / Minuscules
Pas de distinction pour les mots cls Distinction pour les valeurs des conditions
Exercice 1
Soit le schma suivant
CLIENT (N NomC, AdrC, CP, Ville, Tl, CondPart) C, PRODUIT (N Description, Prix, QtP, Ville) P, C, P, COMMANDE (N Comm, N N QtC, DateC)
Divers
les motifs comme les valeurs sont crits entre ngation possible : NOT LIKE condition dintervalle : att. BETWEEN val1 AND val2
11
Dpartement Informatique
Rpondez aux requtes suivantes (Q1) Lister la description et le prix des produits dont le prix est compris entre 500 et 1000 euros (Q2) Lister les noms et adresses des clients dont le nom commence par un D
12
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Exercice 1 (Correction)
Manipulation de dates
format : aaaa-mm-jj DATE 2003-11-06 BETWEEN DATE 2003-09-25 AND DATE 2004-02-15
13
Dpartement Informatique
14
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Smantique formelle cas multi-relation multiSELECT B1, ..., Bk FROM R1 ... Bp WHERE C
Introduction de variables
Les films avec leur ralisateur et leurs acteurs dans lesquels joue M-F Pisier (ie dans le quel M-F Pisier joue)
B1Bk[C R1 x x Bp]
Les cinmas qui projettent un film dans lequel M.F. Pisier est actrice (pour chaque cinma donner le titre du film et lhoraire)
SELECT Nom-Cine, [Link], Horaire FROM FILM , PROG WHERE [Link]=[Link] AND Acteur=M-F. Pisier
[ Titre[Acteur=[Link] [Link] = [Link] [FILM 1 X [FILM 2 FILM 1] FILM 2]]] SELECT [Link], [Link], [Link]
FROM FILM AS F1 FILM AS F2 -- quiv. FILM F1, FILM F2 F1, WHERE [Link] = [Link] AND [Link]=M-F. Pisier F1 et F2 sont des copies virtuelles de FILM F1 et F2 sont des variables utilises pour dsigner nimporte quel couple de n-uplets de FILM
16
Dpartement Informatique
[Nom-Cine,Titre,Horaire [Acteur=[Link]
Dpartement Informatique
[FILM |X| PROG]]
15
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
SQL : Union, Intersection, Diffrence
Les titres des films dans lesquels joue M-F. Pisier et qui sont laffiche
SELECT Titre FROM FILM WHERE Acteur=M-F. Pisier INTERSECT SELECT Titre FROM PROG
SQL : ensembles et multi-ensembles multi {1, 2, 1, 3} est un multi-ensemble 1 multi select-from-where (par dfaut ALL, multi-ens) Union, Except,Intersect (DISTINCT par
dfaut, ensemble simple)
Les titres des films qui ne sont pas laffiche
SELECT Titre FROM FILM EXCEPT SELECT Titre FROM PROG
Toutes les personnes ayant participes au tournage du film Marion
SELECT Acteur AS Personne FROM FILM WHERE Titre = Marion UNION SELECT ralisateur AS Personne FROM FILM WHERE Titre = Marion
Dpartement Informatique
limination des dupliqus
SELECT DISTINCT Titre FROM FILM
18
Dpartement Informatique
17
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
SQL : Agrgats
SUM() : somme, AVG() : moyenne, MIN() : minimum, MAX() : maximum, COUNT() : cardinalit dun multiensemble Le nombre de films dirigs par Bergman SELECT COUNT(Titre) FROM PROG WHERE ralisateur = Bergman PB : si un mme film de Bergman est plusieurs fois laffiche, il est compt plusieurs fois ! liminer les dupliqus SELECT COUNT (DISTINCT Titre) DISTINCT FROM PROG WHERE ralisateur = Bergman
19
Dpartement Informatique
Groupement
Nombre dacteurs par film
SELECT Titre, COUNT (distinct Acteur) FROM FILM GROUP BY Titre
Projection, regroupement calcul de lagrgat regroupement, (multi-ensemble)
T R r1 r1 r1 r4 r2 r2 r2 A a1 a2 a3 a2 a1 a2 a3 T A
Film, ralisateur, acteur
t1 t1 t1 t2 t1 t1 t1
t1 t1 t1 t1 t1 t1 t2
a1 a2 a3 a1 a2 a3 a2
T t1 t2 3 1
20
Sarah Cohen-Boulakia, Bases de donnes
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Groupement - Importance du Distinct
SELECT Titre, COUNT (Acteur) -- pas de distinct FROM FILM GROUP BY Titre
SQL : Groupement et agrgat
Ajout dun schma Cinephile(NomPers, Nom-Cine) SELECT NomPers, COUNT (DISTINCT Titre) FROM Cinephile, PROG WHERE [Link]-Cine = [Link]-Cine GROUP BY NomPers Distinct : Cas o plusieurs salles projettent le mme film ! (1) jointure naturelle de Cinephile et PROG, (2) projection, projection (3) regroupement et (4) calcul de lagrgat regroupement, Les personnes et le nombre de films quils peuvent voir Clause SELECT en prsence dagrgat SELECT liste1, agg(liste2) FROM liste-relations WHERE condition GROUP BY liste1
Dpartement Informatique
regroupement, Projection, regroupement calcul de lagrgat (multi-ensemble)
T R r1 r1 r1 r2 r2 r2 r4 A a1 a2 a3 a1 a2 a3 a2 T t1 t1 t1 t1 t1 t1 t2 A a1 a2 a3 a1 a2 a3 a2
21
Film, ralisateur, acteur
t1 t1 t1 t1 t1 t1 t2
T t1 t2 6 1
22
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
SQL : la clause HAVING
Les titre de films et le nombre dacteurs des films de plus de 3 acteurs SELECT Titre, COUNT (DISTINCT Acteur) FROM FILM GROUP BY Titre HAVING COUNT(distinct acteur) >= 3 limination des groupes ne satisfaisant pas la condition Les films dirigs par plus de deux metteurs en scne SELECT Titre FROM FILM GROUP BY Titre HAVING COUNT(distinct ralisateur) > 2
23
Dpartement Informatique
Exercice 2 (faire les exercices 3 et 4 aussi)
Soit le schma suivant
CLIENT (N NomC, AdrC, CP, Ville, Tl, C, CondPart) PRODUIT (N Description, Prix, QtP, Ville) P, COMMANDE (N Comm, N N QtC, DateC) C, P,
Rpondez aux requtes suivantes
Combien de clients habitent Paris ? Quel est le prix moyen des produits ? Lister toutes les paires de numros de Clients, tels que ces 2 Clients habitent dans la mme ville
24
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Correction (Exercice 2)
Correction (Exercice 2)
25
Dpartement Informatique
26
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Exercice 3
Considrons le schma de BD suivant
Appareil Appareil(aid: integer, anom: string, autonomie: integer) Employes Employes(eid: integer, enom: string, salaire: integer) Certifis Certifis(eid: integer, aid: integer) // employs
certifis pour voler sur des appareils
Exercice 3 (suite)
Exprimez les requtes suivantes Q1 : Trouvez les noms des pilotes certifis pour certains appareils Boeing. Q2 : Pour chaque pilote certifi sur plus de 3 appareils trouvez son numro demploy et lautonomie maximum de lappareil pour lequel il est certifi
27 28
Dpartement Informatique
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Exercice 3 (correction)
Exercice 4
Soit le schma suivant
CLIENT (N NomC, AdrC, CP, Ville, Tl, C, CondPart) PRODUIT (N Description, Prix, QtP, Ville) P, COMMANDE (N Comm, N N QtC, DateC) C, P,
Rpondez aux requtes suivantes
Quelle est la somme des quantits commandes par produit Quelle est la somme des quantits commandes par produit et par client ?
30
Dpartement Informatique
29
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Exercice 4 (correction)
SQL : Sous-Requtes et Imbrication Sous Utilisation du rsultat dun Select-From-Where Select-From FILM-DEB(Titre, Acteur) stocke le titre du premier film de chaque acteur. Les acteurs du premier film jou par M-F. Pisier ? SELECT Acteur FROM FILM WHERE Titre = (SELECT Titre FROM FILM-DEB WHERE Acteur=M-F. Pisier) SELECT [Link] FROM FILM, FILM-DEB WHERE [Link]=[Link] AND [Link]=M-F. Pisier
Dpartement Informatique
31
Dpartement Informatique
32
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
SQL : Sous-requte avec loprateur IN Sous Les titres des films dont les ralisateurs sont acteurs (pas forcment dans le mme film) SELECT Titre FROM FILM WHERE ralisateur IN (SELECT Acteur FROM FILM ) Le rsultat de la sous-requte est un ensemble de nuplets SELECT [Link] FROM FILM AS F1, FILM AS F2 WHERE [Link] = [Link]
SQL : Sous-requte avec loprateur EXISTS Sous(1/2)
Les films dirigs par au moins deux metteurs en scne SELECT [Link] FROM FILM AS F1 WHERE EXISTS (SELECT [Link] FROM FILM AS F2 WHERE [Link] = [Link] AND [Link] [Link] ) EXISTS teste si le rsultat de la sous-requte est vide sous Autre formulation possible sans sous-requte (exercice)
33 34
Dpartement Informatique
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
SQL : Sous-requte avec loprateur EXISTS Sous(2/2)
Les films dont au moins un acteur a jou dans un autre film
SELECT [Link] FROM FILM AS F1 WHERE EXISTS (SELECT [Link] FROM FILM AS F2 WHERE [Link] = [Link] AND EXISTS (SELECT [Link] FROM FILM AS F3 WHERE [Link]=[Link] ([Link]=[Link]))) AND Not ([Link]=[Link]))
SousSous-requtes coteuses !
Soient 2 schmas de relation R(ABC) et S(BCD) Deux faons dexprimer une jointure entre R et S (et proj. s/A) (1) SELECT A FROM R WHERE (R.B, R.C) IN (SELECT B, C FROM S) (2) SELECT A FROM R AS R1 WHERE EXISTS (SELECT S2.B, S2.C FROM S WHERE R1.B = S.B AND R1.C = S2.C ) Attention : Les sous requtes sont coteuses, viter lorsque cela est possible (cf 2me anne !)
36
Dpartement Informatique
Trs complexe pour rien !!!
Autre formulation sans sous-requte (exercice)
35
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
SousSous-requtes avec ALL ou ANY (1/2)
Les films projets lUGC plus tard que tous les films projets au Trianon
SELECT Titre FROM PROG WHERE Nom-Cine=UGC AND Horaire > ALL (SELECT Horaire FROM PROG WHERE Nom-Cine=Trianon)
SousSous-requtes avec ALL ou ANY (2/2)
Le tlphone des cinmas qui proposent une programmation aprs 23h
SELECT Telephone FROM CINE AS C1 WHERE 23 < ANY (SELECT Horaire FROM PROG WHERE [Link]-Cine = [Link]-Cine)
> ALL teste si la valeur de Horaire est suprieure tous les lments du rsultat de la sous requte
37
Dpartement Informatique
< ANY teste si la valeur 23 est infrieure UN des lments du rsultat de la sous-requte
38
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
Exercice 5
Soit le schma suivant
CLIENT (N NomC, AdrC, CP, Ville, Tl, C, CondPart) PRODUIT (N Description, Prix, QtP, Ville) P, COMMANDE (N Comm, N N QtC, DateC) C, P,
Exercice 5 (correction 1/3)
Rpondez aux requtes suivantes
Dpartement Informatique
Quels sont les produits plus chers que le prix moyen ? Quels sont les clients (numro de client) ayant command 1 produit dont le prix vaut 1000 euros Quel est le produit le moins cher ?
39
Sarah Cohen-Boulakia, Bases de donnes
Dpartement Informatique
40
Sarah Cohen-Boulakia, Bases de donnes
Exercice 5 (correction 2/3)
Les clients (num) ayant command 1 produit dont le prix = 1000 SELECT N C FROM Commande, Produit WHERE Prix = 1000 AND Commande.N = Produit. N P P Ou bien (inutilement complexe) SELECT N C FROM Commande WHERE N IN (SELECT N P P FROM Produit WHERE Prix = 1000)
41
Dpartement Informatique
Exercice 5 (correction 3/3)
42
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Sarah Cohen-Boulakia, Bases de donnes
SQL : Valeurs nulles
Une valeur nulle remplace une valeur dun attribut
valeur inconnue, attribut inappropri, valeur incertaine, valeur cache...
Jointure externe
Elle est obtenue en calculant la jointure de R et S puis en y ajoutant
# les n-uplets de R non joingnables avec un n-uplet de nS et complts avec des valeurs nulles $ les n-uplets de S non joingnables avec un n-uplet de nR et complts avec des valeurs nulles
R S Jointure externe de R et S
Comparaison avec une valeur nulle
vrai=1, faux=0, inconnu=1/2 : logique tri boolene ! x AND y =min(x,y) , x OR y =max(x,y), Not x =1-x
Attention : loi du tiers exclu nest plus valide
p OR (NOT p) pour p=1/2
A 1 3
B 2 4
B 2 2 7
C 5 6 8
A 1 1 3 Null
B 2 2 4 7
C 5 6 Null 8
44
43
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
SQL : jointure externe
SQL2 propose une varit de formes de jointures : externe, naturelle R NATURAL JOIN S correspond exactement la jointure algbrique R CROSS JOIN S quivaut SELECT * FROM R, S R JOIN S ON R.B = S.B quivaut SELECT * FROM R, S WHERE R.B = S.B R OUTER JOIN S (jointure + $ et #) R RIGHT OUTER JOIN S (seul $ est effectu) R LEFT OUTER JOIN S (seul # est effectu)
45
Dpartement Informatique
Sarah Cohen-Boulakia, Bases de donnes
