path: root/huiswerk.md

# sql huiswerk

|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;`|

## 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)
   ```