Скачать презентацию SQL underforespørgsler og Join RHS IT A Скачать презентацию SQL underforespørgsler og Join RHS IT A

b319687308cc94a63e1431ac65d51a6f.ppt

  • Количество слайдов: 24

SQL underforespørgsler og Join RHS – IT A SQL underforespørgsler og Join RHS – IT A

SQL – flere tabeller • Indtil nu har vi kun anvendt forespørgsler på en SQL – flere tabeller • Indtil nu har vi kun anvendt forespørgsler på en enkelt tabel • Vi er ofte interesserede i at lave forespø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 2

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

SQL – flere tabeller filmid titel land år genre oscars 1 E. T. USA SQL – flere tabeller filmid titel land år genre oscars 1 E. T. USA 1982 Sci-Fi 4 2 Taxi Frankrig 1998 Komedie 0 3 Sult Danmark 1966 Drama 1 4 Leon Frankrig 1994 Thriller 0 5 Hard Boiled Kina 1992 Action 0 6 1984 UK 1984 Sci-Fi 2 7 Seven USA 1995 Thriller 1 RHS – IT A 4

SQL – flere tabeller personid navn land født 1 John Wayne USA 04 -06 SQL – flere tabeller personid navn land født 1 John Wayne USA 04 -06 -1911 nej 2 2 Chow-Yun Fat Kina 12 -11 -1959 ja 0 3 Karl Stegger Danmark 30 -01 -1922 nej 0 4 Jean Reno Frankrig 10 -10 -1949 ja 0 5 Julia Roberts USA 28 -09 -1964 ja 1 6 Natalie Portman Israel 02 -07 -1980 ja 0 7 Marc Duret Frankrig 01 -08 -1966 ja 1 RHS – IT A levende oscars 5

SQL – flere tabeller filmid personid 1 1 4 4 6 1 5 5 SQL – flere tabeller filmid personid 1 1 4 4 6 1 5 5 2 1 7 5 4 2 4 5 6 2 6 6 5 2 7 6 6 3 1 7 2 3 2 7 4 3 3 7 5 RHS – IT A 6

SQL – flere tabeller • Hvordan kan vi svare på et spørgsmål som dette: 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 7

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

SQL – flere tabeller • Vi kan udføre den første forespørgsel, og bruge resultatet 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 ”underforespørgsel” RHS – IT A 9

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

SQL – flere tabeller • Resultat fra indre forespørgsel bruges som input til ydre 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 11

SQL – flere tabeller • Når en forespørgsel involverer flere tabeller, kan der være 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 12

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

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

Opgave 6 – SQL • Brug Filminformation-databasen fra websiten • Kør nedenstående SQL-forespørgsler på 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 15

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

SQL – Join • Kører vi denne forespørgsel, får vi et resultat på 49 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 17

SQL – Join • Som regel vi gerne udvælge resultater hvor visse felters værdier SQL – Join • Som regel 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 18

SQL – Join • Vi har brug for information fra alle tre tabeller i 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 19

SQL – Join • Vi kan imidlertid nøjes med kun at medtage nogle få 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 20

SQL – Join • Desuden skal vi kun bruge de poster, hvor den relevante 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 personid) matche på tværs af tabellerne RHS – IT A 21

SQL – Join • Match id’er for titler og personer: SELECT Film. titel, Skuespiller. 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 22

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

Opgave 7 – SQL • • Brug Filminformation-databasen fra websiten Kør nedenstående SQL-forespørgsler på 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 total. Oscars FROM Film, Skuespiller, Medvirker WHERE (Skuespiller. levende = true) 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 skuespillere (og film) som ikke har vundet en Oscar, men har medvirket i en film, som har vundet en Oscar RHS – IT A 24