602_db_sqlite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Datenbanken mit SQLite\n",
    "\n",
    "[SQLite-Docu](https://www.sqlite.org/lang.html) <br>\n",
    "Web-Client: [Sqlite Browser](https://sqliteonline.com/)    <br>\n",
    "Lokaler-Client: [DB Browser for SQLite](https://sqlitebrowser.org/) <br>\n",
    "Beispieldatenbank: [Sportverein](sportverein.py)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tabelle anlegen "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 as sq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Eine Verbindung zur Datenbank aufbauen. \n",
    "# Die Datei wird neu angelegt, falls sie noch nicht existiert\n",
    "connection = sq.connect(\"verwaltung.db\")       \n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Eine Datenbank anlegen, ohne sie abzuspeichern\n",
    "connection = sq.connect(\":memory:\")       \n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Um mit einer verbunden Datenbank zu arbeiten, wird ein Cursor benötigt. Den kann man sich als einen Zeiger auf die aktuelle Bearbeitungsposition vorstellen. Es kann mehrere Cursor geben.\n",
    "\n",
    "SQL-Anweisungen sind nicht case-sensitiv. Es ist aber üblich, SQL Schlüsselworte mit Großbuchstaben zu schreiben.\n",
    "\n",
    "Beim Anlegen einer Tabelle muss der Datentyp der festgelegt werden.\n",
    "\n",
    "Python - Sqlite3\n",
    "* None - NULL\n",
    "* int - INTEGER\n",
    "* float - REAL\n",
    "* str - TEXT\n",
    "* bytes - BLOB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tabelle in einer Datenbank anlegen\n",
    "connection = sq.connect(\"univerwaltung.db\")  \n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"CREATE TABLE student (name TEXT, ort TEXT, fach TEXT, semester INTEGER)\")\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Beim create können weitere Eigenschaften der Felder definiert werden.\n",
    "CREATE TABLE mitglied (mnr INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, beitrag REAL NOT NULL DEFAULT '0.00') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Daten in eine Tabelle einfügen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Daten in eine Tabelle einfügen\n",
    "connection = sq.connect(\"univerwaltung.db\")  \n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"INSERT INTO student VALUES ('Malte', 'Münster', 'Philosophie', 12)\")\n",
    "connection.commit()    # erst durch commit werden die Daten gespeichert\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Transaktionen, Commit, Rollback\n",
    "\n",
    "**Transaktionen** sind Ketten von Operationen, die vollständig ausgeführt werden müssen, damit\n",
    "die Konsistenz der Datenbank erhalten bleibt. Um die Datenbank transaktionssicher zu machen, werden kritische Anweisungen\n",
    "mit einer try-except-Anweisung umgeben. Der **rollback** macht alles bis zum letzten **commit** rückgängig."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transaktionssicherheit\n",
    "connection = sq.connect(\"univerwaltung.db\")\n",
    "try:\n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(\"INSERT INTO student VALUES ('Malte', 'Münster', 'Philosophie', 12)\")\n",
    "    # Hier können weitere Datenbankmanipulationen stehen\n",
    "    connection.commit()\n",
    "except:\n",
    "    print(\"Ein Problem trat auf -> Rollback\")\n",
    "    connection.rollback()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Daten einfügen mit Fragezeichen-Parameter\n",
    "\n",
    "Häufig ist es einfacher, die einzufügenden Daten unabhängig vom SQL-Statement zu generieren."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sq.connect(\"univerwaltung.db\")  \n",
    "cursor = connection.cursor()\n",
    "\n",
    "daten = (\"Malte\", \"Münster\", \"Philosophie\", 12)\n",
    "sql = \"INSERT INTO student VALUES (?,?,?,?)\"    \n",
    "cursor.execute(sql,daten)\n",
    "\n",
    "connection.commit()     \n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Wir erstellen die Tabelle student neu:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = \\\n",
    "((\"Malte\", \"Münster\", \"Philosophie\", 12),\n",
    "(\"Malte\", \"Münster\", \"Mathematik\", 12),\n",
    "(\"John\", \"Bochum\", \"BWL\", 5),\n",
    "(\"John\", \"Bochum\", \"Informatik\", 5))\n",
    "    \n",
    "connection = sq.connect(\"univerwaltung.db\")  \n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"DROP TABLE IF EXISTS student\")\n",
    "cursor.execute(\"\"\"\n",
    "CREATE TABLE student (name TEXT, ort TEXT, fach TEXT, semester INTEGER)\n",
    "\"\"\")\n",
    "\n",
    "for d in data:\n",
    "    sql = \"INSERT INTO student VALUES (?,?,?,?)\"\n",
    "    cursor.execute(sql,d)\n",
    "\n",
    "connection.commit()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Daten einfügen mit executemany\n",
    "\n",
    "Die Methode **executemany** ist schneller als das INSERT in der Schleife. **Dies ist unsere bevorzugte Art, eine Tabelle mit Daten zu füllen:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = \\\n",
    "((\"Malte\", \"Münster\", \"Philosophie\", 12),\n",
    "(\"Malte\", \"Münster\", \"Mathematik\", 12),\n",
    "(\"John\", \"Bochum\", \"BWL\", 5),\n",
    "(\"John\", \"Bochum\", \"Informatik\", 5))\n",
    "    \n",
    "connection = sq.connect(\"univerwaltung.db\")  \n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"DROP TABLE IF EXISTS student\")\n",
    "cursor.execute(\"\"\"\n",
    "CREATE TABLE student (name TEXT, ort TEXT, fach TEXT, semester INTEGER)\n",
    "\"\"\")\n",
    "\n",
    "cursor.executemany(\"INSERT INTO student VALUES (?,?,?,?)\",data)\n",
    "\n",
    "connection.commit()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Daten auslesen mit fetchall "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Malte', 'Münster', 'Philosophie', 12), ('Malte', 'Münster', 'Mathematik', 12), ('John', 'Bochum', 'BWL', 5), ('John', 'Bochum', 'Informatik', 5)]\n"
     ]
    }
   ],
   "source": [
    "connection = sq.connect(\"univerwaltung.db\")  \n",
    "cursor = connection.cursor()\n",
    "\n",
    "sql = \"SELECT * FROM student\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "data = cursor.fetchall()\n",
    "connection.close()\n",
    "print(data)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Daten auslesen mit cursor-Schleife"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Liefert eine Abfrage sehr große Datenmengen, dann kann das Resultat von fetchall zu Speicherproblemen führen. \n",
    "Wir können die Datensätze des Resultats auch mit einer for-Schleife über den cursor erhalten. **Mit einer Format-Angabe und dem \n",
    "'*'-Operator ist dies unsere bevorzugte Art, Daten aus einer Tabelle zu lesen.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Malte      Münster    Philosophie    12\n",
      "Malte      Münster    Mathematik     12\n",
      "John       Bochum     BWL             5\n",
      "John       Bochum     Informatik      5\n"
     ]
    }
   ],
   "source": [
    "connection = sq.connect(\"univerwaltung.db\")  \n",
    "cursor = connection.cursor()\n",
    "\n",
    "sql = \"SELECT * FROM student\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "form = \"{:10s} {:10s} {:12s} {:4d}\"\n",
    "for zeile in cursor:\n",
    "    print(form.format(*zeile))\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Informationen zur Datenbank abfragen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('student',)\n"
     ]
    }
   ],
   "source": [
    "# Die Namen der Tabellen einer Datenbank listen\n",
    "connection = sq.connect('univerwaltung.db')\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
    "for r in cursor:\n",
    "    print(r)\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['name', 'ort', 'fach', 'semester']"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Die Namen der Felder einer Tabelle listen\n",
    "connection = sq.connect('univerwaltung.db')\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"SELECT * FROM student\")\n",
    "felder = [x[0] for x in cursor.description]\n",
    "connection.close()\n",
    "felder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE student (name TEXT, ort TEXT, fach TEXT, semester INTEGER)\n"
     ]
    }
   ],
   "source": [
    "# die CREATE-Anweisungen für die einzelnen Tabellen listen\n",
    "connection = sq.connect('univerwaltung.db')\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"select sql from sqlite_master where type='table'\")\n",
    "for r in cursor:\n",
    "    print(r[0])\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### SQL-Abfragen "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sportverein\n",
    "sportverein.createDB()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE sqlite_sequence(name,seq)\n",
      "CREATE TABLE \"temp\"(mnr INT,zaehl)\n",
      "CREATE TABLE mitglied (MNr INTEGER PRIMARY KEY AUTOINCREMENT,\n",
      "    Name TEXT NOT NULL, Vorname TEXT NOT NULL, Telefon TEXT DEFAULT NULL,\n",
      "    Konto TEXT NOT NULL, BLZ INTEGER NOT NULL DEFAULT '0',\n",
      "    Beitrag REAL NOT NULL DEFAULT '0.00')\n",
      "CREATE TABLE bank (blz INTEGER PRIMARY KEY, bezeichnung VARCHAR(20))\n",
      "CREATE TABLE uebungsgruppe (Unr INTEGER PRIMARY KEY AUTOINCREMENT,\n",
      "    Sportart TEXT NOT NULL DEFAULT '', MNr INTEGER NOT NULL DEFAULt '0')\n",
      "CREATE TABLE trainingsort (Ort TEXT PRIMARY KEY,\n",
      "    HNr INTEGER NOT NULL DEFAULT '0')\n",
      "CREATE TABLE trainingszeit (Tag TEXT NOT NULL DEFAULT '',\n",
      "    ZEIT TEXT NOT NULL DEFAULT '00:00:00',\n",
      "    UNr INTEGER NOT NULL DEFAULT '0',\n",
      "    ORT TEXT NOT NULL)\n",
      "CREATE TABLE hausmeister (HNr INTEGER PRIMARY KEY, Name TEXT, Telefon Text)\n",
      "CREATE TABLE teilnehmer (MNr INTEGER NOT NULL DEFAULT '0',\n",
      "    UNr INTEGER NOT NULL DEFAULT '0')\n"
     ]
    }
   ],
   "source": [
    "# die CREATE-Anweisungen für die einzelnen Tabellen listen\n",
    "connection = sq.connect('sportverein.db')\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"select sql from sqlite_master where type='table'\")\n",
    "for r in cursor:\n",
    "    print(r[0])\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![a](img/sportverein.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def abfrage(sql):\n",
    "    '''\n",
    "    sql: String mit SQL-Anweisung\n",
    "    returns: None, printed die Ergebniszeilen\n",
    "    '''\n",
    "    connection = sq.connect(\"sportverein.db\")  \n",
    "    cursor = connection.cursor()\n",
    "\n",
    "    cursor.execute(sql)\n",
    "    connection.commit()\n",
    "    for zeile in cursor:\n",
    "        print(*zeile)\n",
    "    connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 Schwimmen 1990\n",
      "2 Fußball 2010\n",
      "3 Tennis 2229\n",
      "4 Volleyball 2832\n",
      "5 Wasserball 1990\n",
      "6 Tauchen 2595\n",
      "7 Basketball 2577\n",
      "8 Turnen 2643\n",
      "9 Langlauf 2349\n",
      "10 Fußball 1977\n",
      "11 Volleyball 1993\n",
      "12 Schwimmen 2173\n",
      "13 Fußball 2010\n",
      "14 Volleyball 1993\n",
      "15 Fußball 2189\n",
      "16 Schwimmen 2638\n",
      "17 Fußball 2632\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT * FROM uebungsgruppe\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Schwimmen\n",
      "Fußball\n",
      "Tennis\n",
      "Volleyball\n",
      "Wasserball\n",
      "Tauchen\n",
      "Basketball\n",
      "Turnen\n",
      "Langlauf\n",
      "Fußball\n",
      "Volleyball\n",
      "Schwimmen\n",
      "Fußball\n",
      "Volleyball\n",
      "Fußball\n",
      "Schwimmen\n",
      "Fußball\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT sportart FROM uebungsgruppe\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Schwimmen\n",
      "Fußball\n",
      "Tennis\n",
      "Volleyball\n",
      "Wasserball\n",
      "Tauchen\n",
      "Basketball\n",
      "Turnen\n",
      "Langlauf\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT DISTINCT sportart FROM uebungsgruppe\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT COUNT(DISTINCT sportart) FROM uebungsgruppe\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mit der **WHERE**-Klausel können wir Bedingungen formulieren."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 Fußball 2010\n",
      "10 Fußball 1977\n",
      "13 Fußball 2010\n",
      "15 Fußball 2189\n",
      "17 Fußball 2632\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT * FROM uebungsgruppe  WHERE sportart = 'Fußball'\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1963 Giek\n",
      "1964 Kiefer\n",
      "1965 Voss\n",
      "1966 Matthies\n",
      "1967 Krey\n",
      "1968 Rehbock\n",
      "1969 Siebert\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT mnr, name FROM mitglied  WHERE Mnr < 1970\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1964 Kiefer\n",
      "1965 Voss\n",
      "1966 Matthies\n",
      "1967 Krey\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT mnr, name FROM mitglied WHERE mnr BETWEEN 1964 AND 1967\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1964 Kiefer\n",
      "1975 Kinner\n",
      "2001 Kirchner\n",
      "2568 Kilian\n",
      "2638 Kistner\n",
      "2897 Kistner\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT mnr, name FROM mitglied WHERE name LIKE 'Ki%'\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2232 Braun\n",
      "2339 Kraus\n",
      "2481 Kraft\n",
      "2736 Staps\n",
      "2872 Kraft\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT mnr, name FROM mitglied WHERE name LIKE '__a__'\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1970 Auffarth\n",
      "2823 Aebersold\n",
      "2859 Adolphy\n",
      "2873 Alt\n",
      "2921 Autenrieth\n",
      "2940 Andres\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT mnr, name FROM mitglied WHERE name LIKE 'A%' AND (mnr < 2000 OR mnr > 2800)\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7 Basketball 2577\n",
      "2 Fußball 2010\n",
      "10 Fußball 1977\n",
      "13 Fußball 2010\n",
      "15 Fußball 2189\n",
      "17 Fußball 2632\n",
      "9 Langlauf 2349\n",
      "1 Schwimmen 1990\n",
      "12 Schwimmen 2173\n",
      "16 Schwimmen 2638\n",
      "6 Tauchen 2595\n",
      "3 Tennis 2229\n",
      "8 Turnen 2643\n",
      "4 Volleyball 2832\n",
      "11 Volleyball 1993\n",
      "14 Volleyball 1993\n",
      "5 Wasserball 1990\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT * FROM uebungsgruppe ORDER BY sportart\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 Volleyball 2832\n",
      "8 Turnen 2643\n",
      "6 Tauchen 2595\n",
      "16 Schwimmen 2638\n",
      "17 Fußball 2632\n",
      "7 Basketball 2577\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT * FROM uebungsgruppe WHERE mnr> 2500 ORDER BY sportart DESC\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2502 Tramski Werner 0041-31-8293110 31277895 18750023 52.0\n",
      "2503 Bäz Peter 09324-693 87592808 75000092 34.0\n",
      "2504 Hämmerle Jo  72328166 37500046 17.0\n",
      "2505 Messner Klaus P. 0421-36116776 19491420 18750023 52.0\n",
      "2506 Kunze Michael 06132-99510 92310756 37500046 34.0\n",
      "2507 Friebe Herr 0611-317210 66889770 18750023 52.0\n",
      "2508 Wolff Werner  16379051 37500046 17.0\n",
      "2509 Lohr Claudia 07274-1060 82726781 37500046 17.0\n",
      "2510 Welte Eckhard 02351-95930 89010877 56250069 34.0\n",
      "2511 Frankerl Wolfgang 07131-562452 5817857 56250069 52.0\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT * FROM mitglied WHERE mnr > 2500 LIMIT 10\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1963 2943 980 930\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT MIN(mnr), MAX(mnr), MAX(mnr)-MIN(mnr), COUNT(mnr) FROM mitglied\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "34.11827956989247 31730.0\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT AVG(beitrag), SUM(beitrag) FROM mitglied\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 Schwimmen 1990\n",
      "2 Fußball 2010\n",
      "10 Fußball 1977\n",
      "12 Schwimmen 2173\n",
      "13 Fußball 2010\n",
      "15 Fußball 2189\n",
      "16 Schwimmen 2638\n",
      "17 Fußball 2632\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT * FROM uebungsgruppe WHERE sportart IN ('Fußball','Schwimmen')\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Schinkmann\n",
      "Rienhöfer\n",
      "Ellerbusch\n",
      "Böttcher\n",
      "Weinert\n",
      "Roth\n",
      "Rudolph\n",
      "Zwick\n",
      "Müller\n",
      "Liesch\n",
      "Freyer\n",
      "Kistner\n",
      "Arndt\n",
      "Wolf\n"
     ]
    }
   ],
   "source": [
    "sql = \"SELECT name FROM mitglied WHERE mnr IN (SELECT mnr FROM uebungsgruppe)\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Anlauf 2579\n",
      "Arndt 2643\n",
      "Aluttis 2700\n",
      "Aluttis 2734\n",
      "Aebersold 2823\n",
      "Adolphy 2859\n",
      "Alt 2873\n",
      "Autenrieth 2921\n",
      "Andres 2940\n"
     ]
    }
   ],
   "source": [
    "# Aliasse für Spalten\n",
    "sql = \"SELECT name n, mnr m FROM mitglied WHERE n LIKE 'A%' and m > 2500\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Krause Bad1\n",
      "Krause Bad2\n",
      "Krause Bad3\n",
      "Krause Halle1\n",
      "Krause Halle2\n",
      "Krause Halle3\n",
      "Krause Platz1\n",
      "Krause Platz2\n",
      "Koschulte Bad1\n",
      "Koschulte Bad2\n",
      "Koschulte Bad3\n",
      "Koschulte Halle1\n",
      "Koschulte Halle2\n",
      "Koschulte Halle3\n",
      "Koschulte Platz1\n",
      "Koschulte Platz2\n",
      "Kacmarek Bad1\n",
      "Kacmarek Bad2\n",
      "Kacmarek Bad3\n",
      "Kacmarek Halle1\n",
      "Kacmarek Halle2\n",
      "Kacmarek Halle3\n",
      "Kacmarek Platz1\n",
      "Kacmarek Platz2\n"
     ]
    }
   ],
   "source": [
    "# Jeder mit jedem\n",
    "sql = \"SELECT a.name, b.ort FROM hausmeister a, trainingsort b\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Krause Bad1\n",
      "Koschulte Bad2\n",
      "Koschulte Bad3\n",
      "Koschulte Halle1\n",
      "Koschulte Halle2\n",
      "Kacmarek Halle3\n",
      "Kacmarek Platz1\n",
      "Krause Platz2\n"
     ]
    }
   ],
   "source": [
    "# die Verbindung über die WHERE-Klausel\n",
    "sql = \"SELECT a.name, b.ort FROM hausmeister a, trainingsort b WHERE a.hnr = b.hnr\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Krause Bad1\n",
      "Koschulte Bad2\n",
      "Koschulte Bad3\n",
      "Koschulte Halle1\n",
      "Koschulte Halle2\n",
      "Kacmarek Halle3\n",
      "Kacmarek Platz1\n",
      "Krause Platz2\n"
     ]
    }
   ],
   "source": [
    "# besser: die Verbindung über JOIN\n",
    "sql = \"\"\"\n",
    "SELECT a.name, b.ort FROM hausmeister a \n",
    "JOIN trainingsort b ON b.hnr = a.hnr\n",
    "\"\"\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Müller\n",
      "Ellerbusch\n"
     ]
    }
   ],
   "source": [
    "# liste die Namen aller Übungsleiter in Halle1\n",
    "sql = \"\"\"\n",
    "SELECT a.name FROM mitglied a \n",
    "JOIN uebungsgruppe b ON b.mnr = a.mnr\n",
    "JOIN trainingszeit c ON c.unr = b.unr\n",
    "JOIN trainingsort d ON d.ort = c.ort\n",
    "WHERE d.ort = \"Halle1\"\n",
    "\"\"\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "18750023 186\n",
      "37500046 166\n",
      "56250069 196\n",
      "75000092 201\n",
      "93750115 181\n"
     ]
    }
   ],
   "source": [
    "# GROUP BY wird oft mit den Aggregatfunktionen (COUNT, MAX, MIN, SUM, AVG) benutzt\n",
    "sql = \"SELECT blz, COUNT(blz) FROM mitglied GROUP BY blz\"\n",
    "abfrage(sql) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "201 Sparkasse Rhynern\n",
      "196 Volksbank Unna\n",
      "186 Volksbank Mawicke\n",
      "181 Deutsche Bank Soest\n",
      "166 Sparkasse Echthausen\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "SELECT COUNT(a.blz) zaehl, b.bezeichnung FROM mitglied a\n",
    "JOIN bank b ON b.blz = a.blz\n",
    "GROUP BY a.blz ORDER BY zaehl DESC\n",
    "\"\"\"\n",
    "abfrage(sql) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 Fußball\n",
      "3 Schwimmen\n",
      "3 Volleyball\n"
     ]
    }
   ],
   "source": [
    "# HAVING ersetzt das WHERE bei Aggregatfunktionen.\n",
    "# Liste alle sportarten, die mehr als einen Übungsleiter haben\n",
    "sql = \"SELECT COUNT(mnr), sportart FROM uebungsgruppe GROUP BY sportart HAVING count(mnr) > 1\"\n",
    "abfrage(sql)"
   ]
  },
  {
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}