1. Geef code en omschrijving van alle cursussen die precies vier dagen duren

Leerboek Oracle SQL 11g/12c

Appendix D

Antwoorden Deze bijlage geeft de antwoorden van de opgaven waarmee de hoofdstukken 4, 5, 7, 8, 9, en 10 zijn afgesloten. Waar dat van toepassing is geven we alternatieve oplossingen aan, evenals waarschuwingen voor mogelijke fouten. Het is ondoenlijk om per opgave alle goede oplossingen op te nemen; de mogelijkheden van de taal SQL zijn daartoe te uitgebreid. Dit betekent dat het heel goed mogelijk is dat bepaalde opgaven op een volstrekt andere manier worden aangepakt en opgelost. Vergelijking van de resultaattabellen is dan een mogelijkheid om de correctheid te controleren. Pas echter op: als een query het goede resultaat geeft, wil dat nog niet zeggen dat de query correct geformuleerd is: foute oplossingen kunnen - per ongeluk - de juiste rijen opleveren. Dit zijn de meest verraderlijke queries, omdat ze op een onverwacht moment verkeerde oplossingen kunnen produceren. De opgaven zijn hier en daar beslist moeilijk. We gebruiken als het motto dat u zelf voldoende eenvoudige opdrachten kunt verzinnen om uw kennis te testen. Van een paar opgaven is het begrijpen van de gegeven oplossing al een hele prestatie.

Antwoorden opgaven paragraaf 4.10 1. Geef code en omschrijving van alle cursussen die precies vier dagen duren. SQL> select code, omschrijving 2 from cursussen 3 where lengte = 4; CODE ---S02 JAV GEN

OMSCHRIJVING -------------------------------------------------Introductiecursus SQL Java voor Oracle ontwikkelaars Systeemgeneratie

SQL>

2. Geef alle medewerkers, alfabetisch gesorteerd op functie, en per functie op leeftijd (van jong naar oud). SQL> select * 2 from medewerkers 3 order by functie, gbdatum desc; MNR ---7900 7934 7839 7566 7782 7698 7876 7369 7788 7902 7844 7521 7499

NAAM -----------JANSEN MOLENAAR DE KONING JANSEN CLERCKX BLAAK ADAMS SMIT SCHOTTEN SPIJKER DEN DRAAIER DE WAARD ALDERS

VOORL ----R TJA CC JM AB R AA N SCJ MG JJ TF JAM

© Academic Service, Den Haag

FUNCTIE ---------BOEKHOUDER BOEKHOUDER DIRECTEUR MANAGER MANAGER MANAGER TRAINER TRAINER TRAINER TRAINER VERKOPER VERKOPER VERKOPER

CHEF ---7698 7782 7839 7839 7839 7788 7902 7566 7566 7698 7698 7698

GBDATUM MAANDSAL COMM AFD ----------- -------- ---- --03-DEC-1969 800 30 23-JAN-1962 1300 10 17-NOV-1952 5000 10 02-APR-1967 2975 20 09-JUN-1965 2450 10 01-NOV-1963 2850 30 30-DEC-1966 1100 20 17-DEC-1965 800 20 26-NOV-1959 3000 20 13-FEB-1959 3000 20 28-SEP-1968 1500 0 30 22-FEB-1962 1250 500 30 20-FEB-1961 1600 300 30

1


7654 MARTENS

P

Appendix D

VERKOPER

7698 28-SEP-1956

1250 1400

30

SQL>

3. Welke cursussen zijn in Utrecht en/of in Maastricht uitgevoerd? SQL> select cursus 2 from uitvoeringen 3 where locatie in ('UTRECHT','MAASTRICHT'); CURSUS -----OAG S02 JAV XML RSO SQL>

4. Welke medewerkers hebben zowel de Java als de XML cursus gevolgd? Geef hun nummers. SQL> 2 3 4 5 6

select from where and

cursist inschrijvingen cursus = 'JAV' cursist in (select cursist from inschrijvingen where cursus = 'XML');

CURSIST -------7499 SQL>

NB: Er zijn diverse oplossingen mogelijk, bijvoorbeeld met twee subqueries. Duidelijk moge zijn dat een oplossing met AND of OR op rijniveau fout is! 5. Geef de naam en voorletters van alle medewerkers, behalve van R. Jansen. SQL> select naam, voorl 2 from medewerkers 3 where not (naam = 'JANSEN' and voorl = 'R'); NAAM -----------SMIT ALDERS DE WAARD JANSEN MARTENS BLAAK CLERCKX SCHOTTEN DE KONING DEN DRAAIER ADAMS SPIJKER MOLENAAR

VOORL ----N JAM TF JM P R AB SCJ CC JJ AA MG TJA

SQL>


2


Appendix D

Alternatief zonder haakjes (let op: met OR): SQL> select naam, voorl 2 from medewerkers 3 where naam <> 'JANSEN' or voorl <> 'R';

6. Geef nummer, functie, en geboortedatum van alle medewerkers die vóór 1960 geboren zijn, en trainer of verkoper zijn. SQL> 2 3 4 MNR ----7654 7788 7902


mnr, functie, gbdatum medewerkers gbdatum < to_date('01-JAN-1960','DD-MON-YYYY') functie in ('TRAINER','VERKOPER');

FUNCTIE ---------VERKOPER TRAINER TRAINER

GBDATUM ----------28-SEP-1956 26-NOV-1959 13-FEB-1959

SQL>

Alternatief (let op de haakjes voor de precedentie): SQL> 2 3 4

select mnr, functie, gbdatum from medewerkers where gbdatum < to_date('01-JAN-1960','DD-MON-YYYY') and (functie = 'TRAINER' or functie = 'VERKOPER');

7. Geef de nummers van alle medewerkers die niet aan de afdeling opleidingen zijn verbonden. SQL> select mnr 2 from medewerkers 3 where afd <> (select anr 4 from afdelingen 5 where naam = 'OPLEIDINGEN'); MNR ----7499 7521 7654 7698 7782 7839 7844 7900 7934 SQL>

8. Geef de nummers van alle medewerkers die de Java cursus niet hebben gevolgd. SQL> select mnr 2 from medewerkers 3 where mnr not in (select cursist 4 from inschrijvingen 5 where cursus = 'JAV');


3


Appendix D

MNR ----7369 7521 7654 7844 7900 7902 7934 SQL>

Let op: De volgende twee oplossingen zijn FOUT: SQL> select distinct cursist 2 from inschrijvingen 3 where cursist not in (select cursist 4 from inschrijvingen 5 where cursus = 'JAV'); CURSIST -------7521 7844 7900 7902 7934 SQL> SQL> select distinct cursist 2 from inschrijvingen 3 where cursus <> 'JAV'; CURSIST -------7499 7521 7566 7698 7788 7839 7844 7876 7900 7902 7934 SQL>

