1 2021-10-05

1.1 SQL

SQL staat voor Structured Query Language, en het is een veelgebruikte taal om met databases te spreken. Je kan deze taal dan ook gebruiken om met vele databases te werken zoals b.v.:

  • PostgreSQL: populaire open source database

  • MySQL/MariaDB: populaire open source database

  • SQLite: populaire embedded database

  • IBM Db2

  • Oracle database

1.2 SQLite

Omwille van de eenvoud, gebruiken we hier SQLite.

Download SQLite hier: https://www.sqlite.org/download.html

De directe link: https://www.sqlite.org/2021/sqlite-tools-win32-x86-3360000.zip

Start de commandline shell “sqlite3.exe”.

1.3 SQL - create database

Start de sqlite3 executable:

sqlite3

Je krijgt nu een prompt:

sqlite>

Je kan nu een database aanmaken met het ‘.open’ commando:

sqlite> .open moviedb.sqlite3

1.4 SQL - create table

We kunnen een database maken door een parameter mee te geven op de commandline:

sqlite> create table movie (name varchar(100) primary key, year int);

“Create table” geeft aan dat je een nieuwe tabel wil aanmaken. “Movie” is de naam van deze nieuwe tabel. Het lijstje tussen de haakjes geeft de kolommen in deze tabel aan. De eerste kolom heet ‘name’ en bevat maximaal 100 letters. De tweede kolom heet ‘year’ en bevat een geheel getal. “Primary key” geeft aan dat dit de uniek identificerende kolom is. Er kunnen met deze tabel-structuur geen twee films met dezelfde naam opgeslagen worden.

1.4.1 Opgave

Maak een database aan met naam ‘seriesdb.sqlite3’. Maak hierin een tabel met naam ‘series’ met drie kolommen:

  • name: naam van de serie, maximaal 100 letters

  • year: jaartal, integer

  • season: seizoen, integer

1.4.2 Opgave

Gebruik het ‘.help’ commando om een overzicht van de beschikbare commando’s te krijgen.

1.4.3 Opgave

Toon de tabellen met het “.tables” commando.

1.5 SQL - insert rows

We kunnen rijen aan de tabel toevoegen met het ‘INSERT’ statement:

sqlite> insert into movie (name, year) values('The Matrix', 1999);

Als alle kolommen ingevuld worden mag dit ook verkort worden tot:

sqlite> insert into movie values('The Matrix Reloaded', 2003);

Hier voegden we twee films toe aan de tabel ‘movie’.

1.5.1 Opgave

Open je database met naam ‘seriesdb.sqlite3’. Voeg 3 series toe met behulp van het ‘insert’ statement:

  • Fargo, 2014, 1

  • Peaky Blinders, 2013, 1

  • Misfits, 2009, 1

1.6 SQL - list rows

Met het ‘select’ statement kunnen we rijen in de tabel tonen:

sqlite> select * from movie;
The Matrix|1999
The Matrix Reloaded|2003

Het sterretje geeft aan dat we alle kolommen willen zien. We kunnen ook expliciet opgeven welke kolommen we willen zien:

sqlite> select name from movie;
The Matrix
The Matrix Reloaded
sqlite> select year from movie;
1999
2003

1.6.1 Opgave

Open je database met naam ‘seriesdb.sqlite3’. Toon alle series in je tabel ‘series’ met behulp van het ‘select’ statement.

1.7 SQL - primary key uniqueness

We proberen nu de twee Dune films toe te voegen, maar dit faalt:

sqlite> insert into movie values('Dune', 1984);
sqlite> insert into movie values('Dune', 2021);
Error: UNIQUE constraint failed: movie.name

De reden hiervoor is dat we de kolom ‘name’ als primary key instelden bij het aanmaken van de tabel. Deze primary key dient uniek te zijn.

1.7.1 Opgave

Open je database met naam ‘seriesdb.sqlite3’. Voeg het tweede seizoen van ‘Fargo’ toe, met release jaar 2015.

