[PDF] 1 Requêtes SQL (encore plus) avancées

View - Download 1 Requêtes SQL (encore plus) avancées

Previous PDF

Next PDF

Base de donnees

TD 3L3 MIASHS-M1 ISIFAR

2016-2017

Aujourd'hui, nous allons utiliser un autre schema, le schemasakilaqui contient des informations utilisees par un magasin de location de DVD. Le schema est visible sur Moodle (Sciences / UFR de math / Master ISIFAR / BD ). Vous pouvez aussi inspecter les tables comme d'habitude avec bd_2017=# \d sakila.film bd_2017=# \d sakila.actor

1 Requ^etes SQL (encore plus) avancees

1.1 Requ^etes imbriquees

Les requ^etes imbriquees permettent d'utiliser le resultat d'une requ^ete dans la clause WHEREd'une requ^ete. On utilisera essentiellement les deux connecteurs suivants :IN, EXISTS. INpermet de tester la presence d'une valeur dans le resultat d'une requ^ete.EXISTSrenvoie Truesi la requ^ete donnee est non-vide etFalsesinon. On peut les combiner avecNOTpour inverser leur comportement :NOT INetNOT EXISTS. Par exemple, pour conna^tre les lms

disponibles en allemand, on pourra ecrire :SELECTFROMs akila. f ilmWHEREl anguageidI N( SELECTl anguageid

FROM s akila. l anguage WHERE n ame=

German

Pour conna^tre les acteurs qui ont joue dans au moins un lm, on pourra ecrire :SELECTFROMs akila. a ctorA Sa cWHEREE XISTS( SELECTFROMs akila. f ilmactora sf a

WHERE f a. a ctorid = ac . actorid ) ;

1.2 Jointure externe

La jointure externe est une jointure un peu particuliere. On a vu la semaine derniere que lorsqu'on faisait une jointure, les lignes de la table de droit etaient recollees aux lignes de la table de gauche. Si une ligne a gauche ne pouvaient pas ^etre recollee, elle disparaissait de la jointure. La jointure exterieure permet de garder ces lignes-la malgre tout. On utiliseraLEFT JOINetRIGHT JOIN. Par exemple, la requ^ete suivante renvoie la liste des pays et leur langages. Les pays qui ne se trouvent pas dans la tablecountrylanguage(il y en a, l'antarctique par exemple) seront listes quand m^eme et les informations manquantes

seront remplies avec des valeursNULL.SELECTFROMw orld. c ountrya sp LEFTJO INw orld. c ountrylanguagea sl

ON p .c ountrycode= l . c ountrycode; On peut utiliser cette requ^ete pour trouver les pays qui n'ont pas de langue ocielle par exemple :SELECTFROMw orld. c ountrya sp LEFTJO INw orld. c ountrylanguagea sl ON p .c ountrycode= l . c ountrycode A ND l . i s o f f i c i a l WHERE l . c ountrycodeI S N ULL 1

Base de donnees

TD 3L3 MIASHS-M1 ISIFAR

2016-2017

2 Exercices

2.1 Requ^etes

1. Q uelsson tl esl anguesqu ine s ontoc iellesd ansauc unpa ys?

Ecrivez une version avec

NOT INet une autre avecLEFT JOIN.

Solution:Premiere version :SELECTl anguageF ROMc ountrylanguage WHERE l anguage N OT I N

SELECT

l anguage F ROM c ountrylanguage

W HERE

i s o f f i c i a l ) ; Deuxieme version :SELECTl 1. l anguageF ROMc ountrylanguagea sl 1 LEFT JO IN c ountrylanguage a s l ON ( l 1. l anguage= l . l anguage a nd l . i s o f f i c i a l ) WHERE l . l anguageI S N ULL Troisieme version :SELECTl anguageF ROMc ountrylanguagec l WHERE N OT

E XISTS

SELECT

language F ROM c ountrylanguagec l1 WHERE c l1. l anguage=cl. l anguage A ND cl1 . i s o f f i c i a l ) ;

