sqlcompletosubir.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to MySQL database... MySQL Server version on  8.0.37\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector # !pip install mysql-connector-python \n",
    "from mysql.connector import Error\n",
    "\n",
    "try: \n",
    "    connection = mysql.connector.connect(host='', \n",
    "                                         database='',\n",
    "                                         user='',\n",
    "                                         password='', connection_timeout=180)\n",
    "\n",
    "    if connection.is_connected():\n",
    "        db_Info = connection.get_server_info()\n",
    "        print(\"Connected to MySQL database... MySQL Server version on \", db_Info)\n",
    "\n",
    "        cursor = connection.cursor()\n",
    "        connection.commit()\n",
    "\n",
    "except Error as e:\n",
    "    print(\"Error while connecting to MySQL\", e)\n",
    "finally:\n",
    "    # closing database connection.\n",
    "    if connection.is_connected():\n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL connection is closed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ejemplo query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query1 = \"\"\" SELECT * FROM PRODUCTO; \"\"\"\n",
    "cursor.execute(query1)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_sql(query1, connection)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.to_csv('producto.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Esquema de info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None, 'display.max_rows', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "\n",
    "esquema = pd.read_sql(\"SELECT * FROM INFORMATION_SCHEMA.Tables\", connection)\n",
    "esquema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 2. Obtener los nombres de todos los empleados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('María Luisa Galdón Ter',),\n",
       " ('Úrsula Delta Camacho',),\n",
       " ('Luis Ramírez Pardo',),\n",
       " ('Sergio Lérida Campos',),\n",
       " ('Piedad Colmenero Zapillo',),\n",
       " ('María Sánchez Cid',),\n",
       " ('Martín Guerrero López',),\n",
       " ('Carmen Hernández Pío',),\n",
       " ('Pedro Jiménez Ruiz',),\n",
       " ('Soledad Campillo Molina',),\n",
       " ('Raúl Rodrigo Roca',)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query2 = \"\"\" SELECT Nombre FROM empleado; \"\"\"\n",
    "cursor.execute(query2)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_sql(query2, connection)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv('query2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 3. Contar el número de  realizados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(11,)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query3 = \"\"\" SELECT COUNT(*) AS 'Numero ' FROM pedido; \"\"\"\n",
    "cursor.execute(query3)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.read_sql(query3, connection)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_csv('query3.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 4. Listar los repartidores que trabajan en el turno de tarde."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query4 = \"\"\" SELECT Nombre FROM repartidor WHERE Turno = 'Tarde'; \"\"\"\n",
    "cursor.execute(query4)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = pd.read_sql(query4, connection)\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4.to_csv('query4.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 5. Encontrar el precio más alto de los productos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query5 = \"\"\" SELECT MAX(Precio) FROM producto; \"\"\"\n",
    "cursor.execute(query5)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5 = pd.read_sql(query5, connection)\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5.to_csv('CSVS\\query5.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 6. Mostrar todos los  con un importe mayor a 100.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query6 = \"\"\" SELECT * FROM pedido WHERE Importe > 100.00; \"\"\"\n",
    "cursor.execute(query6)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df6 = pd.read_sql(query6, connection)\n",
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "df6.to_csv('CSVS\\query6.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 7. Obtener el salario promedio de los empleados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1027.272727,)]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query7 = \"\"\" SELECT AVG(Salario) FROM empleado; \"\"\"\n",
    "cursor.execute(query7)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df7 = pd.read_sql(query7, connection)\n",
    "df7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.to_csv('CSVS\\query7.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 8. Listar los productos que tienen un nombre único (sin duplicados)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query8 = \"\"\" SELECT DISTINCT(Nombre) FROM producto; \"\"\"\n",
    "cursor.execute(query8)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8 = pd.read_sql(query8, connection)\n",
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8.to_csv('CSVS\\query8.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 9. Mostrar el número y la fecha de todos los  realizados en el último mes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query9 = \"\"\" SELECT Numero, Fecha FROM pedido WHERE Fecha >= '2020-11-01'; \"\"\"\n",
    "cursor.execute(query9)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df9 = pd.read_sql(query9, connection)\n",
    "df9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "df9.to_csv('CSVS\\query9.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 10. Encontrar el empleado con el salario más bajo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query10 = \"\"\" SELECT Nombre, salario FROM empleado WHERE salario =(SELECT MIN(Salario) FROM empleado); \"\"\"\n",
    "cursor.execute(query10)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df10 = pd.read_sql(query10, connection)\n",
    "df10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "df10.to_csv('CSVS\\query10.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 11. Listar los productos y sus componentes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query11 = \"\"\" SELECT p.nombre, c.Codigo_P_compuesto AS Componente FROM producto p LEFT JOIN esta_compuesto c ON p.Codigo = c.Codigo_P; \"\"\"\n",
    "cursor.execute(query11)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df11 = pd.read_sql(query11, connection)\n",
    "df11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "df11.to_csv('CSVS\\query11.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 12. Obtener el importe total de todos los ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query12 = \"\"\" SELECT SUM(Importe) FROM pedido; \"\"\"\n",
    "cursor.execute(query12)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df12 = pd.read_sql(query12, connection)\n",
    "df12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df12.to_csv('CSVS\\query12.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 13. Mostrar los empleados que no son repartidores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query13 = \"\"\" SELECT Nombre FROM empleado; \"\"\"\n",
    "cursor.execute(query13)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df13 = pd.read_sql(query13, connection)\n",
    "df13"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df13.to_csv('CSVS\\query13.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 14. Listar los  junto con el nombre del empleado que tomó el pedido."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query14 = \"\"\" SELECT p.Numero, e.Nombre FROM pedido p, empleado e WHERE p.DNI_ETM = e.DNI; \"\"\"\n",
    "cursor.execute(query14)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df14 = pd.read_sql(query14, connection)\n",
    "df14"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "df14.to_csv('CSVS\\query14.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 15. Encontrar la cantidad total de productos en cada pedido."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query15 = \"\"\" SELECT c.cantidad, pr.Nombre, p.Numero FROM consta c, producto pr, pedido p WHERE c.Numero_P = p.Numero AND c.Codigo_Pr = pr.Codigo; \"\"\"\n",
    "cursor.execute(query15)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df15 = pd.read_sql(query15, connection)\n",
    "df15"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "df15.to_csv('CSVS\\query15.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 16. Obtener el nombre del repartidor y el importe de los  que ha entregado."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query16 = \"\"\" SELECT Nombre, Importe FROM repartidor r, pedido p WHERE r.DNI = p.DNI_R; \"\"\"\n",
    "cursor.execute(query16)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df16 = pd.read_sql(query16, connection)\n",
    "df16"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df16.to_csv('CSVS\\query16.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 17. Listar todos los  junto con la hora en que fueron tomados y preparados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query17 = \"\"\" SELECT Numero, Hora_tm, Hora_pre FROM pedido; \"\"\"\n",
    "cursor.execute(query17)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df17 = pd.read_sql(query17, connection)\n",
    "df17"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "df17.to_csv('CSVS\\query17.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 18. Mostrar el total de incentivos pagados a los repartidores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query18 = \"\"\" SELECT SUM(Incentivo) FROM repartidor; \"\"\"\n",
    "cursor.execute(query18)\n",
    "cursor.fetchall()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df18 = pd.read_sql(query18, connection)\n",
    "df18"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "df18.to_csv('CSVS\\query18.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 19. Encontrar los empleados que han tomado más de 5 . "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query19 = \"\"\" SELECT e.Nombre, COUNT(p.DNI_ETM) FROM empleado e, pedido p WHERE e.DNI = p.DNI_ETM GROUP BY e.Nombre HAVING COUNT(p.DNI_ETM) > 5; \"\"\"\n",
    "cursor.execute(query19)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df19 = pd.read_sql(query19, connection)\n",
    "df19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df19.to_csv('CSVS\\query19.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 20. Listar todos los  junto con el nombre del empleado que los preparó."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query20 = \"\"\" SELECT Numero, Nombre FROM pedido p, empleado e WHERE p.DNI_EP = e.DNI; \"\"\"\n",
    "cursor.execute(query20)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df20 = pd.read_sql(query20, connection)\n",
    "df20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "df20.to_csv('CSVS\\query20.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 21. Mostrar los productos cuyo precio es mayor que el precio promedio de todos los productos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query21 = \"\"\" SELECT Nombre, Precio FROM producto WHERE Precio >(SELECT AVG(Precio) FROM producto); \"\"\"\n",
    "cursor.execute(query21)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df21 = pd.read_sql(query21, connection)\n",
    "df21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "df21.to_csv('CSVS\\query21.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 22. Listar los empleados y repartidores con el mismo turno."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query22 = \"\"\" SELECT e.Nombre, r.Nombre, e.Turno, r.Turno FROM empleado e, repartidor r WHERE e.Turno = r.Turno; \"\"\"\n",
    "cursor.execute(query22)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df22 = pd.read_sql(query22, connection)\n",
    "df22"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "df22.to_csv('CSVS\\query22.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 23. Obtener los  junto con el nombre del empleado que los tomó y el nombre del repartidor que los entregó."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query23 = \"\"\" SELECT p.Numero, e.Nombre AS Empleado, r.Nombre AS Repartidor FROM pedido p, empleado e, repartidor r WHERE p.DNI_R = r.DNI AND p.DNI_ETM = e.DNI; \"\"\"\n",
    "cursor.execute(query23)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df23 = pd.read_sql(query23, connection)\n",
    "df23"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "df23.to_csv('CSVS\\query23.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 24. Mostrar los nombres de los productos en mayúsculas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query24 = \"\"\" SELECT UPPER(Nombre) FROM producto; \"\"\"\n",
    "cursor.execute(query24)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df24 = pd.read_sql(query24, connection)\n",
    "df24"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "df24.to_csv('CSVS\\query24.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 25. Obtener el número de  y el total del importe para cada repartidor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query25 = \"\"\" SELECT r.Nombre, COUNT(p.Numero) AS 'Nº ', SUM(p.Importe) AS 'Importe total' FROM repartidor r, pedido p WHERE r.DNI = p.DNI_R group by r.Nombre; \"\"\"\n",
    "cursor.execute(query25)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df25 = pd.read_sql(query25, connection)\n",
    "df25"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "df25.to_csv('CSVS\\query25.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 26. Mostrar los  realizados en el año 2023."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query26 = \"\"\" SELECT * FROM pedido WHERE YEAR(Fecha) = 2023; \"\"\"\n",
    "cursor.execute(query26)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df26 = pd.read_sql(query26, connection)\n",
    "df26"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "df26.to_csv('CSVS\\query26.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 27. Listar los repartidores que han entregado  en la última semana. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query27 = \"\"\" SELECT r.Nombre FROM repartidor r, pedido p WHERE r.DNI = p.DNI_R AND p.Fecha >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK); \"\"\"\n",
    "cursor.execute(query27)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df27 = pd.read_sql(query27, connection)\n",
    "df27"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df27.to_csv('CSVS\\query27.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 28. Obtener la diferencia en días entre la fecha del pedido y la fecha actual para todos los . "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query28 = \"\"\" SELECT Numero, DATEDIFF(CURDATE(), Fecha) AS 'Dias de diferencia' FROM PEDIDO; \"\"\"\n",
    "cursor.execute(query28)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df28 = pd.read_sql(query28, connection)\n",
    "df28"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df28.to_csv('CSVS\\query28.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 29. Listar los empleados que trabajan en el turno de mañana y tienen un salario mayor a 2000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query29 = \"\"\" SELECT * FROM empleado WHERE Turno = 'mañana' AND Salario > 2000; \"\"\"\n",
    "cursor.execute(query29)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df29 = pd.read_sql(query29, connection)\n",
    "df29"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "df29.to_csv('CSVS\\query29.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 30. Mostrar los productos y su precio con un incremento del 10%. TENER EN CUENTA AVISO EN SQL!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query30 = \"\"\" SELECT Nombre, Precio + (Precio * 0.1) FROM producto ; \"\"\"\n",
    "cursor.execute(query30)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df30 = pd.read_sql(query30, connection)\n",
    "df30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "df30.to_csv('CSVS\\query30.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 31. Obtener los  cuyo importe es el mayor entre todos los ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query31 = \"\"\" SELECT * FROM pedido WHERE Importe = (SELECT MAX(Importe) FROM pedido); \"\"\"\n",
    "cursor.execute(query31)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df31 = pd.read_sql(query31, connection)\n",
    "df31"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "df31.to_csv('CSVS\\query31.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 32. Listar los empleados que no han tomado ningún pedido."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query32 = \"\"\" SELECT e.Nombre, e.DNI FROM empleado e LEFT JOIN pedido p ON e.DNI = p.DNI_ETM WHERE p.DNI_ETM IS NULL; \"\"\"\n",
    "cursor.execute(query32)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df32 = pd.read_sql(query32, connection)\n",
    "df32"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "df32.to_csv('CSVS\\query32.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 33. Mostrar el nombre del repartidor y la fecha del pedido para los  entregados en el turno de noche."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query33 = \"\"\" SELECT r.Nombre, p.Fecha FROM repartidor r, pedido p WHERE r.DNI = p.DNI_R AND r.Turno = 'noche'; \"\"\"\n",
    "cursor.execute(query33)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df33 = pd.read_sql(query33, connection)\n",
    "df33"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "df33.to_csv('CSVS\\query33.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 34. Obtener el nombre del producto más caro y su precio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query34 = \"\"\" SELECT Nombre, Precio FROM producto WHERE Precio = (SELECT MAX(Precio) FROM producto); \"\"\"\n",
    "cursor.execute(query34)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df34 = pd.read_sql(query34, connection)\n",
    "df34"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "df34.to_csv('CSVS\\query34.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- 35. Mostrar el nombre del empleado y el importe total de los  que ha tomado.ERROR!!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = mysql.connector.connect(host='', database='', user='', password='', connection_timeout=180000)\n",
    "cursor=connection.cursor()\n",
    "query35 = \"\"\" SELECT e.Nombre, p.importe FROM empleado e, pedido p WHERE p.DNI_ETM = e.DNI and p.importe = (SELECT SUM(importe) FROM pedido);  \"\"\"\n",
    "cursor.execute(query35)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df35 = pd.read_sql(query35, connection)\n",
    "df35"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "df35.to_csv('CSVS\\query35.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}