9. Welke medewerkers hebben voorvoegsels in hun naam? SQL> select mnr, naam, voorl 2 from medewerkers 3 where naam like '% %'; MNR ----7521 7839 7844

NAAM -----------DE WAARD DE KONING DEN DRAAIER


VOORL ----TF CC JJ

4


Appendix D

SQL>

10a. Welke medewerkers hebben ondergeschikten? SQL> select mnr, naam, voorl 2 from medewerkers 3 where mnr in (select chef 4 from medewerkers); MNR ----7566 7698 7782 7788 7839 7902

NAAM -----------JANSEN BLAAK CLERCKX SCHOTTEN DE KONING SPIJKER

VOORL ----JM R AB SCJ CC MG

SQL>

10b. En welke niet? SQL> select mnr, naam, voorl 2 from medewerkers 3 where mnr not in (select chef 4 from medewerkers 5 where chef is not null); MNR ------7369 7499 7521 7654 7844 7876 7900 7934

NAAM -----------SMIT ALDERS DE WAARD MARTENS DEN DRAAIER ADAMS JANSEN MOLENAAR

VOORL ----N JAM TF P JJ AA R TJA

8 rows selected. SQL>

NB: Let op de laatste WHERE-component; die is beslist nodig! 11. Geef een overzicht van alle uitvoeringen van algemene cursussen (type ALG) in 1999. SQL> 2 3 4 5 6 7

select * from uitvoeringen where begindatum between date '1999-01-01' and date '1999-12-31' and cursus in (select code from cursussen where type = 'ALG');

CURSUS -----OAG S02 S02 S02

BEGINDATUM DOCENT LOCATIE ----------- -------- -------------------10-AUG-1999 7566 UTRECHT 12-APR-1999 7902 DE MEERN 04-OCT-1999 7369 MAASTRICHT 13-DEC-1999 7369 DE MEERN


5


Appendix D

SQL>

Het jaar 1999 kan ook op een aantal andere manieren worden opgelost door gebruik te maken van functies, die in hoofdstuk 5 aan de orde komen. where to_char(begindatum,'YYYY') = '1999' … where extract(year from begindatum) = 1999 … where begindatum between to_date('01-JAN-1999','DD-MON-YYYY') and to_date('31-DEC-1999','DD-MON-YYYY') …

12. Geef naam en voorletters van iedereen die ooit bij N. Smit een cursus heeft gevolgd. Aanwijzing: gebruik sub-queries, en werk vervolgens van binnen naar buiten. Dus: bepaal het nummer van N. Smit, zoek dan naar de cursussen die hij heeft gegeven, enzovoorts. SQL> select naam, voorl 2 from medewerkers 3 where mnr in 4 (select cursist 5 from inschrijvingen 6 where (cursus,begindatum) in 7 (select cursus,begindatum 8 from uitvoeringen 9 where docent = 10 (select mnr 11 from medewerkers 12 where naam = 'SMIT' 13 and voorl = 'N' 14 ) 15 ) 16 ); NAAM -----------ALDERS BLAAK SCHOTTEN DE KONING JANSEN SPIJKER

VOORL ----JAM R SCJ CC R MG

SQL>

13. Wat is de verklaring van het resultaat 'no rows selected' in figuur 4.41? De WHERE-component: 2

where evaluatie not in (1,2,3,NULL)

is equivalent met: 2 3 4 5

where AND AND AND

evaluatie evaluatie evaluatie evaluatie

<> <> <> <>

1 2 3 NULL

Als we nu een rij hebben met een evaluatie-waarde van 1, 2, of 3 dat is het vrij duidelijk dat één van de eerste drie condities 'onwaar' oplevert, en daarmee levert de WHERE-component als geheel 'onwaar' op. Als de evaluatie-waarde een null-waarde bevat, dan resulteren alle vier de voorwaarden in 'onbekend' en daarmee wordt het eindresultaat ook 'onbekend'. Tot zover geen verrassingen.


6


Appendix D

Als de evaluatie-waarde 4 of 5 is (de andere twee toegestane waarden) dan zijn de eerste drie condities alledrie waar, maar levert de laatste conditie 'onbekend' op. De laatste conditie is dus de spelbreker, waardoor het eindresultaat 'onbekend' wordt. waar AND waar AND waar AND onbekend <=> onbekend

Antwoorden opgaven paragraaf 5.9 1. Geef van alle medewerkers eerst de achternaam, dan een komma, gevolgd door de voorletter(s) en voorvoegsels. SQL> select substr(naam,instr(naam,' ')+1) 2 ||', '||voorl||' ' 3 ||substr(naam,1,instr(naam,' ')-1) 4 as naam 5 from medewerkers; NAAM ----------------------------SMIT, N ALDERS, JAM WAARD, TF DE JANSEN, JM MARTENS, P BLAAK, R CLERCKX, AB SCHOTTEN, SCJ KONING, CC DE DRAAIER, JJ DEN ADAMS, AA JANSEN, R SPIJKER, MG MOLENAAR, TJA SQL>

2. Geef van alle medewerkers hun naam en de geboortedatum, in het formaat zoals bijvoorbeeld ‘11 April 1997’. SQL> select naam 2 , to_char(gbdatum,'dd Month yyyy') 3 from medewerkers; NAAM -----------SMIT ALDERS DE WAARD JANSEN MARTENS BLAAK CLERCKX SCHOTTEN DE KONING DEN DRAAIER ADAMS JANSEN SPIJKER MOLENAAR

TO_CHAR(GBDATUM,'DDMONTHYYYY') -------------------------------------------17 December 1965 20 Februari 1961 22 Februari 1962 02 April 1967 28 September 1956 01 November 1963 09 Juni 1965 26 November 1959 17 November 1952 28 September 1968 30 December 1966 03 December 1969 13 Februari 1959 23 Januari 1962


7


Appendix D

SQL>

NB: We kunnen de taal waarin de maandnamen in het resultaat worden weergegeven als volgt veranderen in het Nederlands: SQL> alter session set nls_language=dutch; Session altered. SQL>

3a. Op welke dag ben (of was!) je precies 10.000 dagen oud? SQL> select to_date('11-aug-1954','dd-mon-yyyy') + 10000 2 as "10000 dagen" 3 from dual; 10000 dagen ----------27-DEC-1981 SQL>

We kunnen in plaats van de TO_DATE-functie ook gebruik maken van een DATE-constante: SQL> select date '1954-08-11' + 10000 2 as "10000 dagen" 3 from dual; 10000 dagen ----------27-DEC-1981 SQL>