En calcul relationnel

n l:language:countrylanguage(l)^

9tcountrylanguage(t)^

l:language=t:language^t:isofficialo2.Q uelsson tl es lmsqu in' ontjam ais etelou es? Solution:La encore, plusieurs possibilites. Avec ce qu'on a vu la semaine derniere :WITH DejaLoueA S

SELECT

f ilmidF ROMs akila. r entalN ATURALJO INs akila. i nventory) ,

NonLoue

A S

SELECT

f ilmidF ROMs akila. f ilmE XCEPTS ELECTFROMD ejaLoue)SELECTt i t l e F ROMf ilmN ATURALJ OINN onLoue;

Avec les requ^etes imbriquees :SELECTt itle, f ilmidF ROMs akila. f ilm WHERE f ilmidN OTI N( SELECTf ilmidF ROM sakila . rental

N ATURAL

J OIN s akila. i nventory) ; 2

Base de donnees

TD 3L3 MIASHS-M1 ISIFAR

2016-2017

En calcul relationnel

n f:title:film(f)^

9t;t1inventory(t)^ 9t1rental(t1)^

f:film_id=t:film_id^t:inventory_id=t1:inventory_idoCette question est exactement du m^eme type que la precedente. On y repond de la

m^eme de la m^eme maniere : pour trouverdans la baseles objets d'un certain type qui ne possedent pas une propriete, on cherche dans la base tous les objets de ce type et on fait la dierence avec l'ensemble des objets de ce type qui possedent la propriete dans la base. 3. Q uelsson tl esac teursq uion tjou esdan st outesle sc ategoriesd e lm? Solution:WITH ActCatA S( SELECTa ctorid , categoryidF ROMs akila. f ilmactor

NATURAL

J OIN s akila. f ilmcategory ) ,

ActNot

A S

SELECT

a ctoridF ROMs akila. a ctor, s akila. c ategory WHERE ( a ctorid , categoryid )N OTI N( SELECTFROMA ctCat)) , ActIdA S( SELECTa ctoridF ROMs akila. a ctor

EXCEPT

S ELECTFROMA ctNot)SELECTf irstname , lastnameF ROMs akila. a ctorN ATURALJO INA ctId; Cette requ^ete realise une operation sophistiquee de l'algebre relationnelle ladivision ou. Il ne s'agit pas d'une operation primitive comme;;. La version suivante calcule le m^eme resultat, et suit delemenyt le plan d'execution le plus elementaire pour realiser la division.WITH ActCatA S( SELECTa ctorid , categoryidF ROM sakila . filmactorN ATURALJ OINs akila. f ilmcategory ) ,

ActCrosCat

A S

SELECT

a ctorid , categoryidF ROMs akila. a ctor, sakila . category ) ,

ActNotCat

A S SELECTFROMA ctCrosCatEXCEPTS ELECTFROMA ctCat), ActIdA S( SELECTa ctoridF ROMs akila. a ctorE XCEPT

SELECT

a ctoridF ROMA ctNotCat)

SELECT

f irstname , lastnameF ROMs akila. a ctorN ATURALJO INA ctId; 4. Ex iste-t-ild esac teursq uin ej ouenta vecauc unau treact eur?

Solution:WITH CopainA S

SELECT

R 1.a ctoridF ROMs akila. f ilmactora sR 1

JOIN s akila. f ilmactora sR 2 ON ( R1.f ilmid = R2. filmidA NDR 1.a ctorid != R2. actorid ) 3

Base de donnees

TD 3L3 MIASHS-M1 ISIFAR

2016-2017SELECTa ctoridF ROMs akila. a ctor

WHERE a ctoridn oti n( SELECTFROMC opain); ou avecNOT EXISTSSELECTa ctoridF ROMs akila. a ctora WHERE N OT

E XISTS

SELECT

f a2. a ctorid FROM s akila. f ilmactor fa1J OINs akila. f ilmactor fa2 ON ( f a1. a ctorid=a . actoridA ND fa2 . actorid<>a . actoridA ND fa1 . filmid=fa2 . filmid )

