SQL underforespørgsler og Join

1. SQL underforespørgsler og Join

2. SQL – flere tabeller • Indtil videre har vi kun anvendt forespørg-sler på en enkelt tabel • Vi er ofte interesserede i at lave fore-spørgsler, der involverer flere tabeller • Vi kan dermed besvare mere komplekse spørgsmål, men forespørgslerne selv bliver også mere komplekse… RHS – IT A

3. SQL – flere tabeller • Vi benytter en database med information om film som eksempel Film filmid titel land år genre oscars Skuespiller personid navn land født levende oscars Medvirker filmid personid RHS – IT A

4. SQL – flere tabeller RHS – IT A

5. SQL – flere tabeller RHS – IT A

6. SQL – flere tabeller RHS – IT A

7. SQL – flere tabeller • Hvordan kan vi svare på et spørgsmål som dette: • Hvor mange film er lavet i det land som Marc Duret kommer fra? • Dette spørgsmål kan ikke besvares med en forespørgsel, der kun berører én tabel • Informationen er spredt ud i flere tabeller RHS – IT A

8. SQL – flere tabeller SELECT land FROM Skuespiller WHERE navn = ’Marc Duret’ SELECTCOUNT(titel) AS filmCount FROM Film WHERE Film.land = ’Frankrig’ Resultat er ’Frankrig’ ’Frankrig’ brugt som input NB! RHS – IT A

9. SQL – flere tabeller • Vi kan udføre den første forespørgsel, og bruge resultatet fra den som ”input” til den anden forespørgsel • Den første forespørgsel bliver til en ”under-forespørgsel” RHS – IT A

10. SQL – flere tabeller SELECTCOUNT(titel) AS filmCount FROM Film WHERE land = (SELECT land FROM Skuespiller WHERE navn = ’Marc Duret’) Ydre forespørgsel Indre forespørgsel (under-forespørgsel) RHS – IT A

11. SQL – flere tabeller • Resultat fra indre forespørgsel bruges som input til ydre forespørgsel • Den indre forespørgsel producerer – som alle andre forespørgsler – en resultattabel, som bruges af den ydre forespørgsel • Giver et par komplikationer • Navnesammenfald • Mulighed for mere end et resultat RHS – IT A

12. SQL – flere tabeller • Når en forespørgsel involverer flere tabeller, kan der være felter med samme navn i tabellerne • Vi kan præcisere et feltnavn ved at skrive tabelnavnet foran (med et .) • Film.land (land feltet i Film tabellen) • Skuespiller.land (land feltet i Skuespiller tabellen) RHS – IT A

13. SQL – flere tabeller • Hvad nu hvis en under-forespørgsel giver mere end ét resultat? SELECT land FROM Skuespiller WHERE (oscars > 0) Resultat: USA Frankrig RHS – IT A

14. SQL – flere tabeller • Vi må i så fald bruge IN SELECTCOUNT(titel) AS FilmCount FROM Film WHERE land IN (SELECT land FROM Skuespiller WHERE (oscars > 0)) Øhmm… RHS – IT A

15. Opgave 6 – SQL • Brug Filminformation-databasen fra websiten • Kør nedenstående SQL-forespørgsler på databasen • SELECT titel, år, oscars FROM Film WHERE oscars > (SELECT SUM(oscars)/5 FROM Film) • SELECT * FROM Film WHERE år > (SELECT MAX(år) FROM Film WHERE (oscars > 0)) • Formulér selv SQL-forespørgsler til at finde nedenstående data : • Find navn og land for skuespillere fra samme land som ’E.T’ er fra • Find titel og Oscars for film som har vundet mindst lige så mange Oscars som John Wayne har vundet • Find navn og land for skuespillere som har vundet mindst dobbelt så mange Oscars som gennemsnittet af Oscars vundet af alle skuespillere RHS – IT A

16. SQL – Join • En anden tilgangsvinkel til spørgsmål som involverer flere tabeller er join (forening) • At ”joine” tabeller er en slags ”multiplika-tion” af tabeller • Simpelt tilfælde: Join uden betingelser: SELECT * FROM Film, Skuespiller RHS – IT A

17. SQL – Join • Kører vi denne forespørgsel, får vi et resultat på 49 poster… • 49 = 7 x 7. Alle kombinationer af Film poster (7) og Skuespiller poster (7) • Alle felter fra begge tabeller er inkluderet • Som regel er det ikke lige det resultat vi ønsker os… RHS – IT A

18. SQL – Join • Som regel vil vi gerne udvælge resultater hvor visse felters værdier matcher • Eksempel: For alle film, find filmens titel samt navne på alle skuespillere som medvirker i den enkelte film • Denne information findes jo allerede i Medvirker tabellen (20 poster), men kun i form af talpar (filmid + personid) RHS – IT A

19. SQL – Join • Vi har brug for information fra alle tre tabeller i databasen, så et første forsøg på en forespørgsel kunne være SELECT * FROM Film, Medvirker, Skuespiller • Resultat: 980 poster med mange felter… RHS – IT A

20. SQL – Join • Vi kan imidlertid nøjes med kun at medtage nogle få felter fra tabellerne: SELECT Film.titel, Skuespiller.navn FROM Film, Medvirker, Skuespiller • Resultat: 980 poster med de ønskede felter RHS – IT A

21. SQL – Join • Desuden skal vi kun bruge de poster, hvor den relevante information matcher • Vi prøver at ”udskifte” tallene i Medvirker tabellen med rigtigt data (titler og navne) • For at udvælge korrekte titler og personer, skal de tilsvarende id’er (filmid og person-id) matche på tværs af tabellerne RHS – IT A

22. SQL – Join • Match id’er for titler og personer: SELECT Film.titel, Skuespiller.navn FROM Film, Medvirker, Skuespiller WHERE ((Film.filmid = Medvirker.filmid) AND (Skuespiller.personid = Medvirker.personid)) • Resultat: 20 poster med de rigtige felter! RHS – IT A

23. SQL – Join • Dette er et meget normalt ”mønster” for multi-tabel forespørgsler • Tabeller der repræsenterer relationer inde-holder kun id’er (nøgler i andre tabeller) • ”Rigtigt data” findes i tabeller som repræsen-terer entiteter (med id’er som nøgle) • ”Rigtigt data” for relationer findes ved brug af join, hvor der matches på nøglefelter RHS – IT A

24. Opgave 7 – SQL • Brug Filminformation-databasen fra websiten • Kør nedenstående SQL-forespørgsler på databasen 1) SELECT Film.titel, COUNT(Skuespiller.personid) AS Roller FROM Film, Skuespiller, Medvirker WHERE (Skuespiller.land = 'Frankrig') AND (Medvirker.personid = Skuespiller.personid) AND (Medvirker.filmid = Film.filmid) GROUP BY Film.titel 2) SELECT Film.Genre, SUM(Skuespiller.Oscars) AS totalOscars FROM Film, Skuespiller, Medvirker WHERE (Skuespiller.levende = ‘ja') AND (Medvirker.personid = Skuespiller.personid) AND (Medvirker.filmid = Film.filmid) GROUP BY Film.genre • Formulér selv SQL-forespørgsler til at finde nedenstående data: • Find navn, land og film-titel for skuespillere (og film) som medvirker i film fra deres eget land • Find navn og film-titel for for skuespillere (og film) som ikke har vundet en Oscar, men har medvirket i en film, som har vundet en Oscar RHS – IT A