3b. Op welke dag van de week valt/viel dat? SQL> select to_char(to_date('11-aug-1954','dd-mon-yyyy')+10000,'day') 2 as "op een:" 3 from dual; op een: --------zondag SQL>

Ook hier zouden we op dezelfde wijze gebruik kunnen maken van een DATE-constante. 4. Herschrijf het voorbeeld in figuur 5.24 met gebruikmaking van de NVL2-functie. SQL> 2 3 4

select , from where

naam, maandsal, comm nvl2(comm,12*maandsal+comm,12*maandsal) as jaarsal medewerkers naam like '%T%';

NAAM MAANDSAL COMM JAARSAL ------------ -------- ----- -------SMIT 800 9600 MARTENS 1250 1400 16400


8


SCHOTTEN

3000

Appendix D

36000

SQL>

5. Herschrijf het voorbeeld in figuur 5.25 met gebruikmaking van CASE-expressies, zowel in de SELECT-component als in de ORDER BY-component. SQL> 2 3 4 5 6 7 8 9 10 11 12 13

select functie, naam , case when maandsal <= 2500 then 'goedkoop' else 'duur' end as klasse from medewerkers where gbdatum < date '1964-01-01' order by case functie when 'DIRECTEUR' then 1 when 'MANAGER' then 2 else 3 end;

FUNCTIE ---------DIRECTEUR MANAGER VERKOPER VERKOPER BOEKHOUDER TRAINER TRAINER VERKOPER

NAAM -----------DE KONING BLAAK ALDERS DE WAARD MOLENAAR SPIJKER SCHOTTEN MARTENS

KLASSE -------duur duur goedkoop goedkoop goedkoop duur duur goedkoop

SQL>

Merk op dat we (ongevraagd) ook de TO_DATE-functie hebben vervangen door een DATE-constante. 6. Herschrijf het voorbeeld in figuur 5.21 met gebruikmaking van DATE en INTERVAL constantes, zodat ze onafhankelijk worden van de NLS_DATE_FORMAT instelling. SQL> 2 3 4

select , , from

date '1996-01-29' + interval '1' month as kolom1 date '1997-01-29' + interval '1' month as kolom2 date '1997-08-11' - interval '3' month as kolom3 dual;

,

date '1997-01-29' + interval '1' month as kolom2 * ERROR at line 2: ORA-01839: date not valid for month specified SQL> c/29/28 2* , date '1997-01-28' + interval '1' month as kolom2 SQL> / KOLOM1 KOLOM2 KOLOM3 ----------- ----------- ----------29-FEB-1996 28-FEB-1997 11-MAY-1997 SQL>

Hieruit blijkt dat 29 januari tijdens een niet-schrikkeljaar problemen oplevert; als we 29 in 28 veranderen gaat het goed.


9


Appendix D

7. Onderzoek het verschil tussen de datum-formaten WW en IW (weeknummer en ISO weeknummer) aan de hand van een willekeurige datum, en verklaar het eventuele verschil. SQL> 2 3 4

select , , from

sysdate to_char(sysdate, 'ww') as ww to_char(sysdate, 'iw') as iw dual;

SYSDATE WW IW ----------- -- -09-FEB-2004 06 07 SQL>

Het verschil heeft te maken met de manier waarop het weeknummer is gedefinieerd. Het formaat WW laat de eerste week van het jaar altijd op 1 januari beginnen, ongeacht op welke dag van de week dat valt. De ISO standaard hanteert andere regels. Een ISO week begint altijd op een maandag, en rond de jaarwisseling zijn de regels als volgt: Als 1 januari op een vrijdag, zaterdag, of zondag valt dan behoort de week nog tot het vorige jaar, en anders tot het nieuwe jaar.

Antwoorden opgaven paragraaf 7.11 1

In figuur 7.10 wordt de constraint M_VERK_CHK op een nogal cryptische manier gedefinieerd. Formuleer dezelfde constraint zonder DECODE en NVL2 te gebruiken.

Hier volgen twee mogelijkheden: check ((functie = 'VERKOPER' and comm is not null) or (functie <>'VERKOPER' and comm is null) ) check ((functie = 'VERKOPER' or comm is null) and not (functie = 'VERKOPER' and comm is null) )

2

Waarom zou de constraint in figuur 7.12 met een apart ALTER TABLE–commando moeten worden gedefinieerd?

De constraint moet met ALTER TABLE worden gedefinieerd vanwege een “kip-ei” probleem: een refererende sleutel moet altijd naar een bestaande tabel verwijzen, en er is hier sprake van twee tabellen (MEDEWERKERS en AFDELINGEN) die naar elkaar verwijzen. 3

Het is weliswaar niet behandeld, maar beredeneer waarom bij het gebruik van sequences de pseudo-kolom CURRVAL in een transactie niet kan worden gebruikt zonder eerst een beroep te doen op NEXTVAL.

In een multi-user omgeving kunnen diverse database-gebruikers tegelijkertijd gebruik maken van sequences, dus tegelijkertijd met verschillende waarden voor CURRVAL aan het werk zijn; er is dus geen eenduidige globale waarde voor CURRVAL. NEXTVAL is daarentegen op ieder moment gedefinieerd als de volgende sequence-waarde om uit te delen. 4 Hoe komt het dat de kolom evaluatie van de tabel inschrijvingen null-waarden accepteert, ondanks de constraint I_EVAL_CHK (zie figuur 7.16)? Dat komt door de driewaardige logica; een CHECK-conditie kan als resultaat ‘waar’, ‘onwaar’ of ‘onbekend’ opleveren. Een constraint wordt pas geschonden als de conditie ‘onwaar’ oplevert. Dit betekent dat voor een verplichte kolom altijd expliciet een NOT NULL-constraint moet worden toegevoegd.


10


5

Appendix D

Als een PRIMARY KEY of UNIQUE constraint wordt gedefinieerd, creëert Oracle normaal gesproken impliciet een unieke index om de constraint te kunnen bewaken. Onderzoek wat er gebeurt als een dergelijke constraint DEFERRABLE wordt gedefinieerd.

Als PRIMARY KEY of UNIQUE constraints DEFERRABLE worden gedefinieerd, creëert Oracle nietunieke indexen. Indexen moeten namelijk onmiddellijk worden bijgewerkt, zodat tijdens een transactie tijdelijk dubbele waarden in de index moeten kunnen optreden. 6

Met behulp van functie-gebaseerde indexen kunnen we "conditionele uniciteit" implementeren. Maak een unieke index op de INSCHRIJVINGEN-tabel die er voor zorgt dat de OAG-cursus maar één keer mag worden gevolgd.