Cette question ressemble beaucoup aux questions 1 et 2.5.Nom, p renomde scl ientsi nstallesdan sd esvi llessan smagas ins?

Solution:WITH CustomerCityA S

SELECT

f irstname , lastname , customerid , cityid FROM s akila. c ustomer

N ATURAL

JO IN s akila. a ddress) ,

StoreCity

A S

SELECT

c ityidF ROMs akila. s toreN ATURALJ OINs akila. a ddress)

SELECT

f irstname , lastnameF ROMC ustomerCity WHERE

c ityidN OTI N( SELECTFROMS toreCity) ; 6.Li sterl espa ysp ourl esquelst outesl esv illeson tau m oinsu nmagasi n.

Solution:Chaque fois qu'on lit dans la questions"tous»ou"toutes», on est tente de voir une division. Ici ce n'est pas le cas. Il faut d'abord determiner les

villes sans magasins, puis leur pays et retirer ces pays de la liste de tous les pays.WITH citywithoutstoreA S(

SELECT

c ityidF ROMs akila. c ity

EXCEPT

SELECT

D ISTINCT

c . c ityid FROM s akila. s tore

N ATURAL

J OIN s akila. a ddress

NATURAL

J OIN s akila. c ityc

SELECTFROMs akila. c ountryc oWHEREN OTE XISTS( SELECTFROMsakila . countryN ATURALJO INc itywithoutstore ) ;

2.2 Fonctions SQL

Dans votre schema personnel (qui porte le nom de votre identiant ENT), ecrire une fonction SQLfilm_id_catqui prend en parametre une cha^ne de caractereset renvoie la liste des lms de categories. On rappelle la syntaxe : 4

Base de donnees

TD 3L3 MIASHS-M1 ISIFAR

2016-2017CREATEO RR EPLACEF UNCTIONe ntid. f ilmidcat ( s TEXT)

RETURNS

T ABLE

(f ilmidI NTEGER)

LANGUAGE

s ql A S requete Solution:CREATEO RR EPLACEF UNCTIONe ntid. f ilmidcat ( s text )

RETURNS

T ABLE

(f ilmids mallint) L ANGUAGEs ql AS

SELECT

f ilmidF ROMf ilmcategoryN ATURALJ OINc ategory WHERE c ategory. name=s; Utilisez votre fonction pour ecrire les requ^etes suivantes : 1. Q uelsson tl esac teursq uion td ejaj oued ansun l md 'horreur( categorie' Horror')? Solution:SELECTD ISTINCTa c. FROMs akila. a ctora cNATURALJ OIN

SELECTFROMs akila. f ilmactor

WHERE f ilmidI N

SELECTFROMe ntid. f ilmidcat (' H orror' )

) fa ; ouSELECTD ISTINCTa c. FROMs akila. a ctora cN ATURALJ OIN sakila . filmactorN ATURALJ OIN entid . filmidcat (' H orror' ) ; (156 tuples renvoyes).2.Q uelsson tl esac teurq uin' ontjam aisjou ed ansu necom edie( 'Comedy')?

Solution:

Attention! Cette requ^ete ne repond pas a la question :SELECTD ISTINCTa c. FROMs akila. a ctora cN ATURALJ OIN

SELECTFROMs akila. f ilmactor

WHERE f ilmidN OTI N

SELECTFROMe ntid. f ilmidcat (' Comedy' )

) fa ; 5

Base de donnees

TD 3L3 MIASHS-M1 ISIFAR

2016-2017

Elle repond a la question : Quels sont les acteur qui ont joue dans un lm qui n'est pas une comedie? Une reponse correcte estSELECTD ISTINCTa c. l astname , acfirstname FROM s akila. a ctora c WHERE N OT

E XISTS

SELECTFROMs akila. f ilmactor fa

WHERE f ilmidI N

SELECTFROMe ntid. f ilmidcat (' Comedy' ) )