Merk op dat dit niet lukt.

1.8 SQL - ID kolom

Om dit probleem te kunnen omzeilen, kunnen we een andere kolom toevoegen die we als uniek beschouwen (‘primary key’), waardoor de kolom ‘name’ niet meer uniek hoeft te zijn:

sqlite3 moviedb2.sqlite3
sqlite> create table movie (id integer primary key, name varchar(100), year int);
sqlite> insert into movie values(1, 'The Matrix', 1999);
sqlite> insert into movie values(2, 'The Matrix Reloaded', 2003);
sqlite> insert into movie values(3, 'Dune', 1984);
sqlite> insert into movie values(4, 'Dune', 2021);

1.8.1 Opgave

Maak een nieuwe database aan met naam ‘series2db.sqlite3’. Maak hierin een tabel met naam ‘series’ met drie kolommen:

  • id: integer, primary key

  • name: naam van de serie, maximaal 100 letters

  • year: jaartal, integer

  • season: seizoen, integer

Voeg nu het eerste en het tweede seizoen van de serie ‘Fargo’ toe.

1.9 SQL - Autoincrement

Als er geen waarde voor de ID opgegeven wordt, maakt SQLite3 automatisch een nieuwe ID aan:

sqlite3 moviedb2.sqlite3
sqlite> insert into movie (name, year) values('Fargo', 1996);
sqlite> insert into movie (name, year) values('Pulp Fiction', 1994);

1.9.1 Opgave

Open je database met naam ‘series2db.sqlite3’. Voeg nu de volgende series toe, gebruik makend van de verkorte notatie:

  • Peaky Blinders, 2013, 1

  • Misfits, 2019, 1

1.10 SQL - Update

Met behulp van het “update” statement kunnen we bestaanden rijen in de tabel aanpassen:

sqlite> select * from movie2;
1|The Matrix|1999
2|The Matrix Reloaded|2003
3|Dune|1984
4|Dune|2021
5|Fargo|1996
6|Pulp Fiction|1994

sqlite> update movie set year=2021 where id=2;

Dit zou het jaartal van de film met ID 2 op 2021 instellen:

sqlite> select * from movie2;
1|The Matrix|1999
2|The Matrix Reloaded|2021
3|Dune|1984
4|Dune|2021
5|Fargo|1996
6|Pulp Fiction|1994


sqlite> update movie set year=2021;

Dit zou het jaartal van alle films op 2021 instellen:

sqlite> select * from movie2;
1|The Matrix|2021
2|The Matrix Reloaded|2021
3|Dune|2021
4|Dune|2021
5|Fargo|2021
6|Pulp Fiction|2021

1.10.1 Opgave

Open je database met naam ‘series2db.sqlite3’. Corrigeer het jaartal van de serie Misfits aan naar 2009.

1.11 SQL - Delete

Met het ‘DELETE’ statement kunnen rijen uit een tabel verwijderd worden:

sqlite> delete from movie2 where id > 2;
sqlite> select * from movie2;
1|The Matrix|2021
2|The Matrix Reloaded|2021

1.11.1 Opgave

Open je database met naam ‘series2db.sqlite3’. Verwijder “Peaky Blinders’ uit de tabel series.

1.12 Django dbshell

Kopieer de sqlite3.exe executable naar de map waarin je manage.py zich bevindt.

Voer vervolgens het volgende commando uit:

python manage.py dbshell

Je krijgt nu een database-shell te zien waarin je SQL commando’s kan gebruiken om de data te bekijken of aan te passen.

1.12.1 Opgave

Gebruik het “.tables” commando om te zien welke tabellen beschikbaar zijn.

1.12.2 Opgave

Gebruik het “select” statement om alle tweets in je database te bekijken.

1.12.3 Opgave

Gebruik het “update” statement om het aantal likes van je eerste tweet in te stellen op 10000.