SQL> create unique index oag_inschr on inschrijvingen 2 ( case cursus when 'OAG' then cursist else null end 3 , case cursus when 'OAG' then cursus else null end ); Index created. SQL>

De truc is om een index te bouwen op combinaties (cursist, cursus) waarbij we alle niet-OAG inschrijvingen negeren. Test de oplossing met het volgende commando (dat zou moeten falen): SQL> insert into inschrijvingen values (7900,'OAG',sysdate,null); insert into inschrijvingen values (7900,'OAG',sysdate,null) * ERROR at line 1: ORA-00001: unique constraint (BOEK.OAG_INSCHR) violated SQL>


Produceer een overzicht van alle cursusuitvoeringen; geef de cursuscode, de begindatum, de cursuslengte, en de naam van de docent.

SQL> 2 3 4 5 6 7 8 9

select , , , from , , where and

c.code u.begindatum c.lengte m.naam as docent medewerkers m cursussen c uitvoeringen u u.docent = m.mnr u.cursus = c.code;

CODE ---XML S02 S02 OAG JAV RSO PLS JAV S02 OAG

BEGINDATUM LENGTE DOCENT ----------- -------- -----------03-FEB-2000 2 SMIT 13-DEC-1999 4 SMIT 04-OKT-1999 4 SMIT 10-AUG-1999 1 JANSEN 13-DEC-1999 4 JANSEN 24-FEB-2001 2 SCHOTTEN 11-SEP-2000 1 SCHOTTEN 01-FEB-2000 4 ADAMS 12-APR-1999 4 SPIJKER 27-SEP-2000 1 SPIJKER


11


Appendix D

SQL>

NB: Als we ook de cursusuitvoeringen willen zien met onbekende docent, kunnen we deze oplossing als volgt aanpassen: SQL> 2 3 4 5 6 7 8 9 10 11 12

select , , ,

CODE ---ERM JAV JAV OAG OAG PLS PRO RSO S02 S02 S02 XML XML

BEGINDATUM LENGTE DOCENT ----------- -------- -----------15-JAN-2001 3 13-DEC-1999 4 JANSEN 01-FEB-2000 4 ADAMS 10-AUG-1999 1 JANSEN 27-SEP-2000 1 SPIJKER 11-SEP-2000 1 SCHOTTEN 19-FEB-2001 5 24-FEB-2001 2 SCHOTTEN 12-APR-1999 4 SPIJKER 04-OKT-1999 4 SMIT 13-DEC-1999 4 SMIT 03-FEB-2000 2 SMIT 18-SEP-2000 2

from , , where and

DISTINCT c.code u.begindatum c.lengte case when u.docent is not null then m.naam else null end as docent medewerkers m cursussen c uitvoeringen u coalesce(u.docent,-1) in (m.mnr,-1) u.cursus = c.code;

SQL>

Regel 11 is misschien op het eerste oog niet zo duidelijk; het maakt de join tussen UITVOERINGEN en MEDEWERKERS iets soepeler. We kunnen in plaats van –1 ook een willekeurig ander getal gebruiken, mits het geen bestaand medewerkernummer kan zijn. Merk ook op dat dit de toevoeging van DISTINCT noodzakelijk maakt.

2

Geef in twee kolommen naast elkaar de naam van elke cursist die een S02 cursus heeft gevolgd, met de naam van de docent.

De onderstaande oplossing gebruikt de ANSI/ISO join syntax, voor de variatie: SQL> 2 3 4 5 6 7 8 9 10

select m.naam as , d.naam as from medewerkers d join uitvoeringen u join inschrijvingen i join medewerkers m where cursus = 'S02';

DEELNEMER -----------ALDERS BLAAK

deelnemer docent on (u.docent = d.mnr) using (cursus, begindatum) on (i.cursist = m.mnr)

DOCENT -----------SPIJKER SPIJKER


12


ADAMS MOLENAAR SCHOTTEN DE KONING SPIJKER BLAAK SPIJKER

Appendix D

SPIJKER SPIJKER SMIT SMIT SMIT SMIT SMIT

SQL>

3

Geef van iedere medewerker: naam, voorletters, en het jaarsalaris (inclusief toelage en commissie).

SQL> select m.naam, m.voorl 2 , 12 * (m.maandsal + s.toelage) 3 + nvl(m.comm,0) as jaarsalaris 4 from medewerkers m 5 join 6 schalen s 7 on (m.maandsal 8 between s.ondergrens 9 and s.bovengrens); NAAM -----------SMIT JANSEN ADAMS DE WAARD MARTENS MOLENAAR DEN DRAAIER ALDERS CLERCKX BLAAK JANSEN SCHOTTEN SPIJKER DE KONING

VOORL JAARSALARIS ----- ----------N 9600 R 9600 AA 13200 TF 16100 P 17000 TJA 16200 JJ 19200 JAM 20700 AB 31800 R 36600 JM 38100 SCJ 38400 MG 38400 CC 66000

SQL>

4

Geef van alle cursusuitvoeringen: de cursuscode, de begindatum, en het aantal inschrijvingen. Sorteer op begindatum.

SQL> 2 3 4 5 6 7 8 9 10

select , , from

cursus begindatum count(i.cursist) as inschrijvingen uitvoeringen u left outer join inschrijvingen i using(cursus, begindatum) group by cursus , begindatum order by begindatum;

CURS ---S02 OAG

BEGINDATUM INSCHRIJVINGEN ----------- -------------12-APR-1999 4 10-AUG-1999 3


13


S02 JAV S02 JAV XML PLS XML OAG ERM PRO RSO

04-OCT-1999 13-DEC-1999 13-DEC-1999 01-FEB-2000 03-FEB-2000 11-SEP-2000 18-SEP-2000 27-SEP-2000 15-JAN-2001 19-FEB-2001 24-FEB-2001

Appendix D

3 5 2 3 2 3 0 1 0 0 0


NB: We hebben hier de outerjoin nodig om ook cursussen zonder inschrijvingen in het resultaat te krijgen. Denk er ook aan dat COUNT(*) op de derde regel het verkeerde resultaat zou opleveren!

5

Geef nu code, begindatum, en aantal inschrijvingen van alle cursusuitvoeringen in 1999 met minstens drie inschrijvingen.

SQL> 2 3 4 5 6 7 8

select , , from where group by , having

CURSUS -----JAV OAG S02 S02

cursus begindatum count(*) inschrijvingen extract(year from begindatum) = 1999 cursus begindatum count(*) >= 3;

