diff options
| -rw-r--r-- | huiswerk.md | 265 |
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. + |