Avansert SQL og problemløsning

1. Avansert SQL og problemløsning IDA210 Ola Bø Bygd på notat av Per Sætre Høgskolen i Molde

2. Innhold • Mer om subselect • Mer om union • Mer om view • Rekursjon

3. Skjema som brukes i alle eksempler studie(studieid, studienavn, varighet): person(personnr, etternavn, fornavn, gateadresse, postadresse) telefon(person, tlf) student(studentnr, studie, person, studentfra, studenttil) kurs(kursid, kursnavn, studiepoeng, eksamensform) semester(semesterid, semesterstart, semesterslutt, antalldager) eksamen(student, kurs, semester, karakter) postadresse(postnr, poststed) lærer(initialer, etternavn, fornavn, gateadresse, postadresse) undervisning(lærer, semester, kurs) obligatorisk(kurs, studie) valgbart(kurs, studie) kurs_kurs(før, etter)

4. Subspørring • Vi vil velge ut rader på grunnlag av verdier i andre rader • Eksempel: skal skrive ut vitnemål med beste eksamenskarakter og dato for eksamen SELECT e.kurs,e.semester,e.karakter FROM eksamen e WHERE e.student = 980123 AND karakter < ’F’ AND karakter = (SELECT MIN(karakter) FROM eksamen WHERE e.student = student AND e.kurs = kurs); Spørringen må returnere én og bare én verdi. Er dette sikret? Er dette en korrelert underspørring? Hva er konsekvensen for kjøringen? Den samme spørringa kan løses med ALL

5. Alternativ løsning for å finne beste karakter SELECT e.kurs,e.semester,e.karakter FROM eksamen e WHERE e.student = 980123 AND karakter < ‘F’ AND karakter <= ALL (SELECT karakter FROM eksamen WHERE e.student=student AND e.kurs=kurs); Merk <= all. Forklar hvorfor dette fungerer! Her gir subspørringen flere resultater.

6. Å finne karakterer som ikke teller med fordi de ikke er best SELECT e.kurs,e.semester,e.karakter FROM eksamen e WHERE e.student = 980123 AND karakter < ‘F’ AND karakter > ANY (SELECT karakter FROM eksamen WHERE e.student=student AND e.kurs=kurs);

7. Å finne alle studenter som har avlagt en eller annen eksamen SELECT studentnr,personnr, studie FROM student WHERE studentnr IN (SELECT student FROM eksamen WHERE semester=’V99’); Er dette en korrelert underspørring? Hva er konsekvensen for kjøringen?

8. EXISTS vs IN Studenter som aldri har tatt en eksamen EXISTS gir true hvis underspørringen produserer en eller flere rader. IN gir true hvis verdien fins i kolonnen som unserspørringen produserer Er dette korrelerte underspørringer? Hva er konsekvensen for kjøringen? Hvilken spørring vil gå fortest? SELECT studentnr,person,studie FROM student s WHERE NOT EXISTS (SELECT * FROM eksamen WHERE student= s.studentnr); Et alternativ er å bruke IN SELECT studentnr, personnr, studie FROM student s WHERE NOT studentnr IN (SELECT student FROM eksamen); I mange tilfeller kan vi velge mellom å bruke EXISTS eller IN. Med IN kan det oftere være mulig å unngå korrelert underspørring.

9. Flere underspørringer –Eliminere like beste-resultater SELECT e.kurs,e.semester,e.karakter FROM eksamen e WHERE e.student = 980123 AND e.karakter < ‘F’ AND e.karakter = (SELECT MIN(karakter) FROM eksamen WHERE e.student = student AND e.kurs=kurs) AND NOT EXISTS (SELECT * FROM eksamen WHERE e.student = student AND e.kurs=kurs AND e.karakter=karakter AND e.semester < semester) ; Hvordan klarer denne spørringen å eliminere like beste-resultater?

10. JOIN • I en relasjonsdatabase lagres data i flere tabeller. • Data fra flere tabeller er typisk knyttet sammen med 1 til mange-sammenhenger • Limet som binder data sammen er fremmednøklene • En fremmednøkkel på mange-siden skal typisk være lik en primærnøkkel på 1-siden. • Ved hjelp av JOIN kan vi sammenstille sammenhørende data • JOIN-mekanismen kan brukes også uten at det er definert fremmednøkkel/primærnøkkel