BEGINDATUM COUNT(*) ----------- -------13-DEC-1999 5 10-AUG-1999 3 12-APR-1999 4 04-OKT-1999 3

SQL>

Omdat we toch niet geïnteresseerd zijn in uitvoeringen zonder deelnemers, kunnen we volstaan met de inschrijvingentabel. Dat zou anders zijn geweest als de vraag was geweest “... met minder dan drie inschrijvingen”; nul is immers ook minder dan drie.

6

Geef de nummers van alle medewerkers die wèl ooit een cursus als docent hebben gegeven, maar nog nooit een cursus hebben gevolgd.

SQL> select docent from uitvoeringen 2 minus 3 select cursist from inschrijvingen; DOCENT -------7369 SQL>

Deze oplossing lijkt goed te zijn, maar ziet er voor een geoefend oog verdacht uit. Het resultaat bestaat namelijk niet uit een, maar uit twee rijen: SQL> set feedback 1


14


Appendix D

SQL> / DOCENT -------7369 2 rows selected. SQL>

We zouden dus expliciet null-waarden in de DOCENT-kolom ook nog moeten uitsluiten: SQL> 2 3 4

select docent from uitvoeringen where docent is not null minus select cursist from inschrijvingen;

DOCENT -------7369 SQL>

7

Welke medewerkers hebben een bepaalde cursus meer dan één keer gevolgd?

SQL> 2 3 4

select from group by having

CURSIST -------7698 7788 7902

cursist,cursus inschrijvingen cursist,cursus count(*) > 1 ;

CURSUS -----S02 JAV S02

SQL>

8

Geef van alle docenten: naam en voorletters, het aantal cursussen dat ze hebben gegeven, het totale aantal cursisten dat ze hebben opgeleid, en het gemiddelde evaluatiecijfer. Rond deze berekening af op één decimaal.

SQL> 2 3 4 5 6 7 8 9 10 11 VOORL ----N AA JM

select , , , from ,

d.voorl, d.naam count(distinct begindatum) cursussen count(*) cursisten round(avg(evaluatie),1) evaluatie medewerkers d inschrijvingen i join uitvoeringen u using(cursus, begindatum) where d.mnr = u.docent group by d.voorl, d.naam; NAAM CURSUSSEN CURSISTEN EVALUATIE ------------ --------- --------- --------SMIT 3 7 4 ADAMS 1 3 4 JANSEN 2 8 4.3


15


MG SCJ

SPIJKER SCHOTTEN

Appendix D

2 1

5 3

4

SQL>

NB: We gaan bij het tellen van de cursussen uit van de veronderstelling dat een docent nooit op dezelfde dag twee cursussen kan geven.

9

Geef naam en voorletters van alle trainers die ooit tijdens een algemene cursus (type ALG) hun eigen chef als cursist hebben gehad.

SQL> 2 3 4 5 6 7 8 9 10 11 12

select distinct m.naam, m.voorl from medewerkers m , cursussen c , uitvoeringen u , inschrijvingen i where m.mnr = u.docent and m.chef = i.cursist and c.code = u.cursus and u.cursus = i.cursus and u.begindatum = i.begindatum and c.type = 'ALG';

NAAM VOORL ------------ ----SMIT N SQL>

10 Hebben we op een van de cursuslocaties op enig moment twee lokalen tegelijkertijd in gebruik gehad? SQL> 2 3 4 5 6 7 8

select , , from , , where and

u1.locatie u1.begindatum, u1.cursus u2.begindatum, u2.cursus uitvoeringen u1 uitvoeringen u2 cursussen c u1.locatie = u2.locatie (u1.begindatum < u2.begindatum or

and and

u1.cursus = c.code u2.begindatum between u1.begindatum and u1.begindatum + c.lengte;

9 10 11 12

u1.cursus <> u2.cursus )

LOCATIE BEGINDATUM CURSUS BEGINDATUM CURSUS -------------------- ----------- ------ ----------- -----DE MEERN 01-FEB-2000 JAV 03-FEB-2000 XML SQL>

We zoeken dus twee verschillende cursusuitvoeringen op dezelfde locatie, die elkaar overlappen. Het blijkt dat de Java-cursus van 1 februari 2000 in De Meern overlapt met de XML-cursus die twee dagen later start; de Java-cursus duurt namelijk vier dagen.

11 Geef een matrix-overzicht (voor elke afdeling een kolom, voor elke functie een rij) met in iedere cel het aantal medewerkers. In een query is het dynamisch bepalen van het aantal kolommen ondoenlijk; ga daarom uit van de afdelingsnummers 10, 20 en 30.


16


SQL> 2 3 4 5 6

select , , , from group by

Appendix D

m.functie sum(case m.afd when 10 then 1 else 0 end) as afd_10 sum(case m.afd when 20 then 1 else 0 end) as afd_20 sum(case m.afd when 30 then 1 else 0 end) as afd_30 medewerkers m m.functie;

FUNCTIE AFD_10 AFD_20 AFD_30 ---------- -------- -------- -------BOEKHOUDER 1 0 1 DIRECTEUR 1 0 0 MANAGER 1 1 1 TRAINER 0 4 0 VERKOPER 0 0 4 SQL>

12 Zijn de twee queries in figuur 8.45 en 8.46 equivalent? Onderzoek de queries nader, en verklaar het eventuele verschil. Als we de FEEDBACK-instelling van SQL*Plus laag genoeg zetten, dan wordt het verschil meteen duidelijk: SQL> set feedback 1 SQL> select u.locatie from uitvoeringen u 2 MINUS 3 select a.locatie from afdelingen a; LOCATIE -------------------MAASTRICHT 2 rows selected. SQL> select DISTINCT u.locatie 2 from uitvoeringen u 3 where u.locatie not in 4 (select a.locatie 5 from afdelingen a); LOCATIE -------------------MAASTRICHT 1 row selected. SQL>

Er bestaat een cursusuitvoering waarvan de locatie onbekend is, en zoals we inmiddels weten kunnen we niet voorzichtig genoeg zijn met null-waarden. In de eerste query komt die null-waarde in het resultaat voor, omdat hij er door de MINUS-operator niet wordt uitgehaald. Als de tweede query die bewuste rij aan het controleren is, wordt de WHERE-component: 3

where NULL not in ('LEIDEN','DE MEERN','UTRECHT','GRONINGEN')

Deze conditie levert "onbekend" op, dus wordt de rij niet tot het resultaat toegelaten.


17


Appendix D


Het komt vaak voor dat een (junior) docent een cursus eerst bij een collega volgt voordat hij hem zelf voor het eerst geeft. Voor welke docent/cursus-combinaties is dat gebeurd?

