1.4 - Lösungen.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ausführen um die Verbindung zur Datenbank aufzubauen\n",
    "\n",
    "import urllib.request\n",
    "urllib.request.urlretrieve(\"https://github.com/tschuegge/SqlKurs/raw/master/connect.py\", \"connect.py\")\n",
    "def sql(): pass\n",
    "%run ./connect.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 1.4.1\n",
    "sql(\"\"\"\n",
    "\n",
    "SELECT\n",
    "  ort,\n",
    "  COUNT(*) anzahl\n",
    "FROM kunde\n",
    "GROUP BY ort\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 1.4.2\n",
    "sql(\"\"\"\n",
    "\n",
    "SELECT\n",
    "  h.name,\n",
    "  COUNT(*) anzahl\n",
    "FROM artikel a\n",
    "  INNER JOIN hersteller h ON a.herstellerid = h.id\n",
    "GROUP BY\n",
    "  a.herstellerid\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 1.4.3\n",
    "sql(\"\"\"\n",
    "\n",
    "SELECT\n",
    "  m.name,\n",
    "  m.vorname,\n",
    "  SUM(a.nettopreis * p.liefermenge) nettoumsatz\n",
    "FROM mitarbeiter m\n",
    "  LEFT JOIN bestellungkopf k ON k.verkaeuferid = m.id\n",
    "  LEFT JOIN bestellungposition p ON p.bestellungkopfid = k.id\n",
    "  LEFT JOIN artikel a ON a.id = p.artikelid\n",
    "GROUP BY\n",
    "  m.name,\n",
    "  m.vorname\n",
    "\n",
    "-- Left Joins sind zwingend, damit alle Mitarbeitenden ausgegeben werden, auch jehne ohne Verkäufe.\n",
    "-- NaN bedeutet \"not a number\" und ist eine Besonderheit von Sqlite. Andere DBMS würden \"NULL\" ausgeben.\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 1.4.4\n",
    "sql(\"\"\"\n",
    "\n",
    "SELECT\n",
    "  m.name,\n",
    "  m.vorname,\n",
    "  SUM(a.nettopreis * p.liefermenge) / (m.gehalt * 12) quote\n",
    "FROM mitarbeiter m\n",
    "  LEFT JOIN bestellungkopf k ON k.verkaeuferid = m.id\n",
    "  LEFT JOIN bestellungposition p ON p.bestellungkopfid = k.id\n",
    "  LEFT JOIN artikel a ON a.id = p.artikelid\n",
    "GROUP BY m.name, m.vorname  -- Da die Spalten ausgegeben werden muss nach diesen gruppiert werden\n",
    "ORDER BY quote DESC\n",
    "\n",
    "-- Viele DBMS verhalten sich nur teilweise korrekt und geben jeweils den ersten Wert aus,\n",
    "-- wenn nicht gruppierte Spalten selektiert werden.\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 1.4.5\n",
    "sql(\"\"\"\n",
    "\n",
    "SELECT\n",
    "  k.bezeichnung,\n",
    "  AVG(a.nettopreis * (1 + m.satz/100)) durchschnittspreis,\n",
    "  COUNT(*) anzahl\n",
    "FROM artikelkategorie k\n",
    "  LEFT JOIN artikel a ON a.kategorieid = k.id\n",
    "  INNER JOIN mwstsatz m ON m.id = a.mwstid\n",
    "WHERE k.bezeichnung <> 'Software'\n",
    "GROUP BY k.bezeichnung\n",
    "  HAVING anzahl > 3\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 1.4.6\n",
    "sql(\"\"\"\n",
    "\n",
    "SELECT\n",
    "  h.name,\n",
    "  SUM(a.nettopreis * p.liefermenge) lieferumsatz\n",
    "FROM hersteller h\n",
    "  LEFT JOIN artikel a ON a.herstellerid = h.id\n",
    "  LEFT JOIN bestellungposition p ON p.artikelid = a.id\n",
    "GROUP BY h.name\n",
    "  HAVING lieferumsatz < 500000 OR lieferumsatz IS NULL\n",
    "\n",
    "-- Ohne den Einschluss von Null-Werten beim Lieferumsatz, würden Hersteller ausgefiltert\n",
    "-- von denen kein einziges Produkt geliefert wurde.\n",
    "\n",
    "\"\"\")"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
  },
  "kernelspec": {
   "display_name": "Python 3.8.9 64-bit",
   "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.9.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}