From 94bcc5b83fa451ac851aea19a8956f3531a44d67 Mon Sep 17 00:00:00 2001 From: lonkaars Date: Sun, 9 Oct 2022 13:37:27 +0200 Subject: huiswerk naar submap verplaatst --- .gitignore | 1 + huiswerk.md | 595 --------------------------------------------------- huiswerk/huiswerk.md | 595 +++++++++++++++++++++++++++++++++++++++++++++++++++ huiswerk/makefile | 6 + pdf.mk | 4 + 5 files changed, 606 insertions(+), 595 deletions(-) delete mode 100644 huiswerk.md create mode 100644 huiswerk/huiswerk.md create mode 100644 huiswerk/makefile diff --git a/.gitignore b/.gitignore index 54e250c..81ac420 100644 --- a/.gitignore +++ b/.gitignore @@ -1,4 +1,5 @@ **/*.md +!huiswerk/huiswerk.md **/.tmp.sql opdracht-*/opdracht.pdf eindopdracht/nfs.xlsx diff --git a/huiswerk.md b/huiswerk.md deleted file mode 100644 index 9fd9da1..0000000 --- a/huiswerk.md +++ /dev/null @@ -1,595 +0,0 @@ -# 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;`| -|1.02 |`select * from opleiding;`| -|1.03 |`select naam from student;`| -|1.04 |`select naam,oplcode from student;`| -|1.05 |`select oplcode from opleiding;`| -|1.06 |`select distinct oplcode from student;`| -|1.07 |[`select distinct * from opleiding;`](#107)| -|1.08 |[`select distinct academie from opleiding;`](#108)| -|1.09 |[`select distinct academie,oplcode from opleiding;`](#109)| -|1.10 |`select naam from student order by naam asc;`| -|1.11 |`select * from opleiding order by omschrijving desc;`| -|1.12 |`select actomschr,prijs from activiteit order by prijs asc;`| -|1.13 |`select distinct oplcode from student order by oplcode asc;`| -|1.14 |`select oplcode,studentnr,naam from student order by oplcode asc, naam desc;`| -|1.15 |[`select distinct * from opleiding;`](#115)| -|1.16 |`select * from activiteit where actcode = "KLS";`| -|1.17 |`select * from activiteit where prijs > 10.0;`| -|1.18 |`select * from activiteit where prijs >= 15.0 order by prijs;`| -|1.19 |`select * from opleiding where academie is null;`| -|1.20 |`select * from opleiding where oplcode like "T%";`| -|1.21 |`select * from opleiding where academie = "AII" order by oplcode;`| -|1.22 |`select * from activiteit where actomschr like "P%" and prijs < 10.0 order by actcode desc;`| -|1.23 |`select * from student where oplcode = "IN" or oplcode = "TI";`| -|1.24 |`select * from student where oplcode != "WTB";`| -|1.25 |`select * from student limit 3;`| -|1.26 |`select prijs from activiteit order by prijs limit 4;`| -|1.27 |`select datum from planning order by datum desc limit 1;`| -|1.28 |`select * from activiteit order by actcode limit 1,3;`| -|1.29 |`select * from activiteit order by prijs desc limit 1,1;`| -|1.30 |`select count(*) as aantal_studenten from student;`| -|1.31 |`select min(datum) as vroegste_datum, max(datum) as laatste_datum from planning;`| -|1.32 |`select min(datum) as vroegste_datum_KLS from planning where actcode = "KLS";`| -|1.33 |`select avg(prijs) as gemiddelde_prijs from activiteit;`| -|1.34 |`select sum(prijs) as totale_prijs from activiteit;`| -|1.35 |`select sum(prijs) as totale_prijs_actcode_P from activiteit where actcode like "P%";`| -|1.36 |`select count(distinct academie) as aantal_academies from opleiding where academie is not NULL;`| -|1.37 |`select count(distinct oplcode) as aantal_opleiding_met_student from student;`| -|1.38 |`select count(actcode) as aantal_studenten_KRS from belangstelling where actcode = "KRS";`| -|1.39 |`select count(*) from inschrijving where planningsnr >= 4 and planningsnr <= 9 and betaald = 0;`| -|2.01 |`select naam from student where instr(naam, "a");`| -|2.02 |`select ucase(naam) from student;`| -|2.03 |`select naam, concat(left(naam, 2), right(naam, 2)) as wachtwoord from student;`| -|2.04 |`select left(naam, instr(naam, " ") - 1) as voornaam, right(naam, length(naam) - instr(naam, " ")) as achternaam from student;`| -|2.05 |`select round(avg(prijs),1) as gemiddelde_prijs from activiteit;`| -|2.06 |`select naam, replace(oplcode, "IN", "Informatica") as opleiding from student;`| -|2.07 |`select left(naam, instr(naam, " ") - 1) as voornaam from student where right(naam, length(naam) - instr(naam, " ")) = "Tomeloos";`| -|2.08 |`select count(*) as aantal_eindigend_op_S from activiteit where right(actcode, 1) = "S";`| -|2.09 |`select sum(prijs) as totaalprijs from activiteit where left(actcode, 1) = "K" and right(actcode, 1) = "S";`| -|2.10 |`select * from opleiding where length(oplcode) > 2 order by oplcode;`| -|2.11 |`select actomschr, prijs, prijs + 1 as nieuwe_prijs from activiteit;`| -|2.12 |`select actomschr, prijs, round(prijs * 1.05, 2) as nieuwe_prijs from activiteit;`| -|2.13 |`select actomschr, prijs, round(prijs * 1.21, 2) as bruto_prijs from activiteit order by bruto_prijs desc;`| -|2.14 |`select actomschr, prijs, round(prijs / 1.21, 2) as netto_prijs from activiteit where prijs < 20.0 order by netto_prijs desc;`| -|2.15 |`select * from activiteit where prijs between 10 and 25;`| -|2.16 |`select * from activiteit where prijs > 10 and prijs < 25;`| -|2.17 |`select * from student where oplcode in("ET", "TI") order by oplcode asc, naam asc;`| -|2.18 |`select * from student where naam like "%a%";`| -|2.19 |`select * from student where naam like "_oo%" and right(naam, length(naam) - instr(naam, " ")) not like "Tomeloos";`| -|2.20 |[open vraag](#220)| -|2.21 |`select academie, count(academie) from opleiding where academie is not NULL group by academie order by academie desc;`| -|2.22 |`select actcode, count(actcode) as aantal_deelnemers from belangstelling group by actcode;`| -|2.23 |`select actcode, datum, count(actcode) as aantal_deelnemers from belangstelling group by datum, actcode order by actcode, datum;`| -|2.24 |`select actcode, datum, count(actcode) as aantal_deelnemers from belangstelling group by datum, actcode having datum is not null and aantal_deelnemers >= 2 order by actcode, datum;`| -|2.25 |`select planningsnr, sum(betaald) as betaald from inschrijving group by planningsnr;`| -|2.26 |`select planningsnr from inschrijving group by planningsnr having sum(betaald) = 0;`| -|3.01 |`select count(*) as aantal_records_studenten from student;`| -|3.02 |`select count(*) as aantal_records_studenten from opleiding;`| -|3.03 |[`select count(*) from student cross join opleiding;`](#303)| -|3.04 |[open vraag](#304)| -|3.05 |`select * from student inner join opleiding on student.oplcode = opleiding.oplcode;`| -|3.06 |`select count(*) as aantal_studenten_ABCT from student inner join opleiding on student.oplcode = opleiding.oplcode where academie = "ABCT";`| -|3.07 |`select distinct naam from student inner join belangstelling on student.studentnr = belangstelling.studentnr;`| -|3.08 |`select distinct actomschr from activiteit inner join belangstelling on activiteit.actcode = belangstelling.actcode;`| -|3.09 |`select distinct naam from student inner join inschrijving on inschrijving.studentnr = student.studentnr where inschrijving.betaald = 1 and inschrijving.planningsnr = 12;`| -|3.10 |`select planningsnr, actomschr, datum from planning inner join activiteit on activiteit.actcode = planning.actcode order by planningsnr;`| -|3.11 |`select planning.actcode, planning.datum, student.naam from inschrijving inner join planning on planning.planningsnr = inschrijving.planningsnr inner join student on student.studentnr = inschrijving.studentnr order by planning.actcode, planning.datum, student.naam;`| -|3.12 |`select opleiding.academie, count(inschrijving.betaald) as aantal_niet_betaald from student inner join opleiding on opleiding.oplcode = student.oplcode inner join inschrijving on inschrijving.studentnr = student.studentnr where inschrijving.betaald = 0 group by academie;`| -|3.13 |`select student.naam, sum(activiteit.prijs) as betaald from inschrijving inner join student on student.studentnr = inschrijving.studentnr inner join planning on planning.planningsnr = inschrijving.planningsnr inner join activiteit on activiteit.actcode = planning.actcode where inschrijving.betaald = 1 group by student.naam order by student.studentnr;`| -|3.14 |`select opleiding.academie, sum(activiteit.prijs) as nog_te_betalen from inschrijving inner join student on student.studentnr = inschrijving.studentnr inner join opleiding on opleiding.oplcode = student.oplcode inner join planning on planning.planningsnr = inschrijving.planningsnr inner join activiteit on activiteit.actcode = planning.actcode where inschrijving.betaald = 0 group by academie;`| -|3.15 |`select activiteit.actomschr, sum(activiteit.prijs) as openstaand_bedrag from inschrijving inner join planning on planning.planningsnr = inschrijving.planningsnr inner join activiteit on activiteit.actcode = planning.actcode where inschrijving.betaald = 0 group by actomschr order by openstaand_bedrag desc;`| -|3.16 |[open vraag](#316)| -|3.17 |[open vraag](#317)| -|4.01 |`select distinct student.naam from student left join belangstelling on belangstelling.studentnr = student.studentnr where belangstelling.studentnr is null;`| -|4.02 |`select distinct student.naam from belangstelling right join student on student.studentnr = belangstelling.studentnr where belangstelling.studentnr is null;`| -|4.03 |`select distinct actomschr from activiteit left join belangstelling on belangstelling.actcode = activiteit.actcode where studentnr is null;`| -|4.04 |`select omschrijving, count(studentnr) as aantal from opleiding left join student on student.oplcode = opleiding.oplcode group by omschrijving;`| -|4.05 |`select academie, count(belangstelling.studentnr) as aantal_inschrijvingen from opleiding left join student on student.oplcode = opleiding.oplcode left join belangstelling on belangstelling.studentnr = student.studentnr where opleiding.academie is not null group by academie;`| -|4.06 |`select * from inschrijving left join beschikbaar on beschikbaar.datum = inschrijving.datum union all select * from inschrijving right join beschikbaar on beschikbaar.datum = inschrijving.datum;`| -|4.07 |`select week(date("20120917"));`| -|4.08 |`select datediff(max(datum), min(datum)) as aantal_dagen_ertussen, max(datum) as maximum_datum, min(datum) as minimum_datum from planning;`| -|4.09 |`select to_char(now(), "DD-MM-YYYY") as NL_notatie;`| -|4.10 |`select * from stuk where genrenaam = "klassiek" or jaartal > 1980;`| -|4.11 |`select * from stuk where genrenaam = "pop" or (speelduur < 5 and niveaucode = "A");`| -|4.12 |`select * from stuk where niveaucode in("A", "B") and genrenaam != "pop" and speelduur <= 8;`| -|4.13 |`select stuknr, titel, genrenaam, niveaucode from stuk where (niveaucode in ("B", "C") or niveaucode is null) and genrenaam = "klassiek";`| -|4.14 |`select stuknr, titel, genrenaam, niveaucode from stuk where niveaucode is not null order by niveaucode desc, genrenaam;`| -|4.15 |`select count(distinct niveaucode) as aantal from stuk;`| -|4.16 |`select genrenaam, count(genrenaam) as aantal from stuk where niveaucode in("A", "B") group by genrenaam having aantal > 1;`| -|4.17 |`select genrenaam, count(genrenaam) as aantal from stuk where jaartal > 1900 group by genrenaam having aantal >= 3;`| -|4.18 |`select genrenaam, count(genrenaam) as aantal, avg(speelduur) as gemiddelde from stuk where niveaucode is not null group by genrenaam;`| -|4.19 |`select genrenaam, niveaucode, count(stuknr) as aantal from stuk where niveaucode in("A", "B") group by genrenaam, niveaucode having aantal > 1;`| -|4.20 |`select naam, titel from stuk inner join componist on stuk.componistId = componist.componistId;`| -|4.21 |`select stuknr, speelduur, omschrijving from stuk left join niveau on niveau.niveaucode = stuk.niveaucode where genrenaam = "klassiek";`| -|4.22 |`select componist.naam, muziekschool.plaatsnaam from componist left join muziekschool on muziekschool.schoolId = componist.schoolId where year(geboortedatum) >= 1900;`| -|4.23 |`select stuknr, titel, componist.naam, niveau.omschrijving from stuk left join componist on stuk.componistId = componist.componistId left join niveau on niveau.niveaucode = stuk.niveaucode where omschrijving is not null;`| -|4.24 |`select niveau.omschrijving, stuk.niveaucode, count(stuknr) from stuk left join niveau on niveau.niveaucode = stuk.niveaucode group by niveaucode having niveaucode is not null;`| -|4.25 |`select instrument.instrumentnaam, instrument.toonhoogte from instrument left join stukinstrument on (stukinstrument.instrumentnaam = instrument.instrumentnaam and stukinstrument.toonhoogte = instrument.toonhoogte) where stuknr is null;`| -|5.01 |`select distinct reis.reisnr, vertrekdatum, reisduur from reis left join bezoek on bezoek.reisnr = reis.reisnr where bezoek.verblijfsduur >= 1 and bezoek.objectnaam = "Mars" group by reisnr;`| -|5.02 |`select reis.reisnr, naam, geboortedatum, prijs from reis left join deelname on deelname.reisnr = reis.reisnr left join klant on klant.klantnr = deelname.klantnr where naam is not null;`| -|5.03 |`select hemelobject.objectnaam, count(bezoek.reisnr) as aantal_landingen from hemelobject left join bezoek on bezoek.objectnaam = hemelobject.objectnaam group by objectnaam order by objectnaam;`| -|5.04 |`select reisnr, count(distinct objectnaam) as aantal_verschillende_objecten from bezoek group by reisnr;`| -|5.05 |`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;`| -|5.06 |`select reisnr from bezoek group by reisnr having count(distinct objectnaam) = 1;`| -|5.07 |`select reis.reisnr, count(deelname.reisnr) from reis left join deelname on deelname.reisnr = reis.reisnr group by reis.reisnr order by reis.vertrekdatum;`| -|5.08 |`select reis.reisnr, vertrekdatum, reisduur, sum(verblijfsduur) as totale_verblijfsduur from bezoek left join reis on reis.reisnr = bezoek.reisnr group by reisnr having totale_verblijfsduur >= 14;`| -|5.09 |`select klant.klantnr, naam, count(reisnr) as aantal_reizen from deelname left join klant on klant.klantnr = deelname.klantnr group by klantnr having aantal_reizen >= 3;`| -|5.10 |`select reisnr, count(reisnr) as aantal_klanten from deelname left join klant on klant.klantnr = deelname.klantnr group by reisnr;`| -|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 - -``` -MariaDB [mysql]> select distinct * from opleiding; -+---------+--------------------------+----------+ -| oplcode | omschrijving | academie | -+---------+--------------------------+----------+ -| BK | Bouwkunde | ABCT | -| CT | Civiele Techniek | ABCT | -| ET | Electrotechniek | AII | -| IN | Informatica | AII | -| TBK | Technische Bedrijfskunde | AII | -| TI | Technische Informatica | AII | -| VS | Virtueel Studeren | NULL | -| WTB | Werktuigbouw | AII | -+---------+--------------------------+----------+ -8 rows in set (0.000 sec) - -MariaDB [mysql]> -``` - -Dit commando pakt de distincte resultaten uit de primary key kolom (oplcode), -en weergeeft ook alle andere kolommen voor elk resultaat. - -## 1.08 - -``` -MariaDB [mysql]> select distinct academie from opleiding; -+----------+ -| academie | -+----------+ -| ABCT | -| AII | -| NULL | -+----------+ -3 rows in set (0.000 sec) - -``` - -Dit commando laat alleen de distincte resultaten uit de `academie` kolom van de -`opleiding` tabel zien. - -## 1.09 - -``` -MariaDB [mysql]> select distinct academie,oplcode from opleiding; -+----------+---------+ -| academie | oplcode | -+----------+---------+ -| ABCT | BK | -| ABCT | CT | -| AII | ET | -| AII | IN | -| AII | TBK | -| AII | TI | -| NULL | VS | -| AII | WTB | -+----------+---------+ -8 rows in set (0.001 sec) - -MariaDB [mysql]> -``` - -Dit commando pakt de distincte resultaten uit de primary key kolom (oplcode), en -weergeeft ook de `academie` kolom voor elk resultaat. - -## 1.15 - -``` -MariaDB [mysql]> select * order by actcode from planning; -ERROR 1064 (42000): You have an error in your SQL syntax; -check the manual that corresponds to your MariaDB server version for the right -syntax to use near 'from planning' at line 1 -MariaDB [mysql]> -``` - -Dit commando geeft een foutmelding, maar werkt normaal wanneer het `order by -actcode` gedeelte na het `from planning` gedeelte komt. Dus zijn de volgorde -van SQL statements belangrijk. - -## 2.20 - -``` -MariaDB [mysql]> select count(*) from opleiding; -+----------+ -| count(*) | -+----------+ -| 8 | -+----------+ -1 row in set (0.000 sec) - -MariaDB [mysql]> select count(academie) from opleiding; -+-----------------+ -| count(academie) | -+-----------------+ -| 7 | -+-----------------+ -1 row in set (0.000 sec) - -MariaDB [mysql]> -``` - -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 - -``` -MariaDB [mysql]> select count(*) from student cross join opleiding; -+----------+ -| count(*) | -+----------+ -| 144 | -+----------+ -1 row in set (0.000 sec) - -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 - -`cross join` maakt elke combinatie van rijen. - -## 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; -+------------------------+------------------+ -| omschrijving | aantal_studenten | -+------------------------+------------------+ -| Informatica | 5 | -| Technische Informatica | 4 | -+------------------------+------------------+ -2 rows in set (0.000 sec) - -MariaDB [mysql]> -``` - -Deze query laat voor elke opleiding met meer dan 2 studenten de naam en het -studentenaantal zien. - -## 3.17 - -1. Tabel maken met omschrijving en unieke studentnummers - ``` - MariaDB [mysql]> select omschrijving, studentnr from student join opleiding on student.oplcode = opleiding.oplcode; - +--------------------------+-----------+ - | omschrijving | studentnr | - +--------------------------+-----------+ - | Bouwkunde | 16 | - | Civiele Techniek | 17 | - | Civiele Techniek | 18 | - | Electrotechniek | 5 | - | Electrotechniek | 9 | - | Informatica | 1 | - | Informatica | 2 | - | Informatica | 6 | - | Informatica | 7 | - | Informatica | 10 | - | Technische Bedrijfskunde | 14 | - | Technische Bedrijfskunde | 15 | - | Technische Informatica | 3 | - | Technische Informatica | 4 | - | Technische Informatica | 8 | - | Technische Informatica | 11 | - | Werktuigbouw | 12 | - | Werktuigbouw | 13 | - +--------------------------+-----------+ - 18 rows in set (0.000 sec) - ``` -2. Studentnummers tellen en groeperen per opleiding - ``` - MariaDB [mysql]> select omschrijving, count(studentnr) from student join opleiding on student.oplcode = opleiding.oplcode group by omschrijving; - +--------------------------+------------------+ - | omschrijving | count(studentnr) | - +--------------------------+------------------+ - | Bouwkunde | 1 | - | Civiele Techniek | 2 | - | Electrotechniek | 2 | - | Informatica | 5 | - | Technische Bedrijfskunde | 2 | - | Technische Informatica | 4 | - | Werktuigbouw | 2 | - +--------------------------+------------------+ - 7 rows in set (0.000 sec) - ``` -3. Verberg alle opleidingen met 2 of minder studenten - ``` - MariaDB [mysql]> select omschrijving, count(studentnr) from student join opleiding on student.oplcode = opleiding.oplcode group by omschrijving having count(studentnr) > 2; - +------------------------+------------------+ - | omschrijving | count(studentnr) | - +------------------------+------------------+ - | Informatica | 5 | - | Technische Informatica | 4 | - +------------------------+------------------+ - 2 rows in set (0.000 sec) - ``` -4. Kolomnamen en tabelaliasen toevoegen - ``` - 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; - +------------------------+------------------+ - | omschrijving | aantal_studenten | - +------------------------+------------------+ - | Informatica | 5 | - | Technische Informatica | 4 | - +------------------------+------------------+ - 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. - diff --git a/huiswerk/huiswerk.md b/huiswerk/huiswerk.md new file mode 100644 index 0000000..9fd9da1 --- /dev/null +++ b/huiswerk/huiswerk.md @@ -0,0 +1,595 @@ +# 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;`| +|1.02 |`select * from opleiding;`| +|1.03 |`select naam from student;`| +|1.04 |`select naam,oplcode from student;`| +|1.05 |`select oplcode from opleiding;`| +|1.06 |`select distinct oplcode from student;`| +|1.07 |[`select distinct * from opleiding;`](#107)| +|1.08 |[`select distinct academie from opleiding;`](#108)| +|1.09 |[`select distinct academie,oplcode from opleiding;`](#109)| +|1.10 |`select naam from student order by naam asc;`| +|1.11 |`select * from opleiding order by omschrijving desc;`| +|1.12 |`select actomschr,prijs from activiteit order by prijs asc;`| +|1.13 |`select distinct oplcode from student order by oplcode asc;`| +|1.14 |`select oplcode,studentnr,naam from student order by oplcode asc, naam desc;`| +|1.15 |[`select distinct * from opleiding;`](#115)| +|1.16 |`select * from activiteit where actcode = "KLS";`| +|1.17 |`select * from activiteit where prijs > 10.0;`| +|1.18 |`select * from activiteit where prijs >= 15.0 order by prijs;`| +|1.19 |`select * from opleiding where academie is null;`| +|1.20 |`select * from opleiding where oplcode like "T%";`| +|1.21 |`select * from opleiding where academie = "AII" order by oplcode;`| +|1.22 |`select * from activiteit where actomschr like "P%" and prijs < 10.0 order by actcode desc;`| +|1.23 |`select * from student where oplcode = "IN" or oplcode = "TI";`| +|1.24 |`select * from student where oplcode != "WTB";`| +|1.25 |`select * from student limit 3;`| +|1.26 |`select prijs from activiteit order by prijs limit 4;`| +|1.27 |`select datum from planning order by datum desc limit 1;`| +|1.28 |`select * from activiteit order by actcode limit 1,3;`| +|1.29 |`select * from activiteit order by prijs desc limit 1,1;`| +|1.30 |`select count(*) as aantal_studenten from student;`| +|1.31 |`select min(datum) as vroegste_datum, max(datum) as laatste_datum from planning;`| +|1.32 |`select min(datum) as vroegste_datum_KLS from planning where actcode = "KLS";`| +|1.33 |`select avg(prijs) as gemiddelde_prijs from activiteit;`| +|1.34 |`select sum(prijs) as totale_prijs from activiteit;`| +|1.35 |`select sum(prijs) as totale_prijs_actcode_P from activiteit where actcode like "P%";`| +|1.36 |`select count(distinct academie) as aantal_academies from opleiding where academie is not NULL;`| +|1.37 |`select count(distinct oplcode) as aantal_opleiding_met_student from student;`| +|1.38 |`select count(actcode) as aantal_studenten_KRS from belangstelling where actcode = "KRS";`| +|1.39 |`select count(*) from inschrijving where planningsnr >= 4 and planningsnr <= 9 and betaald = 0;`| +|2.01 |`select naam from student where instr(naam, "a");`| +|2.02 |`select ucase(naam) from student;`| +|2.03 |`select naam, concat(left(naam, 2), right(naam, 2)) as wachtwoord from student;`| +|2.04 |`select left(naam, instr(naam, " ") - 1) as voornaam, right(naam, length(naam) - instr(naam, " ")) as achternaam from student;`| +|2.05 |`select round(avg(prijs),1) as gemiddelde_prijs from activiteit;`| +|2.06 |`select naam, replace(oplcode, "IN", "Informatica") as opleiding from student;`| +|2.07 |`select left(naam, instr(naam, " ") - 1) as voornaam from student where right(naam, length(naam) - instr(naam, " ")) = "Tomeloos";`| +|2.08 |`select count(*) as aantal_eindigend_op_S from activiteit where right(actcode, 1) = "S";`| +|2.09 |`select sum(prijs) as totaalprijs from activiteit where left(actcode, 1) = "K" and right(actcode, 1) = "S";`| +|2.10 |`select * from opleiding where length(oplcode) > 2 order by oplcode;`| +|2.11 |`select actomschr, prijs, prijs + 1 as nieuwe_prijs from activiteit;`| +|2.12 |`select actomschr, prijs, round(prijs * 1.05, 2) as nieuwe_prijs from activiteit;`| +|2.13 |`select actomschr, prijs, round(prijs * 1.21, 2) as bruto_prijs from activiteit order by bruto_prijs desc;`| +|2.14 |`select actomschr, prijs, round(prijs / 1.21, 2) as netto_prijs from activiteit where prijs < 20.0 order by netto_prijs desc;`| +|2.15 |`select * from activiteit where prijs between 10 and 25;`| +|2.16 |`select * from activiteit where prijs > 10 and prijs < 25;`| +|2.17 |`select * from student where oplcode in("ET", "TI") order by oplcode asc, naam asc;`| +|2.18 |`select * from student where naam like "%a%";`| +|2.19 |`select * from student where naam like "_oo%" and right(naam, length(naam) - instr(naam, " ")) not like "Tomeloos";`| +|2.20 |[open vraag](#220)| +|2.21 |`select academie, count(academie) from opleiding where academie is not NULL group by academie order by academie desc;`| +|2.22 |`select actcode, count(actcode) as aantal_deelnemers from belangstelling group by actcode;`| +|2.23 |`select actcode, datum, count(actcode) as aantal_deelnemers from belangstelling group by datum, actcode order by actcode, datum;`| +|2.24 |`select actcode, datum, count(actcode) as aantal_deelnemers from belangstelling group by datum, actcode having datum is not null and aantal_deelnemers >= 2 order by actcode, datum;`| +|2.25 |`select planningsnr, sum(betaald) as betaald from inschrijving group by planningsnr;`| +|2.26 |`select planningsnr from inschrijving group by planningsnr having sum(betaald) = 0;`| +|3.01 |`select count(*) as aantal_records_studenten from student;`| +|3.02 |`select count(*) as aantal_records_studenten from opleiding;`| +|3.03 |[`select count(*) from student cross join opleiding;`](#303)| +|3.04 |[open vraag](#304)| +|3.05 |`select * from student inner join opleiding on student.oplcode = opleiding.oplcode;`| +|3.06 |`select count(*) as aantal_studenten_ABCT from student inner join opleiding on student.oplcode = opleiding.oplcode where academie = "ABCT";`| +|3.07 |`select distinct naam from student inner join belangstelling on student.studentnr = belangstelling.studentnr;`| +|3.08 |`select distinct actomschr from activiteit inner join belangstelling on activiteit.actcode = belangstelling.actcode;`| +|3.09 |`select distinct naam from student inner join inschrijving on inschrijving.studentnr = student.studentnr where inschrijving.betaald = 1 and inschrijving.planningsnr = 12;`| +|3.10 |`select planningsnr, actomschr, datum from planning inner join activiteit on activiteit.actcode = planning.actcode order by planningsnr;`| +|3.11 |`select planning.actcode, planning.datum, student.naam from inschrijving inner join planning on planning.planningsnr = inschrijving.planningsnr inner join student on student.studentnr = inschrijving.studentnr order by planning.actcode, planning.datum, student.naam;`| +|3.12 |`select opleiding.academie, count(inschrijving.betaald) as aantal_niet_betaald from student inner join opleiding on opleiding.oplcode = student.oplcode inner join inschrijving on inschrijving.studentnr = student.studentnr where inschrijving.betaald = 0 group by academie;`| +|3.13 |`select student.naam, sum(activiteit.prijs) as betaald from inschrijving inner join student on student.studentnr = inschrijving.studentnr inner join planning on planning.planningsnr = inschrijving.planningsnr inner join activiteit on activiteit.actcode = planning.actcode where inschrijving.betaald = 1 group by student.naam order by student.studentnr;`| +|3.14 |`select opleiding.academie, sum(activiteit.prijs) as nog_te_betalen from inschrijving inner join student on student.studentnr = inschrijving.studentnr inner join opleiding on opleiding.oplcode = student.oplcode inner join planning on planning.planningsnr = inschrijving.planningsnr inner join activiteit on activiteit.actcode = planning.actcode where inschrijving.betaald = 0 group by academie;`| +|3.15 |`select activiteit.actomschr, sum(activiteit.prijs) as openstaand_bedrag from inschrijving inner join planning on planning.planningsnr = inschrijving.planningsnr inner join activiteit on activiteit.actcode = planning.actcode where inschrijving.betaald = 0 group by actomschr order by openstaand_bedrag desc;`| +|3.16 |[open vraag](#316)| +|3.17 |[open vraag](#317)| +|4.01 |`select distinct student.naam from student left join belangstelling on belangstelling.studentnr = student.studentnr where belangstelling.studentnr is null;`| +|4.02 |`select distinct student.naam from belangstelling right join student on student.studentnr = belangstelling.studentnr where belangstelling.studentnr is null;`| +|4.03 |`select distinct actomschr from activiteit left join belangstelling on belangstelling.actcode = activiteit.actcode where studentnr is null;`| +|4.04 |`select omschrijving, count(studentnr) as aantal from opleiding left join student on student.oplcode = opleiding.oplcode group by omschrijving;`| +|4.05 |`select academie, count(belangstelling.studentnr) as aantal_inschrijvingen from opleiding left join student on student.oplcode = opleiding.oplcode left join belangstelling on belangstelling.studentnr = student.studentnr where opleiding.academie is not null group by academie;`| +|4.06 |`select * from inschrijving left join beschikbaar on beschikbaar.datum = inschrijving.datum union all select * from inschrijving right join beschikbaar on beschikbaar.datum = inschrijving.datum;`| +|4.07 |`select week(date("20120917"));`| +|4.08 |`select datediff(max(datum), min(datum)) as aantal_dagen_ertussen, max(datum) as maximum_datum, min(datum) as minimum_datum from planning;`| +|4.09 |`select to_char(now(), "DD-MM-YYYY") as NL_notatie;`| +|4.10 |`select * from stuk where genrenaam = "klassiek" or jaartal > 1980;`| +|4.11 |`select * from stuk where genrenaam = "pop" or (speelduur < 5 and niveaucode = "A");`| +|4.12 |`select * from stuk where niveaucode in("A", "B") and genrenaam != "pop" and speelduur <= 8;`| +|4.13 |`select stuknr, titel, genrenaam, niveaucode from stuk where (niveaucode in ("B", "C") or niveaucode is null) and genrenaam = "klassiek";`| +|4.14 |`select stuknr, titel, genrenaam, niveaucode from stuk where niveaucode is not null order by niveaucode desc, genrenaam;`| +|4.15 |`select count(distinct niveaucode) as aantal from stuk;`| +|4.16 |`select genrenaam, count(genrenaam) as aantal from stuk where niveaucode in("A", "B") group by genrenaam having aantal > 1;`| +|4.17 |`select genrenaam, count(genrenaam) as aantal from stuk where jaartal > 1900 group by genrenaam having aantal >= 3;`| +|4.18 |`select genrenaam, count(genrenaam) as aantal, avg(speelduur) as gemiddelde from stuk where niveaucode is not null group by genrenaam;`| +|4.19 |`select genrenaam, niveaucode, count(stuknr) as aantal from stuk where niveaucode in("A", "B") group by genrenaam, niveaucode having aantal > 1;`| +|4.20 |`select naam, titel from stuk inner join componist on stuk.componistId = componist.componistId;`| +|4.21 |`select stuknr, speelduur, omschrijving from stuk left join niveau on niveau.niveaucode = stuk.niveaucode where genrenaam = "klassiek";`| +|4.22 |`select componist.naam, muziekschool.plaatsnaam from componist left join muziekschool on muziekschool.schoolId = componist.schoolId where year(geboortedatum) >= 1900;`| +|4.23 |`select stuknr, titel, componist.naam, niveau.omschrijving from stuk left join componist on stuk.componistId = componist.componistId left join niveau on niveau.niveaucode = stuk.niveaucode where omschrijving is not null;`| +|4.24 |`select niveau.omschrijving, stuk.niveaucode, count(stuknr) from stuk left join niveau on niveau.niveaucode = stuk.niveaucode group by niveaucode having niveaucode is not null;`| +|4.25 |`select instrument.instrumentnaam, instrument.toonhoogte from instrument left join stukinstrument on (stukinstrument.instrumentnaam = instrument.instrumentnaam and stukinstrument.toonhoogte = instrument.toonhoogte) where stuknr is null;`| +|5.01 |`select distinct reis.reisnr, vertrekdatum, reisduur from reis left join bezoek on bezoek.reisnr = reis.reisnr where bezoek.verblijfsduur >= 1 and bezoek.objectnaam = "Mars" group by reisnr;`| +|5.02 |`select reis.reisnr, naam, geboortedatum, prijs from reis left join deelname on deelname.reisnr = reis.reisnr left join klant on klant.klantnr = deelname.klantnr where naam is not null;`| +|5.03 |`select hemelobject.objectnaam, count(bezoek.reisnr) as aantal_landingen from hemelobject left join bezoek on bezoek.objectnaam = hemelobject.objectnaam group by objectnaam order by objectnaam;`| +|5.04 |`select reisnr, count(distinct objectnaam) as aantal_verschillende_objecten from bezoek group by reisnr;`| +|5.05 |`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;`| +|5.06 |`select reisnr from bezoek group by reisnr having count(distinct objectnaam) = 1;`| +|5.07 |`select reis.reisnr, count(deelname.reisnr) from reis left join deelname on deelname.reisnr = reis.reisnr group by reis.reisnr order by reis.vertrekdatum;`| +|5.08 |`select reis.reisnr, vertrekdatum, reisduur, sum(verblijfsduur) as totale_verblijfsduur from bezoek left join reis on reis.reisnr = bezoek.reisnr group by reisnr having totale_verblijfsduur >= 14;`| +|5.09 |`select klant.klantnr, naam, count(reisnr) as aantal_reizen from deelname left join klant on klant.klantnr = deelname.klantnr group by klantnr having aantal_reizen >= 3;`| +|5.10 |`select reisnr, count(reisnr) as aantal_klanten from deelname left join klant on klant.klantnr = deelname.klantnr group by reisnr;`| +|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 + +``` +MariaDB [mysql]> select distinct * from opleiding; ++---------+--------------------------+----------+ +| oplcode | omschrijving | academie | ++---------+--------------------------+----------+ +| BK | Bouwkunde | ABCT | +| CT | Civiele Techniek | ABCT | +| ET | Electrotechniek | AII | +| IN | Informatica | AII | +| TBK | Technische Bedrijfskunde | AII | +| TI | Technische Informatica | AII | +| VS | Virtueel Studeren | NULL | +| WTB | Werktuigbouw | AII | ++---------+--------------------------+----------+ +8 rows in set (0.000 sec) + +MariaDB [mysql]> +``` + +Dit commando pakt de distincte resultaten uit de primary key kolom (oplcode), +en weergeeft ook alle andere kolommen voor elk resultaat. + +## 1.08 + +``` +MariaDB [mysql]> select distinct academie from opleiding; ++----------+ +| academie | ++----------+ +| ABCT | +| AII | +| NULL | ++----------+ +3 rows in set (0.000 sec) + +``` + +Dit commando laat alleen de distincte resultaten uit de `academie` kolom van de +`opleiding` tabel zien. + +## 1.09 + +``` +MariaDB [mysql]> select distinct academie,oplcode from opleiding; ++----------+---------+ +| academie | oplcode | ++----------+---------+ +| ABCT | BK | +| ABCT | CT | +| AII | ET | +| AII | IN | +| AII | TBK | +| AII | TI | +| NULL | VS | +| AII | WTB | ++----------+---------+ +8 rows in set (0.001 sec) + +MariaDB [mysql]> +``` + +Dit commando pakt de distincte resultaten uit de primary key kolom (oplcode), en +weergeeft ook de `academie` kolom voor elk resultaat. + +## 1.15 + +``` +MariaDB [mysql]> select * order by actcode from planning; +ERROR 1064 (42000): You have an error in your SQL syntax; +check the manual that corresponds to your MariaDB server version for the right +syntax to use near 'from planning' at line 1 +MariaDB [mysql]> +``` + +Dit commando geeft een foutmelding, maar werkt normaal wanneer het `order by +actcode` gedeelte na het `from planning` gedeelte komt. Dus zijn de volgorde +van SQL statements belangrijk. + +## 2.20 + +``` +MariaDB [mysql]> select count(*) from opleiding; ++----------+ +| count(*) | ++----------+ +| 8 | ++----------+ +1 row in set (0.000 sec) + +MariaDB [mysql]> select count(academie) from opleiding; ++-----------------+ +| count(academie) | ++-----------------+ +| 7 | ++-----------------+ +1 row in set (0.000 sec) + +MariaDB [mysql]> +``` + +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 + +``` +MariaDB [mysql]> select count(*) from student cross join opleiding; ++----------+ +| count(*) | ++----------+ +| 144 | ++----------+ +1 row in set (0.000 sec) + +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 + +`cross join` maakt elke combinatie van rijen. + +## 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; ++------------------------+------------------+ +| omschrijving | aantal_studenten | ++------------------------+------------------+ +| Informatica | 5 | +| Technische Informatica | 4 | ++------------------------+------------------+ +2 rows in set (0.000 sec) + +MariaDB [mysql]> +``` + +Deze query laat voor elke opleiding met meer dan 2 studenten de naam en het +studentenaantal zien. + +## 3.17 + +1. Tabel maken met omschrijving en unieke studentnummers + ``` + MariaDB [mysql]> select omschrijving, studentnr from student join opleiding on student.oplcode = opleiding.oplcode; + +--------------------------+-----------+ + | omschrijving | studentnr | + +--------------------------+-----------+ + | Bouwkunde | 16 | + | Civiele Techniek | 17 | + | Civiele Techniek | 18 | + | Electrotechniek | 5 | + | Electrotechniek | 9 | + | Informatica | 1 | + | Informatica | 2 | + | Informatica | 6 | + | Informatica | 7 | + | Informatica | 10 | + | Technische Bedrijfskunde | 14 | + | Technische Bedrijfskunde | 15 | + | Technische Informatica | 3 | + | Technische Informatica | 4 | + | Technische Informatica | 8 | + | Technische Informatica | 11 | + | Werktuigbouw | 12 | + | Werktuigbouw | 13 | + +--------------------------+-----------+ + 18 rows in set (0.000 sec) + ``` +2. Studentnummers tellen en groeperen per opleiding + ``` + MariaDB [mysql]> select omschrijving, count(studentnr) from student join opleiding on student.oplcode = opleiding.oplcode group by omschrijving; + +--------------------------+------------------+ + | omschrijving | count(studentnr) | + +--------------------------+------------------+ + | Bouwkunde | 1 | + | Civiele Techniek | 2 | + | Electrotechniek | 2 | + | Informatica | 5 | + | Technische Bedrijfskunde | 2 | + | Technische Informatica | 4 | + | Werktuigbouw | 2 | + +--------------------------+------------------+ + 7 rows in set (0.000 sec) + ``` +3. Verberg alle opleidingen met 2 of minder studenten + ``` + MariaDB [mysql]> select omschrijving, count(studentnr) from student join opleiding on student.oplcode = opleiding.oplcode group by omschrijving having count(studentnr) > 2; + +------------------------+------------------+ + | omschrijving | count(studentnr) | + +------------------------+------------------+ + | Informatica | 5 | + | Technische Informatica | 4 | + +------------------------+------------------+ + 2 rows in set (0.000 sec) + ``` +4. Kolomnamen en tabelaliasen toevoegen + ``` + 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; + +------------------------+------------------+ + | omschrijving | aantal_studenten | + +------------------------+------------------+ + | Informatica | 5 | + | Technische Informatica | 4 | + +------------------------+------------------+ + 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. + diff --git a/huiswerk/makefile b/huiswerk/makefile new file mode 100644 index 0000000..fe7229d --- /dev/null +++ b/huiswerk/makefile @@ -0,0 +1,6 @@ +TARGET = huiswerk +MD2LATEX = ../md2latex + +all: $(TARGET).pdf + +include ../pdf.mk diff --git a/pdf.mk b/pdf.mk index 83fc6d6..a8425d7 100644 --- a/pdf.mk +++ b/pdf.mk @@ -1,3 +1,4 @@ +RM = rm -f INKSCAPE = inkscape FIGS_SVG = $(wildcard *.svg) @@ -8,3 +9,6 @@ FIGS_PDF = $(patsubst %.svg,%.pdf, $(FIGS_SVG)) $(TARGET).pdf: $(TARGET).md $(FIGS_PDF) $(MD2LATEX) $(TARGET) + +clean:: + $(RM) $(TARGET).html $(TARGET).pdf $(TARGET).tex $(FIGS_PDF) -- cgit v1.2.3