Øvingsoppgaver, sql3 med Postgres

Opprettelse av tabeller og innlegging av data

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

uml datamodell

arbeidstid

kategori_id(PK) kategori_navn kjerneorarb start stopp
1 faglig k 09:00 14:30
1 renhold a 05:30 13:30
3 teknisk k 09:00 14:00

faglaerer

ansatt_id(PK) personnr navn bilde ansatt_siden stillingsProsent stillingsNavn status arbeidstid fagfelt kontor_id email intern_tlf
f-001 13056802876 Petter Lær bilde 2001-12-25 100 Professor A 1 {linux,programmering,matematikk,"neurale nettverk"} 12 petter.laer@dih.no 8212
f-002 08097284575 Elsa Kromsprang bilde 2003-02-01 100 Høgskolelektor A 1 {databaser,datamodellering,webdesign,matematikk} 21 elsa.kromsprang@dih.no 8221
f-003 21056180254 Konrad Kontakt bilde 1981-01-30 50 Professor II P 1 {"algoritmer og datastrukturer",matematikk} 9 konrad.kontakt@dih.no 8209
f-004 13105587945 Hanne Helsing bilde 1982-10-02 100 Professor A 1 {"software engineering","diskret matematikk",relasjonskalkyle,relasjonsalgebra} 51 hanne.helsing@dih.no 8251
f-005 31057482078 Bertil Bom bilde 2002-08-15 100 Amanuensis D 1 {"C og C++",Simula,OpenGL,matematikk} 14 bertil.bom@dih.no 8214
f-006 28026756193 Janne Julihuset bilde 1998-05-01 80 Førsteamanuensis S 1   32 janne.julihuset@dih.no 8232

tekniker

ansatt_id(PK) personnr navn bilde ansatt_siden stillingsProsent stillingsNavn status arbeidstid kontor_id email intern_tlf
t-007 10027836041 Karl Knekk bilde 2003-10-01 100 Avdelingsingeniør A 3 25 karl.knekk@dih.no 8225
t-008 15096954032 Hilde Sprang bilde 1996-03-30 50 Overingeniør P 3 29 hilde.sprang@dih.no 8229
t-009 14077898574 Solveig Synnøve Sang bilde 2002-05-15 100 Avdelingsingeniør A 3 34 solveig.s.sang@dih.no 8234
t-010 29047356825 Pelle Proff bilde 2004-01-01 100 Lærling A 3 41 pelle.proff@dih.no 8241
t-011 01025687042 Gustav Gralt bilde 2003-10-01 60 Driftsansvarlig D 3 47 gustav.gralt@dih.no 8247

renholder

ansatt_id(PK) personnr navn bilde ansatt_siden stillingsProsent stillingsNavn status arbeidstid mobil_tlf
r-012 24056489205 Victoria Ask bilde 1984-01-30 50 Renholdsassistent A 2 91897824
r-013 16085178236 Per Olsen bilde 1979-08-12 50 Renholdsleder S 2 41657835
r-014 02116576352 Hans Johnsen bilde 1982-10-01 100 Renholder D 2 93568701
r-015 17127164352 Guro Grell bilde 1993-12-01 100 Renholder A 2  
r-016 07067698374 Kjersti Tralt bilde 1999-05-01 75 Renholdsassistent A 2  

fag

fag_id(PK) fag_navn
201 Databaser
102 Programmering for begynnere
104 Matematikk
215 Matematikk II
301 Neurale nettverk

server

ip_adresse(PK) navn tekniker os_o_ver har_ups
132.35.11.23 sirius t-008 Mandrake Linux 9.2 true
132.35.11.45 samos t-011 Microsoft Windows 2003 Server false
132.35.22.11 agora t-008 Debian Linux 3.0 rev2 true

rom

rom_id(PK) rom_navn
101 Auditorium A
102 Lille forelesningssal
201 Auditorium B
205 Auditorium C
302 Store forelesningssal

undervisning

fag_id(PK) faglaerer(PK)
201 f-002
201 f-004
102 f-001
104 f-002
215 f-003
215 f-004
301 f-005
301 f-001

romRenhold

rom_id(PK) renholder(PK)
101 r-012
101 r-013
101 r-015
102 r-012
201 r-016
201 r-014
201 r-015
205 r-016
205 r-014
205 r-013
302 r-016
302 r-012

Diverse SQL

  1. Finn navn og fagfelt for alle faglærerer med linux som en del av sitt fagfelt
  2. Skriv ut i hvert sitt felt kategori_id, time_start og minutt_start
  3. Skriv ut kategori_navn, start som 'time:minutt presis' og stopp som 'time:minutt circa'. Timer skal være på 24-timers format
  4. Finn navn og ansatt_siden for alle renholdere. ansatt_siden skal skrives på formatet dag-måned-år(to siffer)
  5. Finn bilde av Bertil Bom og legg det i samme katalog som du la inn de andre bildene inn fra. Kall bildet trett_bom.png
  6. Lag en trigger som sjekker at servernavn enten både begynner og slutter med en a eller med en s.
    Lag eksempler både på rikrige og feil data, til sammen 4 inserts og 2 updates(på insertene som gikk bra)
  7. Lag en trigger som sjekker at email-adresse som blir lagt inn eller oppdatert på tabellen tekniker er en fulltnavnsadresse(navn.navn) og at det kun er adresser på dih.no
  8. Slett triggerne og dataene du akkurat har laget
  9. Lag to rules som logger innlegging av data og oppdatering av feltene start og stopp i tabellen arbeidstid, ta med når og av hvem innlegging/oppdatering er gjort.
    Opprett først tabellen arbeidstid_logg, og test deretter med to innlegginger to og oppdatering av data
  10. Lag en rule som logger forsøk på sletting av data i tabellen undervisning, og som skriver ut en feilmelding istedenfor å slette noe, ta med når og hvem som forsøkte å slette.
    Opprett først tabellen undervisning_logg, og test deretter med å prøve å slette de to innleggingene i oppgaven over
  11. Slett de tre rules, de to dataene og alle dataene du akkurat har laget
  12. Skriv ut navn på alle teknikere og datoen de er født på formatet 'dag. månedkortnavn tosifferår' (tips: plukk ut datoen fra personnummeret)
  13. Finn navn og fagfelt for faglærerer som har matematikk eller diskret matematikk som fagfelt, men som ikke har undervist i matematikk eller matematikk II

Sletting av tabeller

  1. Slett alle tabeller i databasen