Deze opgave is niet eenvoudig; hij kan op vele manieren worden opgelost. De hier gegeven oplossing, gebaseerd op gebruik van de EXISTS-operator, is als volgt te lezen: ‘Zoek cursusuitvoeringen waarvoor geldt dat er voor de docent een eerdere deelname aan diezelfde cursus bestaat als cursist, èn waarvoor geldt dat het de eerste keer is dat de docent de cursus geeft’. Deze laatste toevoeging is nodig, anders zouden ook de gevallen ‘geven-volgen-geven’ verschijnen. SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14

select u.cursus, u.docent from uitvoeringen u where exists (select i.* from inschrijvingen i where i.cursist = u.docent and i.cursus = u.cursus and i.begindatum < u.begindatum) and not exists (select eu.* from uitvoeringen eu where eu.cursus = u.cursus and eu.docent = u.docent and eu.begindatum < u.begindatum);

CURSUS DOCENT ------ -------JAV 7876 OAG 7902 SQL>

2

Sterker nog: als de beginnende docent de cursus dan voor het eerst geeft, zit de docent waarvan hij eerder de kunst had afgekeken ter ondersteuning als deelnemer in het lokaal. Spoor dit soort cursus/junior/senior-combinaties op.

Laten we deze opgave voor de variatie eens met een join oplossen: SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

select , , from , , , where and and and and and and and ;

u1.cursus u1.docent as senior u2.docent as junior uitvoeringen u1 inschrijvingen i1 uitvoeringen u2 inschrijvingen i2 u1.cursus = i1.cursus u1.begindatum = i1.begindatum u2.cursus = i2.cursus u2.begindatum = i2.begindatum u1.cursus = u2.cursus u1.begindatum < u2.begindatum u1.docent = i2.cursist u2.docent = i1.cursist

-- join i1 met u1 -- join i2 met u2 -----

u1 en u2 dezelfde cursus maar u1 is eerder dan u2 docent u1 volgt u2 docent u2 volgt u1

CURSUS SENIOR JUNIOR ------ -------- -------JAV 7566 7876


18


Appendix D

SQL>

3

Welke medewerkers hebben nog nooit een cursus gegeven?

In eerste instantie zouden we misschien verwachten dat beide hieronder gegeven oplossingen correct zijn. Let echter op de verschillende resultaten. Bepaal zelf wat de juiste oplossing is. SQL> select m.* 2 from medewerkers m 3 where m.mnr not in (select u.docent 4 from uitvoeringen u); no rows selected SQL> SQL> select m.* 2 from medewerkers m 3 where not exists (select u.docent 4 from uitvoeringen u 5 where u.docent = m.mnr); MNR ----7499 7521 7654 7698 7782 7839 7844 7900 7934

NAAM -----------ALDERS DE WAARD MARTENS BLAAK CLERCKX DE KONING DEN DRAAIER JANSEN MOLENAAR

VOORL ----JAM TF P R AB CC JJ R TJA

FUNCTIE CHEF GBDATUM MAANDSAL COMM AFD ---------- ----- ----------- -------- ----- --VERKOPER 7698 20-FEB-1961 1600 300 30 VERKOPER 7698 22-FEB-1962 1250 500 30 VERKOPER 7698 28-SEP-1956 1250 1400 30 MANAGER 7839 01-NOV-1963 2850 30 MANAGER 7839 09-JUN-1965 2450 10 DIRECTEUR 17-NOV-1952 5000 10 VERKOPER 7698 28-SEP-1968 1500 0 30 BOEKHOUDER 7698 03-DEC-1969 800 30 BOEKHOUDER 7782 23-JAN-1962 1300 10


Voor beide oplossingen is wat te zeggen. Het is namelijk zo dat er cursus-uitvoeringen voorkomen met een null-waarde in de DOCENT-kolom. Als we deze null-waarden opvatten als “docent onbekend” dan kunnen we dus nooit met zekerheid zeggen dat een bepaalde medewerker nog nooit een cursus heeft gegeven. De tweede query behandelt de null-waarden duidelijk anders; we moeten dus onze vraag iets preciezer formuleren om te kunnen bepalen welke oplossing gewenst is. 4

Welke werknemers hebben alle bouwcursussen (type BLD) gevolgd? Ze hebben namelijk recht op korting.

Dit is geen gemakkelijke opgave. We geven hier twee oplossingen. SQL> select 2 from 3 where 4 5 6 7 8 9 10 11 12 13

m.mnr, m.naam, m.voorl medewerkers m not exists (select c.* from cursussen c where c.type = 'BLD' and not exists (select i.* from inschrijvingen i where i.cursus = c.code and i.cursist = m.mnr ) );


19


Appendix D

MNR NAAM VOORL ----- ------------ ----7499 ALDERS JAM SQL>

Alternatieve oplossing, met een GROUP BY: SQL> 2 3 4 5 6 7 8 9 10 11

select from , , where and and group by having

m.mnr, m.naam, m.voorl medewerkers m cursussen c inschrijvingen i i.cursus = c.code i.cursist = m.mnr c.type = 'BLD' m.mnr, m.naam, m.voorl count(distinct i.cursus)=(select count(*) from cursussen where type='BLD');

MNR NAAM VOORL ----- ------------ ----7499 ALDERS JAM SQL>

5

Wie hebben (tenminste) dezelfde cursussen gevolgd als medewerker 7788 gevolgd heeft?

Dit is ook geen gemakkelijke opgave. De hier gegeven constructie met de MINUS-operator en een gecorreleerde subquery is elegant. Let overigens op de plaats van de ontkenning. De oplossing kan het beste als volgt worden gelezen: ‘Geef alle medewerkers (behalve 7788 zelf) waarvoor geldt dat er géén cursus is die 7788 wèl heeft gevolgd, en zij niet’. SQL> 2 3 4 5 6 7 8 9 10 11


m.naam,m.voorl medewerkers m m.mnr <> 7788 not exists (select i1.cursus from inschrijvingen i1 where i1.cursist = 7788 MINUS select i2.cursus from inschrijvingen i2 where i2.cursist = m.mnr);

NAAM -----------ALDERS BLAAK DE KONING ADAMS

VOORL ----JAM R CC AA

SQL>

Merk overigens de treffende gelijkenis op van deze opgave en de vorige opgave; het zijn beide voorbeelden uit de categorie ‘deelverzamelingproblemen’. De twee gegeven oplossingsmethoden zijn dan ook uitwisselbaar. 6 Geef de gegevens van alle medewerkers waarvan het maandsalaris en de commissie overeenkomen met het maandsalaris en de commissie van (minstens) een medewerker van


20


Appendix D

