04_SQL_sqlite_aggregazioni.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/Stravanni/Basi_di_dati/blob/main/04_SQL_sqlite_aggregazioni.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7wywHOYZSixy"
   },
   "source": [
    "# Introduzione a SQL\n",
    "@author: giovanni.simonini@unimore.it\n",
    "\n",
    "## To run: \n",
    "- to run a cell: SHIFT + ENTER\n",
    "  \n",
    "## Schema Università\n",
    "\n",
    "S (<u>Matr</u>,SNome,Citta,ACorso)\n",
    "\n",
    "D(<u>CD</u>,CNome,Citta)\n",
    "\n",
    "C(<u>CC</u>,CNome,CD)\n",
    "- FOREIGN KEY (CD) REFERENCES D(CD)\n",
    "\n",
    "E(<u>Matr,CC</u>,DATA,VOTO)\n",
    "- FOREIGN KEY (Matr) REFERENCES S(Matr)\n",
    "- FOREIGN KEY (CC) REFERENCES C(CC)\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8nW04m8ZIJZJ"
   },
   "outputs": [],
   "source": [
    "!pip install SQLAlchemy==1.4.49\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7tWMfJ-KpIIg"
   },
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite://', echo=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Gw8MjowWwv3G"
   },
   "source": [
    "## Create the the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "S-mRiFhSj8HJ",
    "outputId": "24b07046-f944-4049-80c4-0e8eeea421c5"
   },
   "outputs": [],
   "source": [
    "q = '''\n",
    "CREATE TABLE S (\n",
    "    Matr VARCHAR(45),\n",
    "    SNome VARCHAR(45),\n",
    "    Citta VARCHAR(45),\n",
    "    ACorso INT,\n",
    "    PRIMARY KEY (Matr)\n",
    ");\n",
    "'''\n",
    "engine.execute(q)\n",
    "\n",
    "q = '''\n",
    "CREATE TABLE D(\n",
    " CD VARCHAR(45),\n",
    " CNome VARCHAR(45),\n",
    " Citta VARCHAR(45),\n",
    " PRIMARY KEY (CD)\n",
    ");\n",
    "'''\n",
    "engine.execute(q)\n",
    "\n",
    "q = '''\n",
    "CREATE TABLE C(\n",
    " CC VARCHAR(45),\n",
    " CNome VARCHAR(45),\n",
    " CD VARCHAR(45),\n",
    " PRIMARY KEY (CC),\n",
    " FOREIGN KEY (CD) REFERENCES D(CD)\n",
    ");\n",
    "'''\n",
    "engine.execute(q)\n",
    "\n",
    "q = '''\n",
    "CREATE TABLE E(\n",
    " Matr VARCHAR(45),\n",
    " CC VARCHAR(45),\n",
    " DATA DATE,\n",
    " VOTO INT,\n",
    " PRIMARY KEY (Matr, CC),\n",
    " FOREIGN KEY (Matr) REFERENCES S(Matr),\n",
    " FOREIGN KEY (CC) REFERENCES C(CC)\n",
    ");\n",
    "'''\n",
    "engine.execute(q)\n",
    "\n",
    "\n",
    "q = '''\n",
    "INSERT INTO S (Matr, SNome, Citta, ACorso)\n",
    "VALUES\n",
    "('M1','Lucia Quaranta','SA',1),\n",
    "('M2','Giacomo Tedesco','PA',2),\n",
    "('M3','Carla Longo','MO',1),\n",
    "('M4','Ugo Rossi','MO',1),\n",
    "('M5','Valeria Neri','MO',2),\n",
    "('M6','Giuseppe Verdi','BO',1),\n",
    "('M7','Maria Rossi',null,1);\n",
    "'''\n",
    "engine.execute(q)\n",
    "\n",
    "\n",
    "q = '''\n",
    "INSERT INTO D (CD, CNome, Citta)\n",
    "VALUES\n",
    "('D1','Paolo Rossi','MO'),\n",
    "('D2','Maria Pastore','BO'),\n",
    "('D3','Paola Caboni','FI');\n",
    "'''\n",
    "engine.execute(q)\n",
    "\n",
    "\n",
    "q = '''\n",
    "INSERT INTO C (CC,CNome, CD)\n",
    "VALUES\n",
    "('C1','Fisica 1','D1'),\n",
    "('C2','Analisi Matematica 1','D2'),\n",
    "('C3','Fisica 2','D1'),\n",
    "('C4','Analisi Matematica 2','D3');\n",
    "'''\n",
    "engine.execute(q)\n",
    "\n",
    "\n",
    "q = '''\n",
    "INSERT INTO E (Matr,CC,Data,Voto)\n",
    "VALUES\n",
    "('M1','C1','1995-06-29',24),\n",
    "('M1','C2','1996-08-09',33),\n",
    "('M1','C3','1996-03-12',30),\n",
    "('M2','C1','1995-06-29',28),\n",
    "('M2','C2','1996-07-07',24),\n",
    "('M3','C2','1996-07-07',27),\n",
    "('M3','C3','1996-11-11',25),\n",
    "('M4','C3','1996-11-11',33),\n",
    "('M6','C2','1996-01-02',28),\n",
    "('M7','C1','1995-06-29',24),\n",
    "('M7','C2','1996-04-11',26),\n",
    "('M7','C3','1996-06-23',27);\n",
    "'''\n",
    "engine.execute(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "S1lNitr70Vs-"
   },
   "source": [
    "## Query the DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "2WBcfhqmtfTL",
    "outputId": "ff4909d4-bae9-4230-90a0-0c1a72c8bb83"
   },
   "outputs": [],
   "source": [
    "# TEMPLATE QUERY\n",
    "q = '''\n",
    "SELECT *\n",
    "FROM S,C,D,E\n",
    "'''\n",
    "\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "O0QgmeIA_lXw"
   },
   "source": [
    "## Funzioni di Aggregazione"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "gYWDSfd__hE4",
    "outputId": "fbb95789-4a25-430e-d8a4-d22c0c9383e1"
   },
   "outputs": [],
   "source": [
    "# \t\tFunzioni aggregate \n",
    "# \t\t(column functions)\n",
    "\n",
    "# Dato un insieme di tuple che soddisfano un predicato\n",
    "# (e.g., il risultato di una query qualsiasi di quelle\n",
    "# viste finora)\n",
    "# le funzione di aggregazione restituiscono un risultato\n",
    "# \"aggregato\" calcolato su queell'insieme.\n",
    "\n",
    "# Possono essere cosi' facilmente ritrovati:\n",
    "# La media dei voti di uno studete;\n",
    "# Il voto massimo in un dato esame;\n",
    "# Il numero di studenti che proviene da una data citta;\n",
    "# ... \n",
    "\n",
    "q = '''\n",
    "SELECT COUNT(*)\n",
    "FROM S\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "Nub_Xtfx_qxd",
    "outputId": "18397e8d-b696-4d15-cdcc-a4c1fcfbb06f"
   },
   "outputs": [],
   "source": [
    "q = '''\n",
    "SELECT AVG(E.VOTO)\n",
    "FROM E\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "GvJjQvzk_stY",
    "outputId": "5c801a8d-c4ce-4d28-ea84-733ece29b7a7"
   },
   "outputs": [],
   "source": [
    "q = '''\n",
    "SELECT MAX(E.VOTO)\n",
    "FROM E\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "UdqxdXAd_t4N",
    "outputId": "9904f4c9-d0db-4a86-c8e5-42b8de4ebd4a"
   },
   "outputs": [],
   "source": [
    "q = '''\n",
    "SELECT MIN(E.VOTO)\n",
    "FROM E\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "LHevdFoF_vVj",
    "outputId": "08eb019f-06ef-408d-c333-04e303a7d85f"
   },
   "outputs": [],
   "source": [
    "q = '''\n",
    "SELECT SUM(E.VOTO)/COUNT(E.VOTO)\n",
    "FROM E\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 142
    },
    "id": "M_g02TxD_wq5",
    "outputId": "46c0546b-aaa2-4b36-ec83-4dbca21bdc4c"
   },
   "outputs": [],
   "source": [
    "q = '''\n",
    "SELECT DISTINCT(E.CC)\n",
    "FROM E\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 421
    },
    "id": "peyvq12-_x88",
    "outputId": "8417f867-7922-4d71-f9ed-47131948e7c5"
   },
   "outputs": [],
   "source": [
    "q = '''\n",
    "SELECT E.CC\n",
    "FROM E\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "iwP5h5H7_zO4",
    "outputId": "2fb2f8d1-ea7a-45c0-f568-ff3ab509aefb"
   },
   "outputs": [],
   "source": [
    "q = '''\n",
    "SELECT COUNT(DISTINCT(E.CC))\n",
    "FROM E\n",
    "'''\n",
    "# Alcuni DBMS non permettono questa sintassi\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "P_3FGSWz_0fl",
    "outputId": "2ffc0fc3-3e67-4d26-99e3-7aad2ceb3c5b"
   },
   "outputs": [],
   "source": [
    "# Es. “Numero di studenti presenti” \n",
    "q = '''\n",
    "SELECT COUNT(Matr)\n",
    "FROM S\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "EjwUAqa3_7BS",
    "outputId": "906e5a71-7d4a-4ea9-fc7c-bf4237510d3c"
   },
   "outputs": [],
   "source": [
    "# Es. “Numero di studenti che hanno sostenuto almeno un esame” \n",
    "q = '''\n",
    "SELECT COUNT(DISTINCT Matr)\n",
    "FROM E\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "39vmUtPp_8Te",
    "outputId": "2ea5a10c-13f7-475d-dc67-f78552dce18e"
   },
   "outputs": [],
   "source": [
    "# Es. “Numero di studenti con anno di corso non nullo” \n",
    "q = '''\n",
    "SELECT COUNT(ACorso)\n",
    "FROM S\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "ev5cI04k_9sR",
    "outputId": "f8df0e28-eb99-49ae-beb3-37da764cc126"
   },
   "outputs": [],
   "source": [
    "# Es. “Numero di anni di corso di studenti presenti” \n",
    "q = '''\n",
    "SELECT COUNT(DISTINCT ACorso)\n",
    "FROM S\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 579
    },
    "id": "zznHfXYK_-57",
    "outputId": "6138371d-f140-4558-8e71-96fc8d196758"
   },
   "outputs": [],
   "source": [
    "# Es. “Numero di coppie distinte matricola-voto” \n",
    "q = '''\n",
    "SELECT COUNT(DISTINCT Matr, Voto)\n",
    "FROM E\n",
    "'''\n",
    "# Alcuni DBMS non permettono questa sintassi\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "udMQmv8cAAyO",
    "outputId": "17308d6a-3829-451b-eef3-994d47ebafba"
   },
   "outputs": [],
   "source": [
    "# Es. “Voto medio degli esami sostenuti dalla matricola M1”\n",
    "q = '''\n",
    "SELECT AVG(Voto)\n",
    "FROM E \n",
    "WHERE Matr='M1'\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n",
    "\n",
    "\n",
    "# che e' equivalente a\n",
    "q = '''\n",
    "SELECT SUM(Voto)/COUNT(Voto) \n",
    "FROM E \n",
    "WHERE Matr='M1'\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "ZXpUET19APRx",
    "outputId": "c61a0a51-10fb-45fb-ddbd-eb2e875059fb"
   },
   "outputs": [],
   "source": [
    "# Es. “Studenti il cui anno di corso `e minore di quello massimo presente”\n",
    "q = '''\n",
    "SELECT *\n",
    "FROM S\n",
    "WHERE ACorso < ( SELECT MAX(ACorso)\n",
    "\t\t\t\t FROM S)\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 80
    },
    "id": "2pWgN1LOATEs",
    "outputId": "9fc61836-4f45-48f3-86cf-20784934ca88"
   },
   "outputs": [],
   "source": [
    "# Considerate la sequente query, con funzione di aggregazione:\n",
    "\n",
    "q = '''\n",
    "SELECT Matr,MAX(Voto)\n",
    "FROM E\n",
    "'''\n",
    "res = engine.execute(q)\n",
    "df = pd.DataFrame(res.fetchall())\n",
    "df.columns = res.keys()\n",
    "df\n",
    "\n",
    "# PERCHE' E' SBAGLIATA?\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qZe3JNg_l7gi"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "include_colab_link": true,
   "name": "04_SQL_sqlite_aggregazioni.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}