Analysis_SQL.ipynb

{
  "metadata": {
    "kernelspec": {
      "language": "python",
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "version": "3.6.4",
      "file_extension": ".py",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "name": "python",
      "mimetype": "text/x-python"
    },
    "kaggle": {
      "accelerator": "none",
      "dataSources": [
        {
          "sourceId": 589,
          "sourceType": "datasetVersion",
          "datasetId": 63
        }
      ],
      "dockerImageVersionId": 1302,
      "isInternetEnabled": false,
      "language": "python",
      "sourceType": "notebook",
      "isGpuEnabled": false
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat_minor": 0,
  "nbformat": 4,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/arkache/Lab-SQL-python/blob/main/Analysis_SQL.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# INTRODUCCIÓN\n",
        "\n",
        "Usaré esta notebook para proporcionar una introducción a SQL, lenguaje imprescindible para todo analista de datos.\n",
        "\n",
        "Los sistemas gestores de bases de datos interpretan consultas escritas en lenguaje SQL, y así es como interactuamos y obtenemos datos de las bases de datos y aplicaciones.\n",
        "\n",
        "* SQL significa Structured Query Language (Lenguaje de consulta estructurado)\n",
        "* SQL permite acceder y manipular bases de datos\n",
        "* SQL es una herramienta simple para el análisis avanzado de datos\n",
        "\n",
        "SQL no se usa sólo para manipular datos, sino también para administrar la misma base de datos, crear y modificar el diseño de objetos de la base de datos, como tablas. El elemento de SQL que se usa para crear y modificar objetos de base de datos se denomina lenguaje de definición de datos (DDL). Este curso no trata DDL.\n",
        "\n",
        "En esta notebook veremos cómo trabajar/manipular tablas. Aprenderemos a usar SQL para interactuar con una base de datos de fútbol europeo y generar información interesante con sus datos.\n",
        "\n",
        "La base de datos utilizada, pueder ser encontrada [aquí](https://www.kaggle.com/hugomathien/soccer)"
      ],
      "metadata": {
        "_cell_guid": "506bd974-9157-4cc8-a410-cabca6a0f459",
        "_uuid": "fac40b1c1c9e035e1b5d31fb5c702a2c7cc799a1",
        "id": "F9Yu0qtxbtcy"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 1.1 Definiciones importantes\n",
        "\n",
        "Aunque SQL es un estándar ANSI/ISO, existen diferentes versiones del lenguaje SQL.\n",
        "Sin embargo, para cumplir con el estándar del American National Standards Institute (ANSI), todas las versiones deben soportar al menos los comandos principales (como `SELECT`, `UPDATE`, `DELETE`, `INSERT`, `WHERE`) de forma similar.\n",
        "\n",
        "En este caso, vamos a trabajar con SQLite que es una implementación específica. La mayoría de los lenguajes SQL comparten todas las capacidades de este notebook. Las diferencias suelen estar en el rendimiento y en las funcionalidades analíticas avanzadas (y a veces en los errores, por supuesto)."
      ],
      "metadata": {
        "_cell_guid": "581b0fb8-9fe2-475f-a651-1f308d996401",
        "_uuid": "c01b2a7d26a3bc74f8a5fefd5b5e457265e99aab",
        "id": "_0hFQ32dbtcz"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 1.2 ¿Qué es SQLite?\n",
        "SQLite es una biblioteca de software que proporciona un sistema de gestión de bases de datos relacionales que se puede utilizar en casi todos los lenguajes de programación, incluido Python.\n",
        "\n",
        "El lite en SQLite significa facilidad en términos de configuración, administración de la base de datos y recursos necesarios. Las siguientes son sus características más importantes: es autónomo, sin servidor y transaccional.\n",
        "\n",
        "Más información acerca de SQLite:\n",
        "[https://www.sqlitetutorial.net/](https://www.sqlitetutorial.net/what-is-sqlite/)\n",
        "\n"
      ],
      "metadata": {
        "id": "YMjgPPqJbtcz"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 1.2.1 Configuraciones previas\n",
        "\n",
        "SQLite se puede utilizar en casi todos los lenguajes de programación, incluido Python. Esta notebook corre en Python, por lo que vamos a importar la librería de SQLite y otras que nos pueden ser de utilidad más adelante.\n",
        "\n",
        "Asimismo, crearemos la conexión con la BD y listaremos las tablas con las que contamos.\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "LIhvoXEWbtcz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Importación de librerías\n",
        "\n",
        "import pandas as pd # libreria de python para manipulacion de datos\n",
        "import sqlite3 # SQLite\n",
        "\n",
        "# Los datos de entrada están disponibles en el directorio \"../input/\".\n",
        "\n",
        "path = \"/content/Copia de database.sqlite\"\n",
        "database = path\n",
        "# Creamos la conexión con la BD y listaremos las tablas con las que contamos\n",
        "\n",
        "conn = sqlite3.connect(database)\n",
        "print (\"Conexión exitosa\")\n",
        "\n",
        "tables = pd.read_sql(\"SELECT * FROM sqlite_master WHERE type='table';\", conn)\n",
        "\n",
        "tables"
      ],
      "metadata": {
        "_cell_guid": "b24c5690-a816-45cf-b9d2-51a7a9e0a4f5",
        "_kg_hide-input": false,
        "_uuid": "81e2c6b2403df820374e6c93416d15182c7dc28b",
        "trusted": true,
        "id": "bB2Z1WEzbtcz",
        "outputId": "07588dd9-63c3-4e63-9913-7141a3932db4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 318
        },
        "collapsed": true
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Conexión exitosa\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    type               name           tbl_name  rootpage  \\\n",
              "0  table    sqlite_sequence    sqlite_sequence         4   \n",
              "1  table  Player_Attributes  Player_Attributes        11   \n",
              "2  table             Player             Player        14   \n",
              "3  table              Match              Match        18   \n",
              "4  table             League             League        24   \n",
              "5  table            Country            Country        26   \n",
              "6  table               Team               Team        29   \n",
              "7  table    Team_Attributes    Team_Attributes         2   \n",
              "\n",
              "                                                 sql  \n",
              "0             CREATE TABLE sqlite_sequence(name,seq)  \n",
              "1  CREATE TABLE \"Player_Attributes\" (\\n\\t`id`\\tIN...  \n",
              "2  CREATE TABLE `Player` (\\n\\t`id`\\tINTEGER PRIMA...  \n",
              "3  CREATE TABLE `Match` (\\n\\t`id`\\tINTEGER PRIMAR...  \n",
              "4  CREATE TABLE `League` (\\n\\t`id`\\tINTEGER PRIMA...  \n",
              "5  CREATE TABLE `Country` (\\n\\t`id`\\tINTEGER PRIM...  \n",
              "6  CREATE TABLE \"Team\" (\\n\\t`id`\\tINTEGER PRIMARY...  \n",
              "7  CREATE TABLE `Team_Attributes` (\\n\\t`id`\\tINTE...  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-57387e3e-8be9-4bfd-84d3-b06f9bd8ffaa\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>type</th>\n",
              "      <th>name</th>\n",
              "      <th>tbl_name</th>\n",
              "      <th>rootpage</th>\n",
              "      <th>sql</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>table</td>\n",
              "      <td>sqlite_sequence</td>\n",
              "      <td>sqlite_sequence</td>\n",
              "      <td>4</td>\n",
              "      <td>CREATE TABLE sqlite_sequence(name,seq)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>table</td>\n",
              "      <td>Player_Attributes</td>\n",
              "      <td>Player_Attributes</td>\n",
              "      <td>11</td>\n",
              "      <td>CREATE TABLE \"Player_Attributes\" (\\n\\t`id`\\tIN...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>table</td>\n",
              "      <td>Player</td>\n",
              "      <td>Player</td>\n",
              "      <td>14</td>\n",
              "      <td>CREATE TABLE `Player` (\\n\\t`id`\\tINTEGER PRIMA...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>table</td>\n",
              "      <td>Match</td>\n",
              "      <td>Match</td>\n",
              "      <td>18</td>\n",
              "      <td>CREATE TABLE `Match` (\\n\\t`id`\\tINTEGER PRIMAR...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>table</td>\n",
              "      <td>League</td>\n",
              "      <td>League</td>\n",
              "      <td>24</td>\n",
              "      <td>CREATE TABLE `League` (\\n\\t`id`\\tINTEGER PRIMA...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>table</td>\n",
              "      <td>Country</td>\n",
              "      <td>Country</td>\n",
              "      <td>26</td>\n",
              "      <td>CREATE TABLE `Country` (\\n\\t`id`\\tINTEGER PRIM...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>table</td>\n",
              "      <td>Team</td>\n",
              "      <td>Team</td>\n",
              "      <td>29</td>\n",
              "      <td>CREATE TABLE \"Team\" (\\n\\t`id`\\tINTEGER PRIMARY...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>table</td>\n",
              "      <td>Team_Attributes</td>\n",
              "      <td>Team_Attributes</td>\n",
              "      <td>2</td>\n",
              "      <td>CREATE TABLE `Team_Attributes` (\\n\\t`id`\\tINTE...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-57387e3e-8be9-4bfd-84d3-b06f9bd8ffaa')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-57387e3e-8be9-4bfd-84d3-b06f9bd8ffaa button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-57387e3e-8be9-4bfd-84d3-b06f9bd8ffaa');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-28f30b5c-d5dc-40b0-b260-bd3186dce534\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-28f30b5c-d5dc-40b0-b260-bd3186dce534')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-28f30b5c-d5dc-40b0-b260-bd3186dce534 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_fff6058f-1773-45ef-8ee0-c715a99b97b6\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('tables')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_fff6058f-1773-45ef-8ee0-c715a99b97b6 button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('tables');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "tables",
              "summary": "{\n  \"name\": \"tables\",\n  \"rows\": 8,\n  \"fields\": [\n    {\n      \"column\": \"type\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 1,\n        \"samples\": [\n          \"table\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 8,\n        \"samples\": [\n          \"Player_Attributes\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"tbl_name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 8,\n        \"samples\": [\n          \"Player_Attributes\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"rootpage\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 10,\n        \"min\": 2,\n        \"max\": 29,\n        \"num_unique_values\": 8,\n        \"samples\": [\n          11\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"sql\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 8,\n        \"samples\": [\n          \"CREATE TABLE \\\"Player_Attributes\\\" (\\n\\t`id`\\tINTEGER PRIMARY KEY AUTOINCREMENT,\\n\\t`player_fifa_api_id`\\tINTEGER,\\n\\t`player_api_id`\\tINTEGER,\\n\\t`date`\\tTEXT,\\n\\t`overall_rating`\\tINTEGER,\\n\\t`potential`\\tINTEGER,\\n\\t`preferred_foot`\\tTEXT,\\n\\t`attacking_work_rate`\\tTEXT,\\n\\t`defensive_work_rate`\\tTEXT,\\n\\t`crossing`\\tINTEGER,\\n\\t`finishing`\\tINTEGER,\\n\\t`heading_accuracy`\\tINTEGER,\\n\\t`short_passing`\\tINTEGER,\\n\\t`volleys`\\tINTEGER,\\n\\t`dribbling`\\tINTEGER,\\n\\t`curve`\\tINTEGER,\\n\\t`free_kick_accuracy`\\tINTEGER,\\n\\t`long_passing`\\tINTEGER,\\n\\t`ball_control`\\tINTEGER,\\n\\t`acceleration`\\tINTEGER,\\n\\t`sprint_speed`\\tINTEGER,\\n\\t`agility`\\tINTEGER,\\n\\t`reactions`\\tINTEGER,\\n\\t`balance`\\tINTEGER,\\n\\t`shot_power`\\tINTEGER,\\n\\t`jumping`\\tINTEGER,\\n\\t`stamina`\\tINTEGER,\\n\\t`strength`\\tINTEGER,\\n\\t`long_shots`\\tINTEGER,\\n\\t`aggression`\\tINTEGER,\\n\\t`interceptions`\\tINTEGER,\\n\\t`positioning`\\tINTEGER,\\n\\t`vision`\\tINTEGER,\\n\\t`penalties`\\tINTEGER,\\n\\t`marking`\\tINTEGER,\\n\\t`standing_tackle`\\tINTEGER,\\n\\t`sliding_tackle`\\tINTEGER,\\n\\t`gk_diving`\\tINTEGER,\\n\\t`gk_handling`\\tINTEGER,\\n\\t`gk_kicking`\\tINTEGER,\\n\\t`gk_positioning`\\tINTEGER,\\n\\t`gk_reflexes`\\tINTEGER,\\n\\tFOREIGN KEY(`player_fifa_api_id`) REFERENCES `Player`(`player_fifa_api_id`),\\n\\tFOREIGN KEY(`player_api_id`) REFERENCES `Player`(`player_api_id`)\\n)\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 64
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "_oo_LxjLPHvN"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "tables = pd.read_sql_query(\"SELECT name FROM sqlite_master WHERE type='table';\", conn)\n",
        "print(tables)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bBf0T1FBOsWW",
        "outputId": "228f6e47-6cf9-4f3e-fe49-2841dfab9e9a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "                name\n",
            "0    sqlite_sequence\n",
            "1  Player_Attributes\n",
            "2             Player\n",
            "3              Match\n",
            "4             League\n",
            "5            Country\n",
            "6               Team\n",
            "7    Team_Attributes\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 2. Sentencia SELECT\n",
        "\n",
        "Esta es la consulta más básica y su estructura es muy simple:\n",
        "* Se define lo que se quiere ver después del `SELECT`\n",
        "* Se eligen las tablas a consultar después del `FROM`\n",
        "\n",
        "Las únicas partes imprescindibles de una consulta son el `SELECT` y el `FROM`.\n",
        "\n",
        "La sintaxis de esta consulta es así:\n",
        "\n",
        "`SELECT column1, column2, ...`\n",
        "\n",
        "`FROM table_name;`\n",
        "\n",
        "\n",
        "Para consultar por todos los campos en una tabla sin tener que escribir uno por uno el nombre de cada campo, se utiliza `SELECT *`\n",
        "\n",
        "\n",
        "`SELECT *` significa que quieres obtener todas las columnas posibles de la tabla consultada. Los datos devueltos se almacenan en una tabla de resultados, denominada conjunto de resultados.\n",
        "\n",
        "La sintaxis de esta consulta es así:\n",
        "\n",
        "`SELECT *`\n",
        "\n",
        "`FROM table_name;`\n",
        "\n",
        "Es una buena práctica terminar la consulta con un punto y coma para indicar que la consulta está completa. Las palabras clave se escriben en mayúsculas y los nombres de tablas y campos se escriben en minúsculas.\n",
        "\n",
        "## 2.1.1 Ejemplo: Listar los países\n",
        "\n",
        "Queremos consultar qué países se encuentran en la tabla `Country` de la base de datos del fútbol europeo.\n",
        "\n",
        "\n",
        "Importante: La estructura y el orden de las secciones son importantes, mientras que los espacios, las nuevas líneas, las mayúsculas y las sangrías están ahí para facilitar la lectura del código.\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "_cell_guid": "1d749faa-7d12-40e1-96ce-de791dd28526",
        "_uuid": "aa75b8d7a97a5e878fb1482c80889abe8b7beb5b",
        "id": "5ZKb-jaEbtcz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT * FROM Country  \"\"\"\n",
        "\n",
        "countries = pd.read_sql(query, conn)\n",
        "countries"
      ],
      "metadata": {
        "trusted": true,
        "id": "bskb-W8lbtcz",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 394
        },
        "outputId": "ec77a988-0128-4f0d-8e43-43b4f39b0fd6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       id         name\n",
              "0       1      Belgium\n",
              "1    1729      England\n",
              "2    4769       France\n",
              "3    7809      Germany\n",
              "4   10257        Italy\n",
              "5   13274  Netherlands\n",
              "6   15722       Poland\n",
              "7   17642     Portugal\n",
              "8   19694     Scotland\n",
              "9   21518        Spain\n",
              "10  24558  Switzerland"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d2e8bfa1-9d56-46f5-a299-aaadf3f32063\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Belgium</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1729</td>\n",
              "      <td>England</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>4769</td>\n",
              "      <td>France</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>7809</td>\n",
              "      <td>Germany</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10257</td>\n",
              "      <td>Italy</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>13274</td>\n",
              "      <td>Netherlands</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>15722</td>\n",
              "      <td>Poland</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>17642</td>\n",
              "      <td>Portugal</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>19694</td>\n",
              "      <td>Scotland</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>21518</td>\n",
              "      <td>Spain</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>24558</td>\n",
              "      <td>Switzerland</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d2e8bfa1-9d56-46f5-a299-aaadf3f32063')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-d2e8bfa1-9d56-46f5-a299-aaadf3f32063 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-d2e8bfa1-9d56-46f5-a299-aaadf3f32063');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-90d6e295-1399-4ff3-9552-e6435f2d2f92\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-90d6e295-1399-4ff3-9552-e6435f2d2f92')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-90d6e295-1399-4ff3-9552-e6435f2d2f92 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_393ca813-4e42-4e1f-a007-6a6cfbe0660a\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('countries')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_393ca813-4e42-4e1f-a007-6a6cfbe0660a button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('countries');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "countries",
              "summary": "{\n  \"name\": \"countries\",\n  \"rows\": 11,\n  \"fields\": [\n    {\n      \"column\": \"id\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 8215,\n        \"min\": 1,\n        \"max\": 24558,\n        \"num_unique_values\": 11,\n        \"samples\": [\n          13274,\n          1,\n          21518\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 11,\n        \"samples\": [\n          \"Netherlands\",\n          \"Belgium\",\n          \"Spain\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 54
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 2.1.2 Práctica: Listar las ligas del fútbol europeo\n",
        "\n",
        "Deben listar todas las ligas que estén en la tabla `League` de la base de datos de fútbol europeo.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"`  justo después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten haciendo clic en ejecutar (o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "vbYS2tUMbtc0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" justo después de la palabra query =\n",
        "\n",
        "query = \"\"\"SELECT * FROM Match;\"\"\"\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "leagues = pd.read_sql(query, conn)\n",
        "leagues"
      ],
      "metadata": {
        "trusted": true,
        "id": "dWciygVQbtc0",
        "outputId": "54a15ec4-edb7-4b58-b39f-7fdef9ce0c8a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 791
        }
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          id  country_id  league_id     season  stage                 date  \\\n",
              "0          1           1          1  2008/2009      1  2008-08-17 00:00:00   \n",
              "1          2           1          1  2008/2009      1  2008-08-16 00:00:00   \n",
              "2          3           1          1  2008/2009      1  2008-08-16 00:00:00   \n",
              "3          4           1          1  2008/2009      1  2008-08-17 00:00:00   \n",
              "4          5           1          1  2008/2009      1  2008-08-16 00:00:00   \n",
              "...      ...         ...        ...        ...    ...                  ...   \n",
              "25974  25975       24558      24558  2015/2016      9  2015-09-22 00:00:00   \n",
              "25975  25976       24558      24558  2015/2016      9  2015-09-23 00:00:00   \n",
              "25976  25977       24558      24558  2015/2016      9  2015-09-23 00:00:00   \n",
              "25977  25978       24558      24558  2015/2016      9  2015-09-22 00:00:00   \n",
              "25978  25979       24558      24558  2015/2016      9  2015-09-23 00:00:00   \n",
              "\n",
              "       match_api_id  home_team_api_id  away_team_api_id  home_team_goal  ...  \\\n",
              "0            492473              9987              9993               1  ...   \n",
              "1            492474             10000              9994               0  ...   \n",
              "2            492475              9984              8635               0  ...   \n",
              "3            492476              9991              9998               5  ...   \n",
              "4            492477              7947              9985               1  ...   \n",
              "...             ...               ...               ...             ...  ...   \n",
              "25974       1992091             10190             10191               1  ...   \n",
              "25975       1992092              9824             10199               1  ...   \n",
              "25976       1992093              9956             10179               2  ...   \n",
              "25977       1992094              7896             10243               0  ...   \n",
              "25978       1992095             10192              9931               4  ...   \n",
              "\n",
              "        SJA   VCH   VCD   VCA   GBH   GBD   GBA   BSH   BSD   BSA  \n",
              "0      4.00  1.65  3.40  4.50  1.78  3.25  4.00  1.73  3.40  4.20  \n",
              "1      3.80  2.00  3.25  3.25  1.85  3.25  3.75  1.91  3.25  3.60  \n",
              "2      2.50  2.35  3.25  2.65  2.50  3.20  2.50  2.30  3.20  2.75  \n",
              "3      7.50  1.45  3.75  6.50  1.50  3.75  5.50  1.44  3.75  6.50  \n",
              "4      1.73  4.50  3.40  1.65  4.50  3.50  1.65  4.75  3.30  1.67  \n",
              "...     ...   ...   ...   ...   ...   ...   ...   ...   ...   ...  \n",
              "25974   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
              "25975   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
              "25976   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
              "25977   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
              "25978   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
              "\n",
              "[25979 rows x 115 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-bee6d685-cc3e-41dd-99ef-f91b5167b575\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>country_id</th>\n",
              "      <th>league_id</th>\n",
              "      <th>season</th>\n",
              "      <th>stage</th>\n",
              "      <th>date</th>\n",
              "      <th>match_api_id</th>\n",
              "      <th>home_team_api_id</th>\n",
              "      <th>away_team_api_id</th>\n",
              "      <th>home_team_goal</th>\n",
              "      <th>...</th>\n",
              "      <th>SJA</th>\n",
              "      <th>VCH</th>\n",
              "      <th>VCD</th>\n",
              "      <th>VCA</th>\n",
              "      <th>GBH</th>\n",
              "      <th>GBD</th>\n",
              "      <th>GBA</th>\n",
              "      <th>BSH</th>\n",
              "      <th>BSD</th>\n",
              "      <th>BSA</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>2008/2009</td>\n",
              "      <td>1</td>\n",
              "      <td>2008-08-17 00:00:00</td>\n",
              "      <td>492473</td>\n",
              "      <td>9987</td>\n",
              "      <td>9993</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>4.00</td>\n",
              "      <td>1.65</td>\n",
              "      <td>3.40</td>\n",
              "      <td>4.50</td>\n",
              "      <td>1.78</td>\n",
              "      <td>3.25</td>\n",
              "      <td>4.00</td>\n",
              "      <td>1.73</td>\n",
              "      <td>3.40</td>\n",
              "      <td>4.20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>2008/2009</td>\n",
              "      <td>1</td>\n",
              "      <td>2008-08-16 00:00:00</td>\n",
              "      <td>492474</td>\n",
              "      <td>10000</td>\n",
              "      <td>9994</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>3.80</td>\n",
              "      <td>2.00</td>\n",
              "      <td>3.25</td>\n",
              "      <td>3.25</td>\n",
              "      <td>1.85</td>\n",
              "      <td>3.25</td>\n",
              "      <td>3.75</td>\n",
              "      <td>1.91</td>\n",
              "      <td>3.25</td>\n",
              "      <td>3.60</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>2008/2009</td>\n",
              "      <td>1</td>\n",
              "      <td>2008-08-16 00:00:00</td>\n",
              "      <td>492475</td>\n",
              "      <td>9984</td>\n",
              "      <td>8635</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>2.50</td>\n",
              "      <td>2.35</td>\n",
              "      <td>3.25</td>\n",
              "      <td>2.65</td>\n",
              "      <td>2.50</td>\n",
              "      <td>3.20</td>\n",
              "      <td>2.50</td>\n",
              "      <td>2.30</td>\n",
              "      <td>3.20</td>\n",
              "      <td>2.75</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>2008/2009</td>\n",
              "      <td>1</td>\n",
              "      <td>2008-08-17 00:00:00</td>\n",
              "      <td>492476</td>\n",
              "      <td>9991</td>\n",
              "      <td>9998</td>\n",
              "      <td>5</td>\n",
              "      <td>...</td>\n",
              "      <td>7.50</td>\n",
              "      <td>1.45</td>\n",
              "      <td>3.75</td>\n",
              "      <td>6.50</td>\n",
              "      <td>1.50</td>\n",
              "      <td>3.75</td>\n",
              "      <td>5.50</td>\n",
              "      <td>1.44</td>\n",
              "      <td>3.75</td>\n",
              "      <td>6.50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>2008/2009</td>\n",
              "      <td>1</td>\n",
              "      <td>2008-08-16 00:00:00</td>\n",
              "      <td>492477</td>\n",
              "      <td>7947</td>\n",
              "      <td>9985</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>1.73</td>\n",
              "      <td>4.50</td>\n",
              "      <td>3.40</td>\n",
              "      <td>1.65</td>\n",
              "      <td>4.50</td>\n",
              "      <td>3.50</td>\n",
              "      <td>1.65</td>\n",
              "      <td>4.75</td>\n",
              "      <td>3.30</td>\n",
              "      <td>1.67</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25974</th>\n",
              "      <td>25975</td>\n",
              "      <td>24558</td>\n",
              "      <td>24558</td>\n",
              "      <td>2015/2016</td>\n",
              "      <td>9</td>\n",
              "      <td>2015-09-22 00:00:00</td>\n",
              "      <td>1992091</td>\n",
              "      <td>10190</td>\n",
              "      <td>10191</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25975</th>\n",
              "      <td>25976</td>\n",
              "      <td>24558</td>\n",
              "      <td>24558</td>\n",
              "      <td>2015/2016</td>\n",
              "      <td>9</td>\n",
              "      <td>2015-09-23 00:00:00</td>\n",
              "      <td>1992092</td>\n",
              "      <td>9824</td>\n",
              "      <td>10199</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25976</th>\n",
              "      <td>25977</td>\n",
              "      <td>24558</td>\n",
              "      <td>24558</td>\n",
              "      <td>2015/2016</td>\n",
              "      <td>9</td>\n",
              "      <td>2015-09-23 00:00:00</td>\n",
              "      <td>1992093</td>\n",
              "      <td>9956</td>\n",
              "      <td>10179</td>\n",
              "      <td>2</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25977</th>\n",
              "      <td>25978</td>\n",
              "      <td>24558</td>\n",
              "      <td>24558</td>\n",
              "      <td>2015/2016</td>\n",
              "      <td>9</td>\n",
              "      <td>2015-09-22 00:00:00</td>\n",
              "      <td>1992094</td>\n",
              "      <td>7896</td>\n",
              "      <td>10243</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25978</th>\n",
              "      <td>25979</td>\n",
              "      <td>24558</td>\n",
              "      <td>24558</td>\n",
              "      <td>2015/2016</td>\n",
              "      <td>9</td>\n",
              "      <td>2015-09-23 00:00:00</td>\n",
              "      <td>1992095</td>\n",
              "      <td>10192</td>\n",
              "      <td>9931</td>\n",
              "      <td>4</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>25979 rows × 115 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bee6d685-cc3e-41dd-99ef-f91b5167b575')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-bee6d685-cc3e-41dd-99ef-f91b5167b575 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-bee6d685-cc3e-41dd-99ef-f91b5167b575');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-8621d993-4879-47d3-a084-20952c9f600c\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-8621d993-4879-47d3-a084-20952c9f600c')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-8621d993-4879-47d3-a084-20952c9f600c button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_3a6ddd0f-7124-4f4a-ae67-7175a0697f55\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('leagues')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_3a6ddd0f-7124-4f4a-ae67-7175a0697f55 button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('leagues');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "leagues"
            }
          },
          "metadata": {},
          "execution_count": 66
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 2.1.3 Práctica: Listar los jugadores del fútbol europeo\n",
        "\n",
        "Deben listar los campos `id`,`player_name`, `birthday`, `height` y `weight` que estan en la tabla `Player` de la base de datos de Fútbol Europeo.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "_1-Txj3jbtc0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "players = pd.read_sql(query, conn)\n",
        "players"
      ],
      "metadata": {
        "trusted": true,
        "id": "SbErD-tebtc0"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 2.2 Sentencia SELECT DISTINCT\n",
        "\n",
        "La sentencia `SELECT DISTINCT` se utiliza para devolver sólo valores distintos (diferentes).\n",
        "\n",
        "Dentro de una tabla, una columna a menudo contiene muchos valores duplicados y a veces sólo se desea listar los diferentes valores (distintos).\n",
        "\n",
        "La sintaxis de `SELECT DISTINCT` es así:\n",
        "\n",
        "`SELECT DISTINCT column1`\n",
        "\n",
        "`FROM table_name;`\n",
        "\n",
        "## 2.2.1 Ejemplo: Listar las temporadas jugadas de esta Base de Datos\n",
        "\n",
        "Si consultamos por todas las temporadas que se almacenaron en la tabla `Match`de la base de datos de Fútbol Europeo, vamos a tener el siguiente resultado.\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo\n"
      ],
      "metadata": {
        "id": "_krfo6y0btc0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT season FROM Match\"\"\"\n",
        "\n",
        "seasons = pd.read_sql(query, conn)\n",
        "seasons"
      ],
      "metadata": {
        "scrolled": true,
        "trusted": true,
        "id": "6xC7VyFgbtc0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "8dbee8e9-9f4b-4951-b59b-33d5a00a4f76"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          season\n",
              "0      2008/2009\n",
              "1      2008/2009\n",
              "2      2008/2009\n",
              "3      2008/2009\n",
              "4      2008/2009\n",
              "...          ...\n",
              "25974  2015/2016\n",
              "25975  2015/2016\n",
              "25976  2015/2016\n",
              "25977  2015/2016\n",
              "25978  2015/2016\n",
              "\n",
              "[25979 rows x 1 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-dda67064-ca2c-4c06-aa99-93369289fa03\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>season</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2008/2009</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2008/2009</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2008/2009</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2008/2009</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2008/2009</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25974</th>\n",
              "      <td>2015/2016</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25975</th>\n",
              "      <td>2015/2016</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25976</th>\n",
              "      <td>2015/2016</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25977</th>\n",
              "      <td>2015/2016</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25978</th>\n",
              "      <td>2015/2016</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>25979 rows × 1 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-dda67064-ca2c-4c06-aa99-93369289fa03')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-dda67064-ca2c-4c06-aa99-93369289fa03 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-dda67064-ca2c-4c06-aa99-93369289fa03');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-a8ce164e-fb50-4985-a1c0-23b0e68f572e\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-a8ce164e-fb50-4985-a1c0-23b0e68f572e')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-a8ce164e-fb50-4985-a1c0-23b0e68f572e button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_5e3435ad-d52d-4166-9760-80822d48519b\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('seasons')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_5e3435ad-d52d-4166-9760-80822d48519b button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('seasons');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "seasons",
              "summary": "{\n  \"name\": \"seasons\",\n  \"rows\": 25979,\n  \"fields\": [\n    {\n      \"column\": \"season\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 8,\n        \"samples\": [\n          \"2009/2010\",\n          \"2013/2014\",\n          \"2008/2009\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 67
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Con el resultado anterior, es muy difícil responder a la pregunta ¿Cuáles son las temporadas almacenadas en esta base de datos?\n",
        "Para responderla sin mucho esfuerzo, usamos `SELECT DISTINCT` para consultar la tabla `Match` de la base de datos.\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "5z08OiQKbtc0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"\n",
        "SELECT DISTINCT season\n",
        "FROM Match;\n",
        "\"\"\"\n",
        "seasons = pd.read_sql(query, conn)\n",
        "seasons"
      ],
      "metadata": {
        "trusted": true,
        "id": "JJk7igUdbtc1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 2.2.2 Práctica: Listar sólo tasas diferentes de valoración general de los jugadores.\n",
        "\n",
        "En la tabla `Player_Attributes` de la base de datos de Fútbol Europeo hay el campo `overall_rating` que parece contener la valoración de los jugadores. El objetivo es ver sólo tasas diferentes de valoración general de los jugadores.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "5IvHlBkFbtc1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "overall_rating = pd.read_sql(query, conn)\n",
        "overall_rating"
      ],
      "metadata": {
        "trusted": true,
        "id": "jZixwxExbtc1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 3. Sentencia LIMIT\n",
        "\n",
        "Cuando deseamos poner un límite al resultado o no nos interesa conocer la totalidad de los resultados, podemos **limitar** la cantidad de registros resultantes mediante la sentencia `LIMIT`\n",
        "\n",
        "La sintaxis de `LIMIT` es así:\n",
        "\n",
        "`SELECT column1, column2...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "`LIMIT number;`\n",
        "\n",
        "## 3.1.1 Ejemplo: Listar 15 jugadores de la base de datos del fútbol europeo\n",
        "\n",
        "Queremos listar 15 jugadores de la tabla `Player` de la base de datos de Fútbol Europeo. En la tabla de resultados quiero ver los campos `ìd`,`player_name`, `birthday`, `height` y `weight`. Todos se encuentran en la tabla `Player`\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "Id6ZPkpSbtc1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"\n",
        "SELECT id, player_name, birthday, height, weight\n",
        "FROM Player\n",
        "LIMIT 15;\n",
        "\"\"\"\n",
        "players = pd.read_sql(query, conn)\n",
        "players"
      ],
      "metadata": {
        "trusted": true,
        "id": "_hNSU17Bbtc1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 193
        },
        "outputId": "98c6b492-ef72-4e62-ccbb-d2784de89de1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "NameError",
          "evalue": "name 'pd' is not defined",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-1-2568bd9a4eea>\u001b[0m in \u001b[0;36m<cell line: 6>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mLIMIT\u001b[0m \u001b[0;36m15\u001b[0m\u001b[0;34m;\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \"\"\"\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0mplayers\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      7\u001b[0m \u001b[0mplayers\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mNameError\u001b[0m: name 'pd' is not defined"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 3.1.2 Práctica: Listar 25 equipos del fútbol europeo\n",
        "\n",
        "Deben listar 25 equipos del fútbol europeo. La tabla de resultados deberá contener los campos `id` y `team_long_name` de la tabla `Team` de la base de datos de Fútbol Europeo.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "JwUkk_DVbtc1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "teams = pd.read_sql(query, conn)\n",
        "teams"
      ],
      "metadata": {
        "scrolled": true,
        "trusted": true,
        "id": "P7jCSoqdbtc1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 4. Funciones agregativas/ Métricas\n",
        "\n",
        "Todas las métricas tienen que ser agregadas usando funciones. Las funciones agregativas más comunes son:\n",
        "\n",
        "* `SUM()` | Sumatoria total de una columna tipo numérica.\n",
        "* `COUNT()` | Devuelve la cantidad de filas.\n",
        "* `COUNT(DISTINCT)` | Devuelve la cantidad de filas distintas o diferentes.\n",
        "* `AVG()` | Promedia los valores de una columna tipo numérica.\n",
        "* `MIN()` | Menor valor dentro de la columna especificada.\n",
        "* `MAX()` | Mayor valor dentro de la columna especificada.\n",
        "\n",
        "La sintaxis de las métricas en la sentencia `SELECT` es así:\n",
        "* `SUM()`\n",
        "\n",
        "`SELECT SUM(column1), column2...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "* `COUNT()`\n",
        "\n",
        "`SELECT COUNT(column1), column2...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "* `COUNT(DISTINCT)`\n",
        "\n",
        "`SELECT COUNT(DISTINCT column1), column2...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "* `AVG()`\n",
        "\n",
        "`SELECT AVG(column1), column2...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "\n",
        "* `MIN()`\n",
        "\n",
        "`SELECT MIN(column1), column2...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "\n",
        "* `MAX()`\n",
        "\n",
        "`SELECT MAX(column1), column2...`\n",
        "\n",
        "`FROM table_name`\n"
      ],
      "metadata": {
        "id": "Vlm9M4vUbtc1"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.1.1 Ejemplo: ¿Cuántos países diferentes están almacenados en la tabla Country?\n",
        "\n",
        "Para consultar por el número de países diferentes almacenados en la tabla `Country` y que la tabla de resultados nos muestre únicamente el número con la cantidad de países, necesitamos usar una función agregativa que cuente el número de registros pero además, que cuente sólo los registros distintos. Esa función es `COUNT(DISTINCT )`\n",
        "\n",
        "Ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "maO_GyY-btc1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query = \"\"\"\n",
        "SELECT COUNT(DISTINCT name)\n",
        "FROM Country;\n",
        "\"\"\"\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "count_countries = pd.read_sql(query, conn)\n",
        "count_countries"
      ],
      "metadata": {
        "trusted": true,
        "id": "COnNSO1dbtc2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.1.2 Práctica: Traer el número de etapas jugadas de la tabla Match\n",
        "\n",
        "Deben traer el número de etapas `stage` almacenados en la tabla `Match` de la base de datos de Fútbol Europeo. La tabla de resultados deberá mostrar únicamente el número con la cantidad de etapas **diferentes**.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "fFIg4CMDbtc2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "countdis_stages = pd.read_sql(query, conn)\n",
        "countdis_stages"
      ],
      "metadata": {
        "trusted": true,
        "id": "MXmWzf4ibtc2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.1.3 Práctica: Traer la suma de goles hechos por los equipos visitantes de la tabla Match\n",
        "\n",
        "Deben traer la suma de goles hechos por los equipos visitantes `away_team_goal` almacenados en la tabla `Match` de la base de datos de Fútbol Europeo. La tabla de resultados deberá mostrar únicamente el número con la suma de goles.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "HOrAuXaxbtc2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "sum_awayteam = pd.read_sql(query, conn)\n",
        "sum_awayteam"
      ],
      "metadata": {
        "trusted": true,
        "id": "SGJyntrgbtc2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.1.4 Práctica: Traer el promedio de goles hechos por los equipos locales y el promedio de goles hechos por los visitantes de la tabla Match\n",
        "\n",
        "Deben traer el promedio de goles hechos por los equipos locales `home_team_goal` y el promedio de goles hechos por los equipos visitantes `away_team_goal` almacenados en la tabla `Match` de la base de datos de Fútbol Europeo. La tabla de resultados deberá mostrar únicamente el número con el promedio de goles.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "qPUT8pfMbtc2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "avg_teamgoals = pd.read_sql(query, conn)\n",
        "avg_teamgoals"
      ],
      "metadata": {
        "trusted": true,
        "id": "qvjbOBLHbtc2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4.1.5 Práctica: Traer la máxima cantidad de goles que un equipo visitante anotó en los juegos de la tabla Match\n",
        "\n",
        "Deben traer la máxima cantidad de goles un equipo visitante `away_team_goal` almacenados en la tabla `Match` de la base de datos de Fútbol Europeo. La tabla de resultados deberá mostrar únicamente el número con cantidad de goles.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "8iLKCxQzbtc2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "max_teamgoals = pd.read_sql(query, conn)\n",
        "max_teamgoals"
      ],
      "metadata": {
        "trusted": true,
        "id": "Up2ZVEeBbtc2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 5. Operaciones matemáticas\n",
        "\n",
        "Se pueden realizar operaciones matemáticas como: `+`, `-`, `*`, `/`, etc. Asimismo, estás operaciones pueden ser usadas en conjunto con las funciones agregativas/ métricas.  \n",
        "\n",
        "La sintaxis en la sentencia `SELECT` es así:\n",
        "\n",
        "* `+`\n",
        "\n",
        "`SELECT column1+column2`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "La sintaxis combinando con una métrica en la sentencia `SELECT` es así:\n",
        "\n",
        "* `AVG(+)`\n",
        "\n",
        "`SELECT AVG(column1+column2)`\n",
        "\n",
        "`FROM table_name`\n"
      ],
      "metadata": {
        "id": "g5KGkL7Kbtc2"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 5.1.1 Práctica: Traer el promedio de goles por partido de la tabla Match\n",
        "\n",
        "Deben traer el promedio de la suma de goles hechos por los equipos locales `home_team_goal` y visitantes `away_team_goal` en todos los juegos almacenados en la tabla `Match` de la base de datos de Fútbol Europeo. La tabla de resultados deberá mostrar únicamente el número con el promedio de goles por partido.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "89eThWzbbtc2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =  \"\"\"consulta SQL\"\"\"\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "avg_sum_goals = pd.read_sql(query, conn)\n",
        "avg_sum_goals"
      ],
      "metadata": {
        "trusted": true,
        "id": "4a0nhUDmbtc3"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 6. Sentencia WHERE\n",
        "\n",
        "La sentencia `WHERE` se utiliza para filtrar registros. Mediante esta sentencia, se extraen sólo aquellos registros que cumplen una condición específica.\n",
        "\n",
        "La sintaxis de `WHERE` es así:\n",
        "\n",
        "`SELECT column1, column2...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "`WHERE condition;`\n",
        "\n",
        "\n",
        "## 6.1 Campos de texto (y fecha) vs  campos numéricos\n",
        "\n",
        "SQL requiere comillas simples alrededor de los valores del texto y fecha (la mayoría de los sistemas de bases de datos también permiten comillas dobles). Sin embargo, los campos numéricos no deben incluirse entre comillas.\n",
        "\n",
        "Ejemplos de sintaxis con campo númerico:\n",
        "\n",
        "`SELECT *`\n",
        "\n",
        "`FROM Customers`\n",
        "\n",
        "`WHERE CustomerId = 1;`\n",
        "\n",
        "Ejemplos de sintaxis con campo de texto:\n",
        "\n",
        "`SELECT *`\n",
        "\n",
        "`FROM Customers`\n",
        "\n",
        "`WHERE Country = 'Mexico';`\n"
      ],
      "metadata": {
        "id": "19nw2aHnbtc3"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.2.1 Ejemplo: Listar los jugadores que pesan 165 libras\n",
        "\n",
        "Queremos listar a los jugadores que cumplan con la condición de pesar 165 libras. En la tabla de resultados quiero ver los campos `id`,`player_name`, `birthday`, `height` y `weight` de los que cumplen la condición. Todos se encuentran en la tabla `Player`\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "BMKLw7Plbtc3"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT id, player_name, birthday, height, weight\n",
        "        FROM Player\n",
        "        WHERE weight = 165\n",
        "        LIMIT 15;\"\"\"\n",
        "\n",
        "players_weight = pd.read_sql(query, conn)\n",
        "players_weight"
      ],
      "metadata": {
        "trusted": true,
        "id": "XTmPhjMObtc3"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.2.2 Práctica: Listar las temporadas en que un equipo visitante anotó 7 o más goles\n",
        "\n",
        "Deben listar las temporadas en que algún equipo visitante haya anotado 7 o más goles. La tabla de resultados deberá contener los campos `season`, `home_team_goal`, `away_team_goal` de la tabla `Match` de la base de datos de Fútbol Europeo.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "eUdD7cCvbtc6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "away_team_goals = pd.read_sql(query, conn)\n",
        "away_team_goals"
      ],
      "metadata": {
        "trusted": true,
        "id": "T3H9e32Rbtc6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.3 Operadores lógicos en WHERE\n",
        "\n",
        "La sentencia WHERE puede combinarse con los operadores lógicos: `AND`, `OR` y `NOT`. Los operadores `AND` y `OR` se utilizan para filtrar registros basados en más de una condición.\n",
        "* El operador `AND` muestra un registro si todas las condiciones separadas por `AND` son VERDADERA(s).\n",
        "* El operador `OR` muestra un registro si alguna de las condiciones separadas por `OR` es VERDADERA(s).\n",
        "* El operador `NOT` muestra un registro si la(s) condición(es) NO es VERDADERA(s).\n",
        "\n",
        "\n",
        "1. Sintaxis de`AND`:\n",
        "\n",
        "`SELECT column1, column2, ...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "`WHERE condition1 AND condition2 AND condition3 ...;`\n",
        "\n",
        "\n",
        "2. Sintaxis de `OR`\n",
        "\n",
        "`SELECT column1, column2, ...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "`WHERE condition1 OR condition2 ...;`\n",
        "\n",
        "3. Sintaxis de `NOT`\n",
        "\n",
        "`SELECT column1, column2, ...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "`WHERE NOT condition;`"
      ],
      "metadata": {
        "id": "v_N_nbuIbtc6"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.3.1 Práctica: Traer el Id de los equipos que desarrollan un dribbling normal y su velocidad de juego es balanceada\n",
        "\n",
        "Tomando la tabla `Team_Attributes` de la base de datos de Fútbol Europeo, deben obtener el `id`,`buildUpPlayDribblingClass` y `buildUpPlaySpeedClass`  de los equipos que tengan los siguientes atributos:\n",
        "* `buildUpPlayDribblingClass = 'Normal'`\n",
        "* `buildUpPlaySpeedClass = 'Balanced'`\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "DQSaI1vmbtc6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "team_att_and = pd.read_sql(query, conn)\n",
        "team_att_and"
      ],
      "metadata": {
        "trusted": true,
        "id": "YLRWuY90btc6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.3.2 Práctica: Traer el Id de los equipos que desarrollan un juego de pase largo o su velocidad de juego es rápida\n",
        "\n",
        "Tomando la tabla `Team_Attributes` de la base de datos de Fútbol Europeo, deben obtener el `id`,`buildUpPlayPassingClass` y `buildUpPlaySpeedClass`  de los equipos que tengan los siguientes atributos:\n",
        "* `buildUpPlayPassingClass = 'Long'`\n",
        "* `buildUpPlaySpeedClass = 'Fast'`\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "MikGXFoGbtc6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "team_att_or = pd.read_sql(query, conn)\n",
        "team_att_or"
      ],
      "metadata": {
        "trusted": true,
        "id": "4nIg9Xt_btc6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.3.3 Práctica: Traer la tabla de países sin Bélgica\n",
        "\n",
        "Deben filtrar la tabla de países y traerla sin `Belgium`. La tabla de resultados deberá contener los campos `id` y `name` de la tabla `Country` de la base de datos de Fútbol Europeo.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "qwRMHMOybtc7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "countries_not = pd.read_sql(query, conn)\n",
        "countries_not"
      ],
      "metadata": {
        "trusted": true,
        "id": "t2dOTLA7btc7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.4 Otros operadores que trabajan con la sentencia WHERE\n",
        "\n",
        "Los siguientes operadores también pueden ser utilizados en la sentencia WHERE:\n",
        "\n",
        "* | `=`       | Igual\n",
        "\n",
        "* | `>`       | Mayor que\n",
        "\n",
        "* | `<`       | Menor que\n",
        "\n",
        "* | `>=`      | Mayor o igual que  \n",
        "\n",
        "* | `<=`      | Menor o igual que\n",
        "\n",
        "* | `<>`      | No igual. *Nota: En algunas versiones de SQL este operador puede ser escrito como !=*\n",
        "\n",
        "* | `BETWEEN` | Entre cierto rango\n",
        "\n",
        "* | `LIKE`    | Para consultar los datos basados en información parcial, se utiliza el operador `LIKE` en la sentencia `WHERE`. Busca un patrón. El patrón va antes, después o entre un par de % (wildcard). Ejemplo: `LIKE 's%'` busca cualquier texto que comience con 's'. `LIKE '%er'` busca cualquier texto que termine con 'er'. Y `LIKE '%per%'` busca cualquier texto que contenga 'per'.\n",
        "\n",
        "* | `IN`      | Para especificar varios valores posibles para una columna o subquery. Ejemplo de sintaxis cuando se especifica varios valores:\n",
        "\n",
        "`SELECT column1\n",
        "FROM Table_name\n",
        "WHERE column1 IN (value1, value2);`.\n",
        "\n",
        "Ejemplo se sintaxis cuando se usa `IN` en una subquery:\n",
        "\n",
        "`SELECT TrackId, Name, AlbumId\n",
        "FROM Tracks\n",
        "WHERE AlbumId IN\n",
        "    (SELECT\tAlbumId\n",
        "    FROM Albums\n",
        "    WHERE ArtistId = 12);`."
      ],
      "metadata": {
        "id": "o39x0N3pbtc7"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.4.1 Práctica: Traer la tabla de países únicamente con Italia\n",
        "\n",
        "Deben filtrar la tabla de países y traer únicamente a `Italy`. La tabla de resultados deberá contener los campos `id` y `name` de la tabla `Country` de la base de datos de Fútbol Europeo.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo\n"
      ],
      "metadata": {
        "id": "HeA-nGnEbtc7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "countries_equal = pd.read_sql(query, conn)\n",
        "countries_equal"
      ],
      "metadata": {
        "trusted": true,
        "id": "qYM3K0WPbtc7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.4.2 Práctica: Traer la tabla de países sin Polonia\n",
        "\n",
        "Deben traer la tabla de países sin `Poland`. La tabla de resultados deberá contener los campos `id` y `name` de la tabla `Country` de la base de datos de Fútbol Europeo. **No se debe utilizar `NOT`**.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo\n",
        "\n"
      ],
      "metadata": {
        "id": "QAgPelJwbtc7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "countries_dif = pd.read_sql(query, conn)\n",
        "countries_dif"
      ],
      "metadata": {
        "trusted": true,
        "id": "eWbxq9uxbtc7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.4.3 Práctica: Listar los jugadores que pesen entre 174 y 176 libras\n",
        "\n",
        "Queremos listar a los jugadores que cumplan con la condición de pesar entre 174 y 176 libras. En la tabla de resultados quiero ver los campos `id`,`player_name`, `birthday`, `height` y `weight` de los que cumplen la condición. Todos se encuentran en la tabla `Player`. Asimismo, debemos limitar la consulta a 30 registros.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo\n"
      ],
      "metadata": {
        "id": "KTz2vdsbbtc7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "players_weight = pd.read_sql(query, conn)\n",
        "players_weight"
      ],
      "metadata": {
        "trusted": true,
        "id": "4TPkHG6Wbtc7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 6.4.4 Práctica: Listar a los Messi de la base de datos de jugadores\n",
        "\n",
        "Queremos listar a los jugadores que cumplan con la condición de tener la palabra 'Messi' en su nombre. En la tabla de resultados quiero ver los campos `id`,`player_name`, `birthday`, `height` y `weight` de los que cumplen la condición. Todos se encuentran en la tabla `Player`.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "Fq90kSQ1btc8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "players_messi = pd.read_sql(query, conn)\n",
        "players_messi"
      ],
      "metadata": {
        "trusted": true,
        "id": "ZcYLkb-cbtc8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Alias AS\n",
        "\n",
        "Los alias en SQL se utilizan para dar a una tabla o a una columna, un nombre temporal. Se utilizan a menudo para hacer más legibles los nombres de las columnas. Un alias sólo existe durante la duración de la consulta.\n",
        "\n",
        "Sintaxis de `AS`:\n",
        "\n",
        "`SELECT column_name AS alias_name`\n",
        "\n",
        "`FROM table_name AS other_alias;`\n"
      ],
      "metadata": {
        "id": "dmQewC1bbtc8"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 7. Sentencia ORDER BY\n",
        "\n",
        "La sentencia `ORDER BY` se utiliza para ordenar el conjunto de resultados en orden ascendente o descendente. Por defecto, los registros se ordenan en orden ascendente `ASC`. Para ordenar los registros en orden descendente, utilice la palabra `DESC`.\n",
        "\n",
        "Sintaxis de `ORDER BY`\n",
        "\n",
        "`SELECT column1, column2, ...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "`WHERE condition`\n",
        "\n",
        "`ORDER BY column2 ASC`|`DESC;`\n"
      ],
      "metadata": {
        "id": "ftGCyDmnbtc8"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 7.1.1 Ejemplo: Listar a los equipos y ordenarlos por nombre\n",
        "\n",
        "\n",
        "Queremos listar a los equipos de la tabla `Team` y ordenarlos ascedentemente por nombre `team_long_name`. Adicionalmente, vamos a limitar la lista a 25 registros.\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo\n"
      ],
      "metadata": {
        "_cell_guid": "2c791a87-1a24-49b8-9e8a-59d6383bdd56",
        "_uuid": "ca37991951b15e842a35ea168273110328e88e7f",
        "id": "gH9-pkRJbtc8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT id, team_long_name AS team_name\n",
        "        FROM Team\n",
        "        ORDER BY team_long_name ASC\n",
        "        LIMIT 250;\"\"\"\n",
        "teams_orderby = pd.read_sql(query, conn)\n",
        "teams_orderby"
      ],
      "metadata": {
        "_cell_guid": "f2850218-dbfa-432e-874d-af112f399137",
        "_uuid": "1ba6a54bf81ad1c0b1e8348cb2f7cc4365f5953a",
        "trusted": true,
        "id": "BO71hmDEbtc8",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "196b6baa-d986-402b-f5a8-3785f1ca2324"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "        id             team_name\n",
              "0    16848  1. FC Kaiserslautern\n",
              "1    15624            1. FC Köln\n",
              "2    16239        1. FC Nürnberg\n",
              "3    16243       1. FSV Mainz 05\n",
              "4    11817            AC Ajaccio\n",
              "..     ...                   ...\n",
              "245   6504           Southampton\n",
              "246  18079  SpVgg Greuther Fürth\n",
              "247  26559      Sparta Rotterdam\n",
              "248  35285           Sporting CP\n",
              "249     18    Sporting Charleroi\n",
              "\n",
              "[250 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-8f05dca0-aa80-435e-aa7d-317446aae456\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>team_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>16848</td>\n",
              "      <td>1. FC Kaiserslautern</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>15624</td>\n",
              "      <td>1. FC Köln</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>16239</td>\n",
              "      <td>1. FC Nürnberg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>16243</td>\n",
              "      <td>1. FSV Mainz 05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>11817</td>\n",
              "      <td>AC Ajaccio</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>245</th>\n",
              "      <td>6504</td>\n",
              "      <td>Southampton</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>246</th>\n",
              "      <td>18079</td>\n",
              "      <td>SpVgg Greuther Fürth</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>247</th>\n",
              "      <td>26559</td>\n",
              "      <td>Sparta Rotterdam</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>248</th>\n",
              "      <td>35285</td>\n",
              "      <td>Sporting CP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>249</th>\n",
              "      <td>18</td>\n",
              "      <td>Sporting Charleroi</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>250 rows × 2 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8f05dca0-aa80-435e-aa7d-317446aae456')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-8f05dca0-aa80-435e-aa7d-317446aae456 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-8f05dca0-aa80-435e-aa7d-317446aae456');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-dc386f61-5c5d-46b7-afa7-281c1d8584e6\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-dc386f61-5c5d-46b7-afa7-281c1d8584e6')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-dc386f61-5c5d-46b7-afa7-281c1d8584e6 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_92188c50-6b66-4525-961b-25259d3b2e55\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('teams_orderby')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_92188c50-6b66-4525-961b-25259d3b2e55 button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('teams_orderby');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "teams_orderby",
              "summary": "{\n  \"name\": \"teams_orderby\",\n  \"rows\": 250,\n  \"fields\": [\n    {\n      \"column\": \"id\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 15163,\n        \"min\": 1,\n        \"max\": 51606,\n        \"num_unique_values\": 250,\n        \"samples\": [\n          17,\n          49116,\n          26555\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"team_name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 248,\n        \"samples\": [\n          \"Bolton Wanderers\",\n          \"AC Bellinzona\",\n          \"P. Warszawa\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 68
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 7.1.2 Práctica: Listar la tabla de países en orden descendente\n",
        "\n",
        "Deben listar la tabla `Country` de forma descendente `DESC` por el campos `name`. La tabla de resultados deberá contener los campos `id` y `name`.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "XgqtAgd2btc8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "teams_orderdesc = pd.read_sql(query, conn)\n",
        "teams_orderdesc"
      ],
      "metadata": {
        "trusted": true,
        "id": "Ddhp8PlSbtc8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 7.1.3 Práctica: Listar el id de los 15 jugadores con menos peso\n",
        "\n",
        "Deben listar la tabla `Player` de forma ascendente `ASC` por el campo `weight`. La tabla de resultados deberá contener los campos `id`, `player_name` y `weight`.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "n73iA8xlbtc8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "player_weightasc = pd.read_sql(query, conn)\n",
        "player_weightasc"
      ],
      "metadata": {
        "trusted": true,
        "id": "W7dpjeMMbtc9"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 7.1.4 Práctica: Listar el id de los 10 jugadores con mayor estatura\n",
        "\n",
        "Deben listar la tabla `Player` de forma descendente `DESC` por el campos `height`. La tabla de resultados deberá contener los campos `id`, `player_name` y `height`.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "5j7w-5sgbtc9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "player_heightdesc = pd.read_sql(query, conn)\n",
        "player_heightdesc"
      ],
      "metadata": {
        "trusted": true,
        "id": "wXDaRis1btc9"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 7.1.5 Práctica: Listar el id de los 10 jugadores con mayor estatura y ordenarlos alfabéticamente pero de forma descendente\n",
        "\n",
        "Deben listar la tabla `Player` de forma descendente `DESC` por el campos `height` y `player_name`. La tabla de resultados deberá contener los campos `id`, `player_name` y `height`.\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "YkF0UGyCbtc9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "player_height_name = pd.read_sql(query, conn)\n",
        "player_height_name"
      ],
      "metadata": {
        "trusted": true,
        "id": "141gEHQVbtc9"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 8. Sentencia GROUP BY\n",
        "\n",
        "La sentencia `GROUP BY` agrupa las filas que tienen los mismos valores en filas de resumen, como \"encontrar el número de clientes en cada país\". Se utiliza sobre dimensiones y métricas para agrupar el conjunto de resultados en una o más columnas.\n",
        "\n",
        "Las dimensiones son todos aquellos valores que estamos describiendo en el `SELECT`. Y las métricas son que se utilizan a menudo con funciones agregativas (`COUNT`, `MAX`, `MIN`, `SUM`, `AVG`).\n",
        "\n",
        "Sintaxis de `GROUP BY`;\n",
        "\n",
        "`SELECT column1, column2, ...`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "`WHERE condition`\n",
        "\n",
        "`GROUP BY column1`\n",
        "\n",
        "`ORDER BY column2 ASC`|`DESC;`\n",
        "\n",
        "**Nota:** El uso de `WHERE` antes de `GROUP BY` hace que este se ejecute más rápido, ya que el motor de base de datos no se tomará el tiempo de agrupar cosas que no necesita.\n",
        "\n",
        "**Nota2:** Es muy importante utilizar las mismas dimensiones tanto en el `SELECT`, como en el `GROUP BY`. De lo contrario, la salida podría ser errónea."
      ],
      "metadata": {
        "id": "Ja3Kw_9sbtc9"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 8.1.1 Ejemplo: Queremos conocer cuántos equipos tienen cada tipo de velocidad de juego\n",
        "\n",
        "Queremos conocer cuántos equipos de la tabla `Team_Attributes` tienen cada tipo de velocidad juego. Es decir: cuántos tienen `buildUpPlaySpeedClass = 'Fast'`, `buildUpPlaySpeedClass = 'Balanced'` y cuántos `buildUpPlaySpeedClass = 'Slow'`\n",
        "\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "b2tDzqEJbtc9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT buildUpPlaySpeedClass, COUNT(id)\n",
        "        FROM Team_Attributes\n",
        "        GROUP BY buildUpPlaySpeedClass;\"\"\"\n",
        "teams_speed = pd.read_sql(query, conn)\n",
        "teams_speed"
      ],
      "metadata": {
        "trusted": true,
        "id": "sQCu9yIIbtc9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "outputId": "6151cf42-537c-4377-bff3-25b6170f6580"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  buildUpPlaySpeedClass  COUNT(id)\n",
              "0              Balanced       1184\n",
              "1                  Fast        172\n",
              "2                  Slow        102"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-52a72f1d-40e1-4cf5-880f-180a9817353c\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>buildUpPlaySpeedClass</th>\n",
              "      <th>COUNT(id)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Balanced</td>\n",
              "      <td>1184</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Fast</td>\n",
              "      <td>172</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Slow</td>\n",
              "      <td>102</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-52a72f1d-40e1-4cf5-880f-180a9817353c')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-52a72f1d-40e1-4cf5-880f-180a9817353c button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-52a72f1d-40e1-4cf5-880f-180a9817353c');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-f1b5cd92-4f2e-458a-9def-2886571399b6\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-f1b5cd92-4f2e-458a-9def-2886571399b6')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-f1b5cd92-4f2e-458a-9def-2886571399b6 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_0974d95a-4112-468b-a8ac-3946cf4bdffb\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('teams_speed')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_0974d95a-4112-468b-a8ac-3946cf4bdffb button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('teams_speed');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "teams_speed",
              "summary": "{\n  \"name\": \"teams_speed\",\n  \"rows\": 3,\n  \"fields\": [\n    {\n      \"column\": \"buildUpPlaySpeedClass\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          \"Balanced\",\n          \"Fast\",\n          \"Slow\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"COUNT(id)\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 605,\n        \"min\": 102,\n        \"max\": 1184,\n        \"num_unique_values\": 3,\n        \"samples\": [\n          1184,\n          172,\n          102\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 69
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 8.1.2 Práctica: Queremos conocer cuántos equipos tienen cada clase de juego defensivo\n",
        "\n",
        "Queremos conocer cuantos equipos de la tabla `Team_Attributes` que tienen cada clase de juego defensivo. Es decir: cuántos `defencePressureClass = 'Medium'`, `defencePressureClass = 'Deep'` y `defencePressureClass = 'High'`.\n",
        "\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "fi7Fk4Q5btc9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "teams_defence = pd.read_sql(query, conn)\n",
        "teams_defence"
      ],
      "metadata": {
        "trusted": true,
        "id": "RgMIRjefbtc-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 8.1.3 Práctica: Analítica básica de las temporadas\n",
        "\n",
        "Queremos realizar un sencillo análisis sobre la tabla `Match`. Se necesita obtener la siguiente información agrupada por  cada `season`:\n",
        "\n",
        "* Cuantas `stage` distintas tuvo y visualizarlo como `number_of_stages`\n",
        "* El promedio de los goles de equipos locales `home_team_goal` y visualizarlo como `avg_home_team_goals`\n",
        "* El promedio de los goles de equipos visitantes `away_team_goal` y visualizarlo como `avg_away_team_goals`\n",
        "* La suma total de goles realizados por los equipos locales y visitantes en la temporada, visualizandolo como `total_goals`.\n",
        "\n",
        "Asimismo, se requere ver la información de las temporadas más recientes primero.\n",
        "\n",
        "\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo\n"
      ],
      "metadata": {
        "id": "e-Wowfuebtc-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "stages_by_season = pd.read_sql(query, conn)\n",
        "stages_by_season"
      ],
      "metadata": {
        "trusted": true,
        "id": "VcQBmE2Tbtc-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 9. Sentencias JOIN\n",
        "\n",
        "`JOIN` se usa cuando se quiere conectar dos tablas entre sí. Funciona cuando tienes una clave común en cada una de ellas. Entender el concepto de Claves es crucial para conectar (unir) conjuntos de datos (tablas).\n",
        "\n",
        "*Recuerden: Una clave es un valor que podemos usar para referirnos a una fila o registro específico de una tabla.\n",
        "Puede consistir en un valor (celda) que generalmente es un `ID`, o de una combinación de valores que son únicos en la tabla (clave compuesta).*\n",
        "\n",
        "Sintaxis de `JOIN`;\n",
        "\n",
        "`SELECT first_table.column1, firts_table.column2, second_table.columnA, second_table.columnB ...`\n",
        "\n",
        "`FROM first_table (LEFT | RIGHT | FULL) JOIN second_table ON first_table.columnID = second_table.columnID;`\n",
        "\n",
        "\n",
        "## 9.1 Tipos de JOIN\n",
        "\n",
        "* `JOIN`: mantiene sólo los registros que coinciden con la condición (después del `ON`) en ambas tablas, y los registros de ambas tablas que no coincidan, no aparecerían en la tabla de resultado.\n",
        "* `LEFT JOIN`: devuelve todos los registros de la primera tabla (izquierda), y los registros coincidentes de la segunda tabla (derecha). El resultado es `NULL` del lado derecho, si no hay coincidencia.\n",
        "* `RIGHT JOIN`: devuelve todos los registros de la segunda tabla (derecha), y los registros coincidentes de la primera tabla (izquierda). El resultado es `NULL` del lado izquierdo, si no hay coincidencia.\n",
        "* `FULL JOIN`: devuelve todos los registros cuando hay una coincidencia en los registros de la primera tabla (izquierda) o segunda tabla (derecha). Nota: `FULL JOIN` puede potencialmente devolver conjuntos de resultados MUY grandes.\n",
        "    \n",
        " ![image.png](attachment:image.png)\n",
        "\n",
        "Cuando se quiere unir diferentes tablas, es necesario que pienses en lo siguiente:\n",
        "\n",
        "1. Decidir qué tipo de unión usar. Las más comunes son: `JOIN` y `LEFT JOIN`.\n",
        "2. Especificar el valor común que se utiliza para conectar las tablas (la clave).\n",
        "3. Asegurarse de que al menos uno de los valores tiene que ser una clave en su tabla.\n",
        "\n",
        "\n",
        "Y por último, ¡Paciencia! Usar los `JOIN` de forma incorrecta, es el error más común (y a veces peligroso) cuando se escriben consultas complicadas."
      ],
      "metadata": {
        "id": "jsG0TmQVbtc-"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 9.2.1 Ejemplo: Lista de ligas y su país\n",
        "\n",
        "Queremos conocer a qué país corresponde cada liga. Para hacerlo, vamos a necesitar la información de las tablas `Country`y `League`, uniendolas por su clave en común: `Country.id = League.country_id`.\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "GD25Tfjgbtc-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT L.id AS Id_League, L.name AS Nombre_League, C.name AS Pais\n",
        "        FROM League AS L\n",
        "        JOIN Country AS C ON C.id = L.country_id;\"\"\"\n",
        "\n",
        "country_leagues = pd.read_sql(query, conn)\n",
        "country_leagues"
      ],
      "metadata": {
        "trusted": true,
        "id": "sflmfcwGbtc-",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 394
        },
        "outputId": "b6402732-5a6d-46c4-c542-faa2932fdbe8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    Id_League             Nombre_League         Pais\n",
              "0           1    Belgium Jupiler League      Belgium\n",
              "1        1729    England Premier League      England\n",
              "2        4769            France Ligue 1       France\n",
              "3        7809     Germany 1. Bundesliga      Germany\n",
              "4       10257             Italy Serie A        Italy\n",
              "5       13274    Netherlands Eredivisie  Netherlands\n",
              "6       15722        Poland Ekstraklasa       Poland\n",
              "7       17642  Portugal Liga ZON Sagres     Portugal\n",
              "8       19694   Scotland Premier League     Scotland\n",
              "9       21518           Spain LIGA BBVA        Spain\n",
              "10      24558  Switzerland Super League  Switzerland"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-bd96565c-77c5-4497-b512-bef59eb971af\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Id_League</th>\n",
              "      <th>Nombre_League</th>\n",
              "      <th>Pais</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Belgium Jupiler League</td>\n",
              "      <td>Belgium</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1729</td>\n",
              "      <td>England Premier League</td>\n",
              "      <td>England</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>4769</td>\n",
              "      <td>France Ligue 1</td>\n",
              "      <td>France</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>7809</td>\n",
              "      <td>Germany 1. Bundesliga</td>\n",
              "      <td>Germany</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10257</td>\n",
              "      <td>Italy Serie A</td>\n",
              "      <td>Italy</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>13274</td>\n",
              "      <td>Netherlands Eredivisie</td>\n",
              "      <td>Netherlands</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>15722</td>\n",
              "      <td>Poland Ekstraklasa</td>\n",
              "      <td>Poland</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>17642</td>\n",
              "      <td>Portugal Liga ZON Sagres</td>\n",
              "      <td>Portugal</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>19694</td>\n",
              "      <td>Scotland Premier League</td>\n",
              "      <td>Scotland</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>21518</td>\n",
              "      <td>Spain LIGA BBVA</td>\n",
              "      <td>Spain</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>24558</td>\n",
              "      <td>Switzerland Super League</td>\n",
              "      <td>Switzerland</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bd96565c-77c5-4497-b512-bef59eb971af')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-bd96565c-77c5-4497-b512-bef59eb971af button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-bd96565c-77c5-4497-b512-bef59eb971af');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-d2db1671-1ca4-4ec5-9692-51c2e6662939\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-d2db1671-1ca4-4ec5-9692-51c2e6662939')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-d2db1671-1ca4-4ec5-9692-51c2e6662939 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_a26b1ab0-4dc3-4047-948c-9fb24d49c7cf\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('country_leagues')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_a26b1ab0-4dc3-4047-948c-9fb24d49c7cf button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('country_leagues');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "country_leagues",
              "summary": "{\n  \"name\": \"country_leagues\",\n  \"rows\": 11,\n  \"fields\": [\n    {\n      \"column\": \"Id_League\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 8215,\n        \"min\": 1,\n        \"max\": 24558,\n        \"num_unique_values\": 11,\n        \"samples\": [\n          13274,\n          1,\n          21518\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Nombre_League\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 11,\n        \"samples\": [\n          \"Netherlands Eredivisie\",\n          \"Belgium Jupiler League\",\n          \"Spain LIGA BBVA\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Pais\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 11,\n        \"samples\": [\n          \"Netherlands\",\n          \"Belgium\",\n          \"Spain\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 70
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 9.2.2 Práctica: Comparación de cantidad de partidos jugados en cierta temporada de España y Bélgica\n",
        "\n",
        "Queremos realizar una comparación de la cantidad de partidos jugados en `season = '2008/2009'` entre los países `'Spain'` y `'Belgium'`. Los datos necesarios se encuentran en las tablas `Match` y `Country`.\n",
        "Asimismo, la tabla de resultados debe visualizarse de la siguiente manera:\n",
        "* la columna con el nombre del país debe llamarse `country_name`\n",
        "* la columna con el conteo de partidos debe llamarse `count_matches`\n",
        "* debemos visualizar al país con más partidos jugados en primer lugar\n",
        "\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "Lp6Ciyp3btc-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "country_matches = pd.read_sql(query, conn)\n",
        "country_matches"
      ],
      "metadata": {
        "trusted": true,
        "id": "Qj-k8dZ7btc-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 9.2.3 Práctica: Lista detallada de juegos\n",
        "\n",
        "Queremos un detalle de 10 juegos realizados en el país `Spain`, ordenado de forma ascendente por `date`.\n",
        "\n",
        "Lo que nos interesa ver es:\n",
        "* el `id` del encuentro,\n",
        "* el `Country.name` bajo alias `country_name`,\n",
        "* el `League.name` bajo alias `league_name`,\n",
        "* `season`,\n",
        "* `stage`,\n",
        "* `date`,\n",
        "* el `team_long_name` del equipo local bajo alias `home_team`,\n",
        "* el `team_long_name` del equipo visitante bajo alias `away_team`,\n",
        "* `home_team_goal` y\n",
        "* `away_team_goal`\n",
        "\n",
        "Esta información se encuentra repartida entre las tablas: `Match`, `Country`, `League` y `Team`. Teniendo en cuenta que las claves compartidas entre tablas son estas:\n",
        "\n",
        "* `Country.id = Match.country_id`\n",
        "\n",
        "* `League.id = Match.league_id`\n",
        "\n",
        "* `Team.team_api_id = Match.home_team_api_id`\n",
        "\n",
        "\n",
        "Para ello deberán escribir la consulta SQL entre un par de 3 comillas dobles: `\"\"\"consulta SQL\"\"\"` después de la palabra `query =`\n",
        "\n",
        "Una vez que estén lo tengan listo, ejecuten (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "1ZKAi9OXbtc-"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Algunas pistas o consejos para el ejercicio:\n",
        "En el ejercicio queremos mostrar sólo las columnas que nos interesan, así que en lugar de `*` usamos los nombres exactos.\n",
        "Algunas de las columnas tienen el mismo nombre (Ejemplo: `Country.name,League.name`), por lo que las renombramos usando `AS`.\n",
        "\n",
        "Esta consulta tiene algunos `JOIN`. La razón es que la base de datos utilizada está diseñada en una estructura de estrella. Es decir, tiene una tabla (`Match`) con todo el \"rendimiento del encuentro\" y métricas, pero sólo IDs como claves foráneas. Mientras que toda la información descriptiva se encuentra almacenada en otras tablas como: `Country`, `League` y `Team`.\n",
        "\n",
        "Tengan en cuenta que se puede realizar 2 `JOIN` con la tabla `Team`. Esto es un truco, ya que usamos la misma tabla pero básicamente traemos dos copias diferentes de ella, renombrandola con `AS`. Ejemplo: `Team AS HomeTeam` y `Team AS AwayTeam`. La razón es que se necesitaba traer información de goles sobre dos valores diferentes (`home_team_api_id` y `away_team_api_id`), y si unimos a la misma tabla, significaría que son iguales entre sí.\n",
        "\n",
        "También podrías utilizar `LEFT JOIN` sobre la tabla `Team`. Depende de si prefieres mantener los datos de los juegos en la tabla de resultados, incluso si uno de los equipos no aparece en la tabla `Team`.\n",
        "\n",
        "`ORDER BY` se podría usar para definir el orden de la tabla de resultados.\n"
      ],
      "metadata": {
        "id": "d02SpA_Jbtc-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "detailed_matches = pd.read_sql(query, conn)\n",
        "detailed_matches\n"
      ],
      "metadata": {
        "trusted": true,
        "id": "A0BMCzvUbtc_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 10. Sentencia HAVING\n",
        "\n",
        "La sentencia `HAVING` fue añadida a SQL porque la palabra clave `WHERE` no podía ser usada con funciones agregativas. Entonces, `HAVING` es como el `WHERE` para las funciones agregativas.\n",
        "\n",
        "La sintaxis de `HAVING` es así:\n",
        "\n",
        "`SELECT column_name(s)`\n",
        "\n",
        "`FROM table_name`\n",
        "\n",
        "`WHERE condition`\n",
        "\n",
        "`GROUP BY column_name(s)`\n",
        "\n",
        "`HAVING condition`\n",
        "\n",
        "`ORDER BY column_name(s); `\n",
        "\n",
        "\n",
        "`HAVING` añade otra capa de filtrado a los datos, esta vez sobre el resultado de la tabla **después** de la agrupación. Muchas veces se utiliza para limpiar la salida."
      ],
      "metadata": {
        "id": "C6obH3b1btc_"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Función `ROUND()` para redondear\n",
        "\n",
        "En SQLite La función `ROUND()` se utiliza para redondear un número decimal dado a las posiciones decimales especificadas basándose en un núumero de decimales dado.\n",
        "\n",
        "La sintaxis, es la siguiente:\n",
        "`ROUND( number, decimal_value )`\n",
        "\n",
        "* number: Es el número decimal que necesitamos redondear.\n",
        "* decimal_value: Define el número de decimales a redondear.\n",
        "    * Si decimal_value es un número positivo significa que el número se redondea hasta cerca de su valor.\n",
        "    * En caso de que decimal_value se omita o sea negativo, se toma el valor por defecto que es cero (0) y el número se redondeará a cero (0) decimales, pero el número seguirá siendo un valor de punto flotante.\n",
        "\n",
        "\n",
        "## 10.1.1 Ejemplo: Análisis de goles por temporada en las ligas de España, Francia e Inglaterra.\n",
        "\n",
        "Queremos saber en qué temporada el promedio de goles de equipos locales estuvo entre 1.4 y 1.6, para las ligas de `Spain`, `France` y `England`.\n",
        "\n",
        "La tabla de resultados deberá mostrar:\n",
        "* La temporada `season`,\n",
        "* el `Country.name` bajo alias `country`,\n",
        "* el `League.name` bajo alias `league`,\n",
        "* el promedio de goles hechos por equipos locales, bajo el alias `avg_local_goals`, redondeando con 2 decimales,\n",
        "* el promedio de goles hechos por equipos visitantes, bajo el alias `avg_visitor_goals`, redondeando con 2 decimales,\n",
        "* el total de goles por temporada y liga, bajo el alias `total_goals`,\n",
        "\n",
        "Esta información se encuentra repartida entre las tablas: `Match`, `Country` y `League`. Teniendo en cuenta que las claves compartidas entre tablas son estas:\n",
        "\n",
        "* `Country.id = Match.country_id`\n",
        "\n",
        "* `League.id = Match.league_id`\n",
        "\n"
      ],
      "metadata": {
        "id": "mFvyr1Csbtc_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT season,\n",
        "            Country.name AS Country,\n",
        "            League.name AS League,\n",
        "            ROUND(AVG(Match.home_team_goal),2) AS avg_local_goals,\n",
        "            ROUND(AVG(Match.away_team_goal),2) AS avg_visitor_goals,\n",
        "            SUM(home_team_goal+away_team_goal) AS total_goals\n",
        "        FROM Match\n",
        "        JOIN Country ON Country.id = Match.country_id\n",
        "        JOIN League ON League.id = Match.league_id\n",
        "        WHERE Country.name IN ('Spain', 'France', 'England')\n",
        "        GROUP BY season, League\n",
        "        HAVING avg_local_goals BETWEEN 1.4 AND 1.6 AND avg_visitor_goals BETWEEN 1.2 AND 1.6\n",
        "        ORDER BY season DESC;\"\"\"\n",
        "\n",
        "goals_by_season = pd.read_sql(query, conn)\n",
        "goals_by_season"
      ],
      "metadata": {
        "trusted": true,
        "id": "Jl1yugGKbtc_",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 617
        },
        "outputId": "82380d0a-c674-41ab-cea6-9d5413c0c358"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "DatabaseError",
          "evalue": "Execution failed on sql 'SELECT season,\n            Country.name AS Country,\n            League.name AS League,\n            ROUND(AVG(Match.home_team_goal),2) AS avg_local_goals,\n            ROUND(AVG(Match.away_team_goal),2) AS avg_visitor_goals,\n            SUM(home_team_goal+away_team_goal) AS total_goals\n        FROM Match\n        JOIN Country ON Country.id = Match.country_id\n        JOIN League ON League.id = Match.league_id\n        WHERE Country.name IN ('Spain', 'France', 'England')\n        GROUP BY season, League\n        HAVING avg_local_goals BETWEEN 1.4 AND 1.6 AND avg_visitor_goals BETWEEN 1.2 AND 1.6\n        ORDER BY season DESC;': database disk image is malformed",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, sql, params)\u001b[0m\n\u001b[1;32m   2673\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2674\u001b[0;31m             \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2675\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mDatabaseError\u001b[0m: database disk image is malformed",
            "\nThe above exception was the direct cause of the following exception:\n",
            "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-25-c46684260a50>\u001b[0m in \u001b[0;36m<cell line: 15>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     13\u001b[0m         ORDER BY season DESC;\"\"\"\n\u001b[1;32m     14\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 15\u001b[0;31m \u001b[0mgoals_by_season\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     16\u001b[0m \u001b[0mgoals_by_season\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype)\u001b[0m\n\u001b[1;32m    704\u001b[0m     \u001b[0;32mwith\u001b[0m \u001b[0mpandasSQL_builder\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcon\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpandas_sql\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    705\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpandas_sql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mSQLiteDatabase\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 706\u001b[0;31m             return pandas_sql.read_query(\n\u001b[0m\u001b[1;32m    707\u001b[0m                 \u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    708\u001b[0m                 \u001b[0mindex_col\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindex_col\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)\u001b[0m\n\u001b[1;32m   2736\u001b[0m         \u001b[0mdtype_backend\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mDtypeBackend\u001b[0m \u001b[0;34m|\u001b[0m \u001b[0mLiteral\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"numpy\"\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"numpy\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2737\u001b[0m     ) -> DataFrame | Iterator[DataFrame]:\n\u001b[0;32m-> 2738\u001b[0;31m         \u001b[0mcursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2739\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mcol_desc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mcol_desc\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdescription\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2740\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, sql, params)\u001b[0m\n\u001b[1;32m   2684\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2685\u001b[0m             \u001b[0mex\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mDatabaseError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"Execution failed on sql '{sql}': {exc}\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2686\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mex\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2687\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2688\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mstaticmethod\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mDatabaseError\u001b[0m: Execution failed on sql 'SELECT season,\n            Country.name AS Country,\n            League.name AS League,\n            ROUND(AVG(Match.home_team_goal),2) AS avg_local_goals,\n            ROUND(AVG(Match.away_team_goal),2) AS avg_visitor_goals,\n            SUM(home_team_goal+away_team_goal) AS total_goals\n        FROM Match\n        JOIN Country ON Country.id = Match.country_id\n        JOIN League ON League.id = Match.league_id\n        WHERE Country.name IN ('Spain', 'France', 'England')\n        GROUP BY season, League\n        HAVING avg_local_goals BETWEEN 1.4 AND 1.6 AND avg_visitor_goals BETWEEN 1.2 AND 1.6\n        ORDER BY season DESC;': database disk image is malformed"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 10.1.2 Práctica: Nos basaremos en ejemplo \"Lista detallada de juegos\", pero eliminaremos la información del partido y fechas, para comparar las ligas por temporadas.\n",
        "\n",
        "\n",
        "Para las ligas de los paises `Spain`, `Germany`, `France`, `Italy` y `England` que tengan un promedio de goles por partido mayor a 2.5, queremos conocer:\n",
        "* El nombre del país del equipo como `Country`,\n",
        "* El nombre de la liga en la que juega como `League`,\n",
        "* La cantidad de partidos (stages) como `Stages`,\n",
        "* La temporada como `Season`,\n",
        "* La cantidad de equipos como `number_of_teams`,\n",
        "* El promedio de goles realizados por los equipos locales como `avg_local_goals`,redondeando con 2 decimales,\n",
        "* El promedio de goles realizados por los equipos visitantes como `avg_visitor_goals`,redondeando con 2 decimales,\n",
        "* El promedio de goles por partido como `avg_goals`,redondeando con 2 decimales,\n",
        "* El promedio de gol diferencia por partido como `avg_goal_dif`,redondeando con 2 decimales,\n",
        "* El total de goles realizados en la temporada  como `total_goals`\n",
        "\n",
        "Esta información se encuentra repartida entre las tablas: `Match`, `Country`, `League` y `Team`. Teniendo en cuenta que las claves compartidas entre tablas son estas:\n",
        "\n",
        "* `Country.id = Match.country_id`\n",
        "\n",
        "* `League.id = Match.league_id`\n",
        "\n",
        "* `Team.team_api_id = Match.home_team_api_id`\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo"
      ],
      "metadata": {
        "id": "tg4-V7qybtc_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "leages_by_season = pd.read_sql(query, conn)\n",
        "leages_by_season\n"
      ],
      "metadata": {
        "trusted": true,
        "id": "3pw2SyClbtc_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 10.1.3 Práctica: Análisis sobre los equipos que más goles realizaron jugando como locales\n",
        "\n",
        "\n",
        "Queremos un detalle de los equipos que jugando como locales en las ligas de `Spain`, `Germany` y `England`, hayan anotado más de 50 goles. Ordenar de forma descendente por temporada y por equipo con más goles realizados. Nos interesa conocer:\n",
        "\n",
        "* El nombre del país del equipo como `Country`\n",
        "* El nombre de la liga en la que juega como `League`\n",
        "* El nombre del equipo como `Team`\n",
        "* La cantidad de partidos (stages) como `Stages`\n",
        "* La temporada como `Season`\n",
        "* La suma de goles realizados en esa temporada, jugando como local bajo el alias `sum_goals`\n",
        "* El promedio de goles bajo alias `avg_goals`, redondeando con 2 decimales\n",
        "\n",
        "\n",
        "Esta información se encuentra repartida entre las tablas: `Match`, `Country`, `League` y `Team`. Teniendo en cuenta que las claves compartidas entre tablas son estas:\n",
        "\n",
        "* `Country.id = Match.country_id`\n",
        "\n",
        "* `League.id = Match.league_id`\n",
        "\n",
        "* `Team.team_api_id = Match.home_team_api_id`\n",
        "\n",
        "Ejecuta (haciendo clic en ejecutar o Shift+Enter) el códido de abajo\n"
      ],
      "metadata": {
        "id": "OpmwJUzRbtc_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Ingrese la la consulta SQL entre un par de 3 comillas dobles: \"\"\"consulta SQL\"\"\" después de la palabra query =\n",
        "query =\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "teams_by_season = pd.read_sql(query, conn)\n",
        "teams_by_season"
      ],
      "metadata": {
        "trusted": true,
        "id": "MBxAIdQ0btc_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Función `DATE()`\n",
        "`DATE()`: Esta función extrae la parte de fecha de una expresión tipo date o tipo date time y devuelve una cadena de fecha en este formato: AAAA-MM-DD.\n",
        "\n",
        "Su sintaxis es la siguiente:\n",
        "\n",
        "`SELECT DATE(Datetime_field)\n",
        "FROM table;`\n",
        "\n",
        "## 10.1.4 Práctica: Análisis del cambio en overall rating y potential de Cristiano Ronaldo por edad.\n",
        "\n",
        "Se requiere visualizar el cambio de en `overall_rating` y `potential` de `Cristiano Ronaldo` por edad.\n",
        "\n",
        "Entonces, la tabla de resultado deberá contener los siguientes campos: `player_name`, la edad nombrada como `Age`, `overall_rating`, `potential` y `date` como `RatingDate`.\n",
        "\n",
        "Las tablas involucradas son `Player_Attributes` y `Player`. Que tienen una relación a través del campo `player_api_id` con mismo nombre en ambas tablas.\n",
        "\n",
        "\n",
        "¡Éxitos!\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "9AxbhQYpbtc_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query =\n",
        "\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "ratingCristiano = pd.read_sql(query, conn)\n",
        "ratingCristiano\n"
      ],
      "metadata": {
        "trusted": true,
        "id": "x_5ONV1obtdA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 11. Orden de ejecución de las consultas\n",
        "\n",
        "Ahora que estamos familiarizados con la mayoría de las funcionalidades que se utilizan en una consulta, es muy importante entender el orden en que se ejecuta el código.\n",
        "\n",
        "Primero, el orden de cómo lo escribimos (recordatorio):\n",
        "* `SELECT`\n",
        "* `FROM`\n",
        "* `JOIN`\n",
        "* `WHERE`\n",
        "* `GROUP BY`\n",
        "* `HAVING`\n",
        "* `ORDER BY`\n",
        "* `LIMIT`\n",
        "\n",
        "Ahora, el orden real en que las cosas suceden. Primero, puedes pensar en esta parte como la creación de una nueva tabla temporal en la memoria:\n",
        "\n",
        "1. Definir qué tablas usar, y conectarlas (`FROM` + `JOIN`)\n",
        "2. Mantén sólo las filas que se aplican a las condiciones (`WHERE`)\n",
        "3. Agrupar los datos por el nivel requerido (si es necesario) (`GROUP BY`)\n",
        "4. Elige qué información quieres tener en la nueva tabla (`SELECT`). Puede tener sólo datos en bruto (si no hay agrupación), o una combinación de dimensiones (de la agrupación), y métricas.\n",
        "\n",
        "Ahora, tomamos eso para mostrarlo de la siguiente manera:\n",
        "\n",
        "5. Ordenar la salida de la nueva tabla (`ORDER BY`)\n",
        "6. Agregar más condiciones que filtrarían la nueva tabla creada (`HAVING`)\n",
        "7. Límite en número de filas (`LIMIT`)\n",
        "\n"
      ],
      "metadata": {
        "id": "8iE9yWA7btdA"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 12. Sentencia CASE WHEN en SELECT\n",
        "\n",
        "La sentencia `CASE WHEN` de SQLite evalúa una lista de condiciones y devuelve una expresión basada en el resultado de dicha evaluación.\n",
        "\n",
        "`CASE WHEN` es similar a la sentencia `IF-THEN-ELSE` de otros lenguajes de programación.\n",
        "\n",
        "Se puede utilizar la sentencia `CASE WHEN` junto a cualquier sentencia que acepte una validaciones. Por ejemplo, puede utilizar la expresión `CASE WHEN` en sentencias como `WHERE`, `ORDER BY`, `HAVING`, `SELECT` y también en `UPDATE` y `DELETE`.\n",
        "\n",
        "La sentencia `CASE` contiene una expresión opcional seguida de una o más cláusulas `WHEN [condition] THEN [expression]`, terminando con una cláusula opcional `ELSE` y la cláusula obligatoria `END`.\n",
        "\n",
        "Las sintaxis de `CASE` pueden ser de la siguiente manera:\n",
        "\n",
        "* Sintaxis de Simple `CASE`\n",
        "\n",
        "`CASE` case_expression\n",
        "`WHEN` when_expression_1 `THEN` result_1\n",
        "`WHEN` when_expression_2 `THEN` result_2\n",
        "[...]\n",
        "`ELSE` result_else\n",
        "`END`\n",
        "\n",
        "* Sintaxis de Condition `CASE`\n",
        "`CASE`\n",
        "`WHEN` bool_expression_1 `THEN` result_1\n",
        "`WHEN` bool_expression_2 `THEN` result_2\n",
        "[...]\n",
        "`ELSE` result_else\n",
        "`END`"
      ],
      "metadata": {
        "id": "2vPYhcgtbtdA"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 12.1 Ejemplo: Queremos una tabla que me indique los partidos jugados por el FC Barcelona, su rival, la fecha de juego y si perdió, ganó o empato.\n",
        "\n"
      ],
      "metadata": {
        "id": "TIXW43GUbtdA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query= \"\"\"SELECT DATE(Match.date) as MatchDate,\n",
        "                Home.team_long_name AS  HomeTeam,\n",
        "                home_team_goal AS Home_goals,\n",
        "                Visitor.team_long_name AS VisitorTeam,\n",
        "                away_team_goal Visitor_goals,\n",
        "                CASE\n",
        "                    WHEN Home.team_api_id = 8634 AND home_team_goal < away_team_goal THEN 'Barca lost :('\n",
        "                    WHEN Home.team_api_id = 8634 AND home_team_goal > away_team_goal THEN 'Barca won'\n",
        "                    WHEN Visitor.team_api_id = 8634 AND home_team_goal < away_team_goal THEN 'Barca won'\n",
        "                    WHEN Visitor.team_api_id = 8634 AND home_team_goal > away_team_goal THEN 'Barca lost :('\n",
        "                    ELSE 'Tie'\n",
        "                END AS Outcome\n",
        "        FROM Match\n",
        "        LEFT JOIN Team AS Home on Home.team_api_id = Match.home_team_api_id\n",
        "        LEFT JOIN Team AS Visitor on Visitor.team_api_id = Match.away_team_api_id\n",
        "        WHERE (Visitor.team_api_id = 8634 OR Home.team_api_id = 8634) AND season = \"2012/2013\"\n",
        "        Order by MatchDate DESC\n",
        "        LIMIT 15;\"\"\"\n",
        "\n",
        "Barca_results = pd.read_sql(query, conn)\n",
        "Barca_results"
      ],
      "metadata": {
        "trusted": true,
        "id": "fPPQX-uMbtdA",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 520
        },
        "outputId": "b16ed1ca-1409-45b5-e787-713944ea4e18"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     MatchDate                 HomeTeam  Home_goals  \\\n",
              "0   2013-06-01             FC Barcelona           4   \n",
              "1   2013-05-26             RCD Espanyol           0   \n",
              "2   2013-05-19             FC Barcelona           2   \n",
              "3   2013-05-12          Atlético Madrid           1   \n",
              "4   2013-05-05             FC Barcelona           4   \n",
              "5   2013-04-27  Athletic Club de Bilbao           2   \n",
              "6   2013-04-20             FC Barcelona           1   \n",
              "7   2013-04-14            Real Zaragoza           0   \n",
              "8   2013-04-06             FC Barcelona           5   \n",
              "9   2013-03-30         RC Celta de Vigo           2   \n",
              "10  2013-03-17             FC Barcelona           3   \n",
              "11  2013-03-09             FC Barcelona           2   \n",
              "12  2013-03-02           Real Madrid CF           2   \n",
              "13  2013-02-23             FC Barcelona           2   \n",
              "14  2013-02-16               Granada CF           1   \n",
              "\n",
              "                  VisitorTeam  Visitor_goals        Outcome  \n",
              "0                   Málaga CF              1      Barca won  \n",
              "1                FC Barcelona              2      Barca won  \n",
              "2             Real Valladolid              1      Barca won  \n",
              "3                FC Barcelona              2      Barca won  \n",
              "4         Real Betis Balompié              2      Barca won  \n",
              "5                FC Barcelona              2            Tie  \n",
              "6                  Levante UD              0      Barca won  \n",
              "7                FC Barcelona              3      Barca won  \n",
              "8                RCD Mallorca              0      Barca won  \n",
              "9                FC Barcelona              2            Tie  \n",
              "10             Rayo Vallecano              1      Barca won  \n",
              "11  RC Deportivo de La Coruña              0      Barca won  \n",
              "12               FC Barcelona              1  Barca lost :(  \n",
              "13                 Sevilla FC              1      Barca won  \n",
              "14               FC Barcelona              2      Barca won  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-614f52d7-8c1a-4a57-987a-3298008a2be5\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>MatchDate</th>\n",
              "      <th>HomeTeam</th>\n",
              "      <th>Home_goals</th>\n",
              "      <th>VisitorTeam</th>\n",
              "      <th>Visitor_goals</th>\n",
              "      <th>Outcome</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>4</td>\n",
              "      <td>Málaga CF</td>\n",
              "      <td>1</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2013-05-26</td>\n",
              "      <td>RCD Espanyol</td>\n",
              "      <td>0</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>2</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2013-05-19</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>2</td>\n",
              "      <td>Real Valladolid</td>\n",
              "      <td>1</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2013-05-12</td>\n",
              "      <td>Atlético Madrid</td>\n",
              "      <td>1</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>2</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2013-05-05</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>4</td>\n",
              "      <td>Real Betis Balompié</td>\n",
              "      <td>2</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2013-04-27</td>\n",
              "      <td>Athletic Club de Bilbao</td>\n",
              "      <td>2</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>2</td>\n",
              "      <td>Tie</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2013-04-20</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>1</td>\n",
              "      <td>Levante UD</td>\n",
              "      <td>0</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2013-04-14</td>\n",
              "      <td>Real Zaragoza</td>\n",
              "      <td>0</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>3</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2013-04-06</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>5</td>\n",
              "      <td>RCD Mallorca</td>\n",
              "      <td>0</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2013-03-30</td>\n",
              "      <td>RC Celta de Vigo</td>\n",
              "      <td>2</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>2</td>\n",
              "      <td>Tie</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>2013-03-17</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>3</td>\n",
              "      <td>Rayo Vallecano</td>\n",
              "      <td>1</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>2013-03-09</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>2</td>\n",
              "      <td>RC Deportivo de La Coruña</td>\n",
              "      <td>0</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>2013-03-02</td>\n",
              "      <td>Real Madrid CF</td>\n",
              "      <td>2</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>1</td>\n",
              "      <td>Barca lost :(</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>2013-02-23</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>2</td>\n",
              "      <td>Sevilla FC</td>\n",
              "      <td>1</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>2013-02-16</td>\n",
              "      <td>Granada CF</td>\n",
              "      <td>1</td>\n",
              "      <td>FC Barcelona</td>\n",
              "      <td>2</td>\n",
              "      <td>Barca won</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-614f52d7-8c1a-4a57-987a-3298008a2be5')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-614f52d7-8c1a-4a57-987a-3298008a2be5 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-614f52d7-8c1a-4a57-987a-3298008a2be5');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-e0e81576-3317-4398-bf83-0a97c5497bcf\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-e0e81576-3317-4398-bf83-0a97c5497bcf')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-e0e81576-3317-4398-bf83-0a97c5497bcf button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_18e0ce6d-ec47-4772-a04b-8797858d252b\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('Barca_results')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_18e0ce6d-ec47-4772-a04b-8797858d252b button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('Barca_results');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "Barca_results",
              "summary": "{\n  \"name\": \"Barca_results\",\n  \"rows\": 15,\n  \"fields\": [\n    {\n      \"column\": \"MatchDate\",\n      \"properties\": {\n        \"dtype\": \"object\",\n        \"num_unique_values\": 15,\n        \"samples\": [\n          \"2013-03-30\",\n          \"2013-03-09\",\n          \"2013-06-01\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"HomeTeam\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 8,\n        \"samples\": [\n          \"RCD Espanyol\",\n          \"RC Celta de Vigo\",\n          \"FC Barcelona\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Home_goals\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 1,\n        \"min\": 0,\n        \"max\": 5,\n        \"num_unique_values\": 6,\n        \"samples\": [\n          4,\n          0,\n          3\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"VisitorTeam\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 9,\n        \"samples\": [\n          \"RC Deportivo de La Coru\\u00f1a\",\n          \"FC Barcelona\",\n          \"RCD Mallorca\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Visitor_goals\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0,\n        \"min\": 0,\n        \"max\": 3,\n        \"num_unique_values\": 4,\n        \"samples\": [\n          2,\n          3,\n          1\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Outcome\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          \"Barca won\",\n          \"Tie\",\n          \"Barca lost :(\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 71
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 12.2 Práctica: Queremos una tabla que me indique los partidos jugados por el FC Barcelona como local contra el Real Madrid CF, la fecha de juego y si perdió, ganó o empató.\n",
        "\n",
        "La información que necesitamos está repartida entre las tablas Match y Team, cuya relación es:\n",
        "`Team.team_api_id = Match.home_team_api_id`\n",
        "\n",
        "Queremos ver a la fecha del partido como `MatchDate`, el nombre del equipo local como `HomeTeam` y del visitante como `VisitorTeam`. También queremos ver los goles y el resultado bajo alias `Outcome`.\n",
        "\n",
        "Datos necesarios:\n",
        "* FC Barcelona: `team_api_id = 8634`\n",
        "* Real Madrid CF: `team_api_id = 8633`\n",
        "\n",
        "¡Éxitos!"
      ],
      "metadata": {
        "id": "99XXwGGHbtdA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query=\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "BarcaMadrid_results = pd.read_sql(query, conn)\n",
        "BarcaMadrid_results"
      ],
      "metadata": {
        "trusted": true,
        "id": "f4driLuQbtdA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 13. SUB QUERIES\n",
        "\n",
        "El uso de subconsultas es una herramienta esencial en SQL, ya que permite manipular los datos de formas muy avanzadas sin necesidad de ningún script externo, y es especialmente importante cuando tus tablas están estructuradas de tal forma que no se pueden unir directamente.\n",
        "\n",
        "## 13.1 Ejemplo: Rating de Cristiano Ronaldo y Lionel Messi versus los demás jugadores.\n",
        "\n",
        "Se requiere comparar el promedio de `overall_rating` y `potential` de `Cristiano Ronaldo` y `Lionel Messi` contra el promedio de los demás jugadores de la base de datos.\n",
        "\n",
        "Entonces, la tabla de resultado deberá contener los siguientes campos: `player_name`, el promedio redondeado con 2 decimales de `overall_rating` y el promedio redondeado con 2 decimales de `potential`. Mostrando a los demás jugadores como \"Others\".\n",
        "\n",
        "Las tablas involucradas son `Player_Attributes` y `Player`. Que tienen una relación en el campo `player_api_id` que tiene ese nombre en ambas tablas.\n",
        "\n",
        "\n",
        "¡Éxitos!"
      ],
      "metadata": {
        "id": "asXOD4R7btdA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT CASE\n",
        "                   WHEN player_name = 'Cristiano Ronaldo' THEN 'Cristiano Ronaldo'\n",
        "                   WHEN player_name = 'Lionel Messi' THEN 'Lionel Messi'\n",
        "                   ELSE 'Others'\n",
        "                   END AS Player,\n",
        "           PA_Grouped.avg_overall_rating AS avg_overall_rating,\n",
        "           PA_Grouped.avg_potential AS avg_potential\n",
        "           FROM Player\n",
        "           LEFT JOIN\n",
        "                    (SELECT Player_Attributes.player_api_id,\n",
        "                         ROUND(avg(Player_Attributes.overall_rating),2) AS avg_overall_rating,\n",
        "                         ROUND(avg(Player_Attributes.potential),2) AS avg_potential\n",
        "                     FROM Player_Attributes\n",
        "                     GROUP BY Player_Attributes.player_api_id)\n",
        "                    AS PA_Grouped\n",
        "           ON Player.player_api_id = PA_Grouped.player_api_id\n",
        "           GROUP BY Player\n",
        "           ORDER BY Player;\"\"\"\n",
        "\n",
        "players_rating = pd.read_sql(query, conn)\n",
        "players_rating"
      ],
      "metadata": {
        "trusted": true,
        "id": "CQm1Aw_2btdA",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "outputId": "28da62e6-a705-4fb4-9944-7bea61a91e30"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "              Player  avg_overall_rating  avg_potential\n",
              "0  Cristiano Ronaldo               91.28          93.48\n",
              "1       Lionel Messi               92.19          95.23\n",
              "2             Others               63.60          67.60"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-5cc6ed91-f18f-4e46-b21c-9e647c787342\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Player</th>\n",
              "      <th>avg_overall_rating</th>\n",
              "      <th>avg_potential</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Cristiano Ronaldo</td>\n",
              "      <td>91.28</td>\n",
              "      <td>93.48</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Lionel Messi</td>\n",
              "      <td>92.19</td>\n",
              "      <td>95.23</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Others</td>\n",
              "      <td>63.60</td>\n",
              "      <td>67.60</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5cc6ed91-f18f-4e46-b21c-9e647c787342')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-5cc6ed91-f18f-4e46-b21c-9e647c787342 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-5cc6ed91-f18f-4e46-b21c-9e647c787342');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-88406a75-74e0-43ab-be6b-af3cf6dd2955\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-88406a75-74e0-43ab-be6b-af3cf6dd2955')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-88406a75-74e0-43ab-be6b-af3cf6dd2955 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_4142124f-4c47-420b-b900-2ae569aedebf\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('players_rating')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_4142124f-4c47-420b-b900-2ae569aedebf button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('players_rating');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "players_rating",
              "summary": "{\n  \"name\": \"players_rating\",\n  \"rows\": 3,\n  \"fields\": [\n    {\n      \"column\": \"Player\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          \"Cristiano Ronaldo\",\n          \"Lionel Messi\",\n          \"Others\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"avg_overall_rating\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 16.250121025190346,\n        \"min\": 63.6,\n        \"max\": 92.19,\n        \"num_unique_values\": 3,\n        \"samples\": [\n          91.28,\n          92.19,\n          63.6\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"avg_potential\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 15.471768914165358,\n        \"min\": 67.6,\n        \"max\": 95.23,\n        \"num_unique_values\": 3,\n        \"samples\": [\n          93.48,\n          95.23,\n          67.6\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 13.2 Práctica: Comparación de tipo de juego entre el FC Barcelona y Real Madrid FC - SUB QUERY\n",
        "\n",
        "Como práctica del uso de `CASE WHEN`, se requiere simular la asignación de clase de juego de acuerdo a los siguientes ratings.\n",
        "Para el rating buildUpPlaySpeed que debe ser mostrado como PlaySpeed, se considera lo siguiente:\n",
        "\n",
        "* `buildUpPlaySpeed >= 70` como `Fast`\n",
        "* `buildUpPlaySpeed <= 30` como `Slow`\n",
        "* Sino como `Balanced`\n",
        "\n",
        "Para el rating buildUpPlayPassing que debe ser mostrado como PlayPassing, se considera lo siguiente:\n",
        "\n",
        "* `buildUpPlayPassing >= 70` como `Long`\n",
        "* `buildUpPlayPassing <= 30` como `Short`\n",
        "* Sino como `Mixed`\n",
        "\n",
        "Para el rating chanceCreationShooting que debe ser mostrado como CreationShooting, se considera lo siguiente:\n",
        "* `chanceCreationShooting >= 68` como `Lots`\n",
        "* `chanceCreationShooting <= 30` como `Little`\n",
        "* Sino como `Normal`\n",
        "\n",
        "Las tablas necesarias son: `Team_Attributes` y `Team`, cuya relación se da en `Team_Attributes.team_api_id = Team.team_api_id`.\n",
        "\n",
        "La tabla de resultados debe mostrar la fecha de rating como `RatingDate`, `team_api_id` como `TeamID` y `team_long_name` como `Team`. Además, debe estar agrupada por fecha de rating y equipo.\n",
        "\n",
        "¡Éxitos!\n"
      ],
      "metadata": {
        "id": "IzwOokrNbtdB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query=\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "subquery = pd.read_sql(query, conn)\n",
        "subquery"
      ],
      "metadata": {
        "trusted": true,
        "id": "u7qW_GCHbtdB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 13.3 Práctica: Comparación de tipo de juego entre el FC Barcelona y Real Madrid FC - WHERE\n",
        "\n",
        "En esta ocasión se requiere lo mismo que el ejericio anterior, pero mediante el uso de WHERE."
      ],
      "metadata": {
        "id": "d0lGdRihbtdB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query=\n",
        "\n",
        "\n",
        "#No modificar lo siguiente:\n",
        "\n",
        "subquery2 = pd.read_sql(query, conn)\n",
        "subquery2"
      ],
      "metadata": {
        "trusted": true,
        "id": "hZxXj0PKbtdB"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}