1 / 24

SQL underforespørgsler og Join

SQL underforespørgsler og Join. 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

Download Presentation

SQL underforespørgsler og Join

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related