DATABANKEN 1
P1
KDG | 2021
,Inhoudsopgave
1. Inleiding databases en environment ........................................................................................................... 5
1.1 Wat is een informatiesysteem? ..................................................................................................................... 5
1.2 Database gericht benaderen ......................................................................................................................... 5
1.2.1 Voordelen ............................................................................................................................................... 5
1.3 Definitie databanken en DBMS...................................................................................................................... 7
1.4 Gebruikersprofielen ....................................................................................................................................... 7
1.5 Relationeel ..................................................................................................................................................... 8
1.5.1 Relationeel model .................................................................................................................................. 8
1.5.2 Relationele databank.............................................................................................................................. 8
1.5.3 Eigenschappen relationele tabel ............................................................................................................ 9
1.6 Sleutelattributen ............................................................................................................................................ 9
1.6.1 Primaire sleutel ...................................................................................................................................... 9
1.6.2 Alternatieve sleutel .............................................................................................................................. 10
1.6.3 Vreemde sleutel ................................................................................................................................... 10
1.6.4 Integriteitsregels relationeel model ..................................................................................................... 11
1.7 Bewerkingen met relaties ............................................................................................................................ 11
1.7.1 Unie ...................................................................................................................................................... 11
1.7.2 Intersectie............................................................................................................................................. 11
1.7.3 Verschil ................................................................................................................................................. 12
1.7.4 Product ................................................................................................................................................. 12
1.7.5 Projectie ............................................................................................................................................... 12
1.7.6 Selectie ................................................................................................................................................. 13
1.7.7 Join ....................................................................................................................................................... 13
1.8 Wat is SQL? .................................................................................................................................................. 14
1.8.1 Eigenschappen SQL............................................................................................................................... 14
1.9 Visuele voorstelling Databank - het ER-Diagram......................................................................................... 15
2. Select and fetch ........................................................................................................................................ 18
2.1 Inleiding ....................................................................................................................................................... 18
2.1.1 Volgorde uitvoeren............................................................................................................................... 19
2.2 FROM clausule ............................................................................................................................................. 19
2.3 SELECT clausule ........................................................................................................................................... 20
2.3.1 Aliassen................................................................................................................................................. 20
2.3.2 Rekenkundige bewerkingen ................................................................................................................. 21
2.3.3 Functie .................................................................................................................................................. 21
2.3.4 Distrinct ................................................................................................................................................ 21
2.4 WHERE conditie ........................................................................................................................................... 22
2.4.1 BETWEEN … AND .................................................................................................................................. 23
2.4.2 Hoofdlettergevoeligheid....................................................................................................................... 24
2.4.3 LIKE ....................................................................................................................................................... 25
1
, 2.4.4 AND, NOT, OR ....................................................................................................................................... 26
2.4.5 IS NULL en IS NOT NULL........................................................................................................................ 28
2.4.6 Quotes .................................................................................................................................................. 29
2.5 ORDER BY clausule....................................................................................................................................... 29
2.6 Pagination ................................................................................................................................................... 31
2.6.1 OFFSET .................................................................................................................................................. 32
2.6.2 WITH TIES ............................................................................................................................................. 33
3. Joins: het samenvoegen van tabellen ........................................................................................................ 34
3.1 Inner (equi) join ........................................................................................................................................... 34
3.1.1 2 Tabellen ............................................................................................................................................. 34
3.1.2 Meerdere tabellen................................................................................................................................ 36
3.1.3 Regels (inner) join ................................................................................................................................. 37
3.2 Natural join.................................................................................................................................................. 38
3.3 Non equi join................................................................................................................................................ 38
3.4 Auto join ...................................................................................................................................................... 39
3.4.1 Recursieve relationships ....................................................................................................................... 39
3.5 Outer join ..................................................................................................................................................... 45
3.5.1 LEFT [OUTER] JOIN................................................................................................................................ 46
3.5.2 RIGHT [OUTER] JOIN ............................................................................................................................. 46
3.5.3 FULL OUTER JOIN.................................................................................................................................. 47
4. Numerieke en datum functies ................................................................................................................... 49
4.1 Datums en getallen omzetten van en naar tekst......................................................................................... 49
4.1.1 Datumfuncties: formaat ....................................................................................................................... 49
4.1.2 Conversiefuncties: to_char ................................................................................................................... 50
4.1.3 Taal instellen......................................................................................................................................... 50
4.1.4 Omzetten van numeriek naar tekst ..................................................................................................... 50
4.1.5 Conversiefuncties: to_date.................................................................................................................. 51
4.1.6 Conversiefuncties: to_number ............................................................................................................. 52
4.2 Numerieke functies ...................................................................................................................................... 52
4.2.1 Round ................................................................................................................................................... 52
4.2.2 Trunc..................................................................................................................................................... 53
4.2.3 Mod ...................................................................................................................................................... 53
4.3 Datum functies ........................................................................................................................................... 54
4.3.1 Huidige datum en tijd ........................................................................................................................... 54
4.3.2 Date_part ............................................................................................................................................. 54
4.3.3 Leeftijd ................................................................................................................................................. 55
4.3.4 Rekenen met datum/tijd ...................................................................................................................... 56
5. Analytische functies ................................................................................................................................. 57
5.1 AVG (ALL DISTINCT expressie) ..................................................................................................................... 57
5.2 SUM (ALL DISTINCT expressie) ..................................................................................................................... 58
2
, 5.3 MIN / MAX (expressie) ................................................................................................................................. 58
5.4 COUNT (*) & COUNT (ALL DISTINCT expressie) ........................................................................................... 58
6. Group by ................................................................................................................................................... 59
6.1 Nesten van analytische functies .................................................................................................................. 61
7. Having....................................................................................................................................................... 63
8. Set operatoren .......................................................................................................................................... 64
8.1 UNION ......................................................................................................................................................... 64
8.1.1 Opgelet! ................................................................................................................................................ 65
8.2 INTERSECT ................................................................................................................................................... 65
8.3 EXEPT ........................................................................................................................................................... 66
8.3.1 Opmerkingen ........................................................................................................................................ 66
8.3.2 Eigenschappen...................................................................................................................................... 67
9. Tekstfuncties............................................................................................................................................. 67
9.1 UPPER, LOWER, INITCAP ............................................................................................................................. 67
9.2 LENGTH ........................................................................................................................................................ 68
9.3 Substr(ing) ................................................................................................................................................... 68
9.4 POSITION ..................................................................................................................................................... 69
9.5 CONCAT ....................................................................................................................................................... 70
9.5.1 Concat_ws ............................................................................................................................................ 70
9.6 LPAD/RPAD .................................................................................................................................................. 71
9.7 TRIM ............................................................................................................................................................ 72
9.8 REPLACE....................................................................................................................................................... 72
10. Conditionele functies en expressies......................................................................................................... 74
10.1 GREATEST & LEAST .................................................................................................................................... 74
10.2 CASE........................................................................................................................................................... 74
10.3 COALESCE .................................................................................................................................................. 76
10.4 NULLIF ....................................................................................................................................................... 77
3
P1
KDG | 2021
,Inhoudsopgave
1. Inleiding databases en environment ........................................................................................................... 5
1.1 Wat is een informatiesysteem? ..................................................................................................................... 5
1.2 Database gericht benaderen ......................................................................................................................... 5
1.2.1 Voordelen ............................................................................................................................................... 5
1.3 Definitie databanken en DBMS...................................................................................................................... 7
1.4 Gebruikersprofielen ....................................................................................................................................... 7
1.5 Relationeel ..................................................................................................................................................... 8
1.5.1 Relationeel model .................................................................................................................................. 8
1.5.2 Relationele databank.............................................................................................................................. 8
1.5.3 Eigenschappen relationele tabel ............................................................................................................ 9
1.6 Sleutelattributen ............................................................................................................................................ 9
1.6.1 Primaire sleutel ...................................................................................................................................... 9
1.6.2 Alternatieve sleutel .............................................................................................................................. 10
1.6.3 Vreemde sleutel ................................................................................................................................... 10
1.6.4 Integriteitsregels relationeel model ..................................................................................................... 11
1.7 Bewerkingen met relaties ............................................................................................................................ 11
1.7.1 Unie ...................................................................................................................................................... 11
1.7.2 Intersectie............................................................................................................................................. 11
1.7.3 Verschil ................................................................................................................................................. 12
1.7.4 Product ................................................................................................................................................. 12
1.7.5 Projectie ............................................................................................................................................... 12
1.7.6 Selectie ................................................................................................................................................. 13
1.7.7 Join ....................................................................................................................................................... 13
1.8 Wat is SQL? .................................................................................................................................................. 14
1.8.1 Eigenschappen SQL............................................................................................................................... 14
1.9 Visuele voorstelling Databank - het ER-Diagram......................................................................................... 15
2. Select and fetch ........................................................................................................................................ 18
2.1 Inleiding ....................................................................................................................................................... 18
2.1.1 Volgorde uitvoeren............................................................................................................................... 19
2.2 FROM clausule ............................................................................................................................................. 19
2.3 SELECT clausule ........................................................................................................................................... 20
2.3.1 Aliassen................................................................................................................................................. 20
2.3.2 Rekenkundige bewerkingen ................................................................................................................. 21
2.3.3 Functie .................................................................................................................................................. 21
2.3.4 Distrinct ................................................................................................................................................ 21
2.4 WHERE conditie ........................................................................................................................................... 22
2.4.1 BETWEEN … AND .................................................................................................................................. 23
2.4.2 Hoofdlettergevoeligheid....................................................................................................................... 24
2.4.3 LIKE ....................................................................................................................................................... 25
1
, 2.4.4 AND, NOT, OR ....................................................................................................................................... 26
2.4.5 IS NULL en IS NOT NULL........................................................................................................................ 28
2.4.6 Quotes .................................................................................................................................................. 29
2.5 ORDER BY clausule....................................................................................................................................... 29
2.6 Pagination ................................................................................................................................................... 31
2.6.1 OFFSET .................................................................................................................................................. 32
2.6.2 WITH TIES ............................................................................................................................................. 33
3. Joins: het samenvoegen van tabellen ........................................................................................................ 34
3.1 Inner (equi) join ........................................................................................................................................... 34
3.1.1 2 Tabellen ............................................................................................................................................. 34
3.1.2 Meerdere tabellen................................................................................................................................ 36
3.1.3 Regels (inner) join ................................................................................................................................. 37
3.2 Natural join.................................................................................................................................................. 38
3.3 Non equi join................................................................................................................................................ 38
3.4 Auto join ...................................................................................................................................................... 39
3.4.1 Recursieve relationships ....................................................................................................................... 39
3.5 Outer join ..................................................................................................................................................... 45
3.5.1 LEFT [OUTER] JOIN................................................................................................................................ 46
3.5.2 RIGHT [OUTER] JOIN ............................................................................................................................. 46
3.5.3 FULL OUTER JOIN.................................................................................................................................. 47
4. Numerieke en datum functies ................................................................................................................... 49
4.1 Datums en getallen omzetten van en naar tekst......................................................................................... 49
4.1.1 Datumfuncties: formaat ....................................................................................................................... 49
4.1.2 Conversiefuncties: to_char ................................................................................................................... 50
4.1.3 Taal instellen......................................................................................................................................... 50
4.1.4 Omzetten van numeriek naar tekst ..................................................................................................... 50
4.1.5 Conversiefuncties: to_date.................................................................................................................. 51
4.1.6 Conversiefuncties: to_number ............................................................................................................. 52
4.2 Numerieke functies ...................................................................................................................................... 52
4.2.1 Round ................................................................................................................................................... 52
4.2.2 Trunc..................................................................................................................................................... 53
4.2.3 Mod ...................................................................................................................................................... 53
4.3 Datum functies ........................................................................................................................................... 54
4.3.1 Huidige datum en tijd ........................................................................................................................... 54
4.3.2 Date_part ............................................................................................................................................. 54
4.3.3 Leeftijd ................................................................................................................................................. 55
4.3.4 Rekenen met datum/tijd ...................................................................................................................... 56
5. Analytische functies ................................................................................................................................. 57
5.1 AVG (ALL DISTINCT expressie) ..................................................................................................................... 57
5.2 SUM (ALL DISTINCT expressie) ..................................................................................................................... 58
2
, 5.3 MIN / MAX (expressie) ................................................................................................................................. 58
5.4 COUNT (*) & COUNT (ALL DISTINCT expressie) ........................................................................................... 58
6. Group by ................................................................................................................................................... 59
6.1 Nesten van analytische functies .................................................................................................................. 61
7. Having....................................................................................................................................................... 63
8. Set operatoren .......................................................................................................................................... 64
8.1 UNION ......................................................................................................................................................... 64
8.1.1 Opgelet! ................................................................................................................................................ 65
8.2 INTERSECT ................................................................................................................................................... 65
8.3 EXEPT ........................................................................................................................................................... 66
8.3.1 Opmerkingen ........................................................................................................................................ 66
8.3.2 Eigenschappen...................................................................................................................................... 67
9. Tekstfuncties............................................................................................................................................. 67
9.1 UPPER, LOWER, INITCAP ............................................................................................................................. 67
9.2 LENGTH ........................................................................................................................................................ 68
9.3 Substr(ing) ................................................................................................................................................... 68
9.4 POSITION ..................................................................................................................................................... 69
9.5 CONCAT ....................................................................................................................................................... 70
9.5.1 Concat_ws ............................................................................................................................................ 70
9.6 LPAD/RPAD .................................................................................................................................................. 71
9.7 TRIM ............................................................................................................................................................ 72
9.8 REPLACE....................................................................................................................................................... 72
10. Conditionele functies en expressies......................................................................................................... 74
10.1 GREATEST & LEAST .................................................................................................................................... 74
10.2 CASE........................................................................................................................................................... 74
10.3 COALESCE .................................................................................................................................................. 76
10.4 NULLIF ....................................................................................................................................................... 77
3
