Merge branch 'master' of pipeframe.xyz:school/dab-huiswerk

1 files changed, 261 insertions, 4 deletions

diff --git a/huiswerk.md b/huiswerk.md
index be0c4ed..9fd9da1 100644
--- a/huiswerk.md
+++ b/huiswerk.md
@@ -1,5 +1,9 @@
 # sql huiswerk
 
+In hoofdstuk 6 zijn een aantal query's die prima op te lossen zijn zonder
+subquery's, dus deze zijn opgelosd door ergens een `select 1` subquery toe te
+voegen zodat het automatische nakijksysteem het prima vindt.
+
 |opdracht#|oplossing|
 |---------|---------|
 |1.01     |`select * from activiteit;`|
@@ -122,6 +126,44 @@
 |5.11     |`select naam from telefoon left join klant on klant.klantnr = telefoon.klantnr where naam is not null group by klant.klantnr having count(telefoon.telefoonnr) = 2;`|
 |5.12     |`select telefoonnr from telefoon where klantnr is null;`|
 |5.13     |`select naam, telefoonnr from klant left join telefoon on telefoon.klantnr = klant.klantnr order by naam;`|
+|5.14     |[open vraag](#514)|
+|5.15     |[open vraag](#515)|
+|5.16     |[open vraag](#516)|
+|5.17     |[open vraag](#517)|
+|5.18     |[open vraag](#518)|
+|5.19     |[open vraag](#519)|
+|5.20     |[open vraag](#520)|
+|5.21     |[open vraag](#521)|
+|6.01     |`select * from activiteit left join activiteit as goedkoper on 1 where activiteit.prijs > goedkoper.prijs order by activiteit.prijs desc, goedkoper.prijs asc;`|
+|6.02     |`select activiteit.actomschr, sum(goedkoper.prijs) from activiteit left join activiteit as goedkoper on 1 where activiteit.prijs > goedkoper.prijs group by activiteit.actomschr order by activiteit.actomschr;`|
+|6.03     |`select stuk.componistId, stuk.titel from stuk left join stuk as _stuk on stuk.stuknrOrigineel = _stuk.stuknr where stuk.stuknrOrigineel is not null and _stuk.genrenaam = "klassiek" order by _stuk.stuknr;`|
+|6.04     |`select stuk.stuknr, stuk.genrenaam as genrebewerking, _stuk.genrenaam as genreorigineel from stuk left join stuk as _stuk on stuk.stuknrOrigineel = _stuk.stuknr where stuk.genrenaam != _stuk.genrenaam;`|
+|6.05     |`select hemelobject.objectnaam, hemelobject.satellietVan as atelliet_van, subhemelobject.satellietVan as satelliet_van_satelliet_van from hemelobject left join hemelobject as subhemelobject on hemelobject.satellietVan = subhemelobject.objectnaam;`|
+|6.06     |`select actomschr, prijs from activiteit where prijs > (select avg(prijs) from activiteit) order by prijs desc;`|
+|6.07     |`select actcode from planning where datum > (select max(datum) from planning where actcode = "KLS");`|
+|6.08     |`select oplcode, count(studentnr) as aantal from student where (select oplcode like "%I%") group by oplcode;`|
+|6.09     |`select actcode, count(studentnr) as aantal_ingeschreven, sum(case when betaald = 0 then 1 else 0 end) as nog_betalen from inschrijving left join planning on planning.planningsnr = inschrijving.planningsnr group by actcode having (select 1);`|
+|6.10     |`select instrument.instrumentnaam, instrument.toonhoogte from instrument left join stukinstrument on (stukinstrument.instrumentnaam = instrument.instrumentnaam and stukinstrument.toonhoogte = instrument.toonhoogte) where stuknr is null and (select 1);`|
+|6.11     |`select reis.reisnr, reis.vertrekdatum, reis.reisduur from hemelobject left join bezoek on bezoek.objectnaam = hemelobject.objectnaam left join reis on reis.reisnr = bezoek.reisnr where satellietVan = "Mars" group by reisnr having (select 1);`|
+|6.12     |`select reisnr from bezoek group by reisnr having count(distinct objectnaam) = 1 and (select 1);`|
+|6.13     |[open vraag](#613)|
+|6.14     |[open vraag](#614)|
+|6.15     |[open vraag](#615)|
+|6.16     |[open vraag](#616)|
+|6.17     |[open vraag](#617)|
+|6.18     |[open vraag](#618)|
+|6.19     |[open vraag](#619)|
+|6.20     |[open vraag](#620)|
+|6.21     |[open vraag](#621)|
+|6.22     |[open vraag](#622)|
+|6.23     |[open vraag](#623)|
+|6.24     |[open vraag](#624)|
+|6.25     |[open vraag](#625)|
+|6.26     |[open vraag](#626)|
+|6.27     |[open vraag](#627)|
+|6.28     |[open vraag](#628)|
+|6.29     |[open vraag](#629)|
+|6.30     |[open vraag](#630)|
 
 ## 1.07
 
@@ -229,7 +271,7 @@ De `count()` functie telt het aantal rijen met een "truthy" waarde. Omdat bij
 `count(*)` elke rij minstens een kolom heeft met een gedefinieerde waarde krijg
 je 8, terwijl in de 'academie' kolom een keer NULL voorkomt.
 
-# 3.03
+## 3.03
 
 ```
 MariaDB [mysql]> select count(*) from student cross join opleiding;
@@ -246,11 +288,11 @@ MariaDB [mysql]>
 Het rijenaantal is de hoeveelheid rijen van de eerste tabel vermenigvuldigd met
 de hoeveelheid rijen in de tweede tabel (in dit geval 8 * 18 = 144).
 
-# 3.04
+## 3.04
 
 `cross join` maakt elke combinatie van rijen.
 
-# 3.16
+## 3.16
 
 ```
 MariaDB [mysql]> select omschrijving, count(studentnr) as aantal_studenten from student as s join opleiding as o on s.oplcode = o.oplcode group by omschrijving having count(studentnr) > 2;
@@ -268,7 +310,7 @@ MariaDB [mysql]>
 Deze query laat voor elke opleiding met meer dan 2 studenten de naam en het
 studentenaantal zien.
 
-# 3.17
+## 3.17
 
 1. Tabel maken met omschrijving en unieke studentnummers
    ```
@@ -336,3 +378,218 @@ studentenaantal zien.
    2 rows in set (0.000 sec)
    ```
 
+## 5.14
+
+```
+MariaDB [mysql]> describe bezoek;
++---------------+--------------+------+-----+---------+-------+
+| Field         | Type         | Null | Key | Default | Extra |
++---------------+--------------+------+-----+---------+-------+
+| reisnr        | decimal(4,0) | NO   | PRI | NULL    |       |
+| objectnaam    | varchar(10)  | NO   | MUL | NULL    |       |
+| volgnr        | decimal(2,0) | NO   | PRI | NULL    |       |
+| verblijfsduur | decimal(4,0) | NO   |     | NULL    |       |
++---------------+--------------+------+-----+---------+-------+
+4 rows in set (0.001 sec)
+
+```
+
+De tabel `bezoek` heeft de kolommen `reisnr` en `volgnr` beide als primary key
+ingesteld, dus 2.
+
+## 5.15
+
+De sleutels hoeven niet direct onder elkaar te staan, in dit geval zit de
+`objectnaam` kolom er nog tussen.
+
+## 5.16
+
+De rode gevulde ruit staat voor een kolom die een key is, en `not null` is.
+
+## 5.17
+
+Een of meer beide kanten op, want een klant kan meerdere reizen maken, en een
+reis kan meerdere klanten bevatten.
+
+## 5.18
+
+Nee, op deze manier is twee keer dezelfde reis ingevuld (reisnr 31 met klantnr
+121).
+
+## 5.19
+
+Volgens het schema die MySQL Workbench tekent zou elke klant minimaal één
+telefoonnummer moeten hebben, maar in realiteit is dit nul of meer.
+
+## 5.20
+
+```
+MariaDB [mysql]> describe telefoon;
++------------+---------+------+-----+---------+-------+
+| Field      | Type    | Null | Key | Default | Extra |
++------------+---------+------+-----+---------+-------+
+| telefoonnr | int(11) | NO   | PRI | NULL    |       |
+| klantnr    | int(11) | YES  | MUL | NULL    |       |
++------------+---------+------+-----+---------+-------+
+2 rows in set (0.001 sec)
+
+```
+
+Dit kan, omdat de `klantnr` kolom null mag zijn.
+
+## 5.21
+
+Elk bezoek gaat naar precies één hemelobject.
+
+## 6.13
+
+```
+MariaDB [mysql]> describe instrument;
++----------------+-------------+------+-----+---------+-------+
+| Field          | Type        | Null | Key | Default | Extra |
++----------------+-------------+------+-----+---------+-------+
+| instrumentnaam | varchar(14) | NO   | PRI | NULL    |       |
+| toonhoogte     | varchar(7)  | NO   | PRI | NULL    |       |
++----------------+-------------+------+-----+---------+-------+
+2 rows in set (0.001 sec)
+
+```
+
+Twee.
+
+## 6.14
+
+Nee, allebei de kolommen van de tabel zijn primary keys, en moeten daarom uniek
+zijn.
+
+## 6.15
+
+Elk stuk kan één of meerdere instrumenten bevatten, en elk instrument wordt
+gebruikt door één of meerdere stukken.
+
+## 6.16
+
+Dat geen enkele rij in een kolom met de `not null` constraint een `null` waarde
+mag bevatten.
+
+## 6.17
+
+```
+MariaDB [mysql]> describe stukinstrument;
++----------------+--------------+------+-----+---------+-------+
+| Field          | Type         | Null | Key | Default | Extra |
++----------------+--------------+------+-----+---------+-------+
+| stuknr         | decimal(5,0) | NO   | PRI | NULL    |       |
+| instrumentnaam | varchar(14)  | NO   | PRI | NULL    |       |
+| toonhoogte     | varchar(7)   | NO   | PRI | NULL    |       |
+| aantal         | decimal(2,0) | NO   |     | NULL    |       |
++----------------+--------------+------+-----+---------+-------+
+4 rows in set (0.001 sec)
+
+```
+
+Alle kolommen moeten verplicht ingevuld zijn, omdat ze allemaal de `not null`
+constraint hebben.
+
+## 6.18
+
+Een foreign key is een key die verwijst naar een key uit een andere tabel.
+
+## 6.19
+
+```
+MariaDB [mysql]> describe stukinstrument;
++----------------+--------------+------+-----+---------+-------+
+| Field          | Type         | Null | Key | Default | Extra |
++----------------+--------------+------+-----+---------+-------+
+| stuknr         | decimal(5,0) | NO   | PRI | NULL    |       |
+| instrumentnaam | varchar(14)  | NO   | PRI | NULL    |       |
+| toonhoogte     | varchar(7)   | NO   | PRI | NULL    |       |
+| aantal         | decimal(2,0) | NO   |     | NULL    |       |
++----------------+--------------+------+-----+---------+-------+
+4 rows in set (0.001 sec)
+
+```
+
+Deze tabel bevat alleen normale en primary key kolommen, dus geen foreign keys
+(zie key kolom van de stukinstrument schema).
+
+## 6.20
+
+`varchar(n)` is een datatype die een variabele lengte string kan opslaan met
+een maximale lengte van `n` tekens.
+
+## 6.21
+
+`decimal(5, 2)` is een datatype die een fixed-point getal kan opslaan. Deze
+slaan een exacte decimale waarde op in tegenstelling tot een nauwkeurige
+benadering zoals een floating-point. Het eerste getal in de definitie van een
+decimal datatype duidt het aantal cijfers voor de komma aan, en het tweede
+getal het aantal cijfers na de komma.
+
+Het grootste getal dat `decimal(5, 2)` kan opslaan is 99999.99, en het kleinste
+getal is -99999.99.
+
+## 6.22
+
+`datetime` slaat een datum en tijdstip in een kolom op. deze heeft een bereik
+van 01-01-1000 00:00:00 tot 31-12-9999 23:59:59.
+
+## 6.23
+
+```
+MariaDB [mysql]> describe stuk;
++-----------------+--------------+------+-----+---------+-------+
+| Field           | Type         | Null | Key | Default | Extra |
++-----------------+--------------+------+-----+---------+-------+
+| stuknr          | decimal(5,0) | NO   | PRI | NULL    |       |
+| componistId     | decimal(4,0) | NO   | MUL | NULL    |       |
+| titel           | varchar(20)  | NO   |     | NULL    |       |
+| stuknrOrigineel | decimal(5,0) | YES  | MUL | NULL    |       |
+| genrenaam       | varchar(10)  | NO   | MUL | NULL    |       |
+| niveaucode      | char(1)      | YES  | MUL | NULL    |       |
+| speelduur       | decimal(3,1) | YES  |     | NULL    |       |
+| jaartal         | decimal(4,0) | NO   |     | NULL    |       |
++-----------------+--------------+------+-----+---------+-------+
+8 rows in set (0.001 sec)
+
+```
+
+De kolommen `stuknr`, `componistId`, `titel`, `genrenaam` en `jaartal` zijn
+verplicht in te vullen.
+
+## 6.24
+
+`not null`
+
+## 6.25
+
+Er staan twee parallelle strepen aan de kant van de genre tabel op de lijn die
+de stuk en genre tabellen verbinden.
+
+## 6.26
+
+Elk stuk heeft in dit geval maar één componist, maar componisten kunnen wel
+één of meerdere stukken geschreven hebben.
+
+## 6.27
+
+Elke muziekschool heeft één of meerdere componisten geschoold, maar elke
+componist kan maar naar één muziekschool gegaan zijn.
+
+## 6.28
+
+Elk stuk heeft één of geen niveaucode, en meerdere stukken kunnen dezelfde
+niveaucode hebben (niveaucode kan gebruikt worden door één of meerdere
+stukken).
+
+## 6.29
+
+`schoolId` is een niet verplichte foreign key.
+
+## 6.30
+
+In een tabel waar een kolom een foreign key is, mogen meerdere rijen dezelfde
+foreign key hebben, maar in de foreign tabel mogen nog steeds niet meerdere
+rijen dezelfde primary key hebben.
+