Samenvatting Databanken 1 - theorie periode 2

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