DATABANKEN P2
KDG | 2021-22
,Inhoudsopgave
1. DDL intro................................................................................................................................ 6
2. Creatie van een tabel............................................................................................................. 6
2.1 Introductie ........................................................................................................................... 6
2.2 Syntax .................................................................................................................................. 7
2.3 Tabelnaam ........................................................................................................................... 8
2.4 Attribuutnaam ..................................................................................................................... 8
2.5 Gegevenstype ...................................................................................................................... 8
2.5.1 Char & Varchar ................................................................................................................. 9
2.5.2 Numeric .......................................................................................................................... 10
2.5.3 Datumattributen............................................................................................................. 11
2.6 Default waarden ................................................................................................................ 14
2.7 Constraints......................................................................................................................... 15
2.7.1 Column constraints ......................................................................................................... 15
2.7.2 Table constraints ............................................................................................................ 19
2.7.3 Opmerkingen .................................................................................................................. 24
3. Data manipulation language (DML) ..................................................................................... 25
3.1 Insert.................................................................................................................................. 25
3.1.1 Scenario a: alle attributen krijgen een waarde toegewezen .......................................... 26
3.1.2 Scenario b: niet alle attributen krijgen een waarde toegewezen ................................... 26
3.2 Update ............................................................................................................................... 27
3.2.1 Met 2 tabellen ................................................................................................................ 28
3.2.2 Met 3 tabellen ................................................................................................................ 28
3.3 Delete ................................................................................................................................ 28
3.3.1 DML met constraints ...................................................................................................... 29
1
,4. Data definition language (DDL)............................................................................................ 31
4.1 Truncate ............................................................................................................................ 31
4.2 Drop ................................................................................................................................... 32
4.3 Alter ................................................................................................................................... 33
4.3.1 Add column .................................................................................................................... 33
4.3.2 Add constraint ................................................................................................................ 34
4.3.3 Drop column ................................................................................................................... 34
4.3.4 Drop constraint ............................................................................................................... 34
4.3.5 Alter column ................................................................................................................... 36
4.3.6 Validate constraint ......................................................................................................... 38
4.3.7 Rename........................................................................................................................... 38
Schema DML DDL .................................................................................................................... 39
4.4 Sequence ........................................................................................................................... 41
4.4.1 Alter sequence ................................................................................................................ 43
4.4.2 Drop sequence................................................................................................................ 43
4.4.3 Create sequence ............................................................................................................. 44
4.5 Identity Columns ............................................................................................................... 46
4.5.1 Create table – generated ................................................................................................ 47
4.5.2 Alter table - alter column – add generated .................................................................... 48
4.5.3 Alter table - alter column – set generated ..................................................................... 49
4.5.4 Alter table - alter column – drop identity ....................................................................... 49
5. Subqueries ........................................................................................................................... 50
5.1 Definitie ............................................................................................................................. 51
5.2 Subqueries die exact 1 rij opleveren ................................................................................. 52
5.3 Subqueries die meerdere rijen opleveren ......................................................................... 53
2
, 6. Views ................................................................................................................................... 57
6.1 Wat is een view?................................................................................................................ 57
6.2 Kenmerken ........................................................................................................................ 58
6.3 Via views rijen invoegen, wijzigen, verwijderen ................................................................ 58
6.3.1 Insert............................................................................................................................... 59
6.3.2 Delete ............................................................................................................................. 59
6.4 With check ......................................................................................................................... 60
6.5 Functies bij views - samengestelde views ......................................................................... 61
6.6 Beperkingen....................................................................................................................... 61
6.6.1 Key preserved table ........................................................................................................ 62
6.7 View verwijderen ............................................................................................................... 63
6.8 Waarom views? ................................................................................................................. 63
6.9 Dictionaire tabellen ........................................................................................................... 64
7. Indexen ................................................................................................................................ 65
7.1 Inleiding ............................................................................................................................. 65
7.2 Hoe werkt een index? ........................................................................................................ 67
7.2.1 B-tree .............................................................................................................................. 68
7.3 Index aanmaken: syntax .................................................................................................... 70
7.4 Analyse van een query....................................................................................................... 71
7.5 Best practices .................................................................................................................... 72
7.6 Verwijderen van een index ................................................................................................ 73
7.7 Data dictionary .................................................................................................................. 73
8. Correlated Subqueries ......................................................................................................... 74
8.1 Oplossing 1: gewone subquery:......................................................................................... 74
8.2 Oplossing 2: correlated subqueries: .................................................................................. 75
3
KDG | 2021-22
,Inhoudsopgave
1. DDL intro................................................................................................................................ 6
2. Creatie van een tabel............................................................................................................. 6
2.1 Introductie ........................................................................................................................... 6
2.2 Syntax .................................................................................................................................. 7
2.3 Tabelnaam ........................................................................................................................... 8
2.4 Attribuutnaam ..................................................................................................................... 8
2.5 Gegevenstype ...................................................................................................................... 8
2.5.1 Char & Varchar ................................................................................................................. 9
2.5.2 Numeric .......................................................................................................................... 10
2.5.3 Datumattributen............................................................................................................. 11
2.6 Default waarden ................................................................................................................ 14
2.7 Constraints......................................................................................................................... 15
2.7.1 Column constraints ......................................................................................................... 15
2.7.2 Table constraints ............................................................................................................ 19
2.7.3 Opmerkingen .................................................................................................................. 24
3. Data manipulation language (DML) ..................................................................................... 25
3.1 Insert.................................................................................................................................. 25
3.1.1 Scenario a: alle attributen krijgen een waarde toegewezen .......................................... 26
3.1.2 Scenario b: niet alle attributen krijgen een waarde toegewezen ................................... 26
3.2 Update ............................................................................................................................... 27
3.2.1 Met 2 tabellen ................................................................................................................ 28
3.2.2 Met 3 tabellen ................................................................................................................ 28
3.3 Delete ................................................................................................................................ 28
3.3.1 DML met constraints ...................................................................................................... 29
1
,4. Data definition language (DDL)............................................................................................ 31
4.1 Truncate ............................................................................................................................ 31
4.2 Drop ................................................................................................................................... 32
4.3 Alter ................................................................................................................................... 33
4.3.1 Add column .................................................................................................................... 33
4.3.2 Add constraint ................................................................................................................ 34
4.3.3 Drop column ................................................................................................................... 34
4.3.4 Drop constraint ............................................................................................................... 34
4.3.5 Alter column ................................................................................................................... 36
4.3.6 Validate constraint ......................................................................................................... 38
4.3.7 Rename........................................................................................................................... 38
Schema DML DDL .................................................................................................................... 39
4.4 Sequence ........................................................................................................................... 41
4.4.1 Alter sequence ................................................................................................................ 43
4.4.2 Drop sequence................................................................................................................ 43
4.4.3 Create sequence ............................................................................................................. 44
4.5 Identity Columns ............................................................................................................... 46
4.5.1 Create table – generated ................................................................................................ 47
4.5.2 Alter table - alter column – add generated .................................................................... 48
4.5.3 Alter table - alter column – set generated ..................................................................... 49
4.5.4 Alter table - alter column – drop identity ....................................................................... 49
5. Subqueries ........................................................................................................................... 50
5.1 Definitie ............................................................................................................................. 51
5.2 Subqueries die exact 1 rij opleveren ................................................................................. 52
5.3 Subqueries die meerdere rijen opleveren ......................................................................... 53
2
, 6. Views ................................................................................................................................... 57
6.1 Wat is een view?................................................................................................................ 57
6.2 Kenmerken ........................................................................................................................ 58
6.3 Via views rijen invoegen, wijzigen, verwijderen ................................................................ 58
6.3.1 Insert............................................................................................................................... 59
6.3.2 Delete ............................................................................................................................. 59
6.4 With check ......................................................................................................................... 60
6.5 Functies bij views - samengestelde views ......................................................................... 61
6.6 Beperkingen....................................................................................................................... 61
6.6.1 Key preserved table ........................................................................................................ 62
6.7 View verwijderen ............................................................................................................... 63
6.8 Waarom views? ................................................................................................................. 63
6.9 Dictionaire tabellen ........................................................................................................... 64
7. Indexen ................................................................................................................................ 65
7.1 Inleiding ............................................................................................................................. 65
7.2 Hoe werkt een index? ........................................................................................................ 67
7.2.1 B-tree .............................................................................................................................. 68
7.3 Index aanmaken: syntax .................................................................................................... 70
7.4 Analyse van een query....................................................................................................... 71
7.5 Best practices .................................................................................................................... 72
7.6 Verwijderen van een index ................................................................................................ 73
7.7 Data dictionary .................................................................................................................. 73
8. Correlated Subqueries ......................................................................................................... 74
8.1 Oplossing 1: gewone subquery:......................................................................................... 74
8.2 Oplossing 2: correlated subqueries: .................................................................................. 75
3