afdeling 30. We zijn natuurlijk alleen geïnteresseerd in medewerkers van andere afdelingen. SQL> 2 3 4 5 6 7 8 9 10

select m.naam , m.maandsal , m.comm from medewerkers m where m.afd <> 30 and (m.maandsal,coalesce(m.comm,-1)) in (select a.maandsal , coalesce(a.comm,-1) from medewerkers a where a.afd = 30 );

NAAM MAANDSAL COMM ------------ -------- ----SMIT 800 SQL>

NB: Let op de toepassing van de COALESCE-functie, die nodig is om vergelijking van null-waarden in dit geval als ‘true’ te behandelen. De waarde -1 is gekozen omdat we aannemen dat er géén negatieve waarden in deze kolom voorkomen. 7

Zijn de twee queries van figuur 9.4 en 9.5 wel equivalent? Zoek eens ‘per ongeluk’ op een nietvoorkomende functie, en voer ze dan nog eens allebei uit. Verklaar het resultaat.

Laten we eens zoeken op de functie BARMAN: SQL> select m.mnr, m.naam, m.functie, m.maandsal 2 from medewerkers m 3 where m.maandsal > ALL (select n.maandsal 4 from medewerkers n 5 where n.functie='BARMAN'); MNR ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934

NAAM -----------SMIT ALDERS DE WAARD JANSEN MARTENS BLAAK CLERCKX SCHOTTEN DE KONING DEN DRAAIER ADAMS JANSEN SPIJKER MOLENAAR

FUNCTIE MAANDSAL ---------- -------TRAINER 800 VERKOPER 1600 VERKOPER 1250 MANAGER 2975 VERKOPER 1250 MANAGER 2850 MANAGER 2450 TRAINER 3000 DIRECTEUR 5000 VERKOPER 1500 TRAINER 1100 BOEKHOUDER 800 TRAINER 3000 BOEKHOUDER 1300

14 rows selected. SQL> select m.naam, m.functie, m.maandsal 2 from medewerkers m 3 where m.maandsal > (select max(n.maandsal) 4 from medewerkers n 5 where n.functie='BARMAN'); no rows selected


21


Appendix D

SQL>

De verklaring hiervan is dat de subquery een lege verzameling produceert, omdat de functie BARMAN niet voorkomt. De ‘> ALL’ conditie is daarom voor iedere rij van de medewerkerstabel ‘waar’. Dit gedrag is geheel in overeenstemming met de wiskundige logica. Vergelijk het maar met ‘voor alle x uit de lege verzameling geldt: …’; deze uitspraak is altijd waar, welke uitspraak we ook achter de dubbele punt plaatsen. Het maximum van een lege verzameling levert echter een null-waarde op, waardoor de conditie in het tweede geval voor iedere rij ‘onbekend’ oplevert. 8

Een serie voorbeelden in dit hoofdstuk betrof de personalia van alle medewerkers die ooit een S02-cursus hadden gegeven (zie de figuren 9.9 tot en met 9.11). Hoe kunnen we deze queries aanpassen zodat ze antwoord geven op de ontkenning van dezelfde vraag (… die nooit …)

SQL> select m.* -- ontkenning figuur 9.9 2 from medewerkers m 3 where NOT exists (select u.* 4 from uitvoeringen u 5 where u.cursus = 'S02' 6 and u.docent = m.mnr); MNR ----7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7934

NAAM -----------ALDERS DE WAARD JANSEN MARTENS BLAAK CLERCKX SCHOTTEN DE KONING DEN DRAAIER ADAMS JANSEN MOLENAAR

VOORL ----JAM TF JM P R AB SCJ CC JJ AA R TJA

FUNCTIE CHEF GBDATUM MAANDSAL COMM AFD ---------- ----- ----------- -------- ----- --VERKOPER 7698 20-FEB-1961 1600 300 30 VERKOPER 7698 22-FEB-1962 1250 500 30 MANAGER 7839 02-APR-1967 2975 20 VERKOPER 7698 28-SEP-1956 1250 1400 30 MANAGER 7839 01-NOV-1963 2850 30 MANAGER 7839 09-JUN-1965 2450 10 TRAINER 7566 26-NOV-1959 3000 20 DIRECTEUR 17-NOV-1952 5000 10 VERKOPER 7698 28-SEP-1968 1500 0 30 TRAINER 7788 30-DEC-1966 1100 20 BOEKHOUDER 7698 03-DEC-1969 800 30 BOEKHOUDER 7782 23-JAN-1962 1300 10

SQL> SQL> select m.* -- ontkenning figuur 9.10 2 from medewerkers m 3 where m.mnr NOT in (select u.docent 4 from uitvoeringen u 5 where u.cursus = 'S02'); MNR ----7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7934

NAAM -----------ALDERS DE WAARD JANSEN MARTENS BLAAK CLERCKX SCHOTTEN DE KONING DEN DRAAIER ADAMS JANSEN MOLENAAR

VOORL ----JAM TF JM P R AB SCJ CC JJ AA R TJA

FUNCTIE CHEF GBDATUM MAANDSAL COMM AFD ---------- ----- ----------- -------- ----- --VERKOPER 7698 20-FEB-1961 1600 300 30 VERKOPER 7698 22-FEB-1962 1250 500 30 MANAGER 7839 02-APR-1967 2975 20 VERKOPER 7698 28-SEP-1956 1250 1400 30 MANAGER 7839 01-NOV-1963 2850 30 MANAGER 7839 09-JUN-1965 2450 10 TRAINER 7566 26-NOV-1959 3000 20 DIRECTEUR 17-NOV-1952 5000 10 VERKOPER 7698 28-SEP-1968 1500 0 30 TRAINER 7788 30-DEC-1966 1100 20 BOEKHOUDER 7698 03-DEC-1969 800 30 BOEKHOUDER 7782 23-JAN-1962 1300 10

SQL>


22


Appendix D

Dit ziet er goed uit; we krijgen weer dezelfde twaalf medewerkers. Maar in dit geval hebben we geluk gehad; alle S02 cursus-uitvoeringen hebben toevallig een docent. We moeten dus altijd bedacht zijn op null-waarden uit een subquery, als we de NOT IN-operator of de NOT EXISTS-operator gebruiken. De volgende oplossing voor figuur 9.11 is FOUT: SQL> select DISTINCT m.* -- foutieve ontkenning figuur 9.11 2 from medewerkers m 3 join 4 uitvoeringen u 5 on m.mnr = u.docent 6 where u.cursus <> 'S02'; MNR ----7369 7566 7788 7876 7902

NAAM -----------SMIT JANSEN SCHOTTEN ADAMS SPIJKER

