1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
# 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;`|
## 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.
|