AND f a. a ctorid = ac . actorid En calcul relationnel, en considerantfilm_id_cat(0Comedy0) comme une relation (ce qui est coherent avec la denition de la fonction) cette requ^ete s'exprime fa:last_name,a:first_name:actor(a)^ ^film_id_cat(0Comedy0)(fa:film_id) Le calcul relationnel traduit presque litteralement la demarche que nous suivons lorsqu'il faut construire le resultat a la main : pour trouver lesactor_iddes acteurs qui n'ont jamais joue dans une comedie, nous examinons toutes les valeursade actor_idpresentes dans la tableactor(oufilm_actor), et pour chacune de ces valeurs, nous verions qu'il n'existe pas de tuple de la tablefilm_actorou l'attributactor_idsoit egal aaet ou l'attributfilm_iddesigne un lm qui appara^t dans le resultat defilm_id_cat('Comedy'). Nousdecrivons/explicitonsainsi les proprietes du resultat de la requ^eteQuels sont les acteur qui n'ont jamais joue dans une comedie ('Comedy')?. Si maintenant nous cherchons acalculerce resultat, nous pouvons d'abord calcu- ler la liste desactor_iddes acteurs qui ont joue dans une comedie, calculer la liste de tous lesactor_idconnus dans le schema et faire la dierence, en algebre relationnelle, cela se resume a

actor_id(film_actor)nactor_id(film_actor./film_id_cat('Comedy'))3.Q uelsson tl esact eursq uion tjou ed ansun l md 'horreur( 'Horror')et dan su n lm

pour enfant ('Children')?

Solution:Ici l'erreur la plus frequente consiste a ecrireSELECTa ctoridF ROMs akila. f ilmactorA Sf a

WHERE f a. f ilmidI N( SELECTFROMe ntid. f ilmidcat (' C hildren' ) ) A ND fa . filmidI N( SELECTFROMe ntid. f ilmidcat (' H orror' ) ) ; Le resultat est vide et la requ^ete ne correspond pas a la question posee. Elle calcule lesactor_iddes acteurs qui ont dans au moins un lm qui releve simultanement des categoriesHorroretChildren(ce genre de lm est assez rare). Pour calculer un resultat correct, il faut pour chaque valeuradeactor_idre- chercher deux tuples (pas necessairement distincts) defilm_actorou l'attribut actor_idvautaet ou dans un casfilm_iddesigne un lm pour enfants et dans6

Base de donnees

TD 3L3 MIASHS-M1 ISIFAR

2016-2017

l'autre un lm d'horreur. En calcul relationnel, cela donne fa:last_name,a:first_name:actor(a)^ ^film_id_cat(0Children0)(fa:film_id) ^film_id_cat(0Horror0)(fa:film_id)

En algebre relationnelle

last_name,first_name actor./ En SQL, cela peut donnerSELECTD ISTINCTa . f irstname , a . lastnameF ROMs akila. a ctora WHERE

E XISTS

SELECT

f ilmid FROM f ilmactorA Sf a1N ATURALJO IN entid . filmidcat (' C hildren' ) WHERE f a1. a ctorid = a . actorid )A ND

EXISTS

SELECT

f ilmid FROM f ilmactorA Sf a2N ATURALJO IN entid . filmidcat (' H orror' ) WHERE f a2. a ctorid = a . actorid ) ; qui renvoie 129 tuples. 7quotesdbs_dbs35.pdfusesText_40

[PDF] psychologie de la personnalité pdf

[PDF] film d'horreur 2017 sortie

[PDF] film d'horreur recent

[PDF] types de personnalité psychologie

[PDF] film d'horreur 2015

[PDF] film d'horreur 2017

[PDF] la psychologie de la personnalité

[PDF] développement de la personnalité pdf

[PDF] 2017)

[PDF] personnalité psychologie définition

[PDF] atout france

[PDF] comparaison entre le pere goriot et bel ami

[PDF] tp spécificité des enzymes digestives

[PDF] rat de compagnie prix

[PDF] rat de compagnie du docteur house