VOORL ----N JM SCJ AA MG

FUNCTIE CHEF GBDATUM MAANDSAL COMM AFD ---------- ----- ----------- -------- ----- --TRAINER 7902 17-DEC-1965 800 20 MANAGER 7839 02-APR-1967 2975 20 TRAINER 7566 26-NOV-1959 3000 20 TRAINER 7788 30-DEC-1966 1100 20 TRAINER 7566 13-FEB-1959 3000 20

SQL>

Het is niet eenvoudig om deze join-oplossing om te bouwen naar een ontkenning. 9

Kijk nog eens terug naar opgave 4 van het vorige hoofdstuk: Geef van alle cursusuitvoeringen de cursuscode, de begindatum, en het aantal inschrijvingen. Sorteer op begindatum. Kunnen we nu een elegantere oplossing bedenken, zonder een outer join te gebruiken?

SQL> 2 3 4 5 6 7 8 9

select , ,

cursus begindatum (select count(*) from inschrijvingen i where i.cursus = u.cursus and i.begindatum = u.begindatum) as inschrijvingen from uitvoeringen u order by inschrijvingen;

CURSUS -----ERM PRO XML RSO OAG S02 XML JAV S02 PLS OAG S02 JAV

BEGINDATUM INSCHRIJVINGEN ----------- -------------15-JAN-2001 0 19-FEB-2001 0 18-SEP-2000 0 24-FEB-2001 0 27-SEP-2000 1 13-DEC-1999 2 03-FEB-2000 2 01-FEB-2000 3 04-OKT-1999 3 11-SEP-2000 3 10-AUG-1999 3 12-APR-1999 4 13-DEC-1999 5

SQL>

10 Geef naam en voorletters van de medewerkers die ‘onderaan’ de hiërarchie hangen (dus geen ondergeschikten hebben) met in een derde kolom het aantal management-niveaus dat ze boven zich hebben.


23


Appendix D

Dit is een vrij complexe vraag waarbij diverse concepten die in dit hoofdstuk behandeld zijn nodig zullen zijn: recursive subquery factoring, venster-functies en subquery in FROM-component. We lichten de oplossing hieronder van binnen naar buiten toe. – Query IEDEREEN geeft standaard de hiërarchie in de medewerkerstabel terug, waarbij we meteen het niveau berekenen. – In de volgende stap breiden we dit resultaat uit met een IS_LEAF-kolom. Hierbij gebruiken we de LEAD-vensterfunctie om te kijken of de volgende medewerker nog dieper in de hiërarchie zit. Indien dit het geval is, dan geven we 0 terug, anders 1 (aangevende dat deze medewerker onderaan in de hiërarchie zit); – Ten slotte filteren we alleen die medewerkers naar het eindresultaat die onderaan zitten. SQL> select naam,voorl,nivo-1 2 from 3 ( 4 WITH iedereen(mnr,naam,voorl,chef,nivo) as 5 (SELECT m.mnr, m.naam, m.voorl, m.chef, 1 6 FROM medewerkers m 7 WHERE m.chef is null 8 UNION ALL 9 SELECT m.mnr, m.naam, m.voorl, m.chef, i.nivo + 1 10 FROM iedereen i, medewerkers m 11 WHERE i.mnr = m.chef) 12 SEARCH DEPTH FIRST BY chef,mnr SET seq 13 select i.naam 14 ,i.voorl 15 ,i.nivo 16 ,case 17 when (nivo - lead(nivo) over (order by seq)) < 0 then 0 18 else 1 19 end is_leaf 20 from iedereen i 21 ) 22* where is_leaf = 1 SQL> / More... NAAM -----------ADAMS SMIT ALDERS DE WAARD MARTENS DEN DRAAIER JANSEN MOLENAAR

VOORL NIVO-1 ----- ---------AA 3 N 3 JAM 2 TF 2 P 2 JJ 2 R 2 TJA 2

8 rows selected. SQL> SQL> SQL>

11 Net zoals subqueries in de SELECT-component een GROUP BY-component overbodig kunnen maken, kunnen subqueries in de FROM-component de HAVING-component overbodig maken. Licht dit toe met een voorbeeld.


24


Appendix D

We illustreren dit door de oplossing van opgave 7 uit hoofdstuk 8 te herformuleren. Die oplossing was: SQL> 2 3 4

select from group by having

cursist,cursus inschrijvingen cursist,cursus count(*) > 1 ;

Dit kunnen we herschrijven als: SQL> select * 2 from (select 3 from 4 group by 5* where aantal >

cursist,cursus,count(*) as aantal inschrijvingen cursist,cursus) 1;

More... CURSIST ---------7698 7788 7902

CURS AANTAL ---- ---------S02 2 JAV 2 S02 2



Kijk nog eens terug naar het voorbeeld in de figuren 10.8, 10.9 en 10.10. Hoe zou je de query in figuur 10.10 met behulp van de WITH-operator kunnen formuleren, zonder gebruik te maken van een view?

SQL> 2 3 4 5 6 7 8 9 10 11 12 13

with

cursusdagen as (select m.mnr, m.naam , sum(c.lengte) as dagen from inschrijvingen i , cursussen c , medewerkers m where m.mnr = i.cursist and c.code = i.cursus group by m.mnr, m.naam) select * from cursusdagen where dagen > (select avg(dagen) from cursusdagen);

MNR -------7499 7698 7788 7839 7876 7902

NAAM DAGEN ------------ -------ALDERS 11 BLAAK 12 SCHOTTEN 12 DE KONING 8 ADAMS 9 SPIJKER 9


25


Appendix D

SQL>

2

Zie figuur 10.13. Hoe is het mogelijk dat we de medewerker met nummer 7654 kunnen verwijderen via view M? Er zijn toch rijen in de HISTORIE-tabel die via een refererende sleutel verwijzen naar die medewerker?

Dat komt omdat de refererende sleutel is gedefinieerd met de optie CASCADE DELETE. 3

Maak een view die het volgende resultaat oplevert, gebaseerd op de HISTORIE-tabel: Voor iedere medewerker zien we de datum van indiensttreding, de datums van de beoordelingsmomenten, en de salarisverhogingen (of verlagingen) als gevolg van die beoordelingen.

SQL> 2 3 4 5 6 7 8 9 10 11

create or replace view sal_historie as select mnr , min(begindatum) over(partition by mnr) as d_in_dienst , begindatum as beoordeling , maandsal - lag(maandsal) over(partition by mnr order by mnr,begindatum) as salaris_verhoging from historie;

View created. SQL>


26

1. Geef code en omschrijving van alle cursussen die precies vier dagen duren

Recommend Documents