SQL sortering og gruppering

1
SQL sortering og gruppering
2
SQL - sortering

• Hvis en forespørgsel returnerer mange rækker, kan
  det være bekvemt at sortere resultatet
• SQL gør det muligt at sortere resultatet efter
  værdien af felter i resultatet
• Til det formål benyttes ORDER BY

3
SQL - sortering
Hvilke felter vil jeg have data fra

• SELECT ltfeltlistegt
• FROM lttabelnavngt
• WHERE ltbetingelsegt
• ORDER BY ltfeltnavngt

Hvilken tabel skal data komme fra
Hvilke betingelser skal data opfylde
Hvilken orden skal resultatet sorteres i
4
SQL - sortering

• 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
5
SQL - sortering
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
SELECT FROM Film ORDER BY titel
6
SQL - sortering
filmid titel land år genre oscars
6 1984 UK 1984 Sci-Fi 2
1 E.T. USA 1982 Sci-Fi 4
5 Hard Boiled Kina 1992 Action 0
4 Leon Frankrig 1994 Thriller 0
7 Seven USA 1995 Thriller 1
3 Sult Danmark 1966 Drama 1
2 Taxi Frankrig 1998 Komedie 0
SELECT FROM Film ORDER BY titel
7
SQL - sortering
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
SELECT titel, år FROM Film WHERE oscars gt
0 ORDER BY titel
8
SQL - sortering
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
SELECT titel, år FROM Film WHERE oscars gt
0 ORDER BY titel
9
SQL - sortering
titel år
1984 1984
E.T. 1982
Seven 1995
Sult 1966
SELECT titel, år FROM Film WHERE oscars gt
0 ORDER BY titel
10
SQL - sortering

• Vi kan endda angive mere end et enkelt felt
  efterfølgende felter benyttes hvis værdi i første
  felt er ens
• Vi kan angive faldende eller stigende orden, ved
  hjælp af DESC (faldende) og ASC (stigende)
• ORDER BY oscars DESC, år ASC

11
SQL - funktioner

• Vi kan udføre simpel aritmetik i SQL, ved brug af
  disse funktioner
• COUNT (optælling)
• SUM (sum)
• AVG (gennemsnit)
• MIN (minimum værdi)
• MAX (maximum værdi)
• Disse funktioner betegnes aggregate functions
  (samlende funktioner?)

Godt med aritmetik!
12
SQL - funktioner

• En sådan funktion arbejder på værdierne i et
  givent felt
• Mængden af værdier styres af den angivne
  søgebetingelse
• SELECT COUNT(titel)
• FROM Film
• WHERE (oscars gt 0)

Hvor mange film har vundet en Oscar
13
SQL - funktioner

• Denne forespørgsel kan også skrives som
• SELECT COUNT(titel) AS oscarWinners
• FROM Film
• WHERE (oscars gt 0)
• Ordet AS gør det muligt at omdøbe et felt i
  søgeresultatet
• Ændrer kun navnet, ikke selve resultatet

NB!
14
SQL - funktioner

• SELECT COUNT(titel) AS oscarWinners,
• AVG(oscars) AS averageOscars,
• MAX(oscars) AS maximalOscars
• FROM Film
• WHERE (oscars gt 0)

15
SQL - funktioner
oscarWinners averageOscars maximalOscars
4 2.0000 4
16
Opgave 4 SQL

• Brug Filminformation-databasen fra websiten
• Kør nedenstående SQL-forespørgsler på databasen
• SELECT FROM Film ORDER BY år ASC
• SELECT titel, år FROM Film ORDER BY titel
• SELECT MAX(år) AS maxUSA FROM Film WHERE land
  USA
• SELECT AVG(år) AS loserYear FROM Film WHERE
  (oscars 0)
• Formulér selv SQL-forespørgsler til at finde
  nedenstående data
• Find alle oscar-vindende film sorteret efter år
  (ældste film først)
• Find en sorteret liste af filmtitler for franske
  film
• Find året for den ældste film der ikke har vundet
  en Oscar
• Find det gennemsnitlige antal Oscars for Sci-Fi
  film
• Find det totale antal Oscars vundet af film fra
  USA

17
SQL - gruppering

• De samlende funktioner er nyttige til at beregne
  værdier baseret på alle elementer i resultatet
• Nogle gange vil vi gerne kunne gøre dette for en
  bestemt del af resultatet
• Dette kan gøres ved at bruge WHERE
• men det kan være meget besværligt i praksis

18
SQL - gruppering

• Antag vi har en database med film fra mere end
  100 lande
• Find det totale antal film lavet i hvert enkelt
  land
• SELECT COUNT(titel) AS filmCount
• FROM Film
• WHERE land
• Mere end 100 forespørgsler

19
SQL - gruppering

• Et bedre alternativ er GROUP BY
• Syntaks
• SELECT ltfeltlistegt
• FROM lttabelnavngt
• GROUP BY ltfeltlistegt
• Producerer et resultat for hver gruppe, defineret
  ved den angivne feltliste

20
SQL - gruppering
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
SELECT land, COUNT(titel) AS filmCount FROM
Film GROUP BY land
21
SQL - gruppering
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
SELECT land, COUNT(titel) AS filmCount FROM
Film GROUP BY land
22
SQL - gruppering
land filmCount
Danmark 1
Frankrig 2
Kina 1
UK 1
USA 2
23
SQL - gruppering
land oscarTotal
Danmark 1
Frankrig 0
Kina 0
UK 2
USA 5
SUM(oscars) AS
24
SQL - gruppering

• I det sidste eksempel kunne det måske være
  nyttigt at udelade grupper, hvor resultatet er
  lig nul
• Mere generelt medtag kun grupper, der opfylder
  en given betingelse
• Dette kan opnås ved at benytte HAVING

25
SQL - gruppering

• Syntaks
• SELECT ltfeltlistegt
• FROM lttabelnavngt
• GROUP BY ltfeltlistegt
• HAVING ltbetingelsegt

26
SQL - gruppering
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
SELECT land, SUM(oscars) AS oscarTotal FROM
Film GROUP BY land HAVING (SUM(oscars) gt 0)
27
SQL - gruppering
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
SELECT land, SUM(oscars) AS oscarTotal FROM
Film GROUP BY land HAVING (SUM(oscars) gt 0)
28
SQL - gruppering
land oscarTotal
USA 5
Frankrig 0
UK 2
Kina 0
Danmark 1
29
SQL - gruppering
land oscarTotal
USA 5
UK 2
Danmark 1
30
SQL - gruppering

• Men
• er HAVING det samme som WHERE..?
• Ikke helt
• WHERE udvælger specifikke poster
• HAVING udvælger specifikke grupper som skal med i
  det endelige resultat
• Vi kan ikke benytte samlende funktioner i en
  WHERE betingelse

31
Opgave 5 SQL

• Brug Filminformation-databasen fra websiten
• Kør nedenstående SQL-forespørgsler på databasen
• SELECT genre, AVG(oscars) FROM Film GROUP BY
  genre
• SELECT oscars, MAX(år) FROM Film WHERE (oscars gt
  0) GROUP BY oscars
• SELECT genre, AVG(oscars) FROM Film GROUP BY
  genre HAVING (COUNT(titel) gt 1)
• Formulér selv SQL-forespørgsler til at finde
  nedenstående data
• Find antallet af film lavet i hvert land. For
  hvert land skal filmene være grupperet efter
  genre
• Find det gennemsnitlige antal Oscars vundet af
  film for hvert land
• Find antallet af film lavet i hvert land. Kun
  film lavet efter 1985 skal være med i resultatet