Øvingsoppgaver, sql2 med Postgres

Opprettelse av tabeller og innlegging av data

Opprett følgende tabeller med gitte primær- og fremmednøkler

teater

teater_id(PK) navn
1 Fredrikshalds teater
2 Chat Noir
3 Nasjonalteateret

skuespiller

skuespiller_id(PK) navn spesialfelt ansatt_teater(FK)
1 Ingar Pedersen Shakespeare 1
2 Inga Johansen Tragedie 1
3 Per Jensen   1
4 Jens Persson Barneteater 1
5 Sara Olsen Tragedie 2
6 Hans Dahl Ibsen 2
7 Even Andersen Farse 2
8 Helge Pettersen   2
9 Anders Evensen Shakespeare 3
10 Heidi Hansen Ibsen 3
11 Ingvild Karlsen Shakespeare 3
12 Petter Iversen Musikal 3
13 Hans Petter Olsen Stand-up  
14 Ellen Johnsen Stand-up  
15 Jon Erlandsen Romanser 3

teaterstykke

stykke_id(PK) navn forfatter
1 Hamlet Shakespeare
2 Et dukkehjem Ibsen
3 Sommer med forviklinger Karsten Iversen
4 I blanke messingen Peter Cattaneo

teaterstykkeOppsetning

stykke_id(PK/FK) teater_id(PK/FK) fra_dato til_dato antall oppsetninger
1 2 10. juni 2004 20.august 2004 30
2 3 10.august 2004 1.desember 2004 50
1 1 2.april 2004 8.juni 2004 10
3 1 4.juni 2004 6.august 2004 20

teaterstykkeRolle

stykke_id(PK/FK) rolle_id(PK) rollenavn
1 1 Hamlet
1 2 Kong Claudius
1 3 Dronning Gertrude
1 4 Polonius
2 1 Thorvald
2 2 Nora
2 3 Christine Linde
2 4 Sakfører Krogstad
3 1 Politi
3 2 Baker
3 3 Politiker
3 4 Lege
3 5 Nudist
3 6 Sommerturist 1
3 7 Sommerturist 2
3 8 Dikter

oppsetningSkuespiller

stykke_id(PK/FK) teater_id(PK/FK) skuespiller_id(PK/FK) rolle_id(PK/FK)
1 1 3 1
1 1 4 2
1 1 2 3
1 1 1 4
1 2 6 1
1 2 7 2
1 2 5 3
1 2 8 4
2 3 9 1
2 3 10 2
2 3 11 3
2 3 12 4
3 1 1 1
3 1 4 2
3 1 3 3
3 1 3 4
3 1 2 5
3 1 15 6
3 1 14 7
3 1 2 8

Diverse SQL

  1. Hent ut all informasjon om alle teaterstykker
  2. Hent ut skuespiller_id og navn på alle skuespillere
  3. Hent ut navn på teater med teater_id lik 2
  4. Hent ut navn på alle skuespillere som ikke jobber for noe teater
  5. Hent ut teater_id for Nasjonalteateret
  6. Hent ut teater_id og navn for teater med navn som begynner på 'Fred'
  7. Hent ut informasjon om alle teater sortert på navn
  8. Hent ut informasjon om alle oppsetningSkuespillere sortert på teater_id og deretter stykke_id
  9. Hent ut antallet oppsetninger for det stykket som har færrest oppsetninger
  10. Hent ut navnet på de(t) stykke(-ne/-t) som har flest oppsetninger
  11. Hent navn og skuespiller_id for alle skuespillere som ikke har spilt i stykket 'Hamlet'
    NB! Du trenger ikke ta med de som ikke har spilt noen roller i det hele tatt
  12. Hent ut navnet på alle skuespillere som ikke er ansatt på Chat Noir
  13. Hent ut navnet på alle skuespillere som har hatt en rolle i en oppsetning.
    List hvert navn kun en gang
  14. Hent ut gjennomsnittlig antall oppsetninger
  15. Hent ut navn og rollenavn for alle skuespillere som har hatt samme rolle.
    Sorter på rolle_id
  16. Hent ut navnet på de(n) skuespiller(-en/-ne) med flest roller og antallet roller de(n) har
  17. Hent ut id, navn og antall oppsetninger for stykker som er satt opp flere ganger enn hvertfall ett annet stykke
  18. Hent ut stykke_id, teater_id og navn på stykke og teater for stykker som går på flere teater
  19. Hent ut navn på skuespillere, hvilke roller de har hatt og hvilke stykker de spiller/har spilt i.
    Endre overskriftene til 'Navn på skuespiller', 'Navn på rollefigur' og 'Navn på stykke'
  20. Hent ut navn, rollenavn og stykkenavn for alle skuespillere, men ta også med navn på alle som ikke har spilt noe stykke i det hele tatt
    Legg inn verdiene 'Ingen roller' og 'Ingen teaterstykker' for de som ikke har spilt noe
    Endre overskriftene til 'Skuespiller', 'Rollefigur' og 'Teaterstykke'
  21. Hent ut navn og stykkenavn for alle skuespillere, men ta også med navn på alle skuespillere som ikke har spilt i noe stykke og alle stykker som ikke har noen skuespillere
    Legg inn verdiene 'Ingen skuespillere' og 'Ingen teaterstykker' for de stykkene som ikke har skuespillere og de som ikke har spilt noe
    Endre overskriftene til 'Skuespiller', og 'Teaterstykke'
  22. Lag en SQL-funksjon som skriver ut all informasjon for alle skuespillere som jobber på et angitt teater
    Lag to eksempler for å vise hvordan funksjonen virker
  23. Lag en PL/pgSQL-funksjon som skriver ut hvilken dag en gitt teateroppsetning starter.
    Dagen skal skrives på norsk. Hvis teateroppsetningen ikke finnes, skal en feilmelding skrives ut.
    Lag to eksempler for å vise hvordan funksjonen virker

Endring og sletting av tabeller og data

  1. Bytt navn på skuespilleren 'Ingvild Karlsen' til 'Ingvild Jensen'
  2. Bytt hvem som skal spille Hamlet på Fredrikshalds teater fra 'Per Jensen' til skuespiller med id lik 13
  3. Slett alle data i tabellen oppsetningSkuespiller
  4. Slett tabellen oppsetningSkuespiller
  5. Slett alle roller i Hamlet fra tabellen teaterstykkeRolle
  6. Slett funksjonene du har laget