11. Ekvi-join Hvilken undervisning har lærerne gitt? SELECT l.initialer,l.etternavn || ’,’ || l.fornavn, k.kursid, k.kursnavn, k.studiepoeng, u.semester FROM lærer l, undervisning u, kurs k WHERE l.initialer=u.lærer AND k.kursid=u.kurs; lærer-undervisning-kurs 1 - M - 1 antall rader i resultatet må bli likt antall rader i undervisning Hvilke felter er fremmednøkler i spørringen over?

12. Ekvi-join kan erstatte IN SELECT DISTINCT studentnr, personnr, studie FROM student s, eksamen e WHERE s.studentnr = e.student AND e.semester = ’V99’; • Denne spørringen finner alle studenter som har avlagt eksamen V99 uten å bruke IN • Ingen data fra tabellen eksamen vises i resultatet! • Eksamen brukes bare for å bestemme hvilke rader som skal vises – da kalles dette et pseudo-join. • DISTINCT sørger for at det bare vises én rad per student

13. Self-joinÅ finne kollisjoner på timeplanen • Self-join brukes når vi trenger å sammenstille flere rader fra samme tabell • Timeplanen er lagret i dette skjemaetforelesningsplan(semester, ukedag, time, kurs) • Skal lage oversikt over alle fagkollisjoner SELECT f1.ukedag, f1.time, f1.kurs, f2.kurs FROM forelesningsplan f1, forelesningsplan f2  WHERE f1.semester = ’H99’  AND f1.semester = f2.semester  AND f1.ukedag = f2.ukedag  AND f1.time = f2.time  AND f1.kurs > f2.kurs;   gir samtidig tilgang til to rader i tabellen.  begrenser resultatsettet til ett bestemt semester.  utgjør til sammen join-betingelsen, nemlig at de to kursene har samme semester, dag og time.  reduserer resultatsettet til reelle konflikter. Uten en slik betingelse ville alle kurs kollidere med seg selv og vi ville også få samme kollisjon to ganger.

14. UNIONSlå sammen resultater fra ulike spørringer Eksempel 1 sette sammen data fra to tabeller SELECT etternavn, fornavn, gateadresse, ’S’ FROM person UNION [ALL] SELECT etternavn, fornavn, gateadresse, ’L’ FROM lærer ORDER BY 1,2; Eksempel 2 behandle data forskjellig – vitnemål med standpunktkarakter med eller uten eksamen SELECT kurs, semester, standpunktkarakter AS karakter FROM resultat WHERE eksamenskarakter IS NULL AND student = 980123 UNION SELECT kurs, semester, (standpunktkarakter + eksamenskarakter)/2 AS karakter FROM resultat WHERE eksamenskarakter NOT IS NULL AND student = 980123 ORDER BY 1;

15. UNION kan erstatte OUTER JOINAlle lærere og telefon SELECT p.personnr, p.etternavn, p.fornavn, p.gateadresse, t.tlf::CHAR(10) AS TelefonFROM person p, telefon tWHERE p.personnr = t.person UNIONSELECT p.personnr, p.etternavn, p.fornavn, p.gateadresse, ’Ingen registrert’ FROM person p WHERE personnr NOT IN (SELECT person FROM telefon)ORDER BY 1,2; Diskuter UNION-kompatibilitet her datatype og kolonnenavn Eksemplet viser CAST av tlf til CHAR(10) Alternativ syntaks: SQLServer CAST(t.tlf, CHAR(10)) Oracle TO_CHAR(t.tlf,’99999999’) Fordelen ved å bruke UNION framfor OUTER JOIN er at vi kan legge inn forklarende tekst i stedet for en brukeruvennlig NULL

16. View • To hensikter • Løse komplekse spørringer ved å bruke en serie med enklere view • Lage et selvstendig presentasjonslag, så brukerne ikke får direkte tilgang til tabellene • Gir økt sikkerhet • Gir økt fleksibilitet • Endringer i den indre strukturen kan skjules • Gir mulighet for å legge inn programkode • Men oppdatering av view er begrenset dvs det er i mange tilfeller ikke mulig å bruke INSERT, UPDATE eller DELETE-setninger på et view • Og da blir det vanskelig å bruke databasen bare via view!

