sqlite-3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Over deze opdrachten\n",
    "\n",
    "* dit is Jupyter Notebook `sqlite-3.ipynb` - CRUD: create, read, update, delete-opdrachten.\n",
    "* voor een inleiding over het gebruik van Jupyter Notebooks: [Inleiding Jupyter Notebook](Inleiding-Jupyter.ipynb)\n",
    "* de hele reeks SQLite opdrachten:\n",
    "    * [SQLite 0 - init database](sqlite-0.ipynb) (om met een schone lei te beginnnen)\n",
    "    * [SQLite 1 - selectie en projectie](sqlite-1.ipynb)    \n",
    "    * [SQLite 2 - joins](sqlite-2.ipynb)\n",
    "    * [SQLite 3 - CRUD](sqlite-3.ipynb)\n",
    "    * [SQLite 4 - Schema](sqlite-4.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CRUD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Met de afkorting CRUD geven we de basisoperaties op een database aan: Create, Read, Update, en Delete.\n",
    "Hieronder geven we de SQL-opdrachten die daarmee overeenkomen. We geven daarbij ook aan hoe deze samenhangen met eventuele constraints van de database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create (INSERT)\n",
    "\n",
    "Met de INSERT opdracht voegen we een nieuwe rij aan een tabel toe (create).\n",
    "Ter controle vragen we de tabel voor en na de INSERT op."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "SELECT *\n",
    "FROM leden;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "INSERT INTO leden (voornaam, achternaam, email) \n",
    "VALUES ('Erica', 'Bezos', 'erica@amazon.com');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Opmerking.** We geven het lidnr (de primary key) hier niet op: dit nummer wordt automatisch door SQLite ingevuld,\n",
    "als uniek volgnr. \n",
    "\n",
    "Controleer of inderdaad het nieuwe lid toegevoegd is (met een uniek `lidnr`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "SELECT *\n",
    "FROM leden;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Constraint error(s) bij INSERT**. Nogmaals uitvoeren van de INSERT-opdracht geeft een foutmelding (bovenaan): *UNIQUE constraint failed: leden.email*.\n",
    "We hebben immers aangegeven dat het `mail`-adres uniek moet zijn (constraint)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "INSERT INTO leden (voornaam, achternaam, email) \n",
    "VALUES ('Erica', 'Bezos', 'erica@amazon.com');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Opdracht** voor de INSERT-opdracht nogmaals uit, met dezelfde namen, en een *ander emailadres*. Wat is het resultaat?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "SELECT *\n",
    "FROM leden;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read (SELECT)\n",
    "\n",
    "Met de SELECT-opdracht zoals we hierboven gebruikt hebben kun je de data uit de database lezen.\n",
    "Het resultaat van SELECT is altijd een tabel; in sommige gevallen bestaat deze uit 1 rij, als we een bestaande rij willen lezen. Soms is de tabel leeg: er zijn dan geen rijen gevonden die aan de voorwaarde voldoen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "SELECT lid.lidnr, lid.voornaam, lid.achternaam, lid.email\n",
    "FROM leden lid\n",
    "WHERE lid.voornaam='Jantien';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Opdracht** Pas bovenstaande zoekopdracht aan zodat deze 0 rijen oplevert; en zodat deze meerdere rijen oplevert."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Update (UPDATE)\n",
    "\n",
    "Met de UPDATE opdracht kun je een bestaande rij selectief veranderen.\n",
    "In het onderstaande voorbeeld passen we de voornaam aan:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "UPDATE leden\n",
    "SET voornaam = \"Janny\"\n",
    "WHERE voornaam = \"Jantien\";\n",
    "\n",
    "SELECT * from LEDEN;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Ga na dat herhalen van deze opdracht hetzelfde resultaat oplevert.\n",
    "* Ga na dat een update voor een lege selectie geen foutmelding oplevert.\n",
    "* Ga na of je in een UPDATE opdracht meerdere rijen kunt veranderen. Kun je bijvoorbeeld alle \"Marie\"-s veranderen in \"Maria\"-s?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete (DELETE)\n",
    "\n",
    "Met de DELETE opdracht verwijder je een rij uit de database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "DELETE FROM leden\n",
    "WHERE voornaam=\"Erica\";\n",
    "\n",
    "SELECT * FROM leden;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Opdracht**. Ga na dat herhalen van de bovenstaande opdracht geen problemen oplevert: het verwijderen van een niet-bestaand element slaagt \"per definitie\". DELETE is een *idempotente* opdracht: deze heeft dezelfde betekenis als je deze eenmaal of vaker uitvoert."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Herstel de database\n",
    "\n",
    "Met de bovenstaande opdrachten heb je de leden-tabel mogelijk behoorlijk verziekt.\n",
    "Met de onderstaande opdracht herstel je de oorspronkelijke tabel weer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "DELETE FROM leden\n",
    "WHERE TRUE;\n",
    "\n",
    ".mode csv\n",
    ".import leden.csv leden\n",
    "\n",
    "SELECT * FROM leden;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Opmerking.** We kunnen de tabel ook helemaal verwijderen, met `DROP TABLE`. In dat geval moeten we ook de tabel opnieuw aanmaken (`CREATE TABLE`), met het schema (kolomnamen, kolomtypes, key, constraints, enz.)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}