17. Begrenset oppdatering av view • Krav for at et view skal være oppdaterbart’ • Består bare av en basistabell eller ett opdaterbart view – ingen join eller union • Ingen kolonner er resultater av aggregatfunksjoner eller andre uttrykk • DISTINCT er ikke brukt i SELECT i view-definisjonen • Det er ikke brukt subselect i WHERE • Det er ikke brukt GROUP BY eller HAVING • Kravene burde ikke være så strenge • MS ACCESS • har noe slappere krav • lagrede spørringer kan brukes som view • En annen løsning er å bruke TRIGGERE, slik at utviklerne kan bestemme hvordan et view skal oppdateres • SQLServer, Oracle og PostgreSQL tilbyr denne løsningen

18. Avansert INSERTSette inn flere rader samtidig Eksempel 1 Vektallsproduksjon per semester skal settes inn i tabellen produksjon INSERT INTO produksjon(semester,antstud,sumpoeng) SELECT e.semester, COUNT(e.karakter), SUM(k.studiepoeng) FROM eksamen e, kurs k WHERE e.kurs = k.kursid AND e.karakter< ‘F’ AND e.semester[2,3] BETWEEN ‘00’ AND ‘03’ GROUP BY e.semester;

19. UPDATE med subselect UPDATE eksamen e SET (eksamensform, studiepoeng) = ((SELECT eksamensform,studiepoeng FROM kurs k WHERE k.kursid = e.kurs)); Syntaks for SQLServer UPDATE eksamen SET eksamensform = k.eksamensform, studiepoeng = k.vektall FROM eksamen e INNER JOIN kurs k ON(e.kurs = k.kursid) WHERE SUBSTRING(semester,2,2)>’03’;

20. Temporær tabell En temporær tabell er et frittstående objekt som ikke holdes oppdatert SELECT s.studentnr, p.etternavn||’,’||fornavn AS navn, studie FROM student s, person p WHERE s.person=p.personnr INTO TEMP t_studperson; I Oracle har vi en mer funksjonell løsning: SNAPSHOT CREATE SNAPSHOT s_studperson REFRESH START WITH ROUND (SYSDATE +1) +11/24 NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24 AS SELECT s.studentnr, p.etternavn||','||fornavn AS navn, studie FROM student s, person p WHERE s.personnr=p.personnr; I SQLserver CREATE TABLE #studperson(studentnr, navn, studie); INSERT INTO #stud_person SELECT s.studentnr, p.etternavn||','||fornavn AS navn, studie FROM student s, person p WHERE s.personnr=p.personnr;

21. Rekursive spørringerÅ finne alle kurs som er forutsetninger for et gitt kurs Tabellen kurs-kurs Rekursiv spørring i SQL 3 WITH RECURSIVE forutsetninger(over, under) AS( SELECT over,under FROM kurs_kurs UNION SELECT k_k.over, f.under FROM kurs_kurs k_k, forutsetninger f WHERE k_k.under = f. over ) SELECT over,under FROM forutsetninger f WHERE f.over=’In601’; Rekursiv SELECT i Oracle SELECT under FROM kurs_kurs START WITH over='IN601' CONNECT BY PRIOR under = over ORDER SIBLINGS BY under; START WITH betingelsen bestemmer roten (røttene) i hierarkiet (hierarkiene) – her In601s hierarki av forutsetninger. CONNECT WITH betingelsen bestemmer hvordan overliggende node i hierarkiet, PRIOR, knyttes til neste nivå. Følgende regler gjelder: CONNECT BY betingelsen kan ikke inneholde subselect. Hvis SELECT.kommandoen inneholder en JOIN, vil WHERE-paragrafen utføres før CONNECT BY-paragrafen. Hvis SELECT-kommandoen ikke inneholder en JOIN, vil en eventuell WHERE-paragraf bli utført etter CONNECT BY er gjort.

22. Rekursiv spørring med nivå SELECT under, LEVEL AS nivåFROM kurs_kurs START WITH over='IN601' CONNECT BY PRIOR under = over ORDER SIBLINGS BY nivå;

